sql server中这些关键字有什么用(UPDLOCK、ROWLOCK READPAST)

2021-09-10 00:00:00 sql tsql sql-server

我想知道这些关键字在 sql server (UPDLOCK, ROWLOCK READPAST) 中的用途以及何时使用?我的意思是在什么样的情况下人们会选择 (UPDLOCK, ROWLOCK READPAST).

i like to know what is the use of these keyword in sql server (UPDLOCK, ROWLOCK READPAST) and when to use it? i mean in what kind of situation people go for (UPDLOCK, ROWLOCK READPAST).

1) select top 1 COMMAND
from EXAMPLE_TABLE
with (UPDLOCK, ROWLOCK)
where PROCESSED=false;

看看上面的sql他们用的是UPDLOCK,ROWLOCK什么是UPDLOCK,ROWLOCK?

just see the above sql they use UPDLOCK, ROWLOCK what is UPDLOCK, ROWLOCK?

UPDLOCK、ROWLOCK 会发生什么?

2) SELECT TOP(1) COMMAND, PROCESSED
  FROM TABLE WITH (READPAST)
  WHERE PROCESSED = 0

什么是READPAST?当我们使用 READPAST 那么会发生什么?

what is READPAST? when we use READPAST then what happen ?

3) UPDATE  OrderTable WITH ( ROWLOCK )
    SET     ProcessorID = @PROCID
    WHERE   OrderID IN ( SELECT TOP ( 20 )
                                        OrderID
                                FROM    OrderTable WITH ( ROWLOCK )
                                WHERE   ProcessorID = 0)

这里上面的 sql 使用 ROWLOCK 进行更新语句以及嵌套的 select 子查询......为什么??

here the above sql use ROWLOCK for update statement and also in nested select sub query......why??

ROWLOCK 会发生什么?

我的要求是请用大量示例解释每一点，以便我可以理解这些关键字的用法.谢谢

it is my request that please explain each point with lots of example in such way as a result i can understand the usage of those keyword. thanks

推荐答案

它们是用于指定锁定方法的 (DML) 语句:

They are (DML) statements for specifying a locking method:

UPDLOCK 将更新锁放置在被选择的行上，直到事务结束.其他事务不能更新或删除该行，但可以选择它.

UPDLOCK places update locks on rows that are being selected until the end of the transaction. Other transaction cannot update or delete the row but they are allowed to select it.

ROWLOCK 将锁放在与页或表锁相对的行级.

ROWLOCK places locks on row level opposed to a page or table lock.

READPAST 被锁定的记录不返回

这是一个同时使用这两个语句的示例，这意味着在第一个事务结束之前，其他事务无法读取更新或删除数据.

This is an example of using both statements, meaning that other transaction cannot read update or delete data until the first transaction ends.

BEGIN TRAN

  select top 1 COMMAND
  from EXAMPLE_TABLE
  with (UPDLOCK, ROWLOCK)
  where PROCESSED=false;

ROLLBACK

这里交易还没有结束:

BEGIN TRANSACTION

      UPDATE TOP(1) EXAMPLE_TABLE
      SET colum1 = colum1 + 1

如果执行select语句，被锁定的记录不返回，例如:

If you execute a select statement records that are locked are not returned, example:

SELECT COUNT(*)

FROM EXAMPLE_TABLE WITH(READPAST)

相关文章