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

  1. 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.

  1. PAGLOCK

The PAGLOCK hint tells the query optimizer to take page level locks. A 8 kb page is locked instead of a table.

  1. TABLOCK

It locks the complete table until the end of statement

  1. DBLOCK

It locks the entire database until the end of statement.

  1. 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.

  1. 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.

  1. HOLDLOCK

With Holdlock query hint the locks are held until end of transaction.

  1. 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

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook