Re: Avoiding SQL deadlocks
Thank you everybody for your responses to my question below. The issue hasn't been resolved yet but I haven't had much time to work on it either. I wanted to clarify a couple points on my earlier email: 1. I didn't meant to say that the information they gave me on their database "is" wrong, but rather that it "could be" wrong. 2. It's not that they weren't going to give me DB access, but just that since it was day one with a new client I didn't have it yet. If/when we get the situation resolved I'll post what the issue was. Thanks again, - Andrew. > 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. > > 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. 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. > > They have 3 pages with around some code. It appears > that this is the code that's throwing this sort of error: > > "Error","jrpp-717","06/09/10","13:47:16","SomeSiteName","Error > Executing Database Query.[Macromedia][SQLServer JDBC > Driver][SQLServer]Transaction (Process ID 187) was deadlocked on lock > resources with another process and has been chosen as the deadlock > victim. Rerun the transaction. The specific sequence of files included > or processed is: C:\Inetpub\wwwroot\SomeSiteName\index.cfm, line: 270 > " > > 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. > > I tried setting the isolation level to "serializable", thinking that > would bypass the deadlocks. 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? > > Thanks, > - Andrew. ~| 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:334601 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding SQL deadlocks
Check out the articles here: http://stackoverflow.com/questions/661908/sql-server-deadlocks-between-select-update-or-multiple-selects and here: http://blogs.msdn.com/b/bartd/archive/2006/09/25/770928.aspx to troubleshoot and fix different deadlock scenarios. Paul Cormier WebmasteryMadeSimple.com ~| 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:334480 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding SQL deadlocks
> > > In a smaller app where there is only one piece of code that performs the > > > actions that are causing deadlocks, sure. > > > > No, in most CF applications with deadlocks I've seen, regardless of > > size and traffic, the deadlocks have in almost all cases been caused > > by the same script being run concurrently. In fact, thinking back, > > most of those applications were both extremely large and high traffic. > > Deadlocks CAN and DO, in a lot of cases, cause other parts of applications > to generate this error. That may well be the case, I'm just telling you what I've seen. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:334474 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding SQL deadlocks
Deadlocks CAN and DO, in a lot of cases, cause other parts of applications to generate this error. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Thursday, June 10, 2010 2:00 PM To: cf-talk Subject: Re: Avoiding SQL deadlocks > In a smaller app where there is only one piece of code that performs the > actions that are causing deadlocks, sure. No, in most CF applications with deadlocks I've seen, regardless of size and traffic, the deadlocks have in almost all cases been caused by the same script being run concurrently. In fact, thinking back, most of those applications were both extremely large and high traffic. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:334462 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding SQL deadlocks
> In a smaller app where there is only one piece of code that performs the > actions that are causing deadlocks, sure. No, in most CF applications with deadlocks I've seen, regardless of size and traffic, the deadlocks have in almost all cases been caused by the same script being run concurrently. In fact, thinking back, most of those applications were both extremely large and high traffic. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:334458 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding SQL deadlocks
I've found this article on SQL deadlocks extremely helpful. Reducing SQL Server Deadlocks http://www.sql-server-performance.com/tips/deadlocks_p1.aspx Paul Alkema http://paulalkema.com/ -Original Message- From: Mark A. Kruger [mailto:mkru...@cfwebtools.com] Sent: Wednesday, June 09, 2010 11:15 PM To: cf-talk Subject: RE: Avoiding SQL deadlocks Even in a larger application if, for example, you are supported 150k users against a database. Say there is regular uniform logging going on - say search results or whatever. Whether the code is in different files or not if insert update queries are being run against the same tables you may suffer DB performance issues (including deadlocks). It's not wether the code is split - it's how the DB is being used and how much pressure is put on shared resources. Mark A. Kruger, MCSE, CFG (402) 408-3733 ext 105 Skype: markakruger www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Bobby Hartsfield [mailto:bo...@acoderslife.com] Sent: Wednesday, June 09, 2010 10:03 PM To: cf-talk Subject: RE: Avoiding SQL deadlocks In a smaller app where there is only one piece of code that performs the actions that are causing deadlocks, sure. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Wednesday, June 09, 2010 7:28 PM To: cf-talk Subject: Re: Avoiding SQL deadlocks > Since there is no deadlock error until a deadlock exists, the error you > reported is most likely the victim and not the culprit. Of course it could > have caused it then the same page was hit again and reported the error. In most CF applications with deadlocks that I've seen, the same code run concurrently is usually the cause of the deadlock. That is, there's a single script being run by multiple users, and that causes the deadlock. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:334449 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding SQL deadlocks
Even in a larger application if, for example, you are supported 150k users against a database. Say there is regular uniform logging going on - say search results or whatever. Whether the code is in different files or not if insert update queries are being run against the same tables you may suffer DB performance issues (including deadlocks). It's not wether the code is split - it's how the DB is being used and how much pressure is put on shared resources. Mark A. Kruger, MCSE, CFG (402) 408-3733 ext 105 Skype: markakruger www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Bobby Hartsfield [mailto:bo...@acoderslife.com] Sent: Wednesday, June 09, 2010 10:03 PM To: cf-talk Subject: RE: Avoiding SQL deadlocks In a smaller app where there is only one piece of code that performs the actions that are causing deadlocks, sure. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Wednesday, June 09, 2010 7:28 PM To: cf-talk Subject: Re: Avoiding SQL deadlocks > Since there is no deadlock error until a deadlock exists, the error you > reported is most likely the victim and not the culprit. Of course it could > have caused it then the same page was hit again and reported the error. In most CF applications with deadlocks that I've seen, the same code run concurrently is usually the cause of the deadlock. That is, there's a single script being run by multiple users, and that causes the deadlock. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:334445 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding SQL deadlocks
In a smaller app where there is only one piece of code that performs the actions that are causing deadlocks, sure. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Wednesday, June 09, 2010 7:28 PM To: cf-talk Subject: Re: Avoiding SQL deadlocks > Since there is no deadlock error until a deadlock exists, the error you > reported is most likely the victim and not the culprit. Of course it could > have caused it then the same page was hit again and reported the error. In most CF applications with deadlocks that I've seen, the same code run concurrently is usually the cause of the deadlock. That is, there's a single script being run by multiple users, and that causes the deadlock. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:33 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Avoiding SQL deadlocks
> Since there is no deadlock error until a deadlock exists, the error you > reported is most likely the victim and not the culprit. Of course it could > have caused it then the same page was hit again and reported the error. In most CF applications with deadlocks that I've seen, the same code run concurrently is usually the cause of the deadlock. That is, there's a single script being run by multiple users, and that causes the deadlock. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:334442 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding SQL deadlocks
Since there is no deadlock error until a deadlock exists, the error you reported is most likely the victim and not the culprit. Of course it could have caused it then the same page was hit again and reported the error. I'd have to think this would be FAR easier to debug from the DB server. management console, profiler etc. These can be a real pain to track down to say the least. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Andrew Clarke [mailto:s...@clarke.ca] Sent: Wednesday, June 09, 2010 2:39 PM To: cf-talk Subject: Avoiding SQL deadlocks 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. 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. 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. They have 3 pages with around some code. It appears that this is the code that's throwing this sort of error: "Error","jrpp-717","06/09/10","13:47:16","SomeSiteName","Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 187) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. The specific sequence of files included or processed is: C:\Inetpub\wwwroot\SomeSiteName\index.cfm, line: 270 " 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. I tried setting the isolation level to "serializable", thinking that would bypass the deadlocks. 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? Thanks, - Andrew. ~| 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:334441 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Avoiding SQL deadlocks
> 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 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
