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, -Ian ------------------------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 _______________________________________________ sqlobject-discuss mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
