How the MySQL transaction isolation level is implemented

Review

Of the many storage engines in MySQL, only InnoDB supports transactions, and all of the transaction isolation levels described here refer to the transaction isolation level under InnoDB.

READ UNCOMMITTED: One transaction can read uncommitted modifications to another transaction. This can lead to dirty reading, phantom reading, non-repeatable reading problems. (Basic useless)

Read committed: One transaction can read only the modifications that have been committed by another transaction. It avoids dirty reads, but there are still non-repeatable read and Phantom read problems.

REPEATABLE READ: Reads the same data multiple times in the same transaction and returns the same result. It avoids dirty reads and non-repeatable read problems, but Phantom reads still exist.

Serialization: Transaction serial execution. Avoid all of the above issues.

These are the four isolation levels defined in the SQL-92 standard. In MySQL, the default isolation level is Repeatable-read (repeatable read), and the Phantom read problem is resolved. Simply put, MySQL's default isolation level addresses dirty reads, Phantom reads, and non-repeatable read issues.

Non-repeatable reading focuses on update and delete, while the focus of Phantom reading is on insert.

Here, we are only talking about repeatable reading.

Knowledge ReserveMvvc

Moqtada

The full name of MVVC is "multi-version concurrency control". This technique makes it possible to perform consistent read operations under the transaction isolation level of InnoDB, in other words, to query for some rows that are being updated by another transaction, and to see the values before they are updated. This is a powerful technique that can be used to enhance concurrency, because a query does not have to wait for another transaction to release the lock. This technology is not universally used in the field of databases. Some other database products, as well as other MySQL storage engines, do not support it.

Description

See a lot of articles on the internet about MVVC are said to give no line to add two hidden fields indicate the creation time of the row and the expiration time, they store not the time, but the transaction version number.

In fact, this statement is not accurate, strictly speaking, InnoDB will add three fields to each row in the database, which are db_trx_id, Db_roll_ptr, db_row_id, respectively.

However, in order to understand the convenience, we can understand, the index in the next explanation is also used in the way the two fields to understand.

Additions and deletions to check and change

In InnoDB, add two hidden fields for each row to implement MVVC, one to record the creation time of the data row, and another to record the expiration time of the row (delete time). In practice, it is not the time that is stored, but the version number of the transaction, and the version number of the transaction is incremented for each new transaction that is opened.

Thus, the default isolation level (repeatable READ), additions and deletions are changed into this:

• SELECT
  • Reads the creation version that is less than or equal to the current transaction version number, and deletes the record with a null version or greater than the current transaction version number. This ensures that the record is present before reading.
• INSERT
  • Saves the version number of the current transaction to the created version number of the row
• UPDATE
  • Inserts a new row with the version number of the current transaction as the version number of the new row, and sets the delete version number of the original record row to the current transaction version number
• DELETE
  • Saves the version number of the current transaction to the deleted version number of the row

Snapshot Read and current read

Snapshot read: Read the snapshot version, which is the historical version

Current read: Read the latest version

The normal select is the snapshot read, and update, DELETE, INSERT, SELECT ... LOCK in SHARE MODE, SELECT ... The for update is currently read.

Consistent non-locking read and lock readLock Read

In one transaction, the standard SELECT statement is not locked, but there are two exceptions. SELECT ... LOCK in SHARE MODE and SELECT ... For UPDATE.

SELECT ... LOCK in SHARE MODE

Assume a shared lock on a record, so that other transactions can only be read and cannot be modified until the current transaction commits

SELECT ... For UPDATE

Lock the index record, in which case the lock is the same as the update

consistent non-lock read

Consistent read (consistent read), InnoDB uses multiple editions to provide a snapshot of the database at a certain point in time. If the isolation level is repeatable read, all consistent reads in the same transaction are read in the first read-read snapshot in the transaction, and in the case of read COMMITTED, every consistent read in a transaction reads its own refreshed snapshot version. consistent read (consistent read) is the default mode of the normal SELECT statement under Read committed and REPEATABLE read isolation level . Consistent read does not add any form of locks to the tables it accesses, so other transactions can modify them concurrently.

Pessimistic lock and optimistic lock

Pessimistic lock, as its name says, the database always thinks that others will modify the data it wants to manipulate, so it locks the data during database processing. Its implementation relies on the database underlying.

An optimistic lock, like its name, always thinks that no one else will change it, only to check the status of the data when the update is submitted. Typically, you add a field to the data to identify the version of the data.

Lock

There are three kinds of locks that we need to know

• Record Locks (recording lock): Locks the index record.
• Gap Locks (Gap Lock): Locks between index records, or locks before the first index record, or after the last index record.
• Next-key Locks: Locks the index record and locks the gap before the index record. It is equivalent to a combination of the record locks and Gap locks.

Suppose an index contains the following values: 10,11,13,20. Then the Next-key lock for this index will cover the following intervals:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(Positive Infinity)

After understanding the above concepts, the following is a simple analysis of how the repeatable read isolation level is implemented

Theoretical Analysis

The reason is theoretical analysis, because if the actual operation proves that I do not know how to prove, after all, the author level is really limited.

However, this does not mean that I am in this nonsense, there are official documents to testify.

The general meaning of this passage is that, in the default isolation level, the normal select uses a consistent read without locking. For locking read, update, and delete, lock is required, as the lock is dependent on the situation. If you use unique search criteria for a unique index, you can simply lock the index record, and if you do not use a unique index as a search condition, or if you are using an index range scan, you will use a gap lock or next-key lock to block other sessions from inserting data into gaps in that range.

The author once had a misunderstanding, that according to the above said MVVC under the act of adding and removing changes will not appear any problem, and will not appear non-repeatable reading and phantom reading. But it was a big mistake.

As a simple example, let's say that transaction a updates the record in the table, and transaction B also updates the record, and b commits first, and if transaction a reads the snapshot version of Id=1 according to the previous MVVC, then it does not see the id=1 of B, and if it is updated directly, it overwrites the modification before B. That's not right, and maybe B and a are not modifying a field, but in this way, the modification of B is lost, which is not allowed.

Therefore, it must not be the snapshot read at the time of the modification, but the current reading.

And, as mentioned above, only the normal select is the snapshot read, other such as update, delete is the current read. It is necessary to add locks when modifying, and to prevent the appearance of Phantom reads, it is necessary to add a gap lock.

• Consistent read ensures repeatable reads are available
• Gap Lock prevents Phantom reading

Think back.

1, the use of MVVC to achieve a consistent non-locking read, it is guaranteed to read the same transaction multiple times the same data returned results are the same, resolved the problem of non-repeatable reading

2. Using gap locks and Next-key can prevent other transactions from inserting data within the lock interval, thus solving the Phantom reading problem

In summary, the implementation of the default isolation level relies on MVVC and locks, and, more specifically, consistent reads and locks.

Demo

Above four comparisons, you can see that because the ID is the primary key, with the ID as the retrieval criteria, only the index record is locked. Next, look at an example of the index range

These two, it can be seen that because the unique index is not used as a search condition, resulting in not only the index record locked, but also locked the gap between the indexes, should be the use of Next-key lock.

Reference https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

How the MySQL transaction isolation level is implemented