SQL Server lock resource Mode

Learning materials, use for memo

1. Shared lock

The shared lock allows concurrent transactions to read (select) resources under the closed concurrency control. When there is a shared lock (s lock) on the resource, no other things can modify the data.

2. Update the lock

In a repeatable or serializable transaction, a modification needs to read the data first (obtain the share lock of the resource page or row), and then modify the data (this operation requires the lock to be converted to the exclusive lock ). If two transactions obtain the Shared Mode Lock on the same resource and attempt to update data at the same time, the transaction will convert the shared lock to the exclusive lock (x lock ). A deadlock occurs because neither transaction can complete the conversion.
To avoid this potential deadlock problem, SQL server uses the update lock (u ). Only one transaction can obtain the resource update lock at a time. When a transaction actually modifies data, it converts the update lock (u) to an exclusive lock.

3. exclusive lock

The exclusive lock (x lock) prevents concurrent transactions from accessing resources. When an exclusive lock is used, no other transaction can read or modify data. The read operation is performed only when the nolock prompt is used or the read isolation level is not submitted.

The data modification Statement (insert, update, or delete) combines the modification and read operations. Before executing the required modification, the statement first performs the read operation to obtain data. Silver, data modification statements usually request a shared lock and exclusive lock. For example, the update statement may modify rows in another table based on the connection with one table. In this case, in addition to the exclusive lock on the row to be updated, the update statement also modifies the join of one table in the request language to the rows in another table. In this case, in addition to the exclusive lock on the row to be updated, the update statement also reads the row share lock from the join table.

4. Intention lock

The database engine uses intention locks to protect the underlying resources at the lock level, so as to prevent other transactions from causing damage to the locked resources and improve the lock conflict detection performance.
Intention locks can improve performance, because the database engine only checks intention locks at the table level to determine whether transactions can safely obtain the locks on the table, you do not need to check the locks on each row or page in the table to determine whether the transaction can lock the entire table.

5. Architecture lock

The database engine uses schema modification locks during Table Data Definition Language (DDL) operations (such as adding columns or deleting tables) to prevent other users from accessing the table.

The database engine uses the schema stability (Sch-S) lock when compiling and executing queries. This lock intelligently organizes the modified DDL and DML operations on tables.

6. large-capacity update lock

The database engine uses the bulk update lock when copying large data volumes to a table, and specifies the tablock prompt or sets the table lock on bulk load table option using sp_tableoption. The large-capacity update lock (Bu lock) allows multiple ready-to-use processes that concurrently load data to the same table and prevent other processes that do not load data in large capacity from accessing the table.

7. Key range lock

When the serializable transaction isolation level is used, the range lock can be used to implicitly protect the row range contained in the record set read by SQL statements. Range lock can prevent phantom read. By protecting the range between rows, it also prevents phantom insertion or deletion of transaction access record sets.

Lock compatibility controls whether multiple transactions can simultaneously obtain the same resource lock. If the resource has been locked by another transaction, a new lock request will be granted only when the request lock mode is compatible with the existing lock mode. On the contrary, transactions requesting new locks will be forced to enter the waiting state, and blocking will occur.

The lock mode and compatibility are pre-defined by SQL Server and can be modified without any parameters or configurations.

If the applicationProgramThe granularity of the applied locks is small, so the chance of blocking will be small. If a connection often applies for page-level, table-level, or even database-Level Lock resources, the program may be congested.

If a connection can always release the applied lock very quickly, blocking will not happen easily. If it always holds some lock resources for a long time, it is easy to block.

There are three situations that affect the lock granularity and holding time:

1. The larger the data volume to be accessed or modified within a transaction, the more locks it applies for and the larger the granularity.

2. The more complicated a transaction is, the larger the scope of the lock it wants to apply

3. The longer a transaction lasts, the longer it will hold the lock.

The transaction isolation level can affect the lock application and release time. The statement execution plan also affects the lock granularity and the number of applications.

Original article address: SQL Server lock resource mode details