Professional Web Applications Themes

Deadlock problem - Microsoft SQL / MS SQL Server

Hi Mike, To minimize deadlocks, you would refer to "Minimizing Deadlocks" in SQL Server Books Online. In addition, some other suggestions to minimize deadlocks. 1. Use update locks when applicable. This reduces Conversion Deadlocks. You can explicitly ask for an update lock by using the (UPDLOCK) hint in a SELECT statement. SELECT AccountBalance FROM Customers WITH (UPDLOCK) WHERE Customer = 'Bob' 2. Create clustered index to distribute inserts across the entire table. This ensures that the inserts are not happening on at last page ONLY. 3. Avoid placing clustered index on columns that are frequently updated. Updates to clustered index ...

  1. #1

    Default Re: Deadlock problem

    Hi Mike,

    To minimize deadlocks, you would refer to "Minimizing Deadlocks" in SQL
    Server Books Online. In addition, some other suggestions to minimize
    deadlocks.
    1. Use update locks when applicable. This reduces Conversion Deadlocks. You
    can explicitly ask for an update lock by using the (UPDLOCK) hint in a
    SELECT statement.
    SELECT AccountBalance
    FROM Customers WITH (UPDLOCK)
    WHERE Customer = 'Bob'
    2. Create clustered index to distribute inserts across the entire table.
    This ensures that the inserts are not happening on at last page ONLY.
    3. Avoid placing clustered index on columns that are frequently updated.
    Updates to clustered index key columns will require locks on the clustered
    index, data pages (to move the row), and all nonclustered indexes (since NC
    indexes point to rows via the clustered index key).
    4. Capture error 1205 and make sure you resubmit the transaction as a
    general practice in your Application code.

    To troubleshoot deadlocks, check "Troubleshooting Deadlocks" topic.
    1. Trace Flags to enable to troubleshoot Deadlock:
    - When the following trace flags are enabled along with trace flag 3605,
    all the information is put in the errorlog for further ysis.
    1204 : Returns the type of locks participating in the deadlock and the
    current command affected.
    1205 : Returns more detailed information about the command being executed
    at the time of a deadlock.

    In addition, Profiler can help capture basic information about deadlocks.


    Bill Cheng
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.
    --------------------
    | Content-Class: urn:content-classes:message
    | From: "Mike LeBlanc" <com>
    | Sender: "Mike LeBlanc" <com>
    | References: <004a01c347b9$2f3acb50$gbl>
    <phx.gbl>
    | Subject: Re: Deadlock problem
    | Date: Fri, 11 Jul 2003 08:49:25 -0700
    | Lines: 184
    | Message-ID: <067701c347c4$014c0ff0$gbl>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | cht="iso-8859-1"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    | Thread-Index: AcNHxAFJwwdfhS9vTu2oDk/VqhnfSg==
    | Newsgroups: microsoft.public.sqlserver.programming
    | Path: cpmsftngxa06.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:372896
    | NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    | Hi Russell, thanks for the reply.
    |
    | In answer to your questions:
    | 1) Yes they are.
    | 2) There are no triggers on the LineItem table
    | 3) The CartDetails table is the source of some of
    | the information, including LineID but some of
    | the fields are populated via VB.NET so I can't
    | just do a copy from the CartDetails table. The
    | SPROC I provided is actually a simplified version
    | of the actual one, all of the statements are there
    | but there are significantly more fields in the
    | actual version.
    |
    | And just to keep you updated a Mr. Frans Bouma posted to
    | the dotnet.framework.adonet group that the likely cause of
    | the problem is the IF EXISTS (...) statement and suggested
    | putting WITH (NOLOCK) on the SELECT statement it is
    | using. I put the hint in to see if it fixes the problem
    | so not it's just a waiting game...
    |
    | I don't think I understand why that statement would cause
    | a conflict...any ideas? (There is a unique index on the
    | linLineItemID field in the LineItem table)
    |
    | Thanks again!
    |
    |
    | >-----Original Message-----
    | >Mike,
    | >
    | >A deadlock is much more likely the longer a transaction
    | is. Your
    | >'transaction' can be lengthy (in SQL Server terms) since
    | there are so many
    | >individual calls.
    | >
    | >Questions:
    | >1 - Are all the inserts embedded in an overall
    | transaction? (I guess that
    | >they are.)
    | >2 - Are there any triggers on LineItem? (For example,
    | the adjust a running
    | >ItemDemand table? If so, the trigger may be the actual
    | cause of the
    | >deadlock.)
    | >
    | >For the trigger possibility, think of the ItemDemand
    | table being updated as
    | >such:
    | >Tran 1 - Item 123
    | > Tran 2 - Item 345
    | >Tran 1 - Item 345
    | > Tran 2 - Item 123
    | >*** DeadLock ***
    | >
    | >
    | >I see that you apparently have the data inserted into
    | LineItem already (to
    | >some degree) stored in CartDetails since you are deleting
    | a line there for
    | >every line inserted to LineItem.
    | >Can you do something like:
    | >
    | >INSERT INTO LineItem
    | >SELECT OrderID,LineID,ProductID,Quantity,Price,Comment
    | >FROM CartDetails
    | >WHERE CustomerID = CustomerID
    | >
    | >DELETE CardDetails WHERE CustomerID = CustomerID
    | >
    | >This would at least narrow the window of deadlock
    | opportunity.
    | >
    | >Russell Fields
    | >
    | >
    | >
    | >"Mike LeBlanc" <com> wrote in message
    | >news:004a01c347b9$2f3acb50$gbl...
    | >> Hello all,
    | >>
    | >> I am having a problem with a SQL Server 2000 deadlock
    | and
    | >> I was wondering if there was some way to fix it that I'm
    | >> missing. (I've also posted this in
    | >> dotnet.framework.adonet)
    | >>
    | >> The deadlock is occuring when I'm saving an order from
    | our
    | >> website. I tracked it down and the deadlock chain says
    | >> there was a different user who was saving another order
    | at
    | >> the same time (the same sproc is blocking itself). Of
    | >> course this eliminates the problem of making sure all
    | >> calls to the DB are in the same order because it's the
    | >> exact same procedure. The catch is that I've got
    | multiple
    | >> line items being saved one-at-a-time within a ADO.NET
    | >> transaction (sqltransaction).
    | >>
    | >> The process looks like this from ASP.NET:
    | >> 1) Fetch all lines from Shopping Cart
    | >> 2) Start Transaction
    | >> 3) Create order header
    | >> 4) Iterate through lines in cart and call
    | >> saveOrderDetail once for each line
    | >> 5) Commit Transaction
    | >>
    | >> The deadlock occurs during #4 and rolls back the entire
    | >> process. At the moment this results in an error being
    | >> displayed to the user and they can then re-submit.
    | >>
    | >> I know I could simply wait and retry the whole order
    | >> automatically but I'm concerned about not only the delay
    | >> this would cause (some orders can contain as many as 100
    | >> lines) the forced rollback causes my OrderNumber (an
    | >> identity field) to be out of sequence. I tried putting
    | a
    | >> ROWLOCK hint on all the statements because I suspected a
    | >> Lock Escalation problem but it didn't fix the
    | problem...do
    | >> I have any other options?
    | >>
    | >> Thanks for any and all feedback!
    | >>
    | >> Here is the offending code:
    | >>
    | >> -- START STORED PROCEDURE CODE --
    | >>
    | >> CREATE PROCEDURE [dbo].[saveOrderDetail] (
    | >> OrderID uniqueidentifier, --\ Primary key
    | >> (clustered)
    | >> LineID uniqueidentifier, --/
    | >> LineItemNum int, -- Sort order
    | >> ProductID uniqueidentifier,
    | >> Quantity decimal(19,2),
    | >> Price as money,
    | >> Comment as varchar(50)
    | >> ) AS
    | >>
    | >> SET NOCOUNT ON
    | >>
    | >> -- Check to see if the line already exists, if it does
    | >> just do an update
    | >> -- I'm reasonably sure the update is never called, it's
    | >> just there for
    | >> -- possible future use and consistency.
    | >> IF EXISTS (SELECT * FROM LineItem WHERE linLineItemID =
    | >> LineID)
    | >> UPDATE LineItem SET
    | >> linComment = Comment ,
    | >> linOrderID = OrderID,
    | >> linPrice = Price,
    | >> linProductID = ProductID ,
    | >> linQuantity = Quantity
    | >> WHERE linLineItemID = LineID -- No index
    | >> ELSE
    | >> BEGIN
    | >> -- Create a new line on the order
    | >> INSERT INTO LineItem
    | >> (linOrderID,linLineItemID,linProductID,linQuantity ,
    | >> linPrice,linComment)
    | >> VALUES
    | >> (OrderID,LineID,ProductID,Quantity,Price,Com ment)
    | >>
    | >> -- Save the original line to the archive table
    | >> INSERT INTO LineItem_original
    | >> (linOrderID,linLineItemID,linProductID,linQuantity ,
    | >> linPrice,linComment)
    | >> VALUES
    | >> (OrderID,LineID,ProductID,Quantity,Price,Com ment)
    | >> END
    | >>
    | >> -- Delete the corresponding shopping cart line
    | >> DELETE FROM cartDetails WHERE LineID = LineID
    | >> GO
    | >>
    | >> -- END STORED PROCEDURE CODE --
    | >>
    | >
    | >
    | >.
    | >
    |

    Bill Guest

  2. #2

    Default Re: Deadlock problem

    Mike:

    Hi. It appears all of the DB processing is as a *transaction* ; so it is
    interesting that you are getting deadlocks...or do you mean blocking?...
    Except for fetching cart records, all of the processing is protected by the
    transaction--no other user-session should be able to access and/or lock the
    DB...hmm...

    Are you getting SQL-Serv time-outs on other user sessions?

    BTW, Mike, are you using *Transaction Objects* for each connection
    session?--I haven't had to the chance to use them yet...

    Derek LaZard

    "Mike LeBlanc" <com> wrote in message
    news:002b01c347b7$95c26c40$gbl... 




    Derek Guest

  3. Moderated Post

    Default Re: DeadLock Problem

    Removed by Administrator
    Vinodk Guest
    Moderated Post

  4. Moderated Post

    Default Re: DeadLock Problem

    Removed by Administrator
    SriSamp Guest
    Moderated Post

  5. Moderated Post

    Default Re: DeadLock Problem

    Removed by Administrator
    Vinodk Guest
    Moderated Post

Similar Threads

  1. Deadlock problem
    By Mike LeBlanc in forum Macromedia ColdFusion
    Replies: 3
    Last Post: February 19th, 08:47 PM
  2. Deadlock?
    By Philip Mak in forum Ruby
    Replies: 3
    Last Post: September 24th, 11:18 AM
  3. Deadlock
    By Lewis in forum IBM DB2
    Replies: 1
    Last Post: July 31st, 05:19 AM
  4. COM Deadlock in W2k but not NT 4.0
    By Steve in forum ASP Components
    Replies: 1
    Last Post: July 24th, 04:27 PM
  5. Deadlock question
    By Jason in forum Informix
    Replies: 2
    Last Post: July 22nd, 04:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139