SQL Server Process Queue Race Condition

by

Edit:

I googled to check my answer: “Processing Data Queues in SQL Server with READPAST and UPDLOCK”. It’s been years since I read about and played with this solution.

Original:

If you use the READPAST hint, then locked rows are skipped. You’ve used ROWLOCK so you should avoid lock escalation. You also need UPDLOCK, as I found out.

So process 1 locks 20 rows, process 2 will take the next 20, process 3 takes rows 41 to 60, etc

The update can also be written like this:

UPDATE TOP (20)
    foo
SET
    ProcessorID = @PROCID
FROM
    OrderTable foo WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE
    ProcessorID = 0

Refresh, Oct 2011

This can be done more elegantly with the OUTPUT clause if you need a SELECT and an UPDATE in one go.

More Related Contents:

  1. How do I perform an IF…THEN in an SQL SELECT?
  2. Inserting multiple rows in a single SQL query? [duplicate]
  3. SQL update query using joins
  4. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  5. Is there a combination of “LIKE” and “IN” in SQL?
  6. How can I list all foreign keys referencing a given table in SQL Server?
  7. SQL query to select dates between two dates
  8. Function vs. Stored Procedure in SQL Server
  9. Multiple INSERT statements vs. single INSERT with multiple VALUES
  10. Removing leading zeroes from a field in a SQL statement
  11. Generate a resultset of incrementing dates in TSQL
  12. converting Epoch timestamp to sql server(human readable format)
  13. Better techniques for trimming leading zeros in SQL Server?
  14. How do I split a delimited string so I can access individual items?
  15. Common Table Expression, why semicolon?
  16. What’s the best way to select the minimum value from several columns?
  17. T-SQL split string based on delimiter
  18. Counting DISTINCT over multiple columns
  19. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  20. Why use Select Top 100 Percent?
  21. How to check if a stored procedure exists before creating it
  22. Correct use of transactions in SQL Server
  23. How do I prevent a database trigger from recursing?
  24. What is the Null Character literal in TSQL?
  25. SQL Server : SUM() of multiple rows including where clauses
  26. How to combine results of two queries into a single dataset
  27. SQL Server 2005 and temporary table scope
  28. Executing SQL Server Agent Job from a stored procedure and returning job result
  29. how does SELECT TOP works when no order by is specified?
  30. SUM total time in SQL Server [duplicate]

Leave a Comment