MySQL Bugs: #22851: Falcon: four-way deadlock

Bug #22851	Falcon: four-way deadlock
Submitted:	29 Sep 2006 23:59	Modified:	2 Oct 2006 22:11
Reporter:	Peter Gulutzan	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.1.12-beta-debug	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:		CPU Architecture:	Any

Description:
I have four connections with four interleaved transactions, T1 + T2 + T3 + T4.

After six INSERT and DROP instructions, all the transactions hang,
waiting for each other. I interpret this as an exotic deadlock.

How to repeat:
/* I have to start up each connection before starting transactions. */

On T1, say:
set @@autocommit=0;

On T2, say:
set @@autocommit=0;

On T3, say:
set @@autocommit=0;

On T4, say:
set @@autocommit=0;

On T1, say:
create table tz (s1 int unique) engine=falcon;
insert into tz values (1);

On T2, say:
insert into tz values (2);
insert into tz values (1);        /* hangs */

On T3, say:
drop table tz;                    /* hangs */

On T4, say:
drop table tz;                    /* hangs */

On T1, say:
insert into tz values (2);        /* hangs */

Observe that T1 + T2 + T3 + T4 are all hanging.

The deadlock is in the server, not Falcon.  Thread T2 is properly
waiting for T1 to commit or rollback.  T3 is hanging in the server
(trace below).  T4 is also hanging in the server. 

Note that if you remove the "drop table" transactions, Falcon behaves
properly.

I have marked it closed.  It should be re-designated as a server bug.

This is the trace of the "drop table" transaction that produced the
deadlock:

 	mysqld.exe!wait_for_locked_table_names(THD * thd=0x01a96118, st_table_list * table_list=0x01a9df88)  Line 980 + 0x13	C++
 	mysqld.exe!lock_table_names(THD * thd=0x01a96118, st_table_list * table_list=0x01a9df88)  Line 1022 + 0x13	C++
 	mysqld.exe!mysql_rm_table_part2(THD * thd=0x01a96118, st_table_list * tables=0x01a9df88, int if_exists=0, int drop_temporary=0, int drop_view=0, int dont_log_query=0)  Line 1636 + 0xd	C++
 	mysqld.exe!mysql_rm_table(THD * thd=0x01a96118, st_table_list * tables=0x01a9df88, char if_exists=0, char drop_temporary=0)  Line 1493 + 0x1b	C++
 	mysqld.exe!mysql_execute_command(THD * thd=0x01a96118)  Line 3584 + 0x21	C++
 	mysqld.exe!mysql_parse(THD * thd=0x01a96118, char * inBuf=0x01a9df18, unsigned int length=13)  Line 6062 + 0x9	C++
 	mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x01a96118, char * packet=0x01aad029, unsigned int packet_length=14)  Line 1828 + 0x1d	C++
 	mysqld.exe!do_command(THD * thd=0x01a96118)  Line 1612 + 0x31	C++
 	mysqld.exe!handle_one_connection(void * arg=0x01a96118)  Line 1227 + 0x9	C++
 	mysqld.exe!pthread_start(void * param=0x01a61270)  Line 63 + 0x9	C
 	mysqld.exe!_threadstart(void * ptd=0x01a9dd70)  Line 196 + 0xd	C
 	kernel32.dll!7c80b50b() 	
 	kernel32.dll!7c8399f3()

I made a new bug because this one had too many references to "Falcon" in it.

Falcon didn't "cause" the bug. But Falcon gets the "effect" of the bug.
With MyISAM I can't do the steps in the stated order.
With InnoDB I cause the deadlock the same way but then there is a timeout,
so with InnoDB the bug is not serious.

The new bug is Bug#22876 Four-way deadlock.