MySQL Bugs: #19104: Getting an error inserting primary key with isolation level serializable.

Bug #19104	Getting an error inserting primary key with isolation level serializable.
Submitted:	14 Apr 2006 17:36	Modified:	14 Apr 2006 19:24
Reporter:	Sergei Rogovskiy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15	OS:	MacOS (Mac OS X)
Assigned to:		CPU Architecture:	Any

Description:
I am trying to generate primary key for my table as max(id) + 1 and therefore decided to use transaction isolation level SERIALIZABLE. I am getting some wierd locking error which I don't really understand.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Another thing is mysql behaviour is different in dependence whether there are entries in database or not: SELECT .. FOR UPDATE doesn't block if  there are no entries in database and block if there are.

How to repeat:
Initialize: 
mysql> drop table test;
mysql> create table test (id int primary key, string varchar (32)) type=innodb;
mysql> set global transaction isolation level serializable;

Client #1: 
mysql> begin;
mysql> select max(id) from test for update;
+---------+
| max(id) |
+---------+
|    NULL |
+---------+
1 row in set (0.01 sec)

Client #2: 
mysql> begin;
mysql> select max(id) from test for update;
+---------+
| max(id) |
+---------+
|    NULL |
+---------+
1 row in set (0.01 sec) 

NOTE: Client 2 is not locked but gets result immediately.

Client #1: 
mysql> insert into test values (1, 'client1');
BLOCKS HERE

Client #2:
mysql> insert into test values (1, 'client2');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Client #1:
UNBLOCKS 

if you insert a dummy record before testing results are different:
mysql> insert into test values (1, 'dummy')

Client #1: 
mysql> begin;
mysql> select max(id) from test for update;
+---------+
| max(id) |
+---------+
|    1       |
+---------+
1 row in set (0.01 sec)

Client #2: 
mysql> begin;
mysql> select max(id) from test for update;
BLOCKS

Client #1:
mysql> insert test values (2, 'init');     
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Client #2: 
UNBLOCKS
+---------+
| max(id) |
+---------+
|       1 |
+---------+
1 row in set (4.62 sec)

mysql> insert test values (2, 'init');    
mysql> commit;

Finally:
mysql> select * from test;
+----+--------+
| id | string |
+----+--------+
|  1 | init   |
|  2 | init   |
+----+--------+

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please, read the manual:

http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlock-detection.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html

I hope, it will explain you the behaviour you reported.

What is the point of using SERIALIZABLE isolation level if it fails pretty frequently? I can do retry on fallback with READ UNCOMMITTED as well. Is there any difference ?

Thanks.