MySQL Bugs: #29452: Falcon: two-way deadlock with unique index and trigger

Bug #29452	Falcon: two-way deadlock with unique index and trigger
Submitted:	29 Jun 2007 21:13	Modified:	3 Dec 2007 13:14
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:	Linux (SUSE 10 64-bit)
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
I create a Falcon table with a single column.
I create a unique index on the column.
I create a trigger that inserts into a MyISAM table.
I insert in a way that causes a deadlock.
I get a deadlock.

So far, this is a variant of
Bug#29206 Falcon: Two-way deadlock with unique index
which is fixed. The main difference is the trigger.
The "how to repeat" is precisely the same as for
Bug#29376 Falcon: two-way deadlock and crash with unique index and trigger
but the crash no longer occurs.

If I used engine=innodb instead of engine=falcon, I would get:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

How to repeat:
Start two connections. Call them T1 and T2.
Default storage engine is MyISAM.

On T1, say:
create table i4 (i varchar(5)) engine=falcon;
create unique index i on i4 (i);
create table i5 (s1 int);
create trigger i4_bi before insert on i4 for each row insert into i5 values (1);
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 */

Thank you for the bug report. Verified as described:

c:\dev\6.0>bin\mysql -uroot --prompt="T2>"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.1-alpha-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

T2>use test
Database changed
T2>set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

T2>insert into i4 values ('j');
Query OK, 1 row affected (0.00 sec)

T2>insert into i4 values ('I'); /* waits for #1 */

I will take a look at this.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35876

ChangeSet@1.2634, 2007-10-19 01:17:21+02:00, hakank@lu0011.(none) +3 -0
  Added test case for Bug #29452.

The lock wait timeout is one of the features not yet implemented in Falcon.

Now that Falcon has a Wait Lock Timeout, thanks to
Jim, this test works just like InnoDB in that the 
deadlock is prevented by timing out of it.
I added a testcase to show this, with a Wait Lock
of 2 seconds.  Note that the default wait lock timeout 
for Falcon is 0, or no timeout.

falcon_bug_29452 passes now:

=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

falcon.falcon_bug_29452        [ pass ]           2062
-------------------------------------------------------
Stopping All Servers
All 1 tests were successful.
The servers were restarted 1 times
Spent 2.062 of 4 seconds executing testcases

Pushed into 6.0.4-alpha

A note has been added to the 6.0.4 changelog:

Using two simultaneous connections it was possible to create a deadlock situation between two different active transactions on the same Falcon table. There is no way to prevent this, but a new parameter, falcon_lock_timeout can set the timeout for deadlocked transactions. The default timeout is 0 (timeout are disabled).