Post reply

Deadlocks

  • July 9, 2008 at 9:03 pm

    #840601

    terry.jago (7/9/2008)

    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

    ...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!).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 9, 2008 at 11:29 pm

    #840619

    Hi Where can I find this in SQL Sever, I ma having endless issues of Deadlocks

  • July 9, 2008 at 11:39 pm

    #840621

    vikkin (7/9/2008)

    Hi Where can I find this in SQL Sever, I ma having endless issues of Deadlocks

    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.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2008 at 11:39 pm

    #840622

    Hi 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.

  • July 9, 2008 at 11:47 pm

    #840628

    terry.jago (7/9/2008)

    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.

    That, IMHO, is the height of laziness. "It's broken, but don't worry about fixing it."

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2008 at 11:57 pm

    #840631

    er.kalidass (7/9/2008)

    Hi guys

    Try with snapshot isolation levels. it will reduce the db blocking Massively .

    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.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 10, 2008 at 8:34 am

    #840871

    Dead 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 need

    Susheel K Chanda

  • July 11, 2008 at 8:50 am

    #841564

    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.

  • July 11, 2008 at 9:36 am

    #841615

    David Griffiths (7/11/2008)

    BTW this happens even though the SELECT is the only statement in transaction.

    Why on Earth would you put a single SELECT in an explicit transaction???

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 11, 2008 at 9:42 am

    #841622

    David Griffiths (7/11/2008)

    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.

    You could force the locking behavior with hints, even using No Lock on the select if you can live with dirty or phantom reads.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • July 11, 2008 at 9:55 am

    #841643

    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.

  • July 11, 2008 at 10:34 am

    #841693

    If 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.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 11, 2008 at 10:50 am

    #841712

    David Griffiths (7/11/2008)

    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.

    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.

  • July 11, 2008 at 11:24 am

    #841730

    Yes, 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.

  • July 11, 2008 at 2:32 pm

    #841817

    David Griffiths (7/11/2008)

    So my point remains: some deadlocks are unavoidable because they are inherent in the design of SQL Server.

    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?

    Something is indeed wrong in the code - in Microsoft's code. Not much I can do to fix it.

    You're welcome to file an issue on Connect if you believe you have found a bug in SQL server http://connect.microsoft.com

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 16 through 30 (of 47 total)

You must be logged in to reply to this topic. Login to reply