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