Step 1: read the wiki page John sent. Step 2: Deadlocks take two resources to tango. You need to see what the two resources in question are. Run a trace and filter for deadlocks and lock escalations. That will give you some more information about ground zero at the time of the error.
Are their other queries in that loop that you omitted for us? Some deadlocks happen between two processes fighting over to different tables in your DB, but other deadlocks can be two processes simply fighting over a number of keys in the same index. Depending on how many times that loop runs, a connection can probably rack up a lot of row locks that wants to escalate to a full table lock, but can't. If you are on SQL server, you might try wrapping a transaction around your loop and playing with the tablockx or holdlock locking hints. ~Brad -------- Original Message -------- Subject: sql deadlock From: "Chad Gray" <cg...@careyweb.com> Date: Thu, August 27, 2009 8:46 am To: cf-talk <cf-talk@houseoffusion.com> I occasionally see this error in an application I wrote. Transaction (Process ID 54) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. The code that is causing this is below. <cfloop list="#variables.basicColorList#" index="i"> <cfquery datasource="#application.dsn#" name="deleteColor"> DELETE FROM #variables.table# WHERE #variables.IDField# = #variables.ID# AND ColorName = '#variables.i#' </cfquery> </cfloop> Pretty simple query... How do I keep this SQL from deadlocking? What is a deadlock? Two users hitting the code at the same time and database cant handle all the requests? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325785 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
