RE: [fw-db] Zend_Db Best Practices for Handling Deadlocks

Hi Bill,

 

Thanks for the reply.  I really appreciate you describing when deadlocks happen. 

 

I wasn’t sure how much InnoDB specific issues would be appropriate to raise in this Zend Framework discussion forum.  So, yes, I didn’t sharing the whole story.  I’d be more than happy to share InnoDB status deadlock info as well as sample SQL in this forum if appropriate. 

 

To be sure, I am not asking for exclusive locks in any of my DML or SQL.  InnoDB (of the MySQL 5.1.42-community non plugin variety) is negotiating this on its own.  When I put this question to the InnoDB forum a while back (link below), for the most part I was told: 1) handle this in your code  2) partition.   #1 brings me here.  But, it sounds like I’m looking in the wrong direction.

 

Thanks,

 

Mike

 

 

 

 

From: Bill Karwin [mailto:bill@karwin.com]
Sent: July-09-10 2:41 PM
To: fw-db@lists.zend.com
Subject: Re: [fw-db] Zend_Db Best Practices for Handling Deadlocks

 

 

On Jul 9, 2010, at 9:34 AM, Caplan, Michael wrote:

 

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

 

A DELETE should not block a simple SELECT unless the SELECT is trying to get a lock itself, for example if you use SELECT ... LOCK IN SHARE MODE.  

 

If the SELECT ... LOCK IN SHARE MODE executes first, then the DELETE will hang pending the termination of the SELECT's transaction.  But even then, it wouldn't cause the deadlock error.

 

What would cause the deadlock is when the SELECT's transaction also tries to get an exclusive lock while the DELETE's transaction is hanging.

 

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

 

This should not deadlock either.  An insert only locks the row it's inserting.  Other rows may be inserted concurrently.

 

Are you sure you're telling us the whole story?  

 

Regards,

Bill Karwin

   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.