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 matthewroberts@NOSPAM.srcp.com.

Matthew Roberts (mroberts_hm@hotmail.com) writes:
[quoted text, click to view]

I think you need to add locking hint to the first SELECT statement:

SELECT TOP 1 @workitem=WorkItemName
FROM WorkItems WITH (UPDLOCK) WHERE Status=1
ORDER BY DateSubmitted

Else two processes can read the same @workitem value, and proceed to
the update statement, and both will wait for each other, until the
deadlock detection mechanism rolls one of them back.

The UPDLOCK changes that, because only one process at a time can have
an UPDLOCK at a row. (But the lock does not block readers that only
requires shared locks.)

There is anothing here which makes me a little nervous. Is WorkItemName
unique in this table? Since it's a varchar(512) it does not look like
a typical primary key. If there could be more than one row with the
same work-item name theoretically, process A and process B could
read the same @workitemname from two different rows. Since the rows
would be different, they would not block each other.

Even if WorkItemName is unique, I would recommend you to put this in
the ORDER BY clause to make it completely deterministic in any given
situation which row you get.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

WorkItemName is indeed the primary key and corresponds to a filename,
hence the large size of the field. I realize that this is a bad
primary key, and it will be changed in production code, but for now it
works since I am just testing my methodology.

Now, as for the (UPDLOCK) directive, what is it's purpose and how will
it change things? I know the BOL answer, but I need something in
English, please. :)

FYI, when I use only the DateSubmitted field in the ORDER BY clause of
my query, everything seems to work fine. Using a series of text files
to determine which threads work on what, I can verify that only one
thread works on any given work item. So far anyway. If however, I add
the WorkItemName to the ORDER BY clause, exactly half of my threads
will fail out with deadlocking issues. So my question here is, is that
WorkItemName field really necessary in the ORDER BY clause? It
obviously makes a difference, but if I were to remove it, can I still

Matthew Roberts (mroberts_hm@hotmail.com) writes:
[quoted text, click to view]

I think I tried to explain, but I'll recap.

A UPDLOCK is a shared lock, that is it does not prevent other processes
from reading that row (or page, table or whatever resource the lock is
on), but it blocks updates to that row. And, this is the gist: it also
blocks other UPDLOCK. That is, UPDLOCK informs SQL Server that you will
update the row.

[quoted text, click to view]

As long as DateSubmitted is unique, there will not be surprises. Even if
it is not unique, you may not get surprises, but theoretically they
could occur.

[quoted text, click to view]

Did you use UPDLOCK? The purpose of UPDLOCK is to prevent deadlocking.

An interesting twist would be to add a READPAST hint to accompany the
UPDLOCK hint. This hint instructs SQL Server to skip blocked rows.
In this way, you would get better concurrency. However, this is not
something I have tested myself, so I cannot tell whether it works at
all.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at