Do stored procedures lock tables/rows?

Question

Quite a simple question. In SQL 2008 if I have a stored procedure (see below) do I run the risk of a race condition between the first two statements or does the stored procedure put a lock on the things it touches like transactions do?

ALTER PROCEDURE [dbo].[usp_SetAssignedTo] 
    -- Add the parameters for the stored procedure here
    @Server varchar(50), 
    @User varchar(50),
    @UserPool varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    Declare @ServerUser varchar(50)

    -- Find a Free record
    SELECT top 1 @ServerUser = UserName 
    from ServerLoginUsers
    where AssignedTo is null and [TsServer] = @Server

    --Set the free record to the user
    Update ServerLoginUsers
    set AssignedTo = @User, AssignedToDate = getdate(), SourcePool = @UserPool
    where [TsServer] = @Server and UserName = @ServerUser

    --report record back if it was updated. Null if it was not available.
    select * 
    from ServerLoginUsers 
    where [TsServer] = @Server 
        and UserName = @ServerUser 
        and AssignedTo = @User
END

score 3 · Accepted Answer · edited May 23 '17 at 10:33

3

You could get a race condition.

It can be done in one statement:

You can assign in an UPDATE
The lock hints allow another process to skip this row
The OUTPUT clause returns data to the caller

Try this... (edit: holdlock removed)

Update TOP (1) ServerLoginUsers WITH (ROWLOCK, READPAST)
OUTPUT INSERTED.*
SET
   AssignedTo = @User, AssignedToDate = getdate(), SourcePool = @UserPool
WHERE
   AssignedTo is null and [TsServer] = @Server   -- not needed -> and UserName = @ServerUser

If not, you may need a separate select

Update TOP (1) ServerLoginUsers WITH (ROWLOCK, READPAST)
SET
    -- yes, assign in an update
   @ServerUser = UserName,
   -- write
   AssignedTo = @User, AssignedToDate = getdate(), SourcePool = @UserPool
OUTPUT INSERTED.*
WHERE
   AssignedTo is null and [TsServer] = @Server   -- not needed -> and UserName = @ServerUser

SELECT ...

See this please for more: SQL Server Process Queue Race Condition

edited May 23 '17 at 10:33

Community

1
1

answered Oct 01 '10 at 16:53

gbn

422,506
82
585
676

output statement needs to be put after the set for the correct syntax – Scott Chamberlain Oct 01 '10 at 18:10
I used your first example but I got the error `You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.` when I ran it. – Scott Chamberlain Oct 01 '10 at 18:14
Ah OK, drop thw HOLDLOCK then – gbn Oct 01 '10 at 18:19

Do stored procedures lock tables/rows?

1 Answers1

Linked