Detailed explanation of MySQL row locks when encountering composite primary keys and multi-column indexes

background

Today, while cooperating with other project teams to do system stress testing, occasional deadlock problems occurred during the process. After analyzing the code, it was found that there was an update of the composite primary key. When updating the composite primary key table, only one field was updated. At the same time, there was an insert operation on the table within the transaction, resulting in occasional deadlock problems.

For example, the table t_lock_test has two primary keys, both of which are primary key(a,b) , but when updating, it is updated through update t_lock_test .. where a = ? , and then there is insert into t_lock_test values(...) in the transaction.

The locking algorithm in InnoDB is Next-Key Locking. It is very likely that the deadlock is caused by this point. However, will Next-Key Locking be triggered under a composite primary key? Will Next-Key Locking be triggered under a multi-column joint unique index? The answer is not found in the book, so it needs to be tested in practice.

Locks in InnoDB

Locking is a key feature that distinguishes database systems from file systems. Locking mechanisms are used to manage concurrent access to shared resources [illustration]. The InnoDB storage engine locks table data at the row level, which is good. However, the InnoDB storage engine also uses locks in many other places inside the database to allow concurrent access to a variety of different resources. For example, to operate the LRU list in the buffer pool, delete, add, and move elements in the LRU list, locks must be involved to ensure consistency. Database systems use locks to support concurrent access to shared resources and to provide data integrity and consistency.

Since locks are basically used under the InnoDB storage engine, we will skip MyISAM and discuss InnoDB directly.

Lock Type

The InnoDB storage engine implements the following two standard row-level locks:

• Shared lock (S Lock), allowing a transaction to read a row of data
• Exclusive lock (x lOCK), allowing a transaction to delete or update a piece of data

If a transaction T1 has already obtained a shared lock on row r, then another transaction T2 can immediately obtain a shared lock on row r, because reading does not change the data of r. This situation is called lock compatible. However, if another transaction T3 obtains an exclusive lock on row r, it will wait for T1 and T2 to release the shared lock on row r - this situation is called lock incompatibility.

Compatibility of exclusive locks and shared locks:

InnoDB will generate row locks when updating data, and row locks can also be added explicitly (also known as "pessimistic locks")

select for update

Locking Algorithm

InnoDB has three row lock algorithms, which are:

Record Lock: A lock on a single row record, which is literally a row lock.

Record Lock will lock the index record (note that we are talking about index here, because the primary key index in InnoDB is the data). If no index is set when the InnoDB storage engine table is created, the InnoDB storage engine will use the hidden primary key to lock it.

Gap Lock: Gap lock, locks a range but does not include the record itself

Next-Key Lock: Gap Lock + Record Lock, locks a range and the record itself

The locking interval division principles of Gap Lock and Next-Key Lock are the same.

For example, if an index has four values ​​10/11/13 and 20, the intervals into which the index is divided are:

(-∞,10]
(10,11]
(11,13]
(13,20]
(20,+∞]

The locking technology using Next-Key Lock is called Next-Key Locking. Its design purpose is to solve the Phantom Problem, which will be introduced in the next section. Using this locking technology, what is locked is not a single value, but a range, which is an improvement on the predicate lock.

When the queried index contains a unique attribute (primary key index, unique index), the InnoDB storage engine optimizes the Next-Key Lock and downgrades it to Record Lock, which means that only the index itself is locked, not the range.

Let's look at a lock example under a secondary index (non-unique index):

CREATE TABLE z ( a INT, b INT, PRIMARY KEY(a), KEY(b) );

INSERT INTO z SELECT 1,1;
INSERT INTO z SELECT 3,1;
INSERT INTO z SELECT 5,3;
INSERT INTO z SELECT 7,6;
INSERT INTO z SELECT 10,8;

Column b of table z is a secondary index. If transaction A executes:

SELECT * FROM z WHERE b=3 FOR UPDATE

Since column b is an auxiliary index, the Next-Key Locking algorithm is used at this time, and the locking range is (1,3]. Note that InnoDB also adds a Gap Lock to the next value of the auxiliary index, that is, there is also a lock for the auxiliary index range of (3,6]. Therefore, if the following SQL is run in the new transaction B, it will be blocked:

1. SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE; //S lock 2. INSERT INTO z SELECT 4,2;
3. INSERT INTO z SELECT 6,5;

The first SQL statement cannot be executed because the SQL statement executed in transaction A has already added an X lock to the value of column a=5 in the clustered index, so the execution will be blocked.

The second SQL statement inserts 4 into the primary key. There is no problem. However, the inserted secondary index value 2 is in the locked range (1,3], so the execution is also blocked.

In the third SQL, the inserted primary key 6 is not locked, and 5 is not in the range (1,3]. However, the inserted b column value 5 is in the next Gap Lock range (3,6), so it also needs to wait.

The following SQL statement will not be blocked because it is not within the Next-Key Lock and Gap Lock range and can be executed immediately:

INSERT INTO z SELECT 8,6;
INSERT INTO z SELECT 2,0;
INSERT INTO z SELECT 6,7;

