Types of Locks in SQL Server.
SQL Server levers all locking choices. It selects which type of
lock to use after it determines the query plan. You can also force the server
to make certain locks more or less restrictive by including the holdlock, noholdlock, or shared keywords with your query.SQL Server has two ranks of locking: page locks & table locks. Page locks are commonly less restrictive than table locks. A page lock locks all of the rows on the page; table locks lock entire tables.
There are three main types of locks that SQL Server.
Shared locks are used for operations that do not change or update data, such as a SELECT statement. Shared locks are compatible with other Shared locks or Update locks.
Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes. Update locks are compatible with Shared locks only
Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE. Exclusive locks are not compatible with other lock types.
The SELECT Statement sets the Shared lock on the page, because there are no another locks on this page.
The Update Statement set Exclusive lock, but it cannot make it before SELECT Statement will be finished, because Exclusive lock is not compatible with other lock types. So, Update Statement sets Update lock.
SQL Server 2000 supports the following Locking optimizer.
SQL Server 2000 supports the following Locking optimizer.
NOLOCK: NOLOCK is known as dirty reads. This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.
HOLDLOCK: HOLDLOCK directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used.
Note: You cannot use HOLDLOCK in a SELECT statement that includes the FOR BROWSE option. HOLDLOCK is equivalent to SERIALIZABLE.
UPDLOCK: it instructs SQL Server to use update locks instead of shared locks while reading a table and holds them until the end of the command or transaction.
TABLOCK: TABLOCK takes a shared lock on the table that is held until the end of the command. If you also specify HOLDLOCK, the lock is held until the end of the transaction.
PAGLOCK: PAGLOCK is used by default. Directs SQL Server to use shared page locks.
TABLOCKX: It takes an exclusive lock on the table that is held until the end of the command or transaction.
READCOMMITTED: It Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server operates at this isolation level.
READUNCOMMITTED: Equivalent to NOLOCK.
REPEATABLEREAD: it Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.
SERIALIZABLE: it Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.
ROWLOCK: Use row-level locks rather than use the coarser-grained page- and table-level locks.
