Bug #10359 InnoDB in MySQL v5.0.4 doesn't lock auto increment reliably
Submitted: 4 May 2005 16:00 Modified: 18 May 2005 16:06
Reporter: Aron Steg Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.4-beta-standard-log OS:Linux (Linux 2.4.20-021stab022.1.777-sm)
Assigned to: Heikki Tuuri CPU Architecture:Any

[4 May 2005 16:00] Aron Steg

When multiple clients insert into my InnoDB tables at the same time, they are each allocated the same auto-increment number. Then, whichever hits the database second gets rejected with (1194697 is the auto generated id):

PHP Fatal error: Database query failed: Duplicate entry '1194697' for key 1 in /home/pss/httpdocs/system/dblogic.php on line 22

I tested with MyISAM and found no problem. I rolled back to v4.1.11-standard-log with InnoDB and the problem went away.

It doesn't matter what the structure of the table is.

How to repeat:
I run the following script (test.php) multiple times using:

php test.php & php test.php & php test.php

Two of the three fail every time. 


require "dblogic.php";
$db = new DBLogic("localhost", "database", "username", "password");

for ($i=0; $i<10000; $i++) {
                $query = "insert into pss_log (id,transid,opid,queueid,queuecount,logtime,message,level)
                                  values (null, 1, 2, 3, 4, now(), 'this is a medium text field', 5)";


Suggested fix:
Don't know. some notes:

- The server has 2 x HT CPU's (total 4 x CPU's).
- mysql (and mysqld) are running within a Virtual Private Server (by Virtuozzo) with plenty of resources. The cpuinfo of one of the four is below:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 15
model           : 2
model name      : Intel(R) Xeon(TM) CPU 3.06GHz
stepping        : 9
cpu MHz         : 3048.967
cache size      : 512 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 2
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm
bogomips        : 6094.84
[4 May 2005 19:57] Heikki Tuuri

I have to check the auto-inc code in ha_innodb.cc in 5.0. It has been changed since 4.1.

Thank you for the bug report.

[6 May 2005 20:47] Heikki Tuuri

A developer had removed code from ha_innodb.cc, and moved some functionality to an upper layer, to implement auto-inc where the increment is > 1.

We must keep in mind the following: since the generation of auto-inc values must be deterministic for an SQL statement like INSERT INTO t SELECT * FROM s (for the binlog to work), we must keep a resource locked for the duration of the SQL statement. Deadlocks are then possible, and the resource must be protected by a transactional lock, like the AUTO-INC table lock in InnoDB. The resource cannot be protected by a mutex. I will probably need to move functionality from an upper layer back to ha_innodb.cc.


[17 May 2005 19:35] Bugs System
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:

[17 May 2005 19:39] Bugs System
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:

[17 May 2005 19:47] Heikki Tuuri
Fixed in 5.0.6.


[18 May 2005 16:06] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at