MySQL Bugs: #100554: behavior of gap lock differs with data_locks tab after delete and insert record

Bug #100554	behavior of gap lock differs with data_locks tab after delete and insert record
Submitted:	18 Aug 2020 6:16	Modified:	19 Aug 2020 15:49
Reporter:	Brian Yue (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	MySQL8.0.18	OS:	Any (rhel-7.4)
Assigned to:		CPU Architecture:	Any (intel x86)

Description:
Dear Verification Team:

  Create a table with unique secondary index, we firstly delete a record, and insert a record of same primary key (other field changes), a gap is locked but not displayed in performance_schema.data_locks.
  Please check `How to repeat` for detail.

How to repeat:
(1) firstly, create a table with unique secondary index:
cretae database yxx;
use yxx;
create table locktest (id int primary key, num int, age int, unique key(num));
insert into locktest values (1,1,1),(5,5,5),(10,10,10),(15,15,15),(20,20,20);

(2) start a transaction and execute dml commands (dnnot commit the transaction)
begin;
delete from locktest where id = 10;
insert into locktest values (10,10,11);

(3) check the table data_locks:
select INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;

mysql> select INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+------------+-----------+---------------+-------------+-----------+
| INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+------------+-----------+---------------+-------------+-----------+
| NULL       | TABLE     | IX            | GRANTED     | NULL      |
| PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 10        |
| num        | RECORD    | X,REC_NOT_GAP | GRANTED     | 10, 10    |
| num        | RECORD    | S,GAP         | GRANTED     | 10, 10    |
| num        | RECORD    | S             | GRANTED     | 15, 15    |
+------------+-----------+---------------+-------------+-----------+
5 rows in set (0.02 sec)

we can see that there is no (S,GAP) lock for (15,15) record in index `num`,
but if we start a new session and try to insert a record between 10 and 15， it's locked:

[new session]
mysql> insert into locktest values (13,13,13);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

(4)
 so, the gap lock of (10,15) exists, right? but why we cannot see it in performance_schema.data_locks?

Suggested fix:
none

Hi Mr. Yue,

Thank you for your bug report.

However, this is not a bug.

It is better seen from the InnoDB lock status, but Performance_Schema shows sufficient info.

Both records, where your column `num` is 10 and 15 are locked. So, when an INSERT comes and tries to insert between these two records, it can't obtain a gap lock. As simple as that.

Not a bug.

Hello there,
It is perhaps a not optimal "naming convention", but LOCK_MODE should be understood like this:

"S"  => shared lock on both the gap before the record and the record (a.k.a. "Next Key lock")
"S,GAP"  => shared lock on the gap before the record only (a.k.a. "Gap Lock")
"S,REC_NOT_GAP" => shared lock on the record only (a.k.a. "Record Lock")

So, this entry you see:

| num        | RECORD    | S             | GRANTED     | 15, 15    |

is actually equivalent to combination of S,GAP + S,REC_NOT_GAP.

Does it help?

Hello, dear Mr.Lopuszanski and Verification Team,
  Thanks for your reply, I have understood the the meaning of LOCK_MODE now.
  It's helpful for me.
  Thanks a lot, again.