Locking Hints and Examples are as follows. The usage of them is the same but the 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 updates it later with the assurance that the data has not changed since the 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'
I hope you enjoyed this video on Locking Hints. Here are my few recent videos and I would like to know what is your feedback about them. You can also subscribe to my YouTube Channel – SQL in Sixty Seconds.
- Copy Database – SQL in Sixty Seconds #169
- 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
- Excel – Sum vs SubTotal – SQL in Sixty Seconds #167
- 3 Ways to Configure MAXDOP – SQL in Sixty Seconds #166
- Get Memory Details – SQL in Sixty Seconds #165
- Get CPU Details – SQL in Sixty Seconds #164
- Shutdown SQL Server Via T-SQL – SQL in Sixty Seconds #163
- SQL Server on Linux – SQL in Sixty Seconds 162
- Query Ignoring CPU Threads – SQL in Sixty Seconds 161
- Bitwise Puzzle – SQL in Sixty Seconds 160
- Find Expensive Queries – SQL in Sixty Seconds #159
- Case-Sensitive Search – SQL in Sixty Seconds #158
- Wait Stats for Performance – SQL in Sixty Seconds #157
- Multiple Backup Copies Stripped – SQL in Sixty Seconds #156
Reference: Pinal Dave (http://blog.SQLAuthority.com)
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 .
in sql server database,as per our requirement we have protect one table values.how to lock single table
I guess.. Rowlock and nolock should be in reverse query.. :)