InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 19:25:42 GMT20http://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxNot that I know of, but its possible that there might be. Without the deadlock graph its hard to know what is occurring behind the scenes leading to the deadlock. I would look first at the two schema's and the list of indexes to see if there is a difference there. Is there a filtered index in 2008? Was an index added?Tue, 21 Dec 2010 08:13:39 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxThanks Jonathan. I was able to see the resources after I saved the xdl file as a txt. The deadlocks happen when executing an SSIS sequence of packages. What is strange is that the identical packages run fine on sql2005 server, but they frequently deadlock in sql2008. The deadlock most often invloves an alter index rebuild all statement and an update. Are there differences between SQL 2005 and 2008 that could potentially cause the deadlocks?ThanksSameeraTue, 21 Dec 2010 07:41:33 GMTsameera33http://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxWithout the graph its really hard to say. Save the deadlock xml as a text file and attach it to your response and it will be easier to help you out.Mon, 20 Dec 2010 13:38:17 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxHi, I captured a deadlock graph in an attempt to resolve a deadlocking issue, but I don't see the resources on the graph, I only see the two oval shapes representing the two SPIDS, with the victim having a big "X" on it. Why is that?ThanksMon, 20 Dec 2010 13:27:56 GMTsameera33http://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxThanks for the additional clarification, Raj.Tue, 08 Jun 2010 11:15:57 GMTkevin77http://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx@kevin77:[quote] it seems to me that the database should be smart enough to analyze the SELECT query and recognize that it cannot acquire all the necessary data using the IX_Trip_startDate_userID index alone and should know and therefore acquire a lock on PK_Trip at the same time.I think the solution of creating a covering index in this scenario isn't a very good one since you'd be duplicating all that data, particularly the 'descriptionText' field.Does anyone have any suggestions or comments? Either on my example scenario or why the database isn't smart enough to acquire the locks that it needs all at the same time. I understand that in order to have a highly performant, highly concurrent system, you need to minimize the use of locks, but your article just surprised me on how seemingly simple it is to generate deadlock in SQL Server by doing something so common and what the database is supposed to be designed to handle, that is, concurrent INSERTs and SELECTs. [/quote]The query still takes row level locks. The scenario shown by Johnathon applies when one attempts to insert and attempts to update the same row ( or group of rows ) and the timing for two conflicting operations is perfect up to some milliseconds. ie the update should occur exactly between query engine applies its locks at NC Index and before it obtains lock on clustered Index. This is very unlikely in reality especially if your indexes are correct. It takes a indefinite loop to simulate the example. So, I wont categorize it as a 'Simple/common' scenario.Btw, at isolation level serializable, the same example it doesn't get into a deadlock as serialzable is lot more pessimistic when it comes to taking Range locks on indexes.Mon, 07 Jun 2010 21:30:36 GMTarr.nagarajhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxI remember where I've seen mention of nHibernate now...Grant Fritchey (aka ScaryDBA) talks about it on his blog:[url=http://scarydba.wordpress.com/2010/04/05/nhibernate-first-look-at-tsql/]http://scarydba.wordpress.com/2010/04/05/nhibernate-first-look-at-tsql/[/url]Which I believe his post is exactly what you maybe seeing.Mon, 07 Jun 2010 11:36:46 GMTShawn Meltonhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx[quote][b]MeltonDBA (6/7/2010)[/b][hr]So to prevent heavy deadlocking they simply add (NO LOCK) on their SELECT queries since they are reading from a few tables that are consistently being updated or inserted into.[/quote]Cool, I'd be fine with that solution in my scenario. Unfortunately, I don't think you can tell NHibernate to add that hint. (Yet another reason not to use ORM crap.) But some day we'll hopefully get rid of NHibernate.Thanks.Mon, 07 Jun 2010 11:19:28 GMTkevin77http://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx[quote][b]kevin77 (6/5/2010)[/b]Does anyone have any suggestions or comments? Either on my example scenario or why the database isn't smart enough to acquire the locks that it needs all at the same time. I understand that in order to have a highly performant, highly concurrent system, you need to minimize the use of locks, but your article just surprised me on how seemingly simple it is to generate deadlock in SQL Server by doing something so common and what the database is supposed to be designed to handle, that is, concurrent INSERTs and SELECTs.[/quote]While I'm not the most experienced when it comes to deadlocks (although reading this article has caused to be become smarter in the subject for some reason:cool:), I do support a database that gets them frequently. Now most of them are short timed deadlocks but every now and then one occurs that will bring the database to a hault. Some instances (well most) are hardware related, which it is on a VM and shouldn't be (which you can check [url=http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Virtualization/default.aspx]Jonathan's blog [/url]on plenty of articles to support this for me;-))However, this application does 10 times more reads than it does writes (updates/inserts/etc), which the major tables have millions of rows of data. So to prevent heavy deadlocking they simply add (NO LOCK) on their SELECT queries since they are reading from a few tables that are consistently being updated or inserted into.Which the software is a BMC product, I leave it to the peanut gallery to guess which one. :hehe:Mon, 07 Jun 2010 06:18:38 GMTShawn Meltonhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxThis is my first time reading the article and although you do state, [quote]The deadlock in this type of scenario is completely timing based, and it can be difficult to reproduce without a looping execution as used in the attached demo.[/quote] The example you give is very simple and I think a very common scenario. Unfortunately I can't agree with the solution. No I'm not talking about the application handling the deadlock exception and retrying. That's been argued enough already and I actually agree that the application should handled the exception (what it wants to do with it is another story).I'm questioning the covered index solution. I know with most things computers, the answer is "it depends". So I'd be curious to get some feedback on the following scenario.We don't have a very busy database in my opinion, but we do experience a lot of deadlocks. Unfortunately the client application uses NHibernate, which I feel is a major part of the problem. But all that aside, let's say we have the following table and requirements:[code="sql"]CREATE TABLE Trip( id int IDENTITY(1,1) NOT NULL, userID int NOT NULL, name varchar(200) NOT NULL CONSTRAINT DF_Trip_name DEFAULT(''), descriptionText varchar(max) NOT NULL CONSTRAINT DF_Trip_summary DEFAULT(''), ulLat float NULL, ulLon float NULL, brLat float NULL, brLon float NULL, created datetime NOT NULL CONSTRAINT DF_Trip_created DEFAULT(getdate()), lastUpdated datetime NOT NULL, startDate datetime NOT NULL, viewCount int NOT NULL CONSTRAINT DF_Trip_viewCount DEFAULT((0)), sourceDevice varchar(50) NOT NULL CONSTRAINT DF_Trip_sourceDevice DEFAULT(''), sourceCarrier varchar(50) NOT NULL CONSTRAINT DF_Trip_sourceCarrier DEFAULT (''), sourceManufacturer varchar(50) NOT NULL CONSTRAINT DF_Trip_sourceManufacturer DEFAULT(''), poiCount int NOT NULL CONSTRAINT DF_Trip_poiCount DEFAULT((0)), mediaCount int NOT NULL CONSTRAINT DF_Trip_mediaCount DEFAULT((0)), startLocationLatitude float NULL, startLocationLongitude float NULL, startLocationQuadKey varchar(50) NOT NULL, CONSTRAINT PK_Trip PRIMARY KEY CLUSTERED ( id ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOCREATE NONCLUSTERED INDEX IX_Trip_startDate_userID ON Trip ( startDate ASC, userID ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY[/code]Many inserts happen on this table and although the 'descriptionText' field is defined as varchar(max), the application limits it to 64KB.Now, let's say that the only SELECT statement that goes against this table is to return a particular user's most recent trips.[code="sql"]SELECT * FROM Trip WHERE startDate > getdate() - 1 AND userID = @userID[/code]The execution plan for that query is, I think, the best it can be. It properly does an Index Seek on the IX_Trip_startDate_userID index to satisfy the WHERE clause, and then does a Key Lookup using the clustered primary key index PK_Trip to get all the data.So given your example, all that has to happen for a deadlock is the SELECT starts and grabs a lock on IX_Trip_startDate_userID, but before it can get a lock for the key lookup, an INSERT starts and grabs the lock on PK_Trip.It seems to me that the database should be smart enough to analyze the SELECT query and recognize that it cannot acquire all the necessary data using the IX_Trip_startDate_userID index alone and should know and therefore acquire a lock on PK_Trip at the same time.I think the solution of creating a covering index in this scenario isn't a very good one since you'd be duplicating all that data, particularly the 'descriptionText' field.Does anyone have any suggestions or comments? Either on my example scenario or why the database isn't smart enough to acquire the locks that it needs all at the same time. I understand that in order to have a highly performant, highly concurrent system, you need to minimize the use of locks, but your article just surprised me on how seemingly simple it is to generate deadlock in SQL Server by doing something so common and what the database is supposed to be designed to handle, that is, concurrent INSERTs and SELECTs.Sat, 05 Jun 2010 16:33:03 GMTkevin77http://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxIt's nice to see good articles like this re-published from time to time.Fri, 04 Jun 2010 15:57:02 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxNice article. Just wanted to point out that you can generate an XDL file from the Profile trace window just by right-clicking the Deadlock Graph event. Choose the Extract Event Data option from the context menu, and a Save As... dialog box pops up allowing you to save the .xdl file for this single event. I use XML Notepad to crack the XML open. (not that I have lots of experience doing this :-))Fri, 04 Jun 2010 11:53:26 GMTstevemchttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx[quote][b]sanokistoka (10/22/2009)[/b][hr]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?[/quote]i forgot the issues with row versioning, but in my experience deadlocks are caused by management issues or poor schema.few years ago our devs changed an app that hit a 200 million row table. at the same time we saw a huge increase in data. they went from single thread to multi-thread. next thing you know we're seeing different threads of the app block each other. i had an idea to change the clustered index so as not be in in accordance with best practices. reason being that the main query of the app selected up to 7 million rows in some cases. with the current index structure it locked every page of the table. we made this change and it fixed all our blocking issues.management is a lot of times people will access data with MS Access and lock the entire table. instead of using replicated data people think they are special and want to access data that is always being modified.Fri, 04 Jun 2010 08:19:31 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxGreat article Jonathon! I look forward to the next SQL Saturday in Tampa so i can sit in our your presentations.Fri, 04 Jun 2010 07:41:41 GMTbbop1322http://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx@maxibeck,check the attachements given at the end of the article.Fri, 04 Jun 2010 01:31:04 GMTarr.nagarajhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxHello everybody.Please explain to me one thing. In the article said that I have to run the script to get execution plan, but in BOL I don`t see any information about procedure "BookMarkLookUpSelect". I have SQL 2005 with SP2 on all of my servers. So I whant to understand when and where I have to run this script.Thanks.Fri, 04 Jun 2010 00:14:35 GMTmaxibekhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx[quote][b]Jonathan Kehayias (2/1/2009)[/b][hr]Comments posted to this topic are about the item [B]<A HREF="/articles/deadlocks/65614/">Using SQL Profiler to Resolve Deadlocks in SQL Server</A>[/B][/quote]well explained and very helpful. Thanks for sharingWed, 10 Mar 2010 15:23:10 GMTBuntyBoyhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx[quote][b]sanokistoka (10/24/2009)[/b][hr]"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).[/quote]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. Sat, 24 Oct 2009 08:24:27 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx"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).Sat, 24 Oct 2009 08:12:03 GMTsanokistokahttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx1) 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' ( :-D ) 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.Fri, 23 Oct 2009 08:50:16 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxThanksFri, 23 Oct 2009 08:50:14 GMTsanokistokahttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxThe Books Online topic would be the most comprehensive location:[url=http://technet.microsoft.com/en-us/library/bb500277.aspx]Database Engine Events and Errors[/url]You can also query sys.messages to get the information:[code="sql"]select message_id, language_id, severity, is_event_logged, textfrom sys.messageswhere language_id = 1033[/code]Fri, 23 Oct 2009 08:47:10 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxI 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?Fri, 23 Oct 2009 08:39:44 GMTsanokistokahttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx[quote][b]sanokistoka (10/23/2009)[/b][hr]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? [/quote]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.[quote] 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? [/quote]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.[quote]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.[/quote]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??Fri, 23 Oct 2009 08:07:37 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxLet 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: [url=http://blogs.msdn.com/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx][/url]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.[size="1"]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 ...[/size]Fri, 23 Oct 2009 07:49:59 GMTsanokistokahttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx[quote][b]sanokistoka (10/22/2009)[/b][hr]Have you tried Oracle, which doesn't exhibit, say SELECT/UPDATE deadlocks because of row versioning? [/quote]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.[quote]Thankfully, there is now row versioning in MSS. [/quote]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.[quote]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.[/quote]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.[quote]What's your experience with Oracle and how does it compare with MSS when it comes to unexpected deadlocks?[/quote]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.Thu, 22 Oct 2009 17:25:23 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx[quote][b]sanokistoka (10/22/2009)[/b][hr]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?[/quote]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.Thu, 22 Oct 2009 16:53:01 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxHave 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?Thu, 22 Oct 2009 14:49:51 GMTsanokistokahttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspx[quote][b]sanokistoka (10/22/2009)[/b][hr]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.[/quote]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.Thu, 22 Oct 2009 14:36:27 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxThe 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.Thu, 22 Oct 2009 14:27:01 GMTsanokistokahttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxGood Article,simple and easy to read.The link of upstairs reply is also usefully !!Mon, 04 May 2009 00:48:37 GMTGerrard8http://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxOK. Got it. Thank you.(there was a lapse in your previous post)Fri, 20 Feb 2009 11:30:14 GMTcutedeveloperhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxRead my response again. Profiler isn't going to give you a graph from SQL Server 2000. That is only applicable to SQL Server 2005/2008.Fri, 20 Feb 2009 11:23:56 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxi am sorry. I just did it but still cannot see graph at the profiler...Fri, 20 Feb 2009 11:20:08 GMTcutedeveloperhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxYou must be connecting to a SQL 2000 server which doesn't have that event. In SQL Server 2000, you have to enable Trace Flag 1204 to get the deadlock graph through the ErrorLog.[code]dbcc traceon(1204, -1)[/code]Fri, 20 Feb 2009 11:11:29 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxGuys, I have a problem to find "Deadlock Graph" event at SQL Profiler. I see Deadlock and DeadLock chain but Deadlock Graph is missing...I have checked SQL 2000 and SQL2005 profiler, both are the same story... Fri, 20 Feb 2009 10:59:48 GMTcutedeveloperhttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxGreat article Jonathan! The article is easy to read, informative, and concise.Great Job.Tue, 03 Feb 2009 08:04:46 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxTook me a minute to overcome the 'owner' and 'request' jargon in the deadlock graph. To me they're all locks and blocks.So an 'owner' arrow could by paraphrased as 'blocked by', and the request arrow is just the lock request?:rolleyes:I'm spoiled with some nice 3rd party tools, but the profiler deadlock graph looks like something I can use.Thanks for the great article.~BOTMon, 02 Feb 2009 13:21:58 GMTSQLBOThttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxThere is actually more than just that link:[url]http://blogs.msdn.com/bartd/archive/tags/SQL+Deadlocks/default.aspx[/url]Bart Duncan has a bunch of them and the above reference is on another article I put in, but I guess I missed adding it to this one.Mon, 02 Feb 2009 12:54:17 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic647806-1365-1.aspxI think any discussion on deadlock troubleshooting should reference this blog series: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspxMon, 02 Feb 2009 12:46:38 GMTTheSQLGuru