Re: Simple deadlock question with InnoDB AUTO INCREMENT

[Harrison Fisk]

Hi,

I belive that your example only works when you do the first insert into a table upon a server restart or table creation. The issue is that InnoDB needs to read the largest value to start the auto_increment counter at. This sets a range lock by the first insert that someone does. Normally an insert with an auto_increment doesn't lock a RANGE on the PRIMARY KEY, just the first time cause of that max(id) thing.

Try your example again, but insert a single row before you begin the first transaction. You will see that no one is blocking (like it should normally work).

If this is a really big issue for you, where you block sometimes at restart, then you could setup a --init-file that inserts and does a rollback in order to intialize the auto_increment counter earlier than your appliation logic starts.

Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com