Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
what is the execution plan of the first select? –  Sebastian Meine Jan 9 at 16:27
1  
Can you just use snapshot isolation, so that selects are not involved in deadlocks at all? –  AlexKuznetsov Jan 9 at 18:38
    
@Sabastian, I added a screenshot of the execution plan. As stated the tsql is a simplification, maybe overly so. –  Dennis Post Jan 10 at 10:54
    
@Alex, That might a possibility. Perhaps I can just use the NOLOCK hint in the SELECT? –  Dennis Post Jan 10 at 11:28
add comment

2 Answers 2

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.

share|improve this answer
add comment

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?

share|improve this answer
    
No transaction. VB6 programmer is as the title suggests, not an early adapter.... PreOrderId is exactly what the query needs to produce. –  Dennis Post Jan 10 at 11:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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