Mysql transaction isolation level principle example analysis

1. Read Uncommitted (READ_UNCOMMITTED)

In fact, this can be seen from the name of isolation. One transaction can read the uncommitted data of another transaction! For the sake of explanation, I will simply draw a picture to illustrate!

As shown in the figure, the data retrieved by one transaction was modified by another uncommitted transaction.

The address defined by the official website for dirty read is

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_dirty_read

Its content is

**dirty read
An operation that retrieves unreliable data, data that was updated by another transaction but not yet committed.
**

Translated it means

The data retrieved from the operation is unreliable and can be modified by another uncommitted transaction!

You will find that our demonstration results are consistent with the official website's definition of dirty reads. According to our initial reasoning, if dirty reads exist, then non-repeatable reads and phantom reads must exist.

2. Read Committed (READ_COMMITTED)

This also shows that one transaction can read the data submitted by another transaction! For the sake of explanation, I will simply draw a picture to illustrate!

As shown in the figure, data retrieved by one transaction can only be modified by another committed transaction.

The address defined by the official website for non-repeatable read is

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_non_repeatable_read

Its content is

**non-repeatable read
The situation when a query retrieves data, and a later query within the same transaction retrieves what should be the same data, but the queries return different results (changed by another transaction committing in the meantime).
**

Translated it means

A query statement retrieves data, and then another query statement retrieves data in the same transaction. The two data should be the same, but in fact different results are returned. !

ps: Author's note: The different results here refer to the situation where the row remains unchanged (to be more professional, the primary key index has not changed), but the data content on the disk pointed to by the primary key index has changed. If the primary key index changes, such as adding a new piece of data or deleting a piece of data, it is not a non-repeatable read.

Obviously, our phenomenon meets the definition of non-repeatable read. Next, let's think about this:

This definition of non-repeatable read can also be applied to the phenomenon of dirty read. Obviously, the phenomenon of dirty read, that is, the example of **READ_UNCOMMITTED**, also meets the condition of returning different results in the same transaction! However, the reverse is not necessarily true. If the results of two queries in transaction A are changed by another transaction B, if transaction B changes the result of transaction A without being committed, it is a dirty read and also a non-repeatable read. If transaction B is committed before changing the result of transaction A, it is not a dirty read, but a non-repeatable read. 3. Repeatable Read (REPEATABLE_READ)

Here, I change the order and first define phantom reading.

The address defined by the official website for phantom reading is

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_phantom

phantom
A row that appears in the result set of a query, but not in the result set of an earlier query. For example, if a query is run twice within a transaction, and in the meantime, another transaction commits after inserting a new row or updating a row so that it matches the WHERE clause of the query.

Translated it means

A row of data appears in the result set of one query, but the data does not appear in the result set of an earlier query. For example, two queries are performed in one transaction, and at the same time, another transaction inserts a row or updates a row of data (the data meets the conditions after the where in the query statement) and commits it!

OK, let's look at the picture below. You can judge for yourself whether this phenomenon meets the definition of phantom reading.

Obviously, this phenomenon meets the definition of phantom reading. Two identical queries from the same transaction produce different rows. Next, let's think about this:

This definition of phantom read can also be applied to the phenomenon of non-repeatable read. Think about it for yourself! The reverse is not necessarily true. The transaction queried a piece of data for the second time, but the data did not appear in the result set of the first query. If the data is modified data, then this phenomenon is both a non-repeatable read and a phantom read. If the data is newly added or deleted, then this phenomenon does not belong to non-repeatable read, but to phantom read.

Next, let’s talk about why many articles have misinformed readers, saying that repeatable reads can solve the phantom read problem! The reason comes from a sentence on the official website
(The address is: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks)

The original content is as follows

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 14.7.4, “Phantom Rows”).

According to the original meaning of this sentence, it should be

InnoDB uses REPEATABLE READ by default. In this case, use next-key locks to solve the phantom read problem!

It is estimated that a domestic translator turned it into

InnoDB uses REPEATABLE READ by default. In this case, the phantom read problem can be solved!

Then everyone continued to copy me and I copied you, and you know the result!

Obviously, after omitting the condition "next-key locks are used!", the meaning changes completely, and we execute the statement under this isolation level.

select * from tx_tb where pId >= 1;

It is a snapshot read, which does not add any locks and cannot solve the phantom read problem at all, unless you use

select * from tx_tb where pId >= 1 lock in share mode;

In this way, you use next-key locks and solve the phantom read problem!

4. Serial read (SERIALIZABLE_READ)

At this isolation level, all select statements are automatically followed by lock in share mode. Therefore, under this isolation level, next-key locks are used regardless of how you query. All select operations are current reads!

OK, pay attention to the red part in the table above! Because of the use of next-key locks, InnoDB locks the index record PiD=1 and the gap (1,++∞). If other transactions want to insert data in this gap, they will be blocked, thus preventing phantom reads from happening!

Some people may say that the result of your second query has also changed, and is obviously different from the first query result? In this regard, I can only say, please look carefully. This is by oneself

It is modified by the transaction, not by other things. This is not a phantom read, nor is it a non-repeatable read.

Summarize

There is a lot of nonsense above, so let me make a table to summarize it. You can just answer this table during the interview. Everything above is in preparation for this table!

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.