From: Ian Charnas <icc@ca...> - 2006-09-17 02:06:56
I'm using Turbogears with SQLObject 0.7, and after much poking around
figured out why I was getting a "Lock wait timeout exceeded" deadlock
error when trying to delete a record, and how to avoid the error.
Hopefully the search engines will pick this up, and it will help some
other forlorn programmer out there....
Here's what was happening.. basically I had some code that was
updating the count on all the Line Items in a web shopping cart
system I wrote. Each Line Item was its own object (record) in
SQLObject. So if any of the counts were now at zero, I would then
try to delete that Line Item from the shopping cart (using the
deleteSelf() method). Sounds typical, right? But when I did that, I
got the dreaded "Lock wait timeout exceeded" error.
Eventually I figured out that because I had transactions turned on
(which is the default when using most database adapters for
SQLObject), what was happening was that modifying the 'count' was
placing an "UPDATE" statement into the transaction, and if the count
was now zero I was putting a "DELETE" statement into the transaction
to delete the same record. Although in my mind that should work just
fine -- in the world of MySQL it was causing a deadlock.
So what's the fix? For me the answer was to keep the two things in
separate transactions. First I would update the line items, then I
would call "hub.commit()" to finish the transaction, and then I would
do the delete's. That little "hub.commit()" was all it took!
hope this helps somebody,