MySQL Bugs: #29206: Falcon: Two-way deadlock with unique index

Bug #29206	Falcon: Two-way deadlock with unique index
Submitted:	19 Jun 2007 11:42	Modified:	2 Jul 2007 4:50
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.1-alpha-debug	OS:	Any (SUSE 10 64-bit)
Assigned to:	Ann Harrison	CPU Architecture:	Any

Description:
I create a Falcon table with a single column.
I create a unique index on the column.
I insert in a way that causes a deadlock.
I tried control-c and mysqladmin/shutdown, it fails.
I have to say 'kill -9' to get out.

The "how to repeat" is remarkably similar to an old closed bug:
Bug #22850 Falcon: duplicate-key error when deadlock-error expected

ChangeSet@1.2574, 2007-06-15 22:27:38+02:00

How to repeat:
Start two connections. Call them T1 and T2.

On T1, say:
create table i4 (i varchar(5)) engine=falcon;
create unique index i on i4 (i);
set @@autocommit=0;
insert into i4 values ('I');

On T2, say:
set @@autocommit=0;
insert into i4 values ('j');

On T1, say:
insert into i4 values ('j'); /* waits for #2 */

On T2, say:
insert into i4 values ('I'); /* waits for #1 */

Missing test in checkUniqueIndexes for a wait/deadlock

Now the last statement on T2 gets

[23:49] root@test>insert into i4 values ('I');
ERROR 1296 (HY000): Got error 210 'deadlock on key I4$0 in table TEST.I4' from Falcon
6.0.1-alpha-debug

A note has been added to the 6.0.1 changelog: 

Inserting into a table with a unique index simultaneously on two connections in a way that would cause a deadlock would cause MySQL to hang. The deadlock situation is now identified and an error will be raised. (Bug#29206)