InnoDB storage engine locks

A lock, in real life, is a tool we use to hide ourselves from the outside world. In computers, a mechanism for coordinating multiple processes or counties to access a resource concurrently. In addition to the contention for traditional computing resources (CPU, RAM, I/O, and so on) in a database, data is a resource that is shared and accessible by many users. How to ensure the consistency and validity of concurrent data access is a problem that must be solved by all databases. Lock conflict is also an important factor that affects the performance of concurrent data access. From this point of view, locks are particularly important for databases.

Compared with other databases, the locking mechanism of MySQL is relatively simple. The most significant feature is that different storage engines support different locking mechanisms. According to different storage engines, the characteristics of locks in MySQL can be summarized as follows:

The locked object is a transaction, which is used to lock objects in the database, such as tables, pages, and rows. And normally locked objects are released only after a transaction commit or ROLLBACK. And there are deadlocks.

Let’s look at two standard row-level locks in InnoDB’s storage engine:

  • A shared Lock (S Lock) allows a transaction to read a row of data

  • X Lock, which allows a transaction to delete or update a row of data

If transaction T holds S lock on data object A, then transaction T can read A but cannot modify A. Other transactions can only hold S lock on A but cannot hold X lock on A until T releases S lock on A. This guarantees that other transactions can read A, but cannot make any changes to A until T releases the S lock on A.

If transaction T locks data object A with X, transaction T can either read A or modify A, and no other transaction can lock A again until T releases the lock on A.

This is called lock incompatibility.

In addition, the InnoDB storage engine supports multi-granularity locking, which allows transaction row-level locks and table-level locks to exist simultaneously. To support locking at different granularity, InnoDB storage engine supports an additional type of locking called intentional locking.

Intentional locking means that the object to be locked is divided into multiple layers. Intentional locking means that the transaction wants to be locked at a finer granularity. If the locked object is viewed as a tree structure (coarse-grained to fine-grained from root to leaf), then the lowest level object must be locked first on its upper level nodes.

For example, if transaction T wants to Lock A row R1 with X, it must first add IX (Intention X Lock) to the table T1 where R1 resides. The corresponding IS Intetion S Lock.

At first I was confused about what intent locks were for. Later, when I was brushing books again, I suddenly saw the light. Now let me talk about my understanding.

Because intent locks are introduced to achieve multi-granularity locking, that is, row locks and table locks exist simultaneously. Let’s see what happens if we don’t introduce intent locks.

If transaction T wants to lock TABLE T1 by X, then it must determine whether each row in table T1 is locked by S or X (because of the lock incompatibility mentioned above). This is certainly inefficient. What about intent locks?

If the transaction T to be on the table T1 and X lock, before this, there has been a transaction to the rows in table T1 R S lock, so at this point in the T1 table IS locked, when a transaction T of table T1 to add X lock, because X lock and lock IS incompatible (about compatibility will give back form), so the transaction T waiting for row locking operation to finish. You see, this eliminates the need for traversal and improves the efficiency of locking the parent node (table T1 in this case).

X, S, IX, IS lock compatibility

Second, the lock algorithm

InnoDB storage engine has three algorithms for row locking:

  • Record Lock: A Lock on a single row Record

  • Gap Lock: A Gap Lock that locks a range but does not contain the record itself

  • Next-key Lock: Gap Lock+Record Lock locks a range, and locks the Record itself.

For example, suppose an index has four values: 10,11,13,20. Then the interval that the index may be next-key Locking is: (-∞, 10] (10,11] (11,13] (13,20] (20,+∞)

InnoDB optimizes the Next_Key Lock to a Record Lock if a row is locked and the index of the query has a unique attribute. InnoDB optimizes the Next_Key Lock to a Record Lock if the index of the query has a unique attribute. InnoDB optimizes the Next_Key Lock to a Record Lock if the index has a unique attribute. If we use more than a unique index to lock a row, InnoDB will lock a range and record as it should. It is also important to note that when a unique index consists of multiple columns, there is no degradation if the query is looking for only one of the columns. Also note that the InnoDB storage engine also applies a gap lock to the next key range of the secondary index (this is also done to prevent phantom reads). Next_Key Lock is designed to solve the problem of phantom read in the database.

For more information on how to lock Mysql, see my article: Consistent unlocked read and consistent locked read

Mysql isolation level and MVCC

InnoDB storage engine default transaction isolation level is RR level, can repeat read. At this level, next-key locking is adopted to lock the device. Therefore can prevent unreal reading phenomenon.

For example, why next-key locking can solve the illusion problem:

The so-called phantom read is the same transaction, two consecutive execution of the same SQL statement may result in different results, the second SQL statement may return a row that did not exist before.

Create table t:

create table t (a int primary key); insert into t select 1; insert into t select 2; insert into t select 5;Copy the code

There are three rows, one, two, five.

Assume the following execution sequence:

Select * from session A; select * from session A; For update statement, add X lock to range (2, +∞). Therefore, any insertion into this range is not allowed, and since 4 is in this range, no insertion is allowed, thus avoiding phantom reading.