Locking hints are used with SELECT/INSERTS/UPDATE/DELETE statements to enforce or change the default locking behavior. Given below are some of the locking hints available in SQL Server
- ROWLOCK
The ROWLOCK hint tells query optimizer to lock rows (KEY level locks) instead of taking PAGE or TABLE level locks when reading or modifying data. A row lock is the lowest level of lock possible in SQL Server. One or more specific rows are locked and the others are available for concurrent sessions.
- PAGLOCK
The PAGLOCK hint tells the query optimizer to take page level locks. A 8 kb page is locked instead of a table.
- TABLOCK
It locks the complete table until the end of statement
- DBLOCK
It locks the entire database until the end of statement.
- UPDLOCK
With UPDLOCK query hint the select statement takes update locks until the end of transaction or statement. The default lock granularity level is ROWLOCK. An exclusive lock is taken if UPDLOCK is combined with TABLOCK or a table lock is taken for some other reason.
- XLOCK
WITH XLOCK query hint the select statement takes exclusive locks until the end of a transaction. The default lock granularity level is ROWLOCK, if no granularity level is specified.
- HOLDLOCK
With Holdlock query hint the locks are held until end of transaction.
- NOLOCK
With NOLOCK query hint no locks are taken. It renders read uncommitted isolation behavior and is applicable to SELECT statements only.
Further reading
http://technet.microsoft.com/en-us/library/ms172398(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms187373.aspx
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
