MySQL transaction four isolation levels

MySQL Transaction ISOLATION level detailed      

MySQL data structure SQL

The SQL standard defines a Class 4 isolation level, which includes specific rules to define which changes within and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrency processing and have lower system overhead.

READ UNCOMMITTED ( reads uncommitted content)

at this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real -world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).
Read Committed ( reads the submitted content)

This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results.
Repeatable Read ( can be reread ) [ri ' Pi:tebl]

This is the default transaction isolation level for MySQL , which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism.

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/8E/93/wKiom1jF_nzS8hG7AADRVjQzGsE696.png "title=" Mysql-can be reread. png "alt=" Wkiom1jf_nzs8hg7aadrvjqzgse696.png "/>

Serializable ( serializable)
This is the highest isolation level , which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

These four isolation levels are implemented with different lock types, which can be problematic if the same data is being read. such as:

Dirty Reads (drity read): A transaction has updated one copy of the data, another transaction reads the same data at this time, for some reason, the previous rollback operation, the latter will read the data is not correct.

Non-repeatable read (non-repeatable Read): Data inconsistency in two queries for a transaction, which may be the original data that was inserted in the middle of a transaction update during the two query process.

Phantom Read (Phantom Read): In a transaction two times the number of data pens inconsistent, for example, one transaction queried several columns (row) of data, while another transaction at this time inserted a new column of data, the previous transaction in the next query, you will find that there are a few columns of data that it did not previously.

In MySQL, these four isolation levels are implemented, each of which can cause problems as follows:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72610/af5b9c1e-4517-3df2-ad62-af25d1672d12.jpg "Style=" VERTICAL-ALIGN:MIDDLE;WIDTH:400PX;HEIGHT:127PX; "title=" "hspace=" 0 "border=" 0 "height=" 127 "width=" 400 " Vspace= "0"/>

Below, you will use MySQL's client program to test several isolation levels, respectively. The test database is testing, the table is TX, and the table structure:

Id	Int
Num	Int

The two command-line clients are a-B, changing the isolation level of a, and modifying the data on the B-side.

(i), set the isolation level of a to read UNCOMMITTED (READ UNCOMMITTED)

Before data is updated in B:

Client A:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72618/1ca8ec0d-3b6c-3ae1-babc-5dd541c4d1a4.jpg "height=" 379 "width=" 645 "/>

b Update Data:

Client B:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72620/b37214c3-7726-3306-95ee-1b2fae5ccd6c.jpg "height=" 397 "width=" 640 "/>

Client A: displayed as 1 before Change , displayed as 10 after the rollback, 1 when rolled back, three times results are different

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72622/200d9c94-197a-3fe5-8925-3411cd88555e.jpg "height=" 481 "width=" 640 "/>

The above experiment concludes that transaction B updates a record, but does not commit, at which point a can query for uncommitted records. Cause dirty reading phenomenon. Non-committed reads are the lowest isolation level.

(ii), set the transaction isolation level of client A to read committed ( read commit)

Before data is updated in B:

Client A:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72626/ae414e52-c216-3bbb-b005-0d972f593456.jpg "height=" 351 "width=" 644 "/>

b Update Data:

Client B:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72628/12051f3d-c01e-34b3-a6b6-8b71e1b1dcc8.jpg "height=" 313 "width=" 641 "/>

Client A:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72630/cc80744e-eb9f-3104-bb24-2218e9986d78.jpg "height=" 351 "width=" 642 "/>

The above experiment can conclude that the read-committed isolation level solves the problem of dirty reads, but there is a non-repeatable read problem where transaction A is inconsistent with data in two queries because transaction b updates a data between two queries. Read submissions allow only read committed records, but do not require repeatable reads.

(iii), Set the isolation level of a to repeatable read (repeatable Read)

Before data is updated in B:

Client A:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72632/0bf52be3-e873-3f3f-8d56-d703a8f678ab.jpg "Height=" 319 "width=" 643 "/>

b Update Data:

Client B:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72634/e58d1814-bdca-3313-bcf5-339e3678536a.jpg "height=" 333 "width=" 645 "/>

Client A:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72636/83bfe583-2d57-345a-917e-4ee163235b62.jpg "height=" 497 "width=" 640 "/>

B Insert Data:

Client B:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72638/192348aa-4fa5-3d5f-a4f9-4660ddd725cd.jpg "Height=" 366 "width=" 641 "/>

Client A:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72640/4398c5b1-434c-3380-ba19-060154cf2070.jpg "height=" 351 "width=" 641 "/>

From the above experiment, it can be concluded that the repeatable read isolation level allows only read committed records, and the other transactions update the record during a transaction that reads one record two times. However, the transaction does not require serialization with other transactions. For example, when a transaction can find a record that is updated by a committed transaction, it may produce a phantom read problem (note that it is possible because the database differs from the implementation of the isolation level). Like the above experiment, there is no problem of data phantom reading.

( IV), Set the isolation level of a to serializable (Serializable)

A end open transaction, B end insert a record

Transaction A-side:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72642/c604c5ce-311d-3923-8dcd-36b0188f4f31.jpg "height=" 234 "width=" 639 "/>

Transaction B-side:

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72644/c488f9d9-7da2-3e6d-9a82-2b92d1051afd.jpg "height=" 141 "width=" 640 "/>

Because transaction A's isolation level is set to serializable at this point, transaction B waits only after the transaction has started and has not been committed.

Transaction a commits the transaction:

Transaction A-side

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72646/322ed59a-b2b9-338c-a2f0-09c9b7707577.jpg "height=" width= "639"/>

Transaction B-Side

650) this.width=650; "Src=" http://dl.iteye.com/upload/picture/pic/72648/8e60e19b-09af-31a7-b8d3-8e638bbf177c.jpg "Height=" width= "644"/>

serializable completely locks the field, and if a transaction queries the same data, it must wait until the previous transaction is complete and unlocked. is the complete isolation level, which locks the corresponding data tables, thus making the problem more efficient.

Eg1. Save Point

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/8E/93/wKiom1jGAxvD71y-AAMB9qGadoA843.png "title=" mysql-isolation level. png "alt=" Wkiom1jgaxvd71y-aamb9qgadoa843.png "/>

---end---

This article is from the "Wind No Trace" blog, please be sure to keep this source http://wangfx.blog.51cto.com/1697877/1905743

MySQL transaction four isolation levels