How MySQL achieves transaction isolation

How MySQL achieves transaction isolation
Review

Among the many storage engines of MySQL, only InnoDB supports transactions. The transaction isolation level mentioned here refers to the transaction isolation level under InnoDB.

Read not committed: one transaction can read the uncommitted changes of another transaction. This may cause dirty reading, phantom reading, and non-repeated reading. (Useless)

Read committed: one transaction can only read the modifications committed by another transaction. It avoids dirty reading, but there are still non-repeated reading and phantom reading problems.

Repeatable read: The results returned by reading the same data multiple times in the same transaction are the same. It avoids dirty reads and non-repeated reads, but Phantom reads still exist.

Serialize: the transaction is executed in sequence. All the above problems are avoided.

The above are four isolation levels defined in the SQL-92 standard. In MySQL, the default isolation level is REPEATABLE-READ (repeatable) and solves phantom read problems. Simply put, the default isolation level of mysql solves the problems of dirty read, phantom read, and non-repeated read.

Repeatable reading focuses on update and delete, while phantom reading focuses on insert.

Here, we will only discuss repeatable reading.

Knowledge reserve MVCC

:

MVCC stands for "Multi-version concurrency control ". This technology ensures that InnoDB performs consistent read operations at the transaction isolation level. In other words, it is used to query rows that are being updated by another transaction, you can also see the values before they are updated. This is a powerful technology that can be used to enhance concurrency, because the query does not have to wait for another transaction to release the lock. This technology is not widely used in the database field. Some other database products and other mysql storage engines do not support it.

 

Description

I have seen a lot of articles on the internet saying that MVCC adds two hidden fields to no row to indicate the row creation time and expiration time. they store the transaction version number instead of the time.

In fact, this statement is not accurate. Strictly speaking, InnoDB will add three fields to each row in the database: DB_TRX_ID, DB_ROLL_PTR, and DB_ROW_ID.

However, for the convenience of understanding, we can understand it in this way. In the next section of the index, we will also use these two fields for understanding.

 

Add, query, modify, and delete

In InnoDB, two hidden fields are added to each row to implement MVCC. One is used to record the creation time of the Data row, and the other is used to record the row expiration time (deletion time ). In actual operations, the transaction version is not stored as time, but as the transaction version number. Each time a new transaction is started, the transaction version number increases progressively.

Therefore, under the default isolation level (repeatable read), the addition, deletion, and query changes to the following:

• SELECT
  • Read the records whose created version is earlier than or equal to the current transaction version number and whose version is null or later than the current transaction version number. This ensures that the record exists before reading.
• INSERT
  • Save the current transaction version number to the row creation version number.
• UPDATE
  • Insert a new row and use the version number of the current transaction as the new row creation version number. At the same time, set the delete version number of the original record row to the current transaction version number.
• DELETE
  • Save the version number of the current transaction to the delete version number of the row.

 

Snapshot read and current read

Snapshot read: Read the snapshot version, that is, the previous version.

Current read: read the latest version.

A normal SELECT statement is a snapshot read, while an UPDATE, DELETE, INSERT, SELECT... lock in share mode, SELECT... for update statement is the current read Statement.

 

Consistent non-locked read and locked read

In a transaction, the standard SELECT statement does not lock, but there are two exceptions. SELECT... lock in share mode and SELECT... for update.

SELECT... LOCK IN SHARE MODE

Assume that the record is a shared lock. In this way, other transactions can only be read and cannot be modified until the current transaction is committed.

SELECT... FOR UPDATE

Apply a lock to the index record, which is the same as the UPDATE lock.

Consistent non-locked read

Consistent read (consistent read). InnoDB uses multiple versions to query database snapshots at a certain time point. If the isolation level is repeatable read, all consistent reads in the same transaction will READ the first READ snapshot in the transaction; if it is read committed, every consistent read in a transaction reads the snapshot version that it refreshes. Consistent read is the default mode for common SELECT statements at the read committed and repeatable read isolation levels. 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 in its name, the database always thinks that others will modify the data it wants to operate on, so the data is locked during database processing. Its implementation relies on the underlying database.

Optimistic lock, like its name, always thinks that others will not modify it, and only checks the data status when submitting updates. Generally, a field is added to the data to identify the data version.

 

Lock

There are three locks we need to know

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

Assume that an index contains the following values: 10, 11, 13, and 20. The next-key lock of this index will overwrite the following range:

(Negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

 

After learning about the above concepts, we will analyze how the repeatable read isolation level is implemented.

Theoretical Analysis

The reason for this is theoretical analysis, because I do not know how to prove it if the actual operation proves it. After all, the author's level is actually limited.

However, this does not mean that I am talking nonsense here, as evidenced by official documentation.

The general meaning of this section is that in the default isolation level, normal SELECT uses consistent read without locking. Locking is required for locking read, UPDATE, and DELETE. The locking depends on the situation. If you use a unique search condition for a unique index, you only need to lock the index record. If you do not use a unique index as the search condition, or use an index range scan, the gap lock or next-key lock will be used to block other sessions from inserting data into the gap within this range.

The author once had a misunderstanding that there would be no problems with the addition, deletion, query, and modification behaviors under MVCC, nor repeated and Phantom reads. But it is a big mistake.

For example, assume that transaction A updates the record with id = 1 in the table, and transaction B updates this record, and transaction B commits the record first, transaction A reads the snapshot version with id = 1, so it cannot see the modifications submitted by B. If it is directly updated, it will overwrite the changes before B, which is incorrect, B and A may not modify A field, but B's modification will be lost, which is not allowed.

Therefore, the modification must not be a snapshot read, but the current read.

In addition, as mentioned earlier, only normal SELECT statements are used to read snapshots. Other statements such as UPDATE and delete statements are used to read snapshots. It is inevitable to lock the changes, and a gap lock is required to prevent Phantom reads.

• Consistent read ensures available duplicate read
• Gap lock prevents phantom read

Recall

1. MVCC is used to implement consistent non-locked read. This ensures that the same results are returned when the same data is read multiple times in the same transaction, and solves the problem of non-repeated read.

2. Gap Locks and Next-Key can be used to prevent other transactions from inserting data in the lock interval. This solves the phantom read problem.

In conclusion, the implementation of the default isolation level depends on MVCC and lock, and the specific point is consistent read and lock.

 

Demo

The above comparison shows that because id is the primary key, only the index record is locked when id is used as the search condition. Next, let's look at the index range example.

We can see that, because no unique index is used as the search condition, not only the index records are locked, but also the gap between indexes is locked, the next-key lock is used.

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