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

发布时间：2020-12-12 06:42:22 所属栏目：MsSql教程来源：网络整理

导读：相当简单的问题.在SQL 2008中,如果我有一个存储过程(见下文),我是否在前两个语句之间存在竞争条件的风险,或者存储过程是否锁定了事务所涉及的事情？ ALTER PROCEDURE [dbo].[usp_SetAssignedTo] -- Add the parameters for the stored procedure here @Server

相当简单的问题.在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 ...

请看更多：SQL Server Process Queue Race Condition

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!