MySQL Bugs: #99095: The n bits which transaction waiting for X lock is already locked by itself

Bug #99095	The n bits which transaction waiting for X lock is already locked by itself
Submitted:	27 Mar 2020 5:39	Modified:	30 Mar 2020 23:41
Reporter:	lao wei lao	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.24-log	OS:	CentOS (7.4.1708)
Assigned to:		CPU Architecture:	x86
Tags:	deadlock select for update

Description:
All sessions select for update twice consecutively, the only different is where conditions, but they search samed index-field value and lock same n bits of the index.
Deadlock occured when Session 1 do the first select for update and try lock n bits which locked by Session 2, Session 2 do the second select for update and try lock the n bits which already locked by itself, mysql 5.7.24-log took it as deadlock and rollback Session 2.

How to repeat:
1. prepare
create table test_table(
  id bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT,
  seqno varchar(30) COLLATE utf8mb4_bin NOT NULL,
  name varchar(40) COLLATE utf8mb4_bin NOT NULL,
  type varchar(1) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (id),
  KEY idx_test_seqno (seqno) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5391 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Insert records(at least 14 records):
insert into test_table(seqno, name, type) values('1', 'A', '0');
insert into test_table(seqno, name, type) values('1', 'A', '1');

insert into test_table(seqno, name, type) values('1', 'B', '0');
insert into test_table(seqno, name, type) values('1', 'B', '1');

insert into test_table(seqno, name, type) values('1', 'C', '0');
insert into test_table(seqno, name, type) values('1', 'C', '1');
...
insert into test_table(seqno, name, type) values('1', 'G', '0');
insert into test_table(seqno, name, type) values('1', 'G', '1');

2. deadlock ( but i can't reproduce )
All sessions select for update twice consecutively.
First:
select id, seqno, name, type from test_table where seqno = ? and name = ? and type = '0' for update;
Second:
select id, seqno, name, type from test_table where seqno = ? and name = ? and type = '1' for update;

2.1 Session 1 try the first select for update and success finally
TRANSACTION 1, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1136, 51 row lock(s)
MySQL thread id 1, OS thread handle 1, query id 1 IP-1 db-user Sending data
select id, seqno, name, type from test_table where seqno = '1' and name = 'B' and type = '0' for update;
InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORDS LOCKS space id 828 page no 65 n bits 416 index idx_test_seqno of table test_table trx id 1 lock_mode X locks rec but not gap waiting

2.2 session 2 try the second select for update and rollback finally
first select for update already done:
select id, seqno, name, type from test_table where seqno = '1' and name = 'C' and type = '0' for update;

try the second select for update:
TRANSACTION 2, ACTIVE 0 sec fetching rows, threads declared inside InnoDB 4992
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 51 row lock(s)
MySQL thread id 2, OS thread handle 2, query id 2 ip2 db-user Sending data
select id, seqno, name, type from test_table where seqno = '1' and name = 'C' and type = '1' for update;
InnoDB: *** (2) HOLDS THE LOCK(S):
RECORDS LOCKS space id 828 page no 65 n bits 416 index idx_test_seqno of table test_table trx id 2 lock_mode X locks rec but not gap
InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORDS LOCKS space id 828 page no 65 n bits 416 index idx_test_seqno of table test_table trx id 2 lock_mode X locks rec but not gap waiting

InnoDB: *** WE ROLL BACK TRANSACTION (2)

==========================================================================
The n bits 416 which TRANSACTION 2 waiting for X lock is already locked by itself. Why it wait again?

Suggested fix:
the transaction should not wait again the n bits which is already locked by itself.

Hi Mr. lao,

Thank you for your bug report.

However, this is not a bug.

Simply, you do not have an index over all three columns involved in the WHERE clause, but only on the first column `seqno`. And , as you can see for yourself, the values of the columns in both queries are the same. Hence, this is not a question of the bits of index, but the entire index entry is locked. This is because MySQL has found a best possible path to execute this query, which is by using index. It is not scanning the rows of the table, which would be much slower.

I need to inform you about another aspect. All transactional and ACID storage engines are designed to detect deadlocks. If a deadlock is reported that means that storage engine is working properly, as designed, and hence is not a bug.

Not a bug.

I known that InnoDB will lock the index entry of seqno == '1', but it's so weird that TRANSACTIONS 2 us waiting for X locks which already holded by itself,

InnoDB: *** (2) HOLDS THE LOCK(S):
RECORDS LOCKS space id 828 page no 65 n bits 416 ... <= holded by itselft

InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORDS LOCKS space id 828 page no 65 n bits 416 ... <= why wait it?

I suggest that 'select for update' ignores index entry locks  firstly，waiting for X locks only if the result matchs search condition.

Hi,

What you are asking for is already implemented in 8.0, with SKIP LOCKED directive.

Not a bug.

if you mean 'select for update skip locked', i say no,
i don't want skip locked records which matchs search conditions, i want skip locked records which do not match search conditions.

Hi,

The index entries that do not match are skipped already.

In your case these are index entries that are skipped, because MySQL server was able to resolve the query with index usage only.

HI,

I have analysed your report in further detail.

You have reported it as a bug, and it is not one. However, if you would agree to set severity to S4, then this could be a feature request. This would be a new feature which would force that entire record is checked before locking and not just the index by which it is searched. This would, however, carry with it a speed penalty.

However, if it is fine with you this could be a new feature request.