In a SELECT statement, you can specify one or more of the following locking options for a table:
| Option | Description |
|---|---|
| NOLOCK | Directs SQL Server not to issue shared locks and not to honor exclusive locks. When this option is in effect, it is possible to read a noncommitted transaction or a set of pages that are rolled back in the middle of a read. This option is also known as "dirty reads." |
| HOLDLOCK | Instructs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used, instead of releasing the lock as soon as the required table, view, or data page is no longer needed. |
| UPDLOCK | Directs SQL Server to use update locks instead of shared locks while reading a table and to hold the locks until the end of the command or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later on with the assurance that the data has not changed since you read it. |
| TABLOCK | Directs SQL Server to use a shared lock on a table. This lock allows others to read a table, but prevents them from updating it. This lock is held until the end of the command; however, if you also specify HOLDLOCK, the lock is held until the end of the transaction. |
| PAGLOCK | Directs SQL Server to use shared page locks. This is the default. |
| TABLOCKX | Directs SQL Server to use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the command or transaction. |
These locking options override corresponding session-level options specified with the SET statement (see Customizing Transaction Isolation Level). For details about these options, see SELECT statement in the Microsoft SQL Server Transact-SQL Reference.