"Nikita Tovstoles" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
