> My understanding and experience is that a well-architected application should > NEVER experience a SQL deadlock. If I am incorrect in this premise, please > let me know.
It's also my understanding and experience is that a well-architected application should NEVER experience bugs of any kind. :) A statement like that may seem safe since the mere existence of a deadlock (or a bug for that matter) could automatically disqualify it from the well-architected list, but honestly in my experience deadlocks more commonly come from the devil-in-the-details, not over-arching failures in system architecture. > Based on that assumption, I'm trying to diagnose a client's deadlock problem. > They are a new client. I have a copy of their code and a copy of the error, > but don't have access to their SQL Server 2005 database. That kind of sucks. It's like me giving an Altas of everywhere I've driven to my mechanic and asking him to fix my car's occasional knocking noise without actually having the car. Due to deadlocks' evasive nature, I've found the best way to capture them is to duplicate them (preferably a staging environment) and analyze SQL traces to identify the database objects involved. > I'm told the database has no stored procedures, no functions, triggers, or > external jobs running on it, but I suppose that information is wrong. Unless your client has a reason to lie, I would assume that is probably true. > They have 3 pages with <cftransaction> around some code. It appears that this > is the code that's throwing this sort of error: Sounds like a good place to start. What tables are being touched in those transactions and in what order? > The error isn't actually in index.cfm but I can deduce what page it's on as > the site isn't very large and there aren't many queries around line 270 of > any pages. Of course, it takes two to tango as well as deadlock. You need to know what the other process was. Was it also the code on line 270 of mysteryPage.cfm or another file entirely? > I tried setting the isolation level to "serializable", thinking that would > bypass the deadlocks. Where? In your cfquery's? In the cftransaction tag? The default for the database? Either way, I'm not surprised if it didn't help. I have usually resolved most deadlocks by refactoring code in such a way that similar locked objects are always locked in the same order. Serializable isolation is enforced in MS SQL with range locks or table locks to eliminate phantom reads, and will probably have little to do with your situation other than lowering concurrency and therefore performance. > However, they're still happening. If there's nothing else hitting the > databases, and if the queries are simple inserts/updates, "should" there > still be deadlocks? It doesn't take a complex system to create a deadlock if your good... er, unlucky. Your biggest problem now is the unknown. The best you can do with only the CF code is guess. Like I said before, many deadlocks occur when multiple places in your application are modifying the same objects in a different order within a transaction. i.e. page 1 updates records in table a, then table b while page 2 updates records in table b, then table a. Of course, deadlocks don't have to be between tables-- it could be pages in an index. That's why you REALLY need to have them run SQL Profiler and capture deadlocks and their related data. This will tell you the exact resources that in are involved in the error and prevent a lot of guesswork for you. They're really crippling you by asking you to solve this problem without the most critical piece (the database). The execution plan of the offending queries will be invaluable to seeing what is happening. Good luck. ~Brad ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334440 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
