To all SQL gurus:
I have a Windows Service that uses a single SQL Server table to
retrieve items of work. Each thread of the service checks this table
for the earliest item of work that is not already in process, marks
that item as in process, then begins to work the item. My concern is
whether the threads will begin to step on each other's toes by picking
the same item of work at the same time. To prevent this, I use the
following SQL table:
[WorkItems]
WorkItem varchar(512)
DateSubmitted datetime
Status int
In requesting the next work item, I use the following SQL syntax:
DECLARE @workitem varchar(512)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT TOP 1 @workitem=WorkItemName FROM WorkItems WHERE Status=1
ORDER BY DateSubmitted
UPDATE WorkItems SET Status=2 WHERE WorkItemName=@workitem
SELECT * FROM WorkItems WHERE WorkItemName=@workitem
COMMIT TRANSACTION
The idea is that the Transaction Isolation Level, along with the three
statements in the transaction block, will only let one thread at a
time request the next work item. The three statements in the
transaction block select the next work item, mark it as in process,
then return the work item to the calling thread. In limited testing,
all seems well. Before going into production, however, I would like to
see if anyone can confirm that my ideas will indeed prevent threads
from duplicating each other's work.
Will the above SQL syntax allow me to run multiple threads all looking
to the same database table for work, but prevent them from selecting
any of the same work at the same time? If you need more information,
please ask.
Reply to newsgroup, or directly at ma************@NOSPAM.srcp.com.
Matthew Roberts
