Re: Deadlocks on inserts to the same table

[Radu Chiriac]

hmm... i thought in this case a duplicate key error should be generated (which i was expecting) instead of a deadlock.. Thank u, i understand now where this deadlock comes from... and understanding this can think of some ways to handle it

So, the order of events is something like this:

__tx1_____________________________tx2
not started________________________begin;
_________________________________get auto-inc lock
_________________________________get index lock for a unique (client_id, client_category_id)
____________________________________________combination (let this combination be (1, '1-1')
_________________________________insert record with uq index value (1, '1-1')
_________________________________... some other similar inserts....
_________________________________... still active
begin;____________________________... still active
get auto-inc lock____________________... still active
try to get lock on uq idx value (1, '1-1')__try to get auto-inc lock (holding the prev locks)
___________________...... deadlock ......
__________________..... rollback tx1 .......

Thank u, Heikki!