In the previous InnoDB consistent Unlocked Reads article, we learned that InnoDB uses consistent unlocked reads to avoid using locks FOR normal query operations (except SELECT FOR UPDATE, etc.). Locking, however, is inevitable. Data writing, modification, and deletion all require locking. Today we will continue to learn about InnoDB locks.

Because the article involves more concepts, I am afraid that everyone will swear after reading, there is a word I know, do not quite understand the feeling, the article will give some examples and experiments, according to specific cases to explain these concepts. After all, the proof is in the pudding.

InnoDB storage engine supports table and row locks. As the name implies, a table lock locks the entire table, while a row lock locks only certain rows. InnoDB implements row locking by locking index entries or, if no index exists, by locking records with hidden clustered indexes. If the operation does not retrieve data by index criteria, InnoDB locks all the records in the table, which works just like a table lock. InnoDB storage engine has three algorithms for row locking:

  • Record Lock: A Lock on a single Record

  • Gap Lock: A Gap Lock that locks a range but not the record book

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

As shown in the picture below,

Three lock algorithms

For example, an index has the values 10,11,13,20. InnoDB can use Record Lock on 10, 11,13,20 as needed, and Gap Lock on (-∞,10), (10,11), (11,13), (13,20), (20, +∞). Next-key Locking is similar to the combination of the above two types of locks. It can be locked in (-∞,10], (10,11], (11,13], (13,20], (20, +∞). It can be seen that it not only locks a range, but also locks the record itself.

Some rules of InnoDB storage engine lock algorithm are shown below, and the corresponding experimental cases and detailed explanations will be given in the following sections.

  • InnoDB locks all records in a table without indexing criteria. Therefore, for performance purposes, all fields in a conditional query in a WHERE statement should be indexed.

  • InnoDB uses indexes to lock rows, not records. Therefore, when two different records of an operation have the same index, the wait also occurs because the row lock is locked.

  • Due to InnoDB’s indexing mechanism, database operations use the primary key index, InnoDB locks the primary key index; When InnoDB uses a non-primary key index, InnoDB locks the non-primary key index first and then the primary key index.

  • When the index of the query is a unique index (no two rows have exactly the same Key value), InnoDB storage engine degrades next-key Lock to Record Lock, which locks only the index itself, not the range.

  • InnoDB has special treatment for secondary indexes. It not only locks the range of secondary indexes, but also applies a Gap LOCK to the value of the next key.

  • InnoDB uses next-key Lock mechanism to avoid Phantom Problem.

Really understand the essence?

InnoDB locks all records in a table without indexing criteria. You can log on to your MySQL server and try it out for yourself.

The sample a

It is found that the query operation of session 2 really does wait. So, is this really true? We can use “InnoDB lock type and state query” to query data locks. Note that the query must be performed while the session 2 operation is still waiting, otherwise the query cannot be found.

Querying Lock Information

The transaction whose LOCK_TRx_id is 1851 is for session 2, and the other transaction is for session 1. We can see that both locks are locked on the primary key index of 1. It is important to note that the primary key is locked. How is the relationship between the two determined? We can determine this from the information_schema.innodb_lock_WAITS data.

That’s weird. What happened to locking all records in the table? I did a lot of digging and found that INNODB_LOCKS is defined as follows:

The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction.

That is, the table does not show information about all locks, but only about locks that are being applied for but are not being applied for, and locks that have been held and are blocking other threads. No wonder you have to query while session 2 is waiting to find the data.

Because both session operations lock all rows, we find that a lock wait occurs on the first row each time. So let’s try an insert statement. Table e1 the primary key of a value of 1-4, we respectively inserted into the key of 1-4 (of course there’s going to be a primary key repeat problem, but with the lock, has been waiting for) the new record, query lock information respectively, can see a transaction session for all the primary key of the lock, also is for all the records are added a lock.

Index, not record

InnoDB storage engine row locking is implemented by locking indexes, not records. This is the key to understanding many database locking problems.

Due to InnoDB’s special indexing mechanism, InnoDB locks primary key indexes when database operations use primary key indexes. When InnoDB uses a non-primary key index, InnoDB locks the non-primary key index first and then the primary key index. For those unfamiliar with InnoDB indexing, see this article

As shown in the figure below, when InnoDB locks non-primary key index B, it also locks its corresponding primary key index, so if InnoDB locks non-primary key index B with b value 2 and 3, then its associated primary key index A with a value 6 and 5 also needs to be locked.

Lock non-primary key indexes

For example, a common deadlock situation occurs in an operation scenario like the one shown below.

Example 2

The statement in session 1 uses the index on B, because it is a non-primary key index, so the lock is added on index B first and then on index A. Session 2, on the other hand, locks index A and then index B. In this case, deadlocks can occur.

What is the use of a next-key Lock?

REPEATABLE-READ, InnoDB uses next-key Lock by default. Only when the query index is unique or primary Key, InnoDB will optimize next-key Lock and downgrade it to Record Lock. That is, only the index itself is locked, not the range. InnoDB uses next-key Lock to Lock secondary indexes. InnoDB has special treatment for secondary indexes. It not only locks the range of secondary indexes, but also applies a Gap LOCK to the value of the next key.

Without further ado, let’s take a look at the relevant experiments to prepare.

CREATE TABLE e4 (a INT, b INT, PRIMARY KEY(a), KEY(b)); INSERT INTO e4 SELECT 1,1; INSERT INTO e4 SELECT 3,1; INSERT INTO e4 SELECT 5,3; INSERT INTO e4 SELECT 7,6; INSERT INTO e4 SELECT 10,8;Copy the code

Then open a session to execute the following statement.

SELECT * FROM e4 WHERE b=3 FOR UPDATE; Copy the code

InnoDB uses next-key Lock because index B is a non-primary Key index, so primary Key a is also locked. For primary key index A, Record Lock is applied only to indexes with value 5 (because of the previous rule). For index b, the next-key Lock index is set to (1,3). In addition, a Gap Lock is applied to the next key value, which also has a Lock in the range (3,6). You can open a new session and execute the following SQL statement, and it will be blocked.

SELECT * FROM e4 WHERE a = 5 FOR UPDATE; INSERT INTO e4 SELECT 1,2; INSERT INTO e4 SELECT 6,5 from (1,3); Insert row b with a value of 5, within the locked (3,6) rangeCopy the code

The InnoDB engine uses next-key Lock to solve the phantom problem. Because a next-key Lock locks a range, there is no phantom problem. Note however that InnoDB only uses this mechanism at Repeatable Read isolation level.

Afterword.

We’ll talk about InnoDB transactions later, so please stay tuned.

  • Mysql > select * from b-tree

  • Explore the internal storage structure of database

  • SQL statement execution process in detail

  • InnoDB memory structure and features

  • InnoDB disk files and drop disk mechanism

  • InnoDB lock type and state query

  • InnoDB read InnoDB read InnoDB read InnoDB read InnoDB read InnoDB read InnoDB read InnoDB


reference

  • Taobao blog http://mysql.taobao.org/monthly/2018/05/04/ database

  • Mysql Innodb lock in https://zhuanlan.zhihu.com/p/31875702

  • https://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html MySQL on the Lock type of official document