Re: Inexplicable Deadlock (show InnoDB status)

[James Day]

Looks as though 2 holds an X lock on the record while 1 holds the insert intention lock. 2 can't then get the insert intention lock it needs to proceed. 1 can't continue past insert intention because 2 holds the X with a previous change. Deadlock, since neither can proceed.

Both 1 and 2 have a fair number of lock structs already used, indicating that each holds quite a few locks. See if you can make the transactions smaller so that les locks are involved. That'll reduce the potential for one to hold a lock needed by the other.

Your application should understand this and retry if it gets a deadlock result. Note that handling of deadlocks has been changing and it's vital that you read the manual to determine exactly how much is rolled back, whether it's the single statement or the whole transaction. You can force the old single statement behavior in versions that now roll back the whole transaction.

If it's a chronic problem and you don't want to retry you might consider serialising the changes using GET_LOCK( ... ). It' particularly useful if the transactions are fairly slow because they are doing a lot of work, since it can leave each free to get its work done quickly without competing with the others for resources like disk seeks. The result in this case can be an increase in throughput.

Keeping transactions as short and fast as possible becomes vital at high loads. Move everything possible out of the part of a transaction between the first time it modifies anything and the time when it commits.

James Day, Support Engineer, MySQL AB