MySQL Bugs: #65531: InnoDB DeadLock with all X mode waiting by various non-trans DML

Bug #65531	InnoDB DeadLock with all X mode waiting by various non-trans DML
Submitted:	6 Jun 2012 9:27	Modified:	29 Jan 2013 18:37
Reporter:	wei liu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	deadlock, indexes, innodb, no gap waiting, non-trans, x mode

Description:
*** (1) TRANSACTION:
update tt01 set status = 2 where name <'e';
Waiting for this lock to be granted:
RECORD LOCKS ... index `PRIMARY` of table `tt01` trx id 1D0AB lock_mode X locks rec but not gap waiting.

*** (2) TRANSACTION:
delete from tt01 where id=5 and name = 'e';
Locked:
RECORD LOCKS ... index `PRIMARY` of table `tt01` trx id 1D0A9 lock_mode X locks rec but not gap.
Waiting for this lock to be granted:
RECORD LOCKS ... index `tt01_name` of table `tt01` trx id 1D0A9 lock_mode X locks rec but not gap waiting

T1 is waiting for 'PRIMARY' index lock.   X lock-mode
T2 is locking 'PRIMARY' index lock.       X lock-mode
T2 is waiting for 'tt01_name' index lock. X lock-mode

1. don't the indexes of the table 'tt01' be locked at the same time? 
i.e. Don't the indexes of one table be locked by one sql at the same time, or locked one by one?

2. the all of SQLs of two con-current sessions are non-transaction.
3. according to the explain of bug #1866, it should be lockmode X-S-X deadlock, but this is X-X-X without gap waiting.
4. So by right, there should be only lock wait exists, but no deadlock.
One transaction should get the lock after the other one release the lock one by one.

If it's not a bug, please help to point out why it happened. thanks.

How to repeat:
1. use attachment 1.Tab_Data.sql to create table and insert data.
2. Run both scripts 2.Test_Script01.sql and 3.Test_Script02.sql concurrently.

Suggested fix:
None.

create test table and insert data

Attachment: 1.Tab_Data.sql (application/octet-stream, text), 562 bytes.

con-current sql script 01

Attachment: 2.Test_Script01.sql (application/octet-stream, text), 394.89 KiB.

con-current sql script 02

Attachment: 3.Test_Script02.sql (application/octet-stream, text), 358.47 KiB.

log

Attachment: 4.DeadLock_Log.txt (text/plain), 1.92 KiB.

Any update?

Thank you for the report.

This is not a bug. See at the deadlock output:

*** (1) TRANSACTION:
TRANSACTION 1D0AB, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 320, 10 row lock(s), undo log entries 4
MySQL thread id 14, OS thread handle 0x340, query id 77916 192.168.253.220 root Updating
update tt01 set status = 2 where name <'e'
....
*** (2) TRANSACTION:
TRANSACTION 1D0A9, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 19, OS thread handle 0x6c8, query id 77915 192.168.253.220 root updating
delete from tt01 where id=5 and name = 'e'

In this period of time row with name = 'e' exists and two transactions compete for this very same row. Since index on column name is not unique this can lead to deadlocks.