sql-server - 存储过程是否锁定表/行？

Question

很简单的问题。在 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

Answer 1

你可能会遇到竞争条件。

可以用一条语句完成:

• 您可以在更新中分配
• 锁定提示允许另一个进程跳过该行
• OUTPUT 子句将数据返回给调用者

试试这个...(编辑:保持锁已删除)

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

如果没有，您可能需要单独选择

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 ...

请参阅此了解更多信息:SQL Server Process Queue Race Condition