MySQL Business

1. Business profile

(1) stay MySQL Only use Innodb Database or table of database engine To support the transaction .
(2) Transactions can be used to maintain database integrity , Make sure that there are lots of SQL Statement or all execution , Or none at all .
(3) Affairs are used to manage insert,update,delete sentence .

2. Four characteristics of business

Generally speaking , Transactions must be satisfied 4 Conditions （ACID）：

（1） Atomicity （Atomicity）

A business （transaction） All operations in , Or it's all done , Or not at all , It doesn't end in the middle . An error occurred during the execution of the transaction , Will be rolled back （Rollback） Status to the beginning of the transaction , It's like this transaction has never been executed .

（2） Uniformity （Consistency）

Before and after transaction start , The integrity of the database is not compromised . This means that the data written must fully comply with all the default rules , This includes the accuracy of the data 、 Serialization and subsequent databases can spontaneously accomplish the scheduled work .( such as ：A towards B Transfer accounts , impossible A Buckle the money ,B But I didn't get it )

（3） Isolation, （Isolation）

database The ability to allow multiple concurrent transactions to read, write, and modify their data at the same time , Isolation can prevent multiple transactions from executing concurrently due to Data inconsistency caused by cross execution . Transaction isolation is divided into different levels , Include read uncommitted （Read uncommitted）、 Read the submission （read committed）、 Repeatable （repeatable read） And serialization （Serializable）.（ Than u people ：A I'm withdrawing money from a bank card , stay A In the process of withdrawing money ,B You can't charge this bank card ）

（4） persistence （Durability）

After transaction ends , Changes to data are permanent , Even if the system fails, it will not be lost .

3. Transaction submission 、 Roll back

-- UNSIGNED Represents an unsigned number , It can't be negative  
create table user(
	id int primary key auto_increment,
	name VARCHAR(20),
	balance DECIMAL(10,2) UNSIGNED
);

insert into user VALUES (1,' Xiao Luo ',200);
insert into user VALUES (2,' Xiao Luo's wife ',50000);

--  Transfer business , Must all succeed , Or they all failed , So you can't do it sentence by sentence , In case it's half done , What if the power is cut off 
--  So program as a whole 
--  They all succeeded 
-- begin;
start transaction;    
UPDATE user set balance = balance - 200 where id = 1;
UPDATE user set balance = balance + 200 where id = 2;
commit;

--  All failed 
start transaction;
UPDATE user set balance = balance - 200 where id = 1;
UPDATE user set balance = balance + 200 where id = 2;
rollback;

3.1、 The principle of implementation is briefly introduced

mysql A log is recorded for each statement executed ,

1、start transaction, Make a log first , Really implement .

2、UPDATE user set balance = balance - 200 where id = 1, Make a log first , True execution .

  2.1 If the power is cut off at this time , Of course, it can't continue , After a while, I called , start-up mysql Will check the log , It is found that a transaction has not been completed , No, commit, The reverse operation will be installed and rolled back .

3、UPDATE user set balance = balance + 200 where id = 2, Make a log first , True execution .

4、 Such as commit, Make a record , perform , It's over , The log can be deleted . If rollback, It will reverse the operation according to the log , Roll back .

4. Transaction features – Isolation,

Isolation emphasizes the simultaneous occurrence of two or more （ Concurrent ） Our business operates a database at the same time , In order to make both transactions visible on the one hand 、 Get the right results , On the one hand, we should also ensure different isolation levels due to certain efficiency .

4.1 Isolation has isolation level (4 individual )

（1） Read uncommitted ：read uncommitted
（2） Read submitted ：read committed
（3） Repeatable ：repeatable read
（4） Serialization ：serializable

	Dirty reading	It can't be read repeatedly	Fantasy reading
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

View and set the isolation level of transactions ：

SELECT @@global.tx_isolation, @@tx_isolation;

set session transaction isolation level repeatable read;

SET  transaction isolation level read uncommitted;
SET  transaction isolation level read committed;
set  transaction isolation level repeatable read;
SET  transaction isolation level serializable;

SET GLOBAL transaction isolation level read uncommitted;
SET GLOBAL transaction isolation level read committed;
set GLOBAL transaction isolation level repeatable read;
SET GLOBAL transaction isolation level serializable;

 among ,SESSION  and  GLOBAL  Keyword is used to specify the scope of the modified transaction isolation level ：
SESSION： Indicates that the modified transaction isolation level will be applied to the current  session（ At present  cmd  window ） All of the things in ;
GLOBAL： Indicates that the modified transaction isolation level will apply to all  session（ overall situation ） Everything in , And what's existing now  session  Unaffected ;
 If omitted  SESSION  and  GLOBAL, Indicates that the modified transaction isolation level will be applied to the current  session  The next transaction that hasn't started in .

4.2 Read uncommitted

• thing A And things B, thing A Uncommitted data , thing B It can be read
• The data read here is called “ Dirty data ”, It's called dirty reading
• The lowest level of isolation , This level generally exists in theory , The database isolation level is generally higher than this level

In short, the first transaction was not committed , Something else can read , This data is not necessarily correct because , People may roll back ！

Case study ：

Xiao Luo got paid , His wife asked Xiao Luo to pay his wife's account , But the transaction was not committed , Just let your wife check , The wife really paid for it , Happily closed the web page , At this time, Xiao Luo was quick to roll back , The money comes back in an instant , For a month . So the data seen by Xiao Luo's wife is called “ Dirty data ”.

There must be two transactions

use test;
SET  transaction isolation level read uncommitted;

1- Xiao Luo , Transfer accounts

start transaction;
UPDATE user set balance = balance - 10000 where id = 1;
UPDATE user set balance = balance + 10000 where id = 2;

2- Xiao Luo's wife , Audit accounts , Pretty good , The money has arrived

start transaction;
select * from user where id = 2;
commit;

3- Xiao Luo , Return fire , Roll back

rollback;

4- Xiao Luo's wife checked the accounts one day , Ah , Why ten thousand less

start transaction;
select * from user where id = 2;
commit;

This is the case , That is what we call dirty reading , Two concurrent transactions ,“ Business A： Leaders give singo salary ”、“ Business B：singo Check salary account ”, Business B Read transaction A Data not yet submitted .

4.3 Read submitted

Can read the data submitted by other things .

A Transaction in this transaction , For the data you have operated on , After reading for many times, it is found that the data is inconsistent , It can't be read repeatedly . That is to say , Repeat the words , The data are different .

Simply put, you can't let me repeat it well , The data read out in a transaction is different , Let no one work .

Statement for update and delete, Can lead to non repeatable reading

Xiao Luo takes his salary card to spend , The system reads that the card does have 10200 element , At this time, his wife also happens to transfer money online , Take Xiao Luo's salary card 2000 Transfer yuan to another account , And submitted the transaction before Ronaldinho , When Ronaldinho deducts money , The system checks that Ronaldinho's salary card is different from that read last time , I wonder very much , It's clear that Cary has money , why …

SET  transaction isolation level read committed;

1- Xiao Luo went to spend , Show balance , Thief happy

start transaction;
select * from user where id = 1;

2- Wife transfer

start transaction;
UPDATE user set balance = balance + 500 where id = 2;
UPDATE user set balance = balance - 500 where id = 1;
commit;

3- Xiao Luo checks the accounts , In the same business , Found less money .

select * from user where id = 1;

When the isolation level is set to Read committed when , Avoid dirty reading , But it may cause non repeatable reading .

The default level for most databases is Read committed, such as Sql Server , Oracle. How to solve the problem of non repeatable reading , Look at the next level of isolation .

4.4 Repeatable

A In this transaction, the transaction makes multiple queries on the data that has not been operated , Found that the first time did not , The second time it happened, like an illusion （ Fantasy reading ）. Or for the first time and not for the second time . in the light of delete and insert.（ Unreal reading demonstration can't ,MySQL Set up MVCC）

Case study

Xiao Luo's wife works in the banking department , She often checks Xiao Luo's account information through the bank's internal system . one day , When she was inquiring about Xiao Luo's account information, she found that Xiao Luo had only one account , I thought this guy should have no private money . At this time, Xiao Luo opened an account in another branch , Ready to save private money . At the same time , Xiao Luo's wife clicked print , As a result, there was one more Xiao Luo's account printed out , It's strange .

set  transaction isolation level repeatable read;

1- Xiao Luo starts business

start transaction;

2- The wife checks the account

start transaction;
select * from user where name = ' Xiao Luo ';

3- Xiao Luo took the opportunity to open an account

insert into user values(3,' Xiao Luo ',10000);
commit;

4- The wife then inquires and prints , It should be found that Ronaldinho has an extra account , But no .

select * from user where name = ' Xiao Luo ';

MySQL Through multi version concurrency control （MVCC）（ Read the snapshot / Read consistency ） In fact, it solves the problem of unreal reading .

principle ： After the transaction is opened , Save a snapshot of historical data , Data added and deleted by other transactions , Not visible to the current transaction .

Of course, it can be measured like this

set  transaction isolation level repeatable read;

1- Xiao Luo starts business

start transaction;

2- The wife checks the account , Opened an account for Xiao Luo

start transaction;
select * from user where name = ' Xiao Luo ';
insert into user values(3,' Xiao Luo ',10000);

3- Xiao Luo doesn't know that his wife opened an account for him , Open one yourself , See yourself without this 3 Account No , Can't insert , It's fantastic .

select * from user where name = ' Xiao Luo ';
insert into user values(3,' Xiao Luo ',10000);

4.5 Serialization

• Business A And transaction B, Business A When working with databases , Business B You can only wait in line
• This isolation level is rarely used , Throughput is too low , Poor user experience
• This level can avoid “ Phantom reading ”, Each read is the real data in the database , Business A And business B Serial , Not concurrent .
• Once this data is used in other places, it cannot be modified or deleted , Until submitted elsewhere

SET  transaction isolation level serializable;

1- Xiao Luo

begin;
select * from user;

2- Xiao Luo's wife

begin;
select * from user;

3- Xiao Luo found it stuck

delete from user where id = 3;

4- As soon as Xiao Luo's wife submits , You can operate over there

commit;