title: tsql: stored procedure and rowlock
body: <p>I have a concurrency in a multiple user system and a stored procedure as shown below:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>It updates a single record status from 'New' to 'InProcess' and returns record's ID.</p>
  
  <p>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? </p>