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
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.
The PAGLOCK hint tells the query optimizer to take page level locks. A 8 kb page is locked instead of a table.
It locks the complete table until the end of statement
It locks the entire database until the end of statement.
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.
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.
With Holdlock query hint the locks are held until end of transaction.
With NOLOCK query hint no locks are taken. It renders read uncommitted isolation behavior and is applicable to SELECT statements only.