MySQL Forums
Forum List  »  InnoDB

Re: Simple deadlock question with InnoDB AUTO INCREMENT
Posted by: Harrison Fisk
Date: June 17, 2005 04:13PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Simple deadlock question with InnoDB AUTO INCREMENT
4430
June 17, 2005 04:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.