CODEWITHSUNDEEP
mysqlinnodbdeadlock
Marius
Marius

Reputation: 4016

Repeating transactions after a deadlock in InnoDB

I'm a little confused on how to handle deadlocks in innodb. Here's acommon scenario i've found online:

while (some deadlock condition)
{
    try {
        begin transaction
        query 1
        query 2 // let's assume this one failed with a deadlock
        ...
        query N
        commit
    }
    catch {
        make sure it's a deadlock AND
        rollback
    }   
}

Upvotes: 2

Views: 1383

Answers (1)

RandomSeed
RandomSeed

Reputation: 29809

Answer 1:

A transaction deadlock causes InnoDB to roll back the entire transaction. Retry the whole transaction when this happens.

Answer 2:

No if the transaction isolation level is lower than SERIALIZABLE (or if run outside of a transaction):

SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE.

Yes if SERIALIZABLE:

For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters.

Remember that

Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

A SELECT may also just time-out, waiting for a lock from another transaction (e.g. a table-lock).

Upvotes: 1

Related Questions