存储过程是否锁定表/行？ [英] Do stored procedures lock tables/rows?

问题描述

这是一个简单的问题。在SQL 2008中，如果我有一个存储过程（见下文），我是否运行前两个语句之间的竞争条件的风险，或者存储过程对它触摸像事务做的事情做锁定吗？

ALTER PROCEDURE [dbo]。[usp_SetAssignedTo]
- 添加存储过程的参数
@Server varchar ），
@User varchar（50），
@UserPool varchar（50）
AS
BEGIN
SET NOCOUNT ON;
Declare @ServerUser varchar（50）

- 查找空闲记录
SELECT top 1 @ServerUser = UserName
来自ServerLoginUsers
其中AssignedTo为null和[TsServer] = @Server

- 将免费记录设置为用户
更新ServerLoginUsers
设置AssignedTo = @User，AssignedToDate = getdate（），SourcePool = @UserPool
其中[TsServer] = @Server和UserName = @ServerUser

- 如果更新，则记录回来。如果不可用，则为空。
select *
来自ServerLoginUsers
其中[TsServer] = @Server
和UserName = @ServerUser
和AssignedTo = @User
END


解决方案

您可以得到一个竞争条件。

可以在一个语句中完成：

• 您可以在UPDATE中指定


• 锁定提示允许其他进程跳过此行


• OUTPUT子句向调用者返回数据






尝试此操作...（编辑：holdlock删除）

  （1）ServerLoginUsers WITH（ROWLOCK，READPAST）
 OUTPUT INSERTED。* 
 SET 
 AssignedTo = @User，AssignedToDate = getdate（），SourcePool = @UserPool 
 WHERE 
 AssignedTo为null，[TsServer] = @Server  - 不需要 - >和UserName = @ServerUser 
  

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

 更新TOP（1）ServerLoginUsers WITH（ROWLOCK，READPAST）
 SET 
  - 是，在更新中分配
 @ServerUser = UserName，
  -  write 
 AssignedTo = @User，AssignedToDate = getdate（），SourcePool = @UserPool 
 OUTPUT INSERTED。* 
 WHERE 
 AssignedTo is null和[TsServer] = @Server  - 不需要 - >和UserName = @ServerUser 
 
 SELECT ... 
  

更多： http://stackoverflow.com/questions/939831/sql-server-process- queue-race-condition




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

解决方案

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: http://stackoverflow.com/questions/939831/sql-server-process-queue-race-condition

这篇关于存储过程是否锁定表/行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！