MySQL Forums
Forum List  »  InnoDB

Re: Deadlocks on inserts to the same table
Posted by: Radu Chiriac
Date: January 12, 2005 01:37AM

Thank you, Heikki

Yes, tx2 is doing a new insert and it has already done several... and yes, the auto-inc lock is only held for the duration of the sql stmt...

But why does tx2 locks the index and only then tries to get the auto-inc lock (which is held by tx1?) while tx1 waits for the index lock (held by tx2) after acquiring the auto-inc lock?

TABLE LOCK table `testdb/category` trx id 0 39964 lock mode AUTO-INC waiting

I do realize that this is probably done to improve speed hoping that the chances of deadlocks are small...
Am i correct asuming that this is happenning because locks are not obtain in the same order (e.g. auto-inc first, then index)? I must mention that i was getting these deadlocks when there was no load on the server - only two threads running 5-6 inserts each every 10 min... which means i'll be getting these deadlocks all the time in a production environment...
I can't drop the unique index... and i need that auto-increment...
Hence, (keeping the READ COMMITTED isolation level), is there any way to avoid these deadlocks? I know the mysql docs say "deadlocks are ok blah blah just handle them", but i've got a feeling that it's very wrong to generate deadlocks in such simple situations... or at least there should be a way to tell "use _this_ order (or a safe order) to obtain locks for these statements - i care less about insert speed than of deadlocks"

Options: ReplyQuote

Written By
Re: Deadlocks on inserts to the same table
January 12, 2005 01:37AM

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.