Unfortunately there is no silver bullet for the problem of deadlocking...they are a fact of life in multi-user, real-time applications. However, you can do some things to severely limit the chances of them occurring, and for properly handling them when they do.
Deadlocks occurr when Process A locks up a resource while executing, at the same time, Process B fires up and locks another resource. In the course of operating, Process A suddenly needs the resource that Process B has locked, so it waits. Before Process B can finish, it requires a resource locked by Process A. The resulting "deadlock" means neither process will ever end. The database manager resolves the situation by selectively killing one of the processes. Things you can do to help: - Break up large processes into smaller, compartmentalized chunks. The longer a process is running, and the greater number of resources it locks while executing, the better the chances of a deadlock. - Always use <cftransaction> in conjunction with your compartments to ensure proper rollback in case of an error. - Trap deadlock errors using try...catch, display a user friendly error to the user (perhaps give them another chance to fire off the process). - Depending on the database, you may be able to do some tweeks with how the DB sets its locks (ie row locking vs. table locking). I'm not an expert at this stuff, but i think it can be something to look into. Hope that helps a little, Brian ----- Original Message ----- From: "Ryan Duckworth" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, October 20, 2004 11:01 AM Subject: SQL Deadlock > Does anyone have any ideas on how to prevent/handle database deadlock? > > Error Executing Database Query. [Macromedia][SQLServer JDBC > Driver][SQLServer]Transaction (Process ID 132) was deadlocked on lock > resources with another process and has been chosen as the deadlock > victim. Rerun the transaction. > > We get a message similar to the one above about once a week. > > My guess is that the deadlock occurred b/c the same row was attempting > to be read and written to at the same time. > > > Ryan Duckworth > Macromedia ColdFusion Certified Professional > Uhlig Communications > 10983 Granada Lane > Overland Park, KS 66211 > (913) 754-4272 > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=34 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182055 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
