Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Do stored procedures lock tables/rows?

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
like image 937
Scott Chamberlain Avatar asked Oct 01 '10 16:10

Scott Chamberlain


People also ask

Does SELECT statement lock the rows?

SELECT statements get shared locks on the rows that satisfy the WHERE clause (but do not prevent inserts into this range). UPDATEs and DELETEs get exclusive locks on a range of rows. INSERT statements get exclusive locks on single rows (and sometimes on the preceding rows).

Are stored procedures secure SQL?

As seen from the process above, stored procedures are a secure and safe way to give access to your database. That means someone can only be able to do what is defined in stored procedures that you have given him permission to call. And that makes stored procedures great for securing data in a database.


1 Answers

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

like image 136
gbn Avatar answered Sep 23 '22 02:09

gbn