Mysql does not prevent other threads from adding a next-key lock to supermum when there is no data in the RR isolation level specified index.

1. Scene repetition

Structural data

Create a test table and index the group_NAME field.

create table t_test
(
  id         char(32)    not null comment 'primary key' primary key,
  group_name varchar(60) not null comment 'group name',
  user_name  varchar(60) null comment 'Group name'.index idx_group (group_name)
);
Copy the code

Simulation of the transaction

Transaction 1 executes SQL:

begin;
SELECT * FROM t_test WHERE group_name = 'xxxx' AND user_name = 'zhangsan' FOR UPDATE;
Copy the code

Transaction 2 executes SQL:

begin;
SELECT * FROM t_test WHERE group_name = 'xxxx' AND user_name = 'zhangsan' FOR UPDATE;
Copy the code

Performance_schema.data_locks: performance_schema.data_locks

As can be seen, transaction 1 [9358831] and transaction 2 [9358842] both hold a next-key lock of supermum pseudo-record, and IX intends to exclude the lock.

This is a bug in the mysql database: bugs.mysql.com/bug.php?id=…

Mysql adds a Gap lock to the (0,supermum pseudo-record) interval, and Gap locks are not mutually exclusive. As a result, both transactions hold a Gap lock in the interval (0,supermum pseudo-record), resulting in a deadlock. The specific and substantial reasons are still to be discussed…

2. Verify the normal scenario

Structural data

Insert a row into t_test table above

INSERT INTO t_test (id, group_name,user_name) VALUES (1.'one'.'lisi');
Copy the code

Simulation of the transaction

Transaction 1 executes SQL:

begin;
SELECT * FROM t_test WHERE group_name = 'one' AND user_name = 'zhangsan' FOR UPDATE;
Copy the code

Transaction 2 executes SQL:

begin;
SELECT * FROM t_test WHERE group_name = 'one' AND user_name = 'zhangsan' FOR UPDATE;
Copy the code

Performance_schema.data_locks: performance_schema.data_locks

As you can see, transaction 1 [9361479] creates two next-key locks in the idx_group index structure, holding all data in the condition group_name=’one’. Add row lock to primary key cluster index (1,’one’,’lisi’) and intentional exclusive lock IX for the whole table.

Transaction 2 [9361488] is blocked by transaction 1 [9361479] because it also needs a next-key lock in idX_group index structure (‘one’,1), and LOCK_STATUS is WAITING.

3. Scope of influence

REPEATABLE READ(RR) this bug applies when Mysql transaction isolation level is REPEATABLE READ(RR).

  1. Execute delete followed by INSERT transactions concurrently.
  2. SQL > select * from UPDATE

In the delete/for UPDATE statement, a next-key range lock is added to the specified index condition. If the specified index condition has no data, the transaction only holds the next-key lock that holds the Supremum pseudo-record. The next-key lock cannot be mutually exclusive with the IX intended exclusive locks of other transactions, resulting in deadlocks and other exceptions in subsequent transactions!

4. Solutions

This bug [25847] has been mentioned in mysql official forums since 2007. It has not been fixed until now, so it is hopeless to fix it.

At present, the solution is to avoid several conditions of starting bug, mainly including three conditions [concurrent transaction], [no data under index condition], [repeatable read (RR) isolation level]

1. Avoid concurrent transactions

Avoiding concurrent transactions can increase the transaction isolation level to the highest level, Serializable, which has no significant impact on small, simple transaction scenarios, but for high frequency, large transaction scenarios have a significant impact on overall database performance. Alternatively, try locking before the transaction starts. The granularity of the lock can be controlled freely (SYNC, distributed lock) to ensure that the specified transactions remain serial between them, which is more controllable than the effect of changing the transaction isolation level.

2. Do not execute delete/for UPDATE statements that will trigger the Gap lock if there is no data in the index condition.

You can avoid delete records that do not exist in the database by performing a select before delete.

In the case of for UPDATE, it is guaranteed that the return result of the SELECT statement must have a record, so that the database will hold at least a next-key lock on that record. In this case, the next-key lock can function properly and be mutually exclusive with IX intended exclusive locks for other transactions.

3. Lower the transaction isolation level to prevent Mysql from adding scope locks

To avoid deadlocks caused by this bug, set transaction isolation level to Read COMMITTED. In this way, Mysql no longer applies Gap locks to the database, but phantom reads will occur.