MySQL transaction gotchas

Question

I want to use MySQL transactions in a project, but having not used them before, I thought I'd read about any potential concerns. I found an article at http://mysqldatabaseadministration.blogspot.com/2007/04/innodb-performance-optimization.html which suggests that deadlocks are common, and your application should be able to deal with them.

What does 'deadlock' refer to in this context? As I understand it, transactions don't lock the database, so it can't become locked up. How would I go about dealing with such a problem.

Are there any other concerns I should watch out for?

Answer 1

A deadlock can happen when transaction 1 acquires (for writing) resource A and in the meantime transaction 2 acquires resource B.

Now imagine that transaction 2 wants resource A, but is busy and so have to wait, and transaction tries to acquire resource B, and finds it busy too. You've a circular wait, a deadlock. Transaction 1 is waiting for a resource that transaction 2 holds and transaction 2 is waiting for a resource of transaction 1. Neither transaction can proceed. This is the simplest example, the chain can be longer.

MySQL's InnoDB detects deadlocks and kills one of the transactions, making it to fail.

What you can do to avoid the problem is:

• simply retry the transaction, for example you can retry 3 times (tested in high concurrency environments, works like a charm)
• acquire the resources in the same order. If transaction 1 and 2 require first resource A and then B, in the same order, the problem couldn't happen

Answer 2

Transactions indeed lock the database. And if two transactions are waiting for each other you got a deadlock.

Transaction 1:

lock table1;
lock table2;

Transaction 2:

 lock table2;
 lock table1;

If now both transactions have done their first step they wait for each other. You have to avoid such sequences.