|
|
Posted on 7/20/2016 2:02:51 PM
|
|
|
NOLOCK
When this option is selected, SQL Server reads or modifies data without any locks. In this case, the user may read data from an Uncommitted Transaction or Roll Back, known as "dirty data".
HOLDLOCK
When this option is selected, SQL Server maintains this shared lock until the end of the entire transaction without releasing it on the way.
UPDLOCK
When this option is selected, SQL Server uses a modify lock instead of a shared lock when reading data and maintains this lock until the end of the entire transaction or command. This option ensures that multiple processes can read data at the same time, but only that process can modify the data.
TABLOCK
When this option is selected, SQL Server will place a shared lock on the entire table until the command ends. This option ensures that other processes can only read and not modify the data.
PAGLOCK
This option is the default option, and when selected, SQL Server uses a shared page lock.
TABLOCKX (Exclusive Table Lock)
When this option is selected, SQL Server will place a lock on the entire table until the command or transaction ends. This will prevent other processes from reading or modifying the data in the table.
HOLDLOCK holds the shared lock until the entire transaction is complete and should be released as soon as the locked object is not needed, equal to the SERIALIZABLE transaction isolation level
The NOLOCK statement is executed without issuing a shared lock, allowing dirty reads, which is equal to the READ UNCOMMITTED transaction isolation level
PAGLOCK uses multiple page locks where one table lock is used
READPAST lets the sql server skip any locked lines and execute transactions, and for READ UNCOMMITTED transaction isolation levels, only skip RID locks, not page, zone, and table locks
ROWLOCK enforces the use of rowlocks
TABLOCKX enforces the use of an exclusive table-level lock, which prevents any other transaction from using the table during the transaction
UPLOCK forces the use of updates when reading a table without a shared lock
Note: The difference between locking a table in a database
SELECT * FROM table WITH (HOLDLOCK) Other transactions can read the table, but cannot update or delete
SELECT * FROM table WITH (TABLOCKX) Other transactions cannot read, update, and delete tables |
Previous:Locks, dirty reads, unrepeatable reads, and false reads in SQLNext:SQL Server looks at the deadlock and releases the deadlock
|
