InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 12:47:21 GMT20http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxI found this information really helpful:A not-so-very technical discussion of Multi Version Concurrency Control[url]http://www.ibphoenix.com/main.nfs?page=ibp_mvcc_roman[/url]Tue, 15 Jul 2008 14:58:40 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]GilaMonster (7/15/2008)[/b]Try doing a save-as[url]http://download.microsoft.com/download/f/1/c/f1cf7b8d-7fb9-4b71-a658-e748e67f9eba/RowVersioningBasedIsolation.doc[/url][quote]I've greatly enjoyed this thread, and a belated congratulations, Gail, on being named an MVP![/quote]Thanks. It's turning out to be a very good year.[/quote]The problem was the link displayed defective in my browser; cutting and pasting into the address bar didn't work. Having a clean link solved it! Thanks to both of you for being so responsive!Tue, 15 Jul 2008 13:10:32 GMTsteve smith-401573http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxSteve,I am not sure why that won't work for you, but here is another link:[url]http://msdn.microsoft.com/en-us/library/ms345124.aspx[/url]If that doesn't work, you can google "SQL Server 2005 Row Versioning-Based Transaction Isolation" and it will be the first document.EDIT: I apparently and not smart enough to put the URL tags in correctly. I have fixed my posts. Sorry for the confusion.Tue, 15 Jul 2008 12:18:11 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]steve smith (7/15/2008)[/b][hr]I can't get this to load in my browser. Is there a problem in the way it's cited?[/quote]Try doing a save-as[url]http://download.microsoft.com/download/f/1/c/f1cf7b8d-7fb9-4b71-a658-e748e67f9eba/RowVersioningBasedIsolation.doc[/url][quote]I've greatly enjoyed this thread, and a belated congratulations, Gail, on being named an MVP![/quote]Thanks. It's turning out to be a very good year.Tue, 15 Jul 2008 12:13:45 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]Jonathan Kehayias (7/15/2008)[/b][hr]Kimberly Tripp wrote an excellent WhitePaper on Row Versioning-based Transaction Isolation that I would recommend as a read for this topic:[url=http://download.microsoft.com/download/f/1/c/f1cf7b8d-7fb9-4b71-a658-e748e67f9eba/RowVersioningBasedIsolation.doc][/url]She covers in depth the comparison of this isolation level against Oracle's locking and row versioning methods. It is a very interesting read, and Kimberly always does a good job explaining the complex concepts where necessary.[/quote]I can't get this to load in my browser. Is there a problem in the way it's cited?I've greatly enjoyed this thread, and a belated congratulations, Gail, on being named an MVP!Tue, 15 Jul 2008 12:05:33 GMTsteve smith-401573http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]Jonathan Kehayias (7/15/2008)[/b][hr]The key thing here is the timing of the events. In this kind of deadlock scenario, a few nanoseconds can make the difference between a successful execution of both queries or a deadlock scenario. [/quote]Agreed. That's why I'm surprised to see it happening more than once in a blue moon. Several times a day is rather ... unexpected.[quote]As for the connect idea, I would be interested in seeing what Microsofts Response would be. [/quote]Likewise. They would probably say to use snapshot isolation. Though, it could be that a small change in the order of the locks taken by the update would prevent this from happening at all. At the time the clustered index update occurs, it is known which of the NC indexes need to be updated, so update locks could be taken on those first. The change would also have to be done for inserts and deletes as well.(Note, I do not, in any way, speak for the dev team. It's possible there's something within the engine that makes that proposition impossible)[quote]Kimberly Tripp wrote an excellent WhitePaper on Row Versioning-based Transaction Isolation that I would recommend as a read for this topic:[/quote]I read it a while back. Excellent as always.Tue, 15 Jul 2008 12:02:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]GilaMonster (7/14/2008)[/b]Up until now, I've considered this kind of deadlock theoretically possibel, but not likely to occur in a real system, due to the frequency of queries necessary.[quote]But I did want to put on record the fact that this astonishing design flaw is present in SqlServer, and that the techniques for avoiding it - whether avoiding bookmark lookups by only using covering indexes, or using snapshot isolation, or reverting to Oracle - are simply workarounds. [/quote]Have you logged it on connect? If so, please post the link so I can add comments and a vote.[/quote]Gail,The key thing here is the timing of the events. In this kind of deadlock scenario, a few nanoseconds can make the difference between a successful execution of both queries or a deadlock scenario. I actually have spent days trying to recreate this kind of deadlock with no luck, even using replay traces that contained multiple deadlocks. I swore I had a bug in SQL until I found Bart Duncans blog.As for the connect idea, I would be interested in seeing what Microsofts Response would be. I would think that it will be something to the point that SQL Server uses Pessimistic Locking which is the most common manner of implementing the ANSI standard for transaction isolation, and that READ_COMMITTED_SNAPSHOT can be enabled at the database level in SQL 2005 to remove the blocking caused by SELECT statements.Kimberly Tripp wrote an excellent WhitePaper on Row Versioning-based Transaction Isolation that I would recommend as a read for this topic:[url]http://download.microsoft.com/download/f/1/c/f1cf7b8d-7fb9-4b71-a658-e748e67f9eba/RowVersioningBasedIsolation.doc[/url]She covers in depth the comparison of this isolation level against Oracle's locking and row versioning methods. It is a very interesting read, and Kimberly always does a good job explaining the complex concepts where necessary.Tue, 15 Jul 2008 08:53:50 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxGood thread everyone. I learned a lot.Thanks,Tue, 15 Jul 2008 08:01:42 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]David Griffiths (7/15/2008)[/b][hr]I understand that covering indexes will often give performance benefits. However, I didn't understand that they were a requirement to prevent deadlocks until I discovered this behaviour for myself. [/quote]They are not. However they speed up queries and reduce required locks, both of which will reduce the chance of deadlocks. If you have a single lock on a table (whether NC or clustered index), you cannot cause a deadlock on that table. That requires 2 locks at minimum. With covering indexes, the only thing you need a lock on is the NC index. The update will have to wait for that, but since the select doesn't need a lock on the cluster at all, no deadlock.Unless you're running 2 or more updates within a transaction, that [b]should[/b] fix thingsTue, 15 Jul 2008 04:57:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxJonathan, thanks for the links. Bart Duncan's explanation of the problem is excellent. I wish I'd seen it before !You say 'This isn't what I would call a design flaw.' I suppose it depends on the meaning of 'design flaw' - I would say that if some behaviour is unexpected, undesirable and undocumented, it's a design flaw. (That's what my customers would tell me too). On the other hand I can perfectly understand why Microsoft designed it that way - I'm certainly not trying to imply I could have done a better job of designing SQL Server ! And obviously I don't really think Oracle is better (otherwise we would be using it instead of SS).Gail,I'm sorry, I wasn't trying to pick a fight ! You have been nothing but helpful and constructive - my crack about 'denial' was aimed at earlier posters.I understand that covering indexes will often give performance benefits. However, I didn't understand that they were a requirement to prevent deadlocks until I discovered this behaviour for myself. And I genuinely was astonished when I found out.In fact, even using covering indexes wouldn't enable me to eliminate deadlocks. There are situations when a SELECT which joins two tables conflicts with UPDATEs to those tables. Depending on the execution plan of the SELECT, this may or may not cause a deadlock. How can I fix that situation ? Force a fixed execution plan - possibly an inefficient plan - with hints ? For every SELECT with a join ?In the end, the decision we have taken is to go with a schema which provides good performance, to avoid execution plan hints as far as possible, and to live with a low level of 'benign' deadlocks.No I haven't posted this on Connect. It obviously [b]is[/b] a known problem, and it probably can't be fixed without rewriting SQL Server.Tue, 15 Jul 2008 04:39:07 GMTDavid Griffiths-273839http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]David Griffiths (7/14/2008)[/b][hr]You state that using a non-covering index forces bookmark look-ups, and that these are the root of the problem. That is precisely correct, and that is precisely what I am complaining about.Surely it cannot be necessary to design all indexes to cover their base tables so that no bookmark lookups take place ? What is that going to do to the database performance ? [/quote]Improve it dramatically. Bookmark lookups are expensive operations. Very expensive operations since they are done one row at a time. If you force a bookmark lookup for 200 riows, that means 200 seeks to the base table (cluster or heap). See [url=http://sqlblog.com/blogs/linchi_shea/archive/2008/07/06/performance-impact-bookmark-lookup-is-expensive-even-in-memory.aspx]Linchi Shea's article[/url] that was linked in this week's database weeklyIt is not necessary to cover all queries, but every performance tuning books will tell you to cover the frequently running or very important ones.To quote Kimberly Tripp "Covering indexes are as close to a silver bullet as there is in performance tuning"I'm not saying it will resolve your issue, but there's a good chance that it will.How long do your selects currently take to run?[quote]Is this a "known" problem ? There seems to be a certain amount of denial on this thread.[/quote]There's no denial. I'm trying to help you fix your problems. If you'd prefer to say they are unfixable and a fault of SQL, that's fine too.Up until now, I've considered this kind of deadlock theoretically possibel, but not likely to occur in a real system, due to the frequency of queries necessary.[quote]But I did want to put on record the fact that this astonishing design flaw is present in SqlServer, and that the techniques for avoiding it - whether avoiding bookmark lookups by only using covering indexes, or using snapshot isolation, or reverting to Oracle - are simply workarounds. [/quote]Have you logged it on connect? If so, please post the link so I can add comments and a vote.Mon, 14 Jul 2008 23:45:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]David Griffiths (7/14/2008)[/b]Surely it cannot be necessary to design all indexes to cover their base tables so that no bookmark lookups take place ? What is that going to do to the database performance ? Where in the documentation does it state that this is a required technique to avoid deadlocks ?Is this a "known" problem ? There seems to be a certain amount of denial on this thread.I guess from your second post that you have been bitten as badly as I have - in fact worse, since at least I can change the schema I use !I guess, when all's said and done, I shall have to live with this until we decide to move to '2008. But I did want to put on record the fact that this astonishing design flaw is present in SqlServer, and that the techniques for avoiding it - whether avoiding bookmark lookups by only using covering indexes, or using snapshot isolation, or reverting to Oracle - are simply workarounds.[/quote]Bart Duncan covers this topic pretty good in his blogging:http://blogs.msdn.com/bartd/attachment/747119.ashxhttp://blogs.msdn.com/bartd/archive/2006/09/13/Deadlock-Troubleshooting_2C00_-Part-2.aspxEven in SQL 2008, you are going to face this problem, because you have a flaw in your indexing design that will lend itself to deadlock scenarios. I have a table in a vendor database that has 36 indexes on it to prevent deadlocking from occuring due to their code design and table structure. It works just fine. The cost of IO isn't all that great, but it isn't all that bad either. If that is what I have to do to prevent deadlocks then that is what I do. If it were an internal table or application, we would have been doing some refactoring long ago, but I can only do so much with somethign that we purchased. Keep in mind, that this table is an extreme scenario. In most cases you are only talking about a couple of extra indexes to cover your queries better. You will see faster performance by avoiding the lookup as an added plus, so I don't see what the objection would be.Oracle is a completely different monster when it comes to Locking and deadlock scenarios. It is true that you can't get a Deadlock from Selects like this, but you can instead get a ORA 1555 Snapshot to old when the undo/redo buffer space runs out. There are trade offs in both environments. This isn't what I would call a design flaw in SQL Server, it is actually function just as they planned for it to, and when you get down into the roots of the storage internals, and concurrency, it actually kind of begins to make some sense.Mon, 14 Jul 2008 11:07:51 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxJonathan,you are right to some extent. The index I am using - designed to pick out a few hundred rows from hundreds of thousands - is the correct index; usually the Query optimizer will pick this index without me needing to force it with a hint. Occasionally - for whatever reason - it chooses some other less efficient plan, which is why I have resorted to the hint.Bear in mind that we are running the identical software application with the identical database schema across hundreds of Customer sites, large and small, busy and quiet. The application contains probably thousands of SELECT statements and hundreds of UPDATEs, INSERTs and DELETEs. Experience has taught us that we need to examine the database performance in detail, particularly locks, deadlocks and execution plans, and it's noticeable how these can vary from site to site.You state that using a non-covering index forces bookmark look-ups, and that these are the root of the problem. That is precisely correct, and that is precisely what I am complaining about.Surely it cannot be necessary to design all indexes to cover their base tables so that no bookmark lookups take place ? What is that going to do to the database performance ? Where in the documentation does it state that this is a required technique to avoid deadlocks ?Is this a "known" problem ? There seems to be a certain amount of denial on this thread.I guess from your second post that you have been bitten as badly as I have - in fact worse, since at least I can change the schema I use !I guess, when all's said and done, I shall have to live with this until we decide to move to '2008. But I did want to put on record the fact that this astonishing design flaw is present in SqlServer, and that the techniques for avoiding it - whether avoiding bookmark lookups by only using covering indexes, or using snapshot isolation, or reverting to Oracle - are simply workarounds.Mon, 14 Jul 2008 10:57:03 GMTDavid Griffiths-273839http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]GilaMonster (7/9/2008)[/b]That, IMHO, is the height of laziness. "It's broken, but don't worry about fixing it."[/quote]It is very unfortunate, but I have been locked in a battle with the Microsoft Sharepoint team for almost 9 months, and recently gave up, about adding 2 indexes to their Queue database to resolve persistent deadlocking. The SQL Engineers agreed with the indexes I recommended, but the Sharepoint Teams answer has consistently been:[quote][b]Microsoft Support[/b]"Office SharePoint Server 2007 Search does deadlock while crawling. On average it is acceptable to see 1 deadlock for every 200-500 documents crawled or 1 deadlock every 2-3 minutes. It is recommended to run the SQL script in KB (http://support.microsoft.com/kb/943345) to reduce fragmentation of the SharePoint Databases. Follow the recommendation in KB 943345 to determine how often to defragment the SharePoint databases. "[/quote]It got to where I had to provide business impact, which there really isn't much since the application handles the deadlock and continues to resubmit the transaction until it is successful. Our impact is me getting phone calls after hours because the Sharepoint admins need help with something else, and the Level 1 support guy wants to look at why there are deadlocks persistently in the logs. I guess that isn't important enough to fix the problem.Mon, 14 Jul 2008 10:25:37 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxDavid,Your execution plan shows why you are deadlocking:145 1 | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Taxi1].[dbo].[CompletedJob] AS [CJ])) 6 6 5 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1000]), OBJECT:([Taxi1].[dbo].[CompletedJob] AS [CJ]) [CJ].[Status], [CJ].[JobID], [CJ].[NeedsVerification] 70.514214 0.21874826 7.7565637E-5 118 2.0717533 [CJ].[Status], [CJ].[JobID], [CJ].[NeedsVerification] NULL PLAN_ROW 0 1.0It looks to me like you are forcing an incorrect index here. What happens is that your select graps a Shared Lock on the Nonclustered index, but it can't satisfy the query completely, so it has to make a lookup to the clustered index. However at the same time, your update has taken a exclusive lock on the clustered index. This will in turn require a Exclusive Lock on each of the non-clustered indexes to update them as well. Since you can't take a shared lock for reading off the clustered index for the select, and the update can't take an exclusive lock on the non-clustered index because the select has a shared lock, you end up deadlocked.You need to create an covering index for the select statement, and remove your index hint to allow the optimizer to select to covering index to resolve your deadlocking here.EDIT:For the above, the following index should cover the query with seeks:CREATE INDEX [NeedsVerification_AccountInvoiceNum_AccJobTypeFK1] ON [CompletedJob](Status, JobID, NeedsVerification)And the following should help with the other query:CREATE INDEX IX_DespatchJob_Test ON DespatchJob (JobID, DespatchMethod, ActiveTaxiFK, VehicleName, DriverName)CREATE INDEX IX_Movement_Test2 ON Movement (JobID, DespatchJobFK)Mon, 14 Jul 2008 09:56:48 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxOK, I have attached a ZIP containing the DDL for all tables and indexes, plus execution plans and statistics for the two SELECT statements. These were measured this afternoon on the Customer's live system (SQL Server 2000 on Windows Server 2003, Dell PowerEdge 2900, 2 x Xeon Quad-Core 2.33GHz, 4MB Ram).Each UPDATE hits exactly one row (because the column JobID is the primary key in each case). I don't have stats for these - it is a live system after all. There is a single update trigger on each table for replication.The SELECT on CompletedJob may return hundreds of rows - however it is looking for a transient state in the lifecycle of a Job so sometimes it will hit fewer. This query is run perhaps 10 or 20 times per hour.The SELECT on DespatchJob will hit, at most, a handful of rows - fewer than 5, often none at all. This is run every two seconds.The UPDATEs are run perhaps every 5 to 10 seconds on average.The CompletedJob and Movement tables have about 400,000 rows. The DespatchJob and ActiveOrder tables have about 1,000 rows. Total database size is running at 5.6 GB on this site.Mon, 14 Jul 2008 09:48:50 GMTDavid Griffiths-273839http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxCould you perhaps post the schema of the two Job tables and the definitions of the indexes that these example queries use?What are the execution times of these selects and updates? How many rows in the tables, how many returned by the select, how many updated by the updates? How frequent are these statements? Any triggers on the tables?Would you mind running a couple of those selects with Showplan_all on, running to text and zipping and attaching the exec plan to your post?Mon, 14 Jul 2008 08:00:15 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxThere are many examples of the SELECT/UPDATE deadlock. Here are a few:[code]SELECT CJ.JobID, CJ.NeedsVerification, JC.Name, JC.IsOutsideMatrix, JC.Quantity, JC.DriverDebit, JC.PassengerDebit, JC.AccountDebitFROM CompletedJob CJ WITH (INDEX (NeedsVerification_AccountInvoiceNum_AccJobTypeFK )) JOIN JobCharge JC ON CJ.JobID = JC.JobFKWHERE JC.Quantity IS NOT NULL AND CJ.Status = 'Complete' AND CJ.NeedsVerification = 1UPDATE CompletedJobSET Status = @P1, NeedsVerification = @P2, AccJobTypeFK = @P3, VehicleName = @P4, DriverName = @P5, PayeeName = @P6, DriverStatementFK = @P7, AccountInvoiceFK = @P8, DriverStatementNum = @P9, AccountInvoiceNum = @P10WHERE JobID = @P11[/code](These two statements are issued by different processes). Checking the execution plan, the SELECT first uses the forced index on NeedsVerification etc followed by a bookmark lookup to get back to the main CompletedJob table. The three columns in the index are all updated by the UPDATE statement. So to summarise: the SELECT locks the index then the CompletedJob table, whereas the UPDATE locks the table then the index.Another similar example has the same SELECT statement deadlocking with: [code]UPDATE CompletedJobSET NeedsVerification = @P1, DriverStatementFK = @P2, DriverStatementNum = @P3, AccountInvoiceFK = @P4, AccountInvoiceNum = @P5WHERE JobID = @P6[/code]A third example is [code]SELECT AO.NominalPickupTime, DJ.VehicleName, DJ.DriverNameFROM DespatchJob DJ JOIN Movement MM ON DJ.JobID = MM.DespatchJobFK JOIN ActiveOrder AO ON MM.JobID = AO.JobIDWHERE DJ.DespatchMethod = @P1 AND AO.NominalPickupTime <= @P2 AND DJ.ActiveTaxiFK IS NULLUPDATE DespatchJobSET Status = @P1, ActiveTaxiFK = @P2, VehicleName = @P3, DriverName = @P4, RegistrationNumber = @P5WHERE JobID = @P6[/code]According to the execution plan, the SELECT statement does an IndexSeek on ActiveOrder.NominalPickupTime, then a NestedLoops join on table Movement, then an IndexSeek on DespatchJob.ActiveTaxiFK, then a NestedLoops join and a bookmark lookup on DespatchJob. Or to summarise: the SELECT locks the index DespatchJob.ActiveTaxiFK followed by the table DespatchJob, whereas the UPDATE locks the table followed by the index.I realise this is all very tortuous. But these are genuine examples of deadlocks which occur daily on a single customer site. There are many other instances but life is too short to list them all. The common factor is that the order of index reads in the SELECT conflicts with the order of index writes in the UPDATE. Result: [b]unavoidable[/b] deadlock.Mon, 14 Jul 2008 07:47:07 GMTDavid Griffiths-273839http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]David Griffiths (7/11/2008)[/b][hr]So my point remains: some deadlocks are unavoidable because they are inherent in the design of SQL Server.[/quote]Could you post some code for the update and the select please, along with the table design and any indexes?Is it possible for you to widen the index and hence remove the need to hit the base table at all to satisfy the select statement?[quote]Something is indeed wrong in the code - in Microsoft's code. Not much I can do to fix it.[/quote]You're welcome to file an issue on Connect if you believe you have found a bug in SQL server [url]http://connect.microsoft.com[/url]Fri, 11 Jul 2008 14:32:06 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxYes, I think SNAPSHOT ISOLATION would solve the problem - it is, after all, the way Oracle does it. Unfortunately it's not available in SQL Server 2000. When and if we upgrade to 2005 (or more likely 2008) I will have the opportunity of trying it - but since the application we are running has many processes accessing the database simultaneously, with thousands of SELECT statements and hundreds of UPDATEs, the only feasible approach would be to run the entire database in snapshot mode - which may cause a performance hit.As for Jeff M's comment about RBAR Update/Select pairs, I'm afraid I don't understand what he is talking about but I'm pretty sure it doesn't relate to my problem.What spurred me into posting my original comment was Jeff M's comment a couple of pages back "If you have any deadlocks, something is wrong in the code somewhere and you need to fix it."Something is indeed wrong in the code - in Microsoft's code. Not much I can do to fix it.Fri, 11 Jul 2008 11:24:58 GMTDavid Griffiths-273839http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]David Griffiths (7/11/2008)[/b][hr]Thanks for 2 prompt replies.My point about having the SELECT in its own transaction was that I wasn't doing a SELECT in a transaction where a previous UPDATE, say, had already locked some rows. To put it another way, the process which is issuing the SELECT is merely trying to read some data !Yes, I understand about the NOLOCK hint and in some places I use it. But obviously reading uncommitted changes is often unacceptable and always contrary to the entire purpose of relational databases.So my point remains: some deadlocks are unavoidable because they are inherent in the design of SQL Server.[/quote]David,I had this very same problem happening all the time. I also didn't want to read uncommited changes.It was solved using Snapshot Isolation Level. The database is still in Read Commited Isolation Level (which I assume is the Isolation Level yours are). I just allowed snapshot isolation (ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON) and put the statement [SET TRANSACTION ISOLATION LEVEL SNAPSHOT] in the beggining of the processes that were blocking each other.I guess just changing the whole database to Snapshot Isolation Level (ALTER DATABASE DatabaseName SET READ_COMMITTED_SNAPSHOT ON) would also do the job, but I think there is some performance decrease making snapshots whenever a session begins. (I never tried this).Luiz.Fri, 11 Jul 2008 10:50:13 GMTLuiz-458831http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxIf you're talking about RBAR update/select pairs, that's just a bad programming practice... between the proprietary UPDATE SET @variable = columnname = expression and the OUTPUT clause available in 2005, there is no reason to make such a mistake unless someone just doesn't know about those things. Update/select pairs are one of the primary sources of Deadlocks and should never be used. Oracle happens to allow it because it doesn't do anything with locking until there's a commit. Essentially, it's doing a dirty read of it's own update.Fri, 11 Jul 2008 10:34:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxThanks for 2 prompt replies.My point about having the SELECT in its own transaction was that I wasn't doing a SELECT in a transaction where a previous UPDATE, say, had already locked some rows. To put it another way, the process which is issuing the SELECT is merely trying to read some data !Yes, I understand about the NOLOCK hint and in some places I use it. But obviously reading uncommitted changes is often unacceptable and always contrary to the entire purpose of relational databases.So my point remains: some deadlocks are unavoidable because they are inherent in the design of SQL Server.Fri, 11 Jul 2008 09:55:05 GMTDavid Griffiths-273839http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]David Griffiths (7/11/2008)[/b][hr]With the greatest respect - I disagree.Admittedly I am using SQL Server 2000 so perhaps this is something that has since been resolved. But I work on a complex real-time application which has an irreducible level of deadlocks, perhaps 1 per day on a busy site.I have spent a lot of time analysing these deadlocks and removed all the defects I can find in my code. I am left with a type of deadlock where an UPDATE conflicts with a SELECT. As a former Oracle programmer (where this is impossible) I still find this kind of deadlock pretty unbelievable. BTW this happens even though the SELECT is the only statement in transaction.What is happening, as far as I can tell, is that the SELECT is using an index and it takes a shared row lock on the index before trying to get a shared row lock on the table itself. Meanwhile the UPDATE takes an exclusive row lock on the table and then tries to get a lock on the index (because the index includes a column modified by the update). This contradicts the rule that all players should take out locks in the same order and leads to a deadlock. In this situation the deadlock victim is always the SELECT statement so no real harm is done and the 'rollback' is a no-op.However it is still annoying and, as far as I am concerned, represents a design bug in SQL server.[/quote]You could force the locking behavior with hints, even using No Lock on the select if you can live with dirty or phantom reads.Fri, 11 Jul 2008 09:42:07 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]David Griffiths (7/11/2008)[/b][hr]BTW this happens even though the SELECT is the only statement in transaction.[/quote]Why on Earth would you put a single SELECT in an explicit transaction???Fri, 11 Jul 2008 09:36:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxWith the greatest respect - I disagree.Admittedly I am using SQL Server 2000 so perhaps this is something that has since been resolved. But I work on a complex real-time application which has an irreducible level of deadlocks, perhaps 1 per day on a busy site.I have spent a lot of time analysing these deadlocks and removed all the defects I can find in my code. I am left with a type of deadlock where an UPDATE conflicts with a SELECT. As a former Oracle programmer (where this is impossible) I still find this kind of deadlock pretty unbelievable. BTW this happens even though the SELECT is the only statement in transaction.What is happening, as far as I can tell, is that the SELECT is using an index and it takes a shared row lock on the index before trying to get a shared row lock on the table itself. Meanwhile the UPDATE takes an exclusive row lock on the table and then tries to get a lock on the index (because the index includes a column modified by the update). This contradicts the rule that all players should take out locks in the same order and leads to a deadlock. In this situation the deadlock victim is always the SELECT statement so no real harm is done and the 'rollback' is a no-op.However it is still annoying and, as far as I am concerned, represents a design bug in SQL server.Fri, 11 Jul 2008 08:50:04 GMTDavid Griffiths-273839http://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxDead locks are common. Best thing one can do about dead locks are:-- Try to use one sequence in selecting or updating your tables in all your stored procedures-- Use "With (Nolock)" in your SELECT statements where ever you are allowed to read uncommited data-- Try to avoid CURSORS.. if you can't atleast try to define READONLY Cursors-- Add proper indexes where ever you needSusheel K ChandaThu, 10 Jul 2008 08:34:17 GMTsusheel_chhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]er.kalidass (7/9/2008)[/b][hr]Hi guys Try with snapshot isolation levels. it will reduce the db blocking Massively .[/quote]Agreed, snapshot isolation will completely prevent deadlocks, but usually the best solution is to find the offending code and take a large hammer to it.Wed, 09 Jul 2008 23:57:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]terry.jago (7/9/2008)[/b][hr]Yes it is possible to have no deadlocks, however, in this day and age most code is written badly, however, if you do get deadlocks there is no need to worry as the system will detect this and kill one of the processes.[/quote]That, IMHO, is the height of laziness. "It's broken, but don't worry about fixing it."Wed, 09 Jul 2008 23:47:43 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxHi guys Try with snapshot isolation levels. it will reduce the db blocking Massively ., bu it will require large tempDB space since it is taking the snapshot of physical data to tempdb.Wed, 09 Jul 2008 23:39:37 GMTer.kalidasshttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]vikkin (7/9/2008)[/b][hr]Hi Where can I find this in SQL Sever, I ma having endless issues of Deadlocks[/quote]Where can you find what?To trace the source of deadlocks. switch traceflag 1204 or 1222 (SQL 2005 only) on. With one of those traceflags on, SQL writes out the deadlock graph into the error log. There's enough info in the deadlock graph to trace the source of the deadlock on both sides. That should give you a good idea where to start fixing.Wed, 09 Jul 2008 23:39:32 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxHi Where can I find this in SQL Sever, I ma having endless issues of DeadlocksWed, 09 Jul 2008 23:29:14 GMTvikkinhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]terry.jago (7/9/2008)[/b][hr]Yes it is possible to have no deadlocks, however, in this day and age most code is written badly, however, if you do get deadlocks there is no need to worry as the system will detect this and kill one of the processes.Terry[/quote]...which also causes either performance loss due to the inherent rollback or dataloss because something didn't happen right, or both. That's why I'm always busting chops about doing it right the first time or take 6 times longer to find it and fix it later. ;)Worry about deadlocks :w00t: Shoot for zero deadlocks and high performance scalable code or find a new profession (NOT directed at you, Terry!).Wed, 09 Jul 2008 21:03:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxYes it is possible to have no deadlocks, however, in this day and age most code is written badly, however, if you do get deadlocks there is no need to worry as the system will detect this and kill one of the processes.TerryWed, 09 Jul 2008 19:48:36 GMTtedohttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxYes I ment was Blocked not DEADLOCK.Yes again i agree with GILA.Wed, 09 Jul 2008 11:00:54 GMTMannySinghhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspx[quote][b]Mani Singh (7/9/2008)[/b][hr]Adding to it:sometimes, the Deadlocks might resolve by themselves in time, but if you kill a long waiting deadlock, you might end up with a Phantom Process/lock and it stays there, unless you have the option to restart the SQL Service.[/quote]For the third or fourth time, No! As I said earlier in this thread[quote]You don't ever have to worry about killing processes involved in a deadlock. SQL has a deadlock detector built in, if it detects an unresolvable locking condition (a deadlock) it will pick one of the processes involved and automatically kill it.[/quote]The definition of a deadlock is an [b]unresolvable[/b] locking condition. Hence left alone they will [b]never[/b] resolve themselves. That said, you will almost never be able to kill a process involved in a deadlock. The SQL deadlock detector is a lot faster than you are.Wed, 09 Jul 2008 10:21:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxI believe you're thinking of blocking.... deadlocks do not resolve themselves in a nice manner... there is always a "victim" that get's rolled back... always.Wed, 09 Jul 2008 08:39:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxAdding to it:sometimes, the Deadlocks might resolve by themselves in time, but if you kill a long waiting deadlock, you might end up with a Phantom Process/lock and it stays there, unless you have the option to restart the SQL Service.Wed, 09 Jul 2008 08:04:22 GMTMannySinghhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxI'm with Gail... but a bit more stringent on the subject. If you have any deadlocks, something is wrong in the code somewhere and you need to fix it. Finding the cause can sometimes be a bit troubling but everytime you have a deadlock, there's a rollback which takes time and that also possibly means some lost data. Finding and repairing code that causes deadlocks should be a top priority for every DBA.Wed, 09 Jul 2008 07:59:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic530084-146-1.aspxAdding to Anders.My Current job, there is a Application "Maximo" which deals with SHOP Floor Order Entry system. There are 100 People in the Steel Mill loggin into the application through CITRIX, which come down the Database and they generate Huge amount of Deadlocks/locking/blocking Scenarios Daily.. I have check them to use HINTS in their SQL Reports and Application CODE. Also WOrked aound ISOLATION LEVELS.. and SEPRATED their READONLY Data from READ/WRITE using FileGroups and also Seprated their REPORTs to USE, a logged shipped version of Database.. which gets updated twice daily... This is SQL server 2000 SP4.Wed, 09 Jul 2008 07:50:18 GMTMannySingh