What is the use of these keyword in sql server (UP

问题:

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;

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

what will happen for UPDLOCK, ROWLOCK ?

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

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)

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

what will happen for 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

回答1:

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

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 places locks on row level opposed to a page or table lock.

READPAST Records that are locked are not returned

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

Here the transaction has not ended:

BEGIN TRANSACTION

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

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

SELECT COUNT(*)

FROM EXAMPLE_TABLE WITH(READPAST)