Some hints for minimizing deadlocks: Update tables in the same order anywhere they are updated.
Use a low isolation level Keep transactions as short as possible, in a single batch if possible. Avoid doing a lot in a transaction that would cause it to wait in the middle or something. Deadlocks can't be avoided completely, just minimized. Its basically a problem when one item is locked and is waiting for another item to become unlocked. The first update is waiting on a certain table/row to become unlocked and the second one is waiting on the table the first update already has locked and vice versa. So they both get into a grizzly battle and neither lets go of their locks, SQL has to kill someone to stop the feud. So one of your updates gets killed :) Also make sure your tables have indexes that are relevant to the update created. (Thanks Daryl) These were just tricks I found useful. We had to get rid of deadlocks in our application a long while back, I have not seen them in quite some time. I shortened my transactions and I also reordered several update statements so they were all accessing objects in the same order. It seems to have done the trick. Hunting down the deadlocks can be a real pain since you can't stop it completely. Thanks Jeremy Allen elliptIQ Inc. -----Original Message----- From: Dave Babbitt [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 16, 2001 4:33 PM To: CF-Talk Subject: How to prevent Serialization failures? Hi Guys! Does anybody know how to prevent deadlocks? I have a lock around every query to the table I use for clocking in and out of, whether inserts, updates, or deletes. Occasionally, though, I get these: ODBC Error Code = 40001 (Serialization failure) [Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction (process ID #10) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction. Any help would be greatly appreciated. Dave ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
