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?

Answer 1

• 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

Answer 2

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.