tsql: stored procedure and rowlock

Question

I have a concurrency in a multiple user system and a stored procedure as shown below:

CREATE PROCEDURE dbo.GetWorkitemID
AS
DECLARE @workitem int;

UPDATE workqueue
SET status = 'InProcess', @workitem = workitemid
WHERE workitemid = (SELECT TOP 1 workitemid
FROM workqueue WITH (ROWLOCK,UPDLOCK,READPAST)
WHERE status = 'New' ORDER BY workitemid)

SELECT @workitem

GO

It updates a single record status from 'New' to 'InProcess' and returns record's ID.

The questions are as follows: Should I use this stored procedure in a transaction scope to enable ROWLOCK, UPDLOCK etc.? Is it required? And the second: Is it really thread safe and guarantee uniqueness?

score 2 · Answer 1 · answered Aug 08 '11 at 10:30

Should I use this stored procedure in a transaction scope...

Every DML statement in SQL runs in the context of a transaction, whether you explicitly open one or not. By default, when executing each statement, SQL server will open a transaction if one is not open, execute the statement, and then commit the transaction (if no error occurred) or roll it back.

Subject to the caveat mention by @Filip (that there's still no guarantee on the order in which items will be selected), it will be safe and each invocation will return a different row, if one is available and not locked.

score 2 · Accepted Answer · edited May 23 '17 at 11:55

2

This is the correct way to run "table as a queue"
See this please: SQL Server Process Queue Race Condition
You don't need a transaction
This is both thread and concurrency safe

Edit:

As a counter example to Filip De Vos's

Note the use of an covering index and UPDLOCK not XLOCK and the same query

DROP table locktest
create table locktest (id int, workitem int, status varchar(50))
insert into locktest (id, workitem) values (1, 1), (2,2), (3,3)
create index ix_test2 on locktest(workitem) INCLUDE (id, status)

--When I run this on one connection
begin tran 
select top (1) id, status 
from locktest with (rowlock, updlock, readpast) 
ORDER BY workitem

... I get expected results in another connection with the same query

edited May 23 '17 at 11:55

Community

1
1

answered Aug 08 '11 at 11:07

gbn

422,506
82
585
676

your original answer by the link? – johnny Aug 08 '11 at 11:50
@Johnny: yes, but I've added stuff based on your other questions – gbn Aug 08 '11 at 12:48

score 1 · Answer 3 · answered Aug 08 '11 at 10:11

It is not reliable. Because the locking hints you gave are just that, locking hints. Additionally, depending on the way the table is indexed the results might be very different.

For example:

create table test (id int, workitem int, status varchar(50))
insert into test (id, workitem) values (1, 1), (2,2), (3,3)
create index ix_test on test(workitem)

When I run this on one connection

begin tran 
select * from test with (rowlock, xlock, holdlock) where workitem = 1

And I run this on a second connection:

select top (1) * from test with (rowlock, readpast) order by workitem

This returns:

workitem
--------
3

Same if i do:

update top (1) test with (rowlock, readpast)
set status = 'Proc' 
output inserted.workitem

So, you can use this to concurrent pick up what you need, but this is not a reliable way to have in-order concurrent processing.

tsql: stored procedure and rowlock

3 Answers3