More and more often I come across a problem of deadlocks in mysql. I like to enclose portions of SQL updates, inserts and deletes in a transaction to ensure the operation is atomic and I don't end up with partially executed action if something goes wrong. Transactions also speed things up a bit. But sometimes a query in a transaction ends up with this error:
Code:
Deadlock found when trying to get lock; try restarting transaction #1213
I've searched information on deadlocks and I know there are certain steps that can be taken to minimize their occurrence like updating in a certain order, etc. However, as the application grows more and more complex it's difficult to guarantee deadlock-free transactions since there are many different functions, methods, modules, stored procedures, etc. that are executed within various transactions and coordinating all those queries so that they do not cause deadlocks in any used scenario seems like an almost impossible task. Moreover, the MySQL manual says that deadlocks are a natural thing in complex databases and the client simply needs to restart the transaction and if this doesn't occur often then this doesn't pose the problem. So I am now at this stage - deadlocks happen rarely but still they do happen and I want to be prepared for that.

So here's my question - how to restart transactions elegantly in PHP?

One thing I have done so far is I have made the query() method in my Db class detect deadlock error #1213 and retry the query up to 3 times. This was easy to implement because I have a central Db class but it does only retry a single query not the whole transaction. And this helps in many (most) cases. However, not always.

When I start transaction, insert a new row and the next query causes deadlock then the new row can be lost and I can't simply retry the second query and continue. I have to start the whole transaction again. Here is some illustration code:

PHP Code:
class Product {
  public function 
save() {
    
$this->db->startTransaction();

    
// INSERT new row to product table
    // ...

    // INSERT a few rows to product_category table
    // ...

    // INSERT a few rows to product_accessory table
    // ...

    // UPDATE a table which holds products indexed for search engine
    // ...

    // DELETE old entries in some other table...
    // ...

    // some other more db operations involving INSERT, UPDATE and DELETE
    // ...

    
$this->db->commit();
  }

Each of the above operations can potentially end up in deadlock and I'd have to start from scratch. What would be the best method to do this? The only solution I can see is to move the code from each method that uses transaction to another method (e.g. saveWithTransaction() ) and in the original method (save() ) use a for loop to run the transaction method and catch any DeadlockExceptions and retry if necessary. However, this seems ugly and I'd have to litter a lot of my code with mechanisms for restarting transactions in places that should not (I think) have to deal with such issues. Any ideas?