I. Introduction to the four isolation levels

  1. Uncommitted read

    Read uncommitted is the ability to read data that has not been committed, so it is clear that this level of isolation cannot handle any of the dirty reads, unrepeatable reads, or phantom reads.Copy the code

  2. Has been submitted to read

    Read committed, that is, the ability to read data that has been committed, naturally prevents dirty reads, but does not limit unrepeatable and phantom readsCopy the code

  3. Repeatable read

    Repeatable read, read a data, the transaction is not finished, other transactions can not change this record, so that solve the problem of dirty read, unrepeatable read.Copy the code

  4. serialization

    Serialization, when multiple transactions are run, other transactions cannot be run until one transaction has been run.Copy the code

Mysql default transaction isolation level is REPEATable -read

Two, the four major isolation specific content

1. Log in to the mysql database using the cli

Command:

Mysql -uroot -p Run the mysql show databases command to display all local databasesCopy the code

Command:

Use test Select which database to use Show tables displays all tables in the current databaseCopy the code

2. Read uncommitted operations

After the transaction is started, client A can check the data modified by CLIENT B (dirty read) without committing.

(1) open client A, set the current transaction mode to read uncommitted, and query the value of user:Copy the code

(2) Open another client B and update table user before client A commits transaction:Copy the code

(3) At this time, although the transaction of client B has not been committed, client A can query the updated data of B:Copy the code

(4) Once client B's transaction is rolled back for some reason, all operations will be cancelled, and the data queried by client A is actually dirty data:Copy the code

Update user set money = money-50 where id =1 update user set money = money-50 where id =1 update user set money = money-50 where id =1 2000-50=1950, not knowing that other sessions rolled back, can be resolved by reading the committed isolation levelCopy the code

3, Read submitted

After the transaction is started, client A cannot read the data modified by B before the transaction is committed. Client A can read the data only after the transaction is committed. The data displayed on client A is inconsistent with the last query result (non-repeatable read).

Select * from 'A' where 'READ committed' mode is set; select * from 'A' where 'user' is set to 'READ COMMITTED';Copy the code

(2) Open another client B and update table user before client A commits transaction:Copy the code

(3) At this time, the transaction of client B has not been committed, and client A cannot query the updated data of CLIENT B, which solves the dirty read problem:Copy the code

(4) Transaction submission of client BCopy the code

(5) Client A executes the same query as the previous step, and the result is inconsistent with the previous step, that is, the problem of unrepeatable read occursCopy the code

3. Repeat

After the transaction is started and B submits the transaction, client A cannot find the data modified by CLIENT B. A still queries the same query result as last time (it can be read repeatedly). However, after A continues the operation, data changes are based on the data after B’s operation

(1) Open A client client A, set the current transaction mode to REPEATable Read, and query all records of the table accountCopy the code

(2) Before the transaction commit of client A, open another client B, update table user and commitCopy the code

(3) All records of table user are queried on client A. The query result is the same as that in Step 1. No unrepeatable read problem occursCopy the code

Update user = money-10 where id =1 update user = money-10 where id =1 update user = money-10 where id =1 update user = money-10 where id =1 update user = money-10 where id =1 update user = money-10 where id =1 update user = money-10 where id =1 update user = money-10 where id =1 The consistency of the data is not broken. At the isolation level of repeatable reads, the MVCC mechanism is used. The select operation does not update the version number, but snapshot reads (historical versions). Insert, UPDATE, and DELETE update the version number and are the current read (current version).Copy the code

(5) Re-open client B, insert a new data and submit itCopy the code

(6) Client A queries all records of table user, and no new data is found, so there is no phantom readCopy the code

serialization

(1) open client A and set transaction mode to SERIalIZABLE; If the transaction isolation level of mysql server is serialIZABLE, the table will be locked, so there will be no magic read. This isolation level has very low concurrency and is rarely used in development.Copy the code

Supplement:

1. When the transaction isolation level is read commit, write data will only lock the corresponding row

2. When the transaction isolation level is set to repeatable read, the default next-key lock is used if the search condition has indexes (including primary key indexes). If the search criteria do not have an index, the entire table is locked when the data is updated. A gap is locked by a transaction and records cannot be inserted by other transactions to prevent phantom reads.

3. When the transaction isolation level is serialized, the entire table is locked for reading and writing data

4. The higher the isolation level is, the more data integrity and consistency can be guaranteed, but the greater the impact on concurrent performance.

MYSQL MVCC implementation mechanismBlog.csdn.net/whoamiyang/…

6. For details about next-key locks, see:Blog.csdn.net/bigtree_372…