Re: please help reading DB deadlock notice

[Maciej Dobrzanski]

"Nikita Tovstoles" <nikita@stripped> wrote in message
news:4611F8A4.2030403@ style="color:#666">stripped...

Do both of your SELECT statements use the same index (`token`) when setting
locks on the rows? My guess is that the situation is as follows.

TX2: SELECT ... WHERE id = ... FOR UPDATE
TX2 sets an exclusive lock for a row on PRIMARY index.

TX1: SELECT ... WHERE token = ... FOR UPDATE
TX1 tries to acquire X-lock for a row in `token` index, which succedes. This
however requires also a lock on PRIMARY, but the corresponding row on that
index is already locked by TX2. TX1 waits.

TX2: DELETE ... WHERE id = ...
TX2 tries to delete the row. This of course requires the removal of its
presence in the indxexes, including `token`, but because of the earlier
exclusive lock set by TX1 this cannot be accomplished without waiting for
the lock to be freed. TX2 waits.

Hence, the deadlock.


I'm wondering if granting X-locks for the secondary indexes could not be
delayed until such lock on the primary key is acquired...?

Maciek