[fw-db] Zend_Db Best Practices for Handling Deadlocks

Hi,

 

I’m looking for some advice.  Recently I have been seeing several deadlock scenarios emerging within my MySQL InnoDB database (transaction isolation set to “REPEATABLE-READ”).  I’m looking for some advice on how to best handle these scenarios at the client level via Zend_Db.   Also, should anyone have some sage advice on rectifying these issues at the database level, that would be warmly welcomed too.

 

I’ve seen two deadlock scenarios emerge:

 

1)      Attempt to SELECT content from a database while another process has an exclusive lock during a large DELETE.  This throws a Zend_Db_Statement_Exception: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

2)      Attempt to INSERT content to the database when another process has an exclusive lock during a competing INSERT.  This two throws a Zend_Db_Statement_Exception: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

 

To date, I’ve been letting these exceptions terminate the script, which has been mostly okay as the external process would just retry again.  Going forward, this a problem -- particularly under scenario 1.

 

So, from a Zend_Db client perspective what is to be done?  I can see the following options, but am unsure of the pros/cons of each:

 

1)      Do nothing.  The issue should be solved at the database level.  You’re in the wrong forum.  Go bug the InnoDB folks (IE: http://forums.innodb.com/read.php?4,629,631#msg-631)

2)      For all CRUD transactions, catch exceptions of the type 1213, rinse and repeat.

3)      For all CRUD transactions, catch exceptions of the type 1213, rinse and repeat.  Keep trying multiple times.

4)      For all CRUD transactions, catch exceptions of the type 1213, rinse and repeat.  Keep trying multiple times and inject artificial wait between retries.

5)      For all SELECT operations, do one of the above.

 

Am I missing other options?   Thoughts on the pros / cons of the above?

 

Thanks for your feedback!

 

Mike

   Please consider the environment before printing this email.   E-mail messages may contain viruses, worms, or other malicious code. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective action against such code. Henry Schein is not liable for any loss or damage arising from this message.  The information in this email is confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this e-mail by anyone else is unauthorized.