February 20, 2009 at 11:30 am
OK. Got it. Thank you.
(there was a lapse in your previous post)
May 4, 2009 at 12:48 am
Good Article,simple and easy to read.
The link of upstairs reply is also usefully !!
October 22, 2009 at 2:27 pm
The claim "that a deadlock in and of itself is not necessarily a problem" and suggestion that "A properly designed and coded application ... can intercept the 1205 error and resubmit the deadlock victim request back to SQL Server" is forgetting a few basic things. Imagine, for example, if all client applications to any server they talk to had to retry their requests all the time - like, say, your web browser to any web server? What was rather meant, I hope, is that a properly designed database would never get you into unexpected deadlocks.
October 22, 2009 at 2:36 pm
sanokistoka (10/22/2009)
The claim "that a deadlock in and of itself is not necessarily a problem" and suggestion that "A properly designed and coded application ... can intercept the 1205 error and resubmit the deadlock victim request back to SQL Server" is forgetting a few basic things. Imagine, for example, if all client applications to any server they talk to had to retry their requests all the time - like, say, your web browser to any web server? What was rather meant, I hope, is that a properly designed database would never get you into unexpected deadlocks.
Nope, I said what I meant, and I meant what I said there.
A properly designed application would have handling in place to deal with a deadlock occurence. Even in the worst instance of deadlocking I have dealt with while consulting, and deadlocks were occuring roughly every 3-5 seconds, this was a very small portion of the overall workload that was actually being submitted. It's not that every request has to be retried, or that they have to retry all the time. The point being made is that the database transaction problem of a deadlock shouldn't break the application on the front end. That is bad application side design coupled with potentially problemattic database design as well.
These days not all deadlocks are preventable. Communication Buffer deadlocks can occur infrequently that you won't fix because the lock is not on a resource you can control or change. Intraquery parallelism deadlocks are also becoming more commonplace due to the reduction of cost for multi-core multi-processor hardware. Those aren't database design problems, they are configuration problems. A properly designed database on a improperly configured SQL Server can most definately deadlock unexpectedly. Which is why the application should be coded to handle that.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
October 22, 2009 at 2:49 pm
Have you tried Oracle, which doesn't exhibit, say SELECT/UPDATE deadlocks because of row versioning? Thankfully, there is now row versioning in MSS. The point being is that, yes an application must be coded to handled deadlocks it expects *where* it expects them, but MSS is causing an egregious number of unexpected deadlocks, and I wonder if that's due to page locking and/or lock escalation policies. What's your experience with Oracle and how does it compare with MSS when it comes to unexpected deadlocks?
October 22, 2009 at 4:53 pm
sanokistoka (10/22/2009)
Have you tried Oracle, which doesn't exhibit, say SELECT/UPDATE deadlocks because of row versioning? Thankfully, there is now row versioning in MSS. The point being is that, yes an application must be coded to handled deadlocks it expects *where* it expects them, but MSS is causing an egregious number of unexpected deadlocks, and I wonder if that's due to page locking and/or lock escalation policies. What's your experience with Oracle and how does it compare with MSS when it comes to unexpected deadlocks?
I don't know that SQL Server has and "egregious" number of deadlocks. I'm involved with managing 250 different applications and only a few of them experience deadlock occurrences and on those we know that it's due to poor structure, poor code, or both.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 22, 2009 at 5:25 pm
sanokistoka (10/22/2009)
Have you tried Oracle, which doesn't exhibit, say SELECT/UPDATE deadlocks because of row versioning?
My companies primary DBMS is Oracle. SQL Server is just a supporting application database server and website/ecommerce database server. There are fundamental differences in concurrency models between Oracle and SQL beyond the row versioning. It is because of these differences that you can get a full consistent hot backup of SQL Server, but you can't do the same with Oracle. Generally people use exp/imp to pull hot copies of the data from Oracle, or in 10g+ they would use data pump. Remember there are trade offs to everything.
Thankfully, there is now row versioning in MSS.
There certainly is, but is has associated expenses with it, like heavy tempdb usage for the version store. Tempdb is already a bottleneck point for some environments so it might not be best to just switch to row versioning without testing. However, modern SSD's are changing the picture behind this and since Tempdb is not persisted between server restarts, a single FusionIO SSD can be used in most cases to offload Tempdb IO for significant performance gains, which would make row versioning more attractive.
The point being is that, yes an application must be coded to handled deadlocks it expects *where* it expects them, but MSS is causing an egregious number of unexpected deadlocks, and I wonder if that's due to page locking and/or lock escalation policies.
I was discussing this with the other MVP's last weekend at SQL Saturday #21 in Orlando. I would agree that there is something fundamentally different in SQL Server 2005/2008 versus SQL Server 2000 that causes deadlocks to occur with greater frequency. A database that never experiences deadlocks in SQL Server 2000 suddenly has numerous deadlocks when upgraded to SQL Server 2005?? I've seen it quite a few times, and generally a few index updates resolves the problems, but I can't necessarily point out exactly what is so different locking wise that makes it deadlock in 2005/2008. However, fixing the structure problem, eliminates the deadlock so I don't think that SQL Server as a product is necessarily the problem here.
Defensive coding is something that has gone to the wayside in the last 4-6 years. I apply this statement to both database development and application development. I blame bigger/cheaper hardware partially, but I also blame developer laziness/greed as well. If it can generate an exception, it should be handled by the application. Unhandled exceptions shouldn't occur, but this requires up front thought about problem areas, and writing additional code to handle them. This takes extra time which costs more, and brings in the greed part. Why pay for that? What most people don't realize is that Test Driven Development and Defensive Programming pay big dividends long term, especially for user experience.
What's your experience with Oracle and how does it compare with MSS when it comes to unexpected deadlocks?
My experience with Oracle is that developers tend to be more experienced, understand the fundamentals of relational database design, and because of the substantial cost to license an Oracle Instance, you have less "cowboy coders" developing databases and writing database code against Oracle. The result is that you have much better databases from the ground up. You also don't find as many Accidental DBA's managing Oracle as you would SQL, so tuning gets more attention on Oracle than SQL Server. I don't think it is necessarily fair to make a comparison without looking at that aspect of the two environments.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
October 23, 2009 at 7:50 am
Let me enlighten the group a little more. I am sure you have heard lots of people point out that their application works fine on other databases, only to be met with deadlock exceptions when porting to SQL Server (or Sybase). Here's an example of a very egregious case with MSS 2008: two updates from two application threads to the same table but different rows in the same page deadlocking because, according to the trace, MSS was using multiple threads per query; the solution was to provide execution plan guides and specify single-threaded processing of each query (could have achieved the same with SQL hints). For everyone's edification, I am referring to an instance of this behavior here:
So are we to draw the conclusion that this application is actually not designed correctly because it didn't catch this and retry the transaction? Do you expect applications to have to handle this kind of behavior (bug?)? In lieu of retry logic in the application code, do you recommend that engineers review and maintain hundreds of queries in their applications for possibilities of such collisions and provide appropriate SQL hints or plan guides? :hehe: Or would perhaps a better solution be to fix the database engine? I have a feeling there are a lot of people who will disagree with you that a database application is properly designed when it can handle transaction retries everywhere queries are executed (or even in a centralized location in the code) for any type of unexpected deadlock.
Below are excerpts from our trace that show the two spids and different ecids on the same sbid for each spid:
process id=process80f2d708 spid=87 sbid=0 ecid=2 ...
update some_table set column1 = @P0 ...
process id=processf3ae22c8 spid=87 sbid=0 ecid=1 ...
update some_table set column1 = @P0 ...
process id=processf3ae2508 spid=81 sbid=0 ecid=1 ...
update some_table set column1 = @P0 ...
process id=process80f2ddc8 spid=81 sbid=0 ecid=2 ...
update some_table set column1 = @P0 ...
October 23, 2009 at 8:07 am
sanokistoka (10/23/2009)
So are we to draw the conclusion that this application is actually not designed correctly because it didn't catch this and retry the transaction?
It depends on how you want to look at things. If you accept that a deadlock will raise a hard error back to the user or result in a lost transaction completely, then the application doesn't have to handle to exception. However, if you want a robust enterprise class application it should be coded to handle the exception correctly.
Do you expect applications to have to handle this kind of behavior (bug?)? In lieu of retry logic in the application code, do you recommend that engineers review and maintain hundreds of queries in their applications for possibilities of such collisions and provide appropriate SQL hints or plan guides?
It's not a bug. If the application handled the deadlock properly it wouldn't require manual implemenation of hints or guides. Go beyond that and set your DOP settings correctly at the server level and you wouldn't have the problem to begin with.
I have a feeling there are a lot of people who will disagree with you that a database application is properly designed when it can handle transaction retries everywhere queries are executed (or even in a centralized location in the code) for any type of unexpected deadlock.
Two of the top authors on database design with SQL agree with me completely on this issue. It was in fact interaction with Louis Davidson years ago that pointed this fact out to me. You can make this a arguement, debate, whatever; but the fact remains that defensive coding that handles exceptions would remove the impact/problems associated with deadlocks. It sounds to me like you'd rather point the finger at Microsoft and make it their problem, rather than fix your own code to implement exception handling. SQL Server is used in extremely large implementations that don't have problems (MySpace, Godaddy, NASA to name a few). I wonder why they aren't screaming about this deadlock bug??
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
October 23, 2009 at 8:39 am
I agree with you that defensive coding (retry logic everywhere) would address deadlock issues, but this is merely _working around_ someone else's architectural shortcomings. I do continue to disagree with the blanket statement on proper application design, I consider other databases properly architected, so let's just agree to disagree.
On to other things... I can't for the life of me find a web page that describes all sqlstate and error codes for SQL Server. MSDN has one on 2000 but it doesn't describe the error codes at all. Is there a definitive guide online somewhere?
October 23, 2009 at 8:47 am
The Books Online topic would be the most comprehensive location:
Database Engine Events and Errors
You can also query sys.messages to get the information:
select
message_id,
language_id,
severity,
is_event_logged,
text
from sys.messages
where language_id = 1033
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
October 23, 2009 at 8:50 am
Thanks
October 23, 2009 at 8:50 am
1) define "works fine on other platforms"
2) having done that, set MAXDOP to 1 (or more likely properly tune the application with the one or more indexes that should be in place for optimal performance) for your update queries and see if you get intraquery parallelism blocking/deadlocks and if in doing so your app doesn't now "work fine on sql server"
3) I don't know jack shit about the Oracle RDBMS but I can say with certainty that EVERY RDBMS has it's good point AND problems. This particular 'feature' ( 😀 ) of sql server is known and has multiple workarounds - one of which (proper indexing) will both eliminate this problem AND result in much better performance to boot.
4) the moment you say sql server should be retrying transactions such as yours that is deadlocking you are instantly set up for infinite-loop race conditions - which is exactly why Microsoft has never and will never do that. As Jonathan says - it is the DEVELOPER's responsibility to a) minimize the likelyhood that an error will occur with an application and b) to properly handle all appropriate errors that do occur.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 24, 2009 at 8:12 am
"works fine" = applications do not get _unexpected_ deadlocks. And I never said that SQL Server should retry transactions; what I alluded to is that it should be designed properly to AVOID unexpected deadlocks in the first place, like other database servers. The way SQL Server often behaves is nothing but hit-or-miss, for which applications and engineers have to invest a lot of energy to work around these issues - with hits, plan guides, often unnecessary indices, et al - and if they miss something, deal with the consequences. Unless, of course, the code is written defensively, as suggested by the original article, but again, this is just working around someone else's architectural and design limitations, not to mention that every time unexpected deadlocks occur throughput goes down the drain (queries victimized, errors raised and sent to the driver, which in turn creates exceptions that the application catches in order to resubmit the query, ad nauseam).
October 24, 2009 at 8:24 am
sanokistoka (10/24/2009)
"works fine" = applications do not get _unexpected_ deadlocks. And I never said that SQL Server should retry transactions; what I alluded to is that it should be designed properly to AVOID unexpected deadlocks in the first place, like other database servers. The way SQL Server often behaves is nothing but hit-or-miss, for which applications and engineers have to invest a lot of energy to work around these issues - with hits, plan guides, often unnecessary indices, et al - and if they miss something, deal with the consequences. Unless, of course, the code is written defensively, as suggested by the original article, but again, this is just working around someone else's architectural and design limitations, not to mention that every time unexpected deadlocks occur throughput goes down the drain (queries victimized, errors raised and sent to the driver, which in turn creates exceptions that the application catches in order to resubmit the query, ad nauseam).
1) avoiding 'unexpected' deadlocks would require doing an examination aforehand of every DML operation to make sure a deadlock is not in the offing. Clearly not desireable.
2) "The way SQL Server often behaves is nothing but hit-or-miss": absolute poppycock there. It may not behave the way you would like, or even best/optimally, and it certainly (like all major software packages) has bugs, but hit-or-miss is way off the mark.
3) Does Oracle have hints, plan guides? I believe what Jonathan said about Oracle devs (and certainly DBAs) often if not usually being more knowledgeable about the platform they are developing against so they already know how to use the RDBMS to develop applications that avoid issues, especially known ones.
4) in my efforts to resolve deadlocking with indexing I have yet to see a situation where one or more applied indexes that prevented the deadlock situation was not also an index that already should have been in place for proper tuning.
Well, that is enough for me on this topic.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply