"MySQL's transaction isolation level"

This article shows the four isolation levels of MySQL transactions through an example.

1 Conceptual description

1) Read UNCOMMITTED (read not submitted)

Other transactions are aware of the current transaction under uncommitted changes.

2) Read Committed (read submit)

Other transactions are aware of the current transaction after committing the change, and will not be aware if other transactions have not committed the change.

3) Repeatable READ (Repeatable Read)

Other transactions commit the changes, and the current transaction commits the action before the changes can be detected.

4) Serializable (serializable)

By locking, only one transaction is maintained to perform the update operation, and if other transactions perform a more line operation, then the blocking

Wait state.

2 Example Show

2.1 How to query the current MySQL transaction isolation level

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/EC/wKiom1TvJ2WQCFt8AABVdQZ4GFY290.jpg "title=" 1.png " alt= "Wkiom1tvj2wqcft8aabvdqz4gfy290.jpg"/>

MySQL default isolation level is REPEATABLE READ (REPEATABLE Read)

2.2 Setting the isolation level for the current session

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/59/E8/wKioL1TvKOajzNsVAACMvJlRaf0017.jpg "title=" 2.png " alt= "Wkiol1tvkoajznsvaacmvjlraf0017.jpg"/>

2.3 Read Uncommitted Isolation Level effect display

1.a,b two transactions, set a transaction to read UNCOMMITTED transaction isolation level, when B transaction, do not commit after modification, A transaction can find the modification content of B transaction.

2. When a transaction modifies a record, B also modifies a record, and a B blocking wait occurs, which means that a transaction modification will have a row-level lock.

(A transaction)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/E8/wKioL1TvKeig7TBNAAB07h7ZgbM307.jpg "title=" 3.png " alt= "Wkiol1tvkeig7tbnaab07h7zgbm307.jpg"/>

(b transaction modified data, but not submitted)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/EC/wKiom1TvKV3hzouWAAB2dVI43Aw543.jpg "title=" 4.png " alt= "Wkiom1tvkv3hzouwaab2dvi43aw543.jpg"/>

(A transaction can detect data modified by B transaction)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/E8/wKioL1TvKrvyDmgGAABTY5qDLK4715.jpg "title=" 5.png " alt= "Wkiol1tvkrvydmggaabty5qdlk4715.jpg"/>

From the above process, it can be found that, under the READ UNCOMMITTED isolation level, changes between transactions are perceived and therefore prone to dirty reads.

And at this time the transaction a:update counter set value=1002 where id=1, but does not commit the transaction; transaction B also executes update counter set value = 1004 where id = 1; blocks wait until timeout. The reason is that when transaction a executes, the id=1 row record is locked, so the other transactions must wait for the processing to complete before execution, but other transactions can handle Id!=1 records.

2.4 Read Committed Isolation Level effect display

1. A and B two transactions, assuming that the isolation level of a transaction is read Committed, then B transaction can find a modification of B after executing commit.

2. As with read UNCOMMITTED, there is a row-level lock phenomenon.

(A transaction)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/59/EC/wKiom1TvK_egT9DbAAEIfN30WuA213.jpg "title=" 6.png " alt= "Wkiom1tvk_egt9dbaaeifn30wua213.jpg"/>

(b transaction updated but not submitted)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/59/EC/wKiom1TvLsHDfe1JAAD1XJiQr0Q763.jpg "title=" 7.png " alt= "Wkiom1tvlshdfe1jaad1xjiqr0q763.jpg"/>

(A transaction can find a modification to B after a transaction commits)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/E9/wKioL1TvMGmBzRwRAABulsgCVNs281.jpg "title=" 8.png " alt= "Wkiol1tvmgmbzrwraabulsgcvns281.jpg"/>

2.5 REPEATABLE READ (REPEATABLE Read)

1. A, b transaction, a transaction is repeatable Read, when transaction B is modified, a is still not aware of the modification effect of B, and when a transaction is submitted, the modification of B can be detected.

2. Locking of row-level locks is also present

(A transaction)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/E9/wKioL1TvMoChe1RsAAEVqDAu61k018.jpg "title=" 9.png " alt= "Wkiol1tvmoche1rsaaevqdau61k018.jpg"/>

(b transaction modified and submitted, a transaction still cannot detect the modification of B)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/EC/wKiom1TvMhbRxZ8kAAD6h5mOlP0808.jpg "title=" 10.png "alt=" Wkiom1tvmhbrxz8kaad6h5molp0808.jpg "/>

(a transaction can also be found after the modification of B)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/59/E9/wKioL1TvM2nTh0QqAABk-DkaAZo083.jpg "title=" 11.png "alt=" Wkiol1tvm2nth0qqaabk-dkaazo083.jpg "/>

2.6 Serializable (SERIALIZABLE)

1. A, b transaction, a transaction is serializable, then B cannot perform any update operations, because a gets a table-level lock that makes other transactions inaccessible.

(A transaction)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/E9/wKioL1TvNGXg4VVHAAEUMvbkZGI922.jpg "title=" 12.png "alt=" Wkiol1tvngxg4vvhaaeumvbkzgi922.jpg "/>

(b Transaction Modification operation, unable to execute)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/EC/wKiom1TvNAWwNYuUAADdcbyu4dw303.jpg "title=" 13.png "alt=" Wkiom1tvnawwnyuuaaddcbyu4dw303.jpg "/>

3 Summary

A repeatable read (REPEATABLE Read) isolation level is used by default in MySQL. For additional knowledge points about isolation levels as well as more subtle introductions, this article does not introduce too much, just introduce basic concepts and understanding, and if interested partners, can consider following my pattern of continuous experimentation.

This article is from the "Java Program Ghost" blog, please be sure to keep this source http://793404905.blog.51cto.com/6179428/1615550

"MySQL's transaction isolation level"