当前位置:网站首页>The isolation level of transaction and its problems

The isolation level of transaction and its problems

2020-11-06 21:19:48 Irving the procedural ape

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

Business one Business two
Start transaction A, Inquire about A=10 Start transaction B
Inquire about A=10, to update A=20
The query value a1
Commit transaction B
The query value a2
Commit transaction A
The query value a3
  • 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.........

版权声明
本文为[Irving the procedural ape]所创,转载请带上原文链接,感谢