MySQL Bugs: #36651: Deadlock - unexpected order locking of tables type InnoDB

Bug #36651	Deadlock - unexpected order locking of tables type InnoDB
Submitted:	11 May 2008 10:24	Modified:	13 May 2008 7:59
Reporter:	Arkadiusz A.	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.32	OS:	Linux (Debian Eth on kernel 2.6.18-5-686)
Assigned to:		CPU Architecture:	Any
Tags:	deadlock, innodb, locking, UPDATE

Description:
When two clients of MySQL connect to the server and the first sets locking on the empty table and then, the second client sets locking on the empty table (locking is set by 'FOR UPDATE' clause) we can observed, that the lock is setting by the second client. 
I expected, that the first client set lock and the second client have to wait for release locking. But I observed that the order is contrary to expectation.
So, such behaviour is cause of Deadlock.

In my opinion a server should set locking on the first client (not in the second) or shouldn't set locking on any table.

How to repeat:
Explain my signing: 
(C1): - means a client of MySQL which make the first connection.
(C2): - means a client of MySQL which make the second connection.

(C1): CREATE TABLE t (col1 INT UNSIGNED NOT NULL PRIMARY KEY) ENGINE = InnoDB;
(C1): START TRANSACTION;
(C1): SELECT col1 FROM t FOR UPDATE;

(C2): START TRANSACTION;
(C2): SELECT col1 FROM t FOR UPDATE;

(C1): INSERT INTO t VALUES(1); - this directive hangs
(C2): INSERT INTO t VALUES(2); - I get a DEADLOCK.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51a at least, and inform about the results.

Thank you for the report.

No need to test additionally.

This is expected behavior described at http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html