The isolation level of transaction and its problems

5 Line code , Can let wechat small program on the shelf to their own APP in | Register and send it to Dajiang 、 Huawei 、 Cherry keyboard ！>>>

One 、 The basic elements of a business （ACID）

　　1、 Atomicity （Atomicity）： All operations after the transaction begins , Or all of them , Or not at all , It's impossible to stay in the middle . An error occurred during the execution of the transaction , It rolls back to the state it was in before the transaction started , All the operations are as if they didn't happen . That is to say, a business is an indivisible whole , It's like atoms in chemistry , Is the basic unit of material composition .

　　 2、 Uniformity （Consistency）： Before and after the transaction starts , The database integrity constraint is not broken . such as A towards B Transfer accounts , impossible A Buckle the money ,B Has received .

　　 3、 Isolation, （Isolation）： At the same time , Only one transaction is allowed to request the same data , There is no interference between the different transactions . such as A Withdrawing money from a bank card , stay A Before the withdrawal process is over ,B You can't transfer money to this card .

　　 4、 persistence （Durability）： After the transaction completes , All updates to the database by the transaction are saved to the database , Cannot be rolled back .

Two 、SQL Standard transaction isolation level (mysql The default is repeatable ,oracle The default is read committed )

• Read uncommitted （read uncommitted） Refer to , When a transaction has not yet been committed , The changes it makes can be seen by other things .
• Read submitted （read committed） Refer to , After a transaction is committed , The changes it makes will be seen by other things .
• Repeatable （repeatable read） Refer to , Data seen during the execution of a transaction , It is always consistent with the data seen when the transaction is started . Of course, at the level of repeatable read isolation , Uncommitted changes are also invisible to other transactions .
• Serialization （serializable ）, As the name suggests, for the same line of records ,“ Write ” Will add “ Write lock ”,“ read ” Will add “ Read the lock ”. When there is a read/write lock conflict , Subsequent transactions must wait for the previous transaction to complete , To carry on That's ok .

Example

• If isolation level is “ Read uncommitted ”, be a1 The value is 20, although B Not submitted 2 But it turns out to be A I saw it

• If isolation level is “ Read submitted ”, be a1 yes 10,a2 The value of is 2. Business B Can't be updated until it's submitted A notice . therefore , a3 The value of is also 2.

• If isolation level is “ Repeatable ”, be a1、a2 yes 10,a3 yes 20. The reason a2 still 10, That's what we're following ： The data that the transaction sees during execution must be consistent before and after .

• If isolation level is “ Serialization ”, It's business B perform “ take A=10 Change to 20” When , Will be locked . Until transaction A After submission , Business B To continue . So from A From the perspective of , a1、a2 The value is 10,a3 The value of is 20

3、 ... and 、 How to set and view isolation level

• Read uncommitted :read uncommitted
• Read submitted :read committed
• Repeatable :repeatable read
• Serialization :serializable

Check the isolation level

show variables like 'transaction_isolation';

Set up innodb The transaction level method is ：set Scope transaction isolation level Transaction isolation level , for example ~

SET [SESSION | GL.........