2

enter image description hereI'm getting 20-30 deadlocks per day on the same table. (2 mil rows) Profiler DeadlockGraphs show KEY LOCK and PAGE LOCK (See attched jpg) Total datatype size per row = 114 bytes

We have multiple departments that run a process that takes orders from our PreOrders table, checks the rows and inserts them into the actual orders table. This very import process is VB6. So to illistrate I simplified it in TSQL (Below).

The first SELECT statement is the deadlock victim and the end UPDATE statement is the winner. (Different SPIDs) There are 4 different departments, each running this process every 30 secs.

After reading several articles, I was thinking my easiest option would be to add WITH (ROWLOCK) to the update statement. I am able to recreate this entire process in TSQL, except for the automatic printing of invalid rows.

What other options do I have? This'll be the first time I've had to use a query hint and am a bit reluctant as my knownledge on hints is (still) limited.

--Loop through headers 
-- THIS SELECT STATEMENT IS ALWAYS THE DEADLOCK VICTIM
SELECT  PreOrderID, <Other columns....> 
FROM    PreOrders PO -- 2 mil rows
        INNER JOIN Customers C -- 50 K rows
            ON PO.CustomerNr = C.CustomerNr
WHERE   Processed = 0 
        AND Department = 4 -- This is also the account number related to the Deadlock process

BEGIN
    -- < 100 orders

    -- Loop through rows
    SELECT  ProductNr, Amount, Price, <Other columns....> 
    FROM    PreOrders 
    WHERE   PreOrderID = @PreOrderID

    BEGIN
            -- AVG 9 rows per order

            -- Get data to validate the order row with
            SELECT  Availibilty, Price, isActive <Other columns....> 
            FROM    Products P -- 100 K rows 
                    LEFT JOIN ProductLocations PL -- 35 K rows
                        ON P.ProductNr = PL.ProductNr
            WHERE   P.ProductNr = @ProductNr
            -- Validate the order rows against the actual product data
            -- Bad rows get emailed and insert with a counter row (Cancels the row order)
            -- Good rows get inserted into actual Orders table
    END

    -- Generate the new order number
    -- Save the head and rows into the acutal order tables
    -- Update the preOrder as having been processed

    -- THIS UPDATE STATEMENT IS ALWAYS THE DEADLOCK WINNER
    UPDATE  PreOrders -- !!*** WITH (ROWLOCK)
    SET     Ordernumbers= @OrderNumber, -- Newly generated number)
            Processed   = 1
    WHERE   PreOrderID  = @PreOrderID

BEGIN

EDIT: Added screenshots

Executionplan 1st SELECT Deadlock Graph

4
  • what is the execution plan of the first select? Jan 9, 2014 at 16:27
  • 1
    Can you just use snapshot isolation, so that selects are not involved in deadlocks at all?
    – A-K
    Jan 9, 2014 at 18:38
  • @Sabastian, I added a screenshot of the execution plan. As stated the tsql is a simplification, maybe overly so. Jan 10, 2014 at 10:54
  • @Alex, That might a possibility. Perhaps I can just use the NOLOCK hint in the SELECT? Jan 10, 2014 at 11:28

2 Answers 2

1

I resolved the deadlocks by using a NOLOCK hint in the first select statement. No dirty reads as this is the only process that manipulates the rows and only does so once.

0

I am assuming that a transaction is held around the entire statement you quote above.

One solution would be to take out the exclusive lock already during the SELECT and hang on to it. You can do this with the WITH (XLOCK) hint. However, for that to give you any concurrency, you probably need to add the filter on PreOrderId to the first SELECT too. Is this possible in your scenario?

1
  • No transaction. VB6 programmer is as the title suggests, not an early adapter.... PreOrderId is exactly what the query needs to produce. Jan 10, 2014 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.