Isolation level for MySQL transactions

First look at the conclusion: (important)

MySQL INNODB transaction default isolation level is repeatable read, can reread the isolation level, with the mysqlinnodb of the row lock mechanism Next-key lock implementation, at the repeatable read level, to avoid the generation of Phantom Read, As a result, the requirements of the serializable level are almost met.

So here's the question:

1. What does the above mean, what is the isolation level?
2. What is repeatable READ?
3. What other isolation levels are there?
4. What issues are addressed at different levels?
5. What is dirty read, non-repeatable read, Phantom read?

Please look at the following content, all have a detailed introduction. (if possible, the video will be recorded)

Isolation, one of the acid four characteristics that a transaction must satisfy, i,isolation, is isolation.

Isolation refers to a transaction that is not visible to other transactions until it is committed.

MySQL uses the lock mechanism to achieve isolation.

The isolation level in this article refers to the degree to which isolation is to be achieved, that is, the degree to which a transaction is to be isolated.

ANSI SQL (Standard SQL), the isolation level for 4 transactions was established, respectively:

(from low to high, isolation level from low to high)

• READ UNCOMMITTED, non-committed
• Read COMMITTED, Reading commit
• REPEATABLE Read, repeatable reading
• SERIALIZABLE, serialization (serialized)

Tables used in the following tests:

-Table structure
drop table if exists kang_tx;
CREATE TABLE Kang_tx (
Num INT,
Primary KEY (NUM)
) Engine=innodb;
-Initial test data
INSERT into KANG_TX values (1), (2), (4);

Perform two transactions with two client connections (note: The following tests are tested in the initial data environment, and the initial data can be obtained by executing the above code repeatedly)

The specific meanings of the different isolation levels are:

READ UNCOMMITTED, not submitted. As the name implies, a transaction can read uncommitted modifications in other transactions.

This level is quite low, almost without isolation. Cannot meet the requirements of the transaction for isolation.

Session-a, turn on transaction a from level READ UNCOMMITTED

-session-a
-Set isolation level to READ UNCOMMITTED
Set TRANSACTION isolation level READ UNCOMMITTED;
Start transaction;

Session-b, open another transaction B, update the data

-session-b
Start transaction;
-Insert a new record
INSERT into KANG_TX values (5);
-Do not commit commits

Session-a, go back to the first transaction a

-session-a
-Wait for session-b to insert new data after execution
SELECT * from Kang_tx;

Results:

Explanation: Session-a opened a transaction with an isolation level of READ UNCOMMITTED. , Session-b performs a modification of table kang_tx, inserting a record, but does not execute the commit. After that, the entire data is viewed in session-a, and the results are found in session-b, which can be queried in session-a. This is the READ UNCOMMITTED isolation level.

The isolation level exists and the dirty read problem occurs. In fact at the same time will appear, non-repetition reading and phantom reading problems. The different levels will solve these problems separately, looking at dirty reads first.

Dirty reads : UNCOMMITTED data, that is, data that is not persisted to disk, is called dirty data, so dirty reads occur at the current isolation level. That is, the dirty data that was not committed is read.

Read COMMITTED, which reads commits, refers to the data that a transaction can read to other transactions that have already been committed.

The test is as follows, reverting to the initial data.

Session-a, turn on transaction A from level Read Committed

-session-a
-Set isolation level to read Committed
Set TRANSACTION ISOLATION level Read Committed;
Start transaction;

Session-b, open another transaction B, update the data

-session-b
Start transaction;
-Update a record
Update Kang_tx set num=20 where num=2;
-Do not commit commits

SESSION-A, tests whether data can be read to other transactions uncommitted

-session-a
-Wait for session-b to update data after execution
SELECT * from Kang_tx;

Results:

Session-b, COMMIT Transaction B

-session-b
-Execute Commit
Commit

SESSION-A, tests whether data submitted to other transactions can be read

-session-a
-Wait for Session-b to execute after submission
SELECT * from Kang_tx;

Results:

Explanation: Transaction A, level read Committed, thought this could read data submitted to other transactions. When transaction b updates the data, it is read directly in transaction A, not read, because there is no commit at this time, but when transaction B executes the commit, transaction a reads the updated data because the data has already been submitted, which is the read commit isolation level!

