Locks in SQL Server 2005 (1)

In a previous essay, simply mention the isolation level in SQL Server. The isolation level of SQL Server is implemented through the locking mechanism. Now go deeper and talk about locks in SQL Server.

Before you begin, define the prerequisites:

1. The implementation of isolation level is actually locked on different resources.
2. Every access to a database (CRUD) We call it a transaction (a block of statements in the Begin Tran,commit Tran|rollback Tran, or an ad hoc query-a single SQL). Different transactions that have access to the same database resource at the same time create a series of problems (see SQL Server Isolation Level (1)).
3. Most transactions attempt to add a corresponding lock (acquire lock, request a lock) to the resource before accessing the resource. Only the READ UNCOMMITTED isolation level and the SELECT statement with the WITH (No Lock) hint on the table will not request a lock.
3. Read locks (Shared lock s) do not block read locks, read locks block write locks (exclusive lock X), and write locks block read locks.
4. In one transaction, the read lock of the SELECT statement is persisted until the end of the transaction (commit or rollback), only at the repeatable read or higher isolation level, read The Committed isolation level causes non-repeatable reads because the read lock is released directly after the execution of the SELECT statement in the transaction (the transaction is not completed).

5. Locks in the current transaction can be viewed through the select * from sys.dm_tran_locks query

==================================== start of Split line =========================================================

There are different granularity locks in the server: row locks, key locks, page locks, table locks, and so on, and the reason for these locks is to make a balance between concurrency and system overhead. Smaller locks with better concurrency and greater overhead such as row lock concurrency are better than table locks, but the overhead of the system is greater than the table lock.

A. When acquiring a table lock: The entire table includes rows that are not queried for use to be locked, and other queries that attempt to request incompatible locks on the table will wait. Only one table lock can be maintained in the system at this time.

B. When a row lock is obtained: Only rows that are used by the query are locked, and queries that request the other row can proceed smoothly. Concurrency is better than a, but maintaining a lock on each of the used row systems increases the overhead of the system.

Here are some of the resources of the lock, here is probably the first to understand the good

Resources	Description
RID	Row ID, which locks one row of the entire table
KEY	Locks a row in the index to lock the range of a row in the serialized isolation level.
PAGE	8KB-size data pages or index pages
EXTENT	A contiguous set of eight pages, such as a data page or index page.
TABLE	The entire table that includes all the data and indexes.
DATABASE	Database.

At the time of a SQL execution, the above resources are requested more or less, and requests for these resources can be seen as requests for locks on those resources. Only successful requests to the locks on these resources, or the successful locking of these resources in the current transaction, can be crud on these resources.

For different resource requests, SQL Server applies a different lock mode to try to lock on the resource. Frequently-contacted lock modes are as follows:

Shared Lock (S):
A request to a shared lock (read lock) allows a transaction to read a resource, at which point the other transaction will not request an exclusive lock (write lock) for that resource, but can request a shared lock. After the resource read is complete, the shared lock is freed (even if the transaction has not ended at this time). A shared lock lasts until the end of the transaction, unless the transaction is performed under read repeatable or higher isolation, or by using SQL hint to hold the lock (for example, with (HOLDLOCK)) while the transaction is executing.

Update Lock (U):
The update lock exists to avoid deadlocks in the update statements of different transactions in a concurrent environment.

A typical update operation will go through the following two steps:
Step 1. Get the shared lock of the resource that will be updated read out the data
Step 2: Convert the shared lock to an exclusive lock and write the updated data to the resource

Considering the concurrency environment, two transactions reached step 1 at the same time, acquiring shared locks to the same resource (no blocking between shared locks). Then both transactions begin to convert the attempted shared lock to an exclusive lock, and the discovery cannot be converted because at this point the two transactions also retain a shared lock on the resource, and the shared lock blocks the exclusive lock. Two transactions are waiting for each other's shared lock to be released, resulting in a deadlock.

In the update mode above, an update operation will no longer be an atomic operation, since steps 1 through 2 will be affected by other transactions. Therefore, it is necessary to introduce the update lock to replace the shared lock acquired in step 1, and the update lock is mutually exclusive, so there will be no two update operations to obtain the updated lock. It is possible that only one transaction gets the update lock, and the other transaction waits for the release of the update lock.

Finding a resource with an update lock instead of a shared lock in the update will prevent the concurrent update from generating a deadlock, and because the update lock can be compatible with the shared lock, other transactions can still place a shared lock on the resource.

Exclusive Lock (X)
An exclusive lock blocks all other locks and cannot read or modify resources that have exclusive locks.

Intent Lock (Is,ix,six)
An intent lock indicates that SQL Server wants to acquire a lower-level shared or exclusive lock on the current resource. For example, an IS lock on a table (intent shared Lock) indicates that a transaction places a shared lock on a page or row in a table (a resource with a smaller granularity than a table), whereas placing an intent lock avoids subsequent transactions acquiring an exclusive lock on the table. Intent locks can improve performance, because when a transaction acquires a lock on a table, it does not need to check the table's resources (pages or rows) to see if it is locked, only to check the table for intentional locking.