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
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).
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.
You could get a race condition.
It can be done in one statement:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With