相当简单的问题.在sql 2008中,如果我有一个存储过程(见下文),我是否在前两个语句之间存在竞争条件的风险,或者存储过程是否锁定了事务所涉及的事情？
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
解决方法
你可能会遇到竞争条件.
它可以在一个声明中完成：
>您可以在UPDATE中进行分配
>锁提示允许另一个进程跳过此行
> OUTPUT子句将数据返回给调用者
试试这个……(编辑：holdlock删除)
Update TOP (1) ServerLoginUsers WITH (ROWLOCK,READPAST) OUTPUT INSERTED.* SET AssignedTo = @User,SourcePool = @UserPool WHERE AssignedTo is null and [TsServer] = @Server -- not needed -> and UserName = @ServerUser
如果没有,您可能需要单独选择
Update TOP (1) ServerLoginUsers WITH (ROWLOCK,READPAST)
SET
-- yes,assign in an update
@ServerUser = UserName,-- write
AssignedTo = @User,SourcePool = @UserPool
OUTPUT INSERTED.*
WHERE
AssignedTo is null and [TsServer] = @Server -- not needed -> and UserName = @ServerUser
SELECT ...
