問題描述
SQL Server 進程隊列競爭條件 (SQL Server Process Queue Race Condition)
I have an order queue that is accessed by multiple order processors through a stored procedure. Each processor passes in a unique ID which is used to lock the next 20 orders for its own use. The stored procedure then returns these records to the order processor to be acted upon.
There are cases where multiple processors are able to retrieve the same 'OrderTable' record at which point they try to simultaneously operate on it. This ultimately results in errors being thrown later in the process.
My next course of action is to allow each processor grab all available orders and just round robin the processors but I was hoping to simply make this section of code thread safe and allow the processors to grab records whenever they like.
So Explicitly - Any idea why I am experiencing this race condition and how I can solve the problem.
BEGIN TRAN
UPDATE OrderTable WITH ( ROWLOCK )
SET ProcessorID = @PROCID
WHERE OrderID IN ( SELECT TOP ( 20 )
OrderID
FROM OrderTable WITH ( ROWLOCK )
WHERE ProcessorID = 0)
COMMIT TRAN
SELECT OrderID, ProcessorID, etc...
FROM OrderTable
WHERE ProcessorID = @PROCID
參考解法
方法 1:
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.
方法 2:
You can use Service Broker. Also you can use sp_getapplock to serialize access to your rows - that will eliminate race conditions:
"Assisting Concurrency by creating your own Locks (Mutexs in SQL) " http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx
(by William Edmondson、gbn、A-K)