This isolation level resolves the dirty read problem, but there is a non-repeatable read and a phantom read problem, which first looks at the non-repeatable read problem:

non-repeatable reads : Reading the submitted data brings up the problem that when transaction a reads the data, transaction B commits the modification to the data, and then transaction a reads the data again, and the data becomes the post-commit data of transaction B. It also means that the same data that was read two times in transaction A is not the same. This is not repeatable reading. Because the results of repeated reads are not guaranteed to be consistent. is also a problem with the current isolation level.

Repeatable read, repeated reading, refers to the same transaction, you can repeatedly read the same data, will not be modified by other firms. Just read the question at the time of submission.

The measured numbers are as follows, re-initial data:

SESSION-A, open repeatable read-level transactions, read first:

-session-a
-Set isolation level to repeatable READ
Set TRANSACTION ISOLATION level repeatable read;
Start transaction;
-session-a
-Wait for Session-b to execute after submission
SELECT * from Kang_tx;

Results:

Session-b, open the transaction, and submit

-session-b
Start transaction;
-Update a record
Update Kang_tx set num=20 where num=2;
-Execute Commit
Commit

SESSION-A, the test reads what the data is:

Explanation: At the current isolation level, if transaction A has already read some data, transaction b updates the data. When transaction a reads the data again, it is not affected by transaction B, even if transaction B has committed the modification. This is the level of isolation that can be read repeatedly.

Note that if transaction A does not have read data, transaction B commits, and after transaction A is read, it can be read to the submitted data. Because this is not repeated reading.

Under the standard repeatable read level, there is a phantom reading problem.

Phantom Read : Suppose the initial data is: 1,2,4,5,8. Transaction a first reads data 1,2,4 that is less than or equal to 4 (where num<=4), when transaction B inserts data 3 into the table and commits, and then transaction a reads the data less than or equal to 4 again, it will find that the 3 data inserted into transaction B is read. This phenomenon is the illusion of reading, this 3 data, for transaction A, like ghosts, like hallucinations, just not yet, repeated reading of the same condition (where num<=4) data, and then appear. Called Phantom Reading.

It is important to note that MySQL's default transaction isolation level is the repeatable read level, but because the MySQL implementation uses the Next-key lock mode (a row lock mode that locks all rows and ranges in a range at the same time, You can refer to my introduction to the MySQL lock, so that once you have read some of the conditions of data such as (where num<=4), you will lock these record lines and <=4 this range, meaning you cannot insert 3 of this data. Thus avoiding the problem of phantom reading. So for MySQL, using the REPEATABLE read level to eliminate: dirty read, non-repeatable read, Phantom Read the typical isolation problem.

SERIALIZABLE, serialized (serialization), refers to the fact that transaction a does not allow transaction B to perform an update operation on the data while it is executing.

Test the following to restore the initial data:

SESSION-A, serializable isolation level transaction is turned on, but not committed

-session-a
-Set isolation level to serializable
Set TRANSACTION isolation level serializable;
Start transaction;
-session-a
-Wait for Session-b to execute after submission
SELECT * from Kang_tx;

Session-b, open transaction, try to get and update data

-session-b
Start transaction;
-Read Content
SELECT * from Kang_tx;
-Update a record
Update Kang_tx set num=20 where num=2;

Results:

Session-a, committing the transaction

-session-a
Commit

Session-b, which was just blocked execution after transaction A was committed, executed a

Results:

Explanation: After transaction A at the serialization level is turned on, a lock is added, blocking all other transaction writes, meaning that only after transaction A, commits, other transactions can write to the data. This is the serialization level. This level avoids the problem of dirty reading, non-repeatable reading, and Phantom reading. Is the ideal isolation level.

However, it is most commonly used because the isolation is ideal, but the lock overhead is increased to achieve isolation. So in order to balance, more databases are implemented: Read committed or repeatable read level.

This level is typically used only when the XA distributed transaction is in use. For distributed XA transactions, you can refer to the article.

Here's a summary:

After reading, try to answer the first question in this article.

Opinion, welcome to shoot bricks.

Hanzhongkang

Any reprint, please keep the source

Isolation level for MySQL transactions