SQL SERVER – 2005 Locking Hints and Examples

Locking Hints and Examples are as follows. The usage of them is same same but effect is different.

ROWLOCK

Use row-level locks when reading or modifying data.

PAGLOCK
Use page-level locks when reading or modifying data.

TABLOCK
Use a table lock when reading or modifying data.

DBLOCK
Use a database lock when reading or modifying data.

UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.

XLOCK

Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.

HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required.

NOLOCK
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.

Examples:

SELECT OrderID
FROM Orders WITH (ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000
UPDATE Products WITH (NOLOCK)
SET ProductCat = 'Machine'
WHERE ProductSubCat = 'Mac'

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – SELECT vs. SET Performance Comparison
Next Post
SQL SERVER – Query to Retrieve the Nth Maximum Value

Related Posts

185 Comments. Leave new

  • Hi Pinal,

    I have question like , we will be doing the data scanning and it will make entry in the table , and in the same time i need to fetch the maximum id from the table . i cannot stop the scanning .. but how will fetch the max id from that table when the scanning is continuous. whick lock needs to be implemented .

    Reply
  • bujji.skillfurys@gmail.com
    January 28, 2016 12:05 pm

    in sql server database,as per our requirement we have protect one table values.how to lock single table

    Reply
  • I guess.. Rowlock and nolock should be in reverse query.. :)

    Reply

Leave a Reply

Menu