SQL Server Lock Encyclopedia

Lock hint Description
The HOLDLOCK retains the shared lock until the transaction completes, instead of releasing the lock as soon as the corresponding table, row, or data page is no longer needed. HOLDLOCK is equivalent to SERIALIZABLE.
NOLOCKDo not issue a shared lock, and do not provide an exclusive lock. When this option is in effect, it is possible to read uncommitted transactions or a set of pages that are rolled back in the middle of the read. Dirty reads may occur. Applies only to select statements.
PaglockPage locks are used where a single table lock is normally used.
The readcommitted performs the scan with the same lock semantics as the transaction running at the Read isolation level of the commit. By default, SQL Server 2000 operates at this isolation level.
READPAST skips the locking line. This option causes the transaction to skip rows that are locked by other transactions, which are normally displayed in the result set, rather than blocking the transaction so that it waits for other transactions to release locks on those rows. The READPAST lock hint is only available for transactions that run at the commit read isolation level, and is read only after row-level locks. Applies only to SELECT statements.
ReadUncommitted is equivalent to NOLOCK.
RepeatableRead performs a scan with the same lock semantics as transactions running at the repeatable read isolation level.
RowlockUse row-level locks instead of coarser-grained page-level and table-level locks.
SERIALIZABLE performs a scan with the same lock semantics as transactions running at the serializable isolation level. Equivalent to HOLDLOCK.
TABLOCK uses table locks instead of finer-grained row-level or page-level locks. SQL Server holds the lock until the end of the statement. However, if you specify HOLDLOCK at the same time, the lock will be held until the end of the transaction.
Tablockx uses the table's exclusive lock. The lock prevents other transactions from reading or updating the table and holding it until the end of the statement or transaction.
UPDLOCK uses an update lock when reading a table instead of a shared lock and retains the lock until the end of the statement or transaction. The advantage of UPDLOCK is that it allows you to read data (without blocking other transactions) and update the data at a later time, while ensuring that the data has not been changed since the last time the data was read.
XLOCKUse an exclusive lock and persist until the end of the transaction on all data processed by the statement. The lock can be specified using Paglock or TABLOCK, in which case the exclusive lock applies to the appropriate level of granularity