Re: Deadlocks on inserts to the same table

[Radu Chiriac]

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?

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `testdb/category` trx id 0 39964 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)

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"