From the above example, we can see that the purpose of Gap Lock is to organize multiple transactions to insert data into the same range, which will lead to phantom problems. In the example, transaction A has locked the record with b=3. If there is no Gap Lock (3,6] at this time, other transactions can insert records with index b column 3, which will cause the user in transaction A to return different records when executing the same query again, resulting in the occurrence of phantom reads.

Users can also explicitly turn off Gap Lock in the following two ways (but not recommended):

• Set the transaction isolation level to READ COMMITED
• Set the parameter innodb_locks_unsafe_for_binlog to 1

In InnoDB, for Insert operations, it will check whether the next record to be inserted is locked. If it is locked, insertion is not allowed. In the above example, transaction A has locked the record with b=3 in table z, that is, it has locked the range (1,3). At this time, if the following insert is executed in other transactions, it will also cause blocking:

INSERT INTO z SELECT 2,0

Because when inserting a record with a value of 2 on the auxiliary index column b, it will be detected that the next record 3 has been indexed. After modifying the value of column b, it can be executed

INSERT INTO z SELECT 2,0

Phantom Problem

Phantom read means that executing the same SQL statement twice in a row under the same transaction may result in different results. The second SQL statement may return rows that did not exist before.

Under the default transaction isolation level (REPEATABLE READ), the InnoDB storage engine uses the Next-Key Locking mechanism to avoid phantom read problems.

Composite (joint) primary key and lock

The above introduction to the lock mechanism (excerpted from "MySQL Technology Insider InnoDB Storage Engine 2nd Edition") is only for auxiliary indexes and clustered indexes, so what is the manifestation of composite primary key downward locks? I didn’t find the answer in the book, so I tried it out in practice.

First create a table with a composite primary key

CREATE TABLE `composite_primary_lock_test` (
 `id1` int(255) NOT NULL,
 `id2` int(255) NOT NULL,
 PRIMARY KEY (`id1`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (10, 10);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 8);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 6);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 6);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 3);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 1);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 1);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (7, 1);

Transaction A first queries the column with id2=6 and adds a row lock

select * from composite_primary_lock_test where id2 = 6 lock in share mode

Will the lock be downgraded to Record Lock at this time? Transaction B updates a piece of data in the Next-Key Lock range (id1=1,id2=8). Prove this:

UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;

The result is that UPDATE is blocked, so let's try to add both primary keys in the where statement when locking:

select * from composite_primary_lock_test where id2 = 6 and id1 = 5 lock in share mode

Execute UPDATE

UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;

The result is that UPDATE is not blocked

The data with id2=6 locked above has more than one record. Then try to lock the unique data with id2=8 based on only one primary key. Will it be downgraded to a row-level lock?

select * from composite_primary_lock_test where id2 = 8 lock in share mode;

UPDATE `composite_primary_lock_test` SE WHERE `id1` = 12 AND `id2` = 10;

The result is also blocked, and the experiment proves that:

Under a composite primary key, if all primary keys are not included when locking, InnoDB will use the Next-Key Locking algorithm. If all primary keys are included, it will be treated as a unique index and downgraded to Record Lock, which only locks the current record.

Multi-column indexes (joint indexes) and locks

The above only verifies the locking mechanism under the composite primary key. What about multi-column indexes? Will it be the same as the composite index mechanism? What about multi-column unique indexes?

Create a new test table and initialize the data

CREATE TABLE `multiple_idx_lock_test` (
 `id` int(255) NOT NULL,
 `idx1` int(255) NOT NULL,
 `idx2` int(255) DEFAULT NULL,
 PRIMARY KEY (`id`,`idx1`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `multiple_idx_lock_test` 
ADD UNIQUE INDEX `idx_multi`(`idx1`, `idx2`) USING BTREE;

INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (1, 1, 1);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (5, 2, 2);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (7, 3, 3);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (4, 4, 4);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (2, 4, 5);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (3, 5, 5);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (8, 6, 5);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (6, 6, 6);

Transaction A adds an S lock to the query. Only the idx1 column is used during the query and the leftmost principle is followed:

select * from multiple_idx_lock_test where idx1 = 6 lock in share mode;

Now insert a piece of data within the Next-Key Lock range:

INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);

The result is blocked. Try again to lock all fields in the multi-column index:

select * from multiple_idx_lock_test where idx1 = 6 and idx2 = 6 lock in share mode;

Insert a piece of data into the Next-Key Lock range:

INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);

The result is that there is no blocking

It can be seen from this that when using a multi-column unique index, locking requires specifying the rows to be locked (that is, all columns of the index used when locking). Only then will InnoDB consider the record to be a unique value and the lock will be downgraded to Record Lock. Otherwise, the Next-Key Lock algorithm will be used to lock the data within the range.

Summarize

Be careful when using locks in MySQL, especially when updating/deleting data. Try to use primary key updates. If updating under a composite primary key table, be sure to update through all primary keys to avoid deadlocks and other problems caused by a larger lock range.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.

refer to

"Mysql Technology Insider InnoDB Storage Engine 2nd Edition" - Jiang Chengyao