none
queue processing using updlock, readpast locking hints RRS feed

  • Question

  • This article instructed me on how to process rows from a table used as a data queue for multiple processes.

     

    http://www.mssqltips.com/tip.asp?tip=1257

     

    I tested this against the AdventureWorks DB (SQL 2005) and multiple SQL connections inside of Sql Mgmt. Studio).

     

    Connection1:

    BEGIN TRANSACTION

    SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows

    --COMMIT TRANSACTION

     

    Connection2:

    BEGIN TRANSACTION

    SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast)  --skips over locked rows

    COMMIT TRANSACTION

     

     

    This works like I want where connection 2 skips over the locked row from connection 1 and gets the next available record from the table / queue.  However, when I add ORDER BY tsql to each sql statement, connection 2 is now blocked waiting for Connection 1 to commit. (This is not what I want)

     

    Connection1:

    BEGIN TRANSACTION

    SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate

    --COMMIT TRANSACTION

     

    Connection2:

    BEGIN TRANSACTION

    SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpastorder by DueDate --is blocked until connection 1 commits transaction

    COMMIT TRANSACTION

     

     

    How do I prevent blocking when using these locking hints with ORDER BY?

     

     

    thanks

     

     

    Friday, April 25, 2008 3:10 PM

Answers

All replies

  • Do you have an index by [DueDate]?

     

     

    AMB

    Friday, April 25, 2008 3:31 PM
    Moderator
  • Tried it with and without an index on DueDate.  Either way it appears to block when adding the ORDER BY to the query.

    Friday, April 25, 2008 4:01 PM
  •  skmcfadden wrote:

    Tried it with and without an index on DueDate.  Either way it appears to block when adding the ORDER BY to the query.

     

    Clustered or nonclustered?

     

    AMB

    Friday, April 25, 2008 5:10 PM
    Moderator
  •  

    Oops, I accidentally hit cancel instead of OK while initially attempting to create the index.   have since gone back and truely created the index (non clustered) and now the behavior is as desired (no blocking / skipping of locked rows).

     

    Why does the Update / ReadPast locking hint need an index on the order by column to work properly?

     

     

    Saturday, April 26, 2008 1:29 AM
  • Check both execution plans. It could be that in the one without the index, a sort operation is needed to satisfy the "order by" clause, and the engine is escalating the lock to a less granular level (page / extend / table), increasing the probability of concurrency contention, locking resources needed by the second session.

     

    This is a guess.

     

     

    AMB

    Saturday, April 26, 2008 3:44 PM
    Moderator