Lock mode in Microsoft SQL Server

When locking an SQL Server database, in addition to locking different resources, you can also use different degrees of locking, that is, there are multiple lock modes. The locking modes in SQL Server include:

1. in SQL Server, shared locks are used for all read-only data operations. The shared lock is exclusive and allows multiple concurrent transactions to read the locked resources. By default, after data is read, SQL Server immediately releases the shared lock. For example, when "select * from authors" is executed, the first page is locked first. After reading the page, the first page is released and the second page is locked. In this way, you can modify the first page that is not locked during the reading operation. However, both the transaction isolation level connection option setting and the lock setting in the SELECT statement can change the default setting of SQL Server. For example, "select * from authors holdlock" requires that the table be locked during the entire query process until the query is complete.

2. The update lock is used to lock resources that may be modified during the initialization phase of the modification operation. This prevents deadlocks caused by the use of shared locks. When a shared lock is used, the data modification operation is divided into two steps. First, a shared lock is obtained, data is read, the shared lock is upgraded to an exclusive lock, and then the modification is performed. In this way, if two or more transactions apply for a shared lock for one transaction at the same time, these transactions must upgrade the shared lock to an exclusive lock when modifying data. At this time, these transactions will not release the shared lock, but will wait for the other party to release, resulting in a deadlock. If you apply for an update lock before data modification and upgrade it to the exclusive lock when data is modified, the deadlock can be avoided.

3. The exclusive lock is retained to modify data. The resources it locks. Other transactions cannot be read or modified.

4. The schema lock is used when the schema lock is used to execute the table's Data Definition Language (DDL) operation (such as adding a column or removing a table. When compiling a query, the schema stability (Sch-S) Lock is used. The schema stability (Sch-S) Lock does not block any transaction lock, including the exclusive lock. Therefore, when compiling a query, other transactions (including those with an exclusive lock on the table) can continue to run. However, you cannot perform DDL operations on tables.

5. Intention lock intention lock indicates that SQL Server has the intention to obtain the shared or exclusive lock at the lower layer of the resource. For example, table-level shared intention locks indicate the transaction intention to release the exclusive locks to the pages or rows in the table. Intention locks can be divided into shared intention locks, exclusive intention locks, and shared exclusive intention locks. The shared intention lock indicates that the transaction intention is to place a shared lock on the lower-level resources locked by the shared intention lock to read data. Exclusive intention lock indicates that the transaction intention is to place an exclusive lock on the lower-level resources locked by the shared intention lock to modify data. The shared exclusive lock indicates that the transaction allows other transactions to use the shared lock to read top-level resources and intends to place the exclusive lock on the lower layer of the resource.

6. A large-capacity update lock is used when the data is copied to a table in large capacity and the tablock prompt is specified or the table lock on bulk table option is set using sp_tableoption. The large-capacity update lock allows a process to concurrently copy large data volumes to the same table. It also prevents other processes that do not perform large-capacity data replication from accessing the table.

 

SQL Server uses select to lock:

Function Description

Nolock (no lock)
When this option is selected, SQL server does not apply any lock when reading or modifying data. In this case, the user may read the data in the uncommitted transaction or roll back, that is, the so-called "dirty data ".
Holdlock)
When this option is selected, SQL Server will keep the shared lock until the end of the entire transaction, instead of releasing it on the way.
Updlock)
When this option is selected, SQL server uses the modification lock to replace the shared lock when reading data, and keeps the lock until the entire transaction or command ends. This option ensures that multiple processes can read data at the same time, but only the process can modify data.
Tablock)
When this option is selected, SQL Server sets a shared lock on the entire table until the command ends. This option ensures that other processes can only read but cannot modify data.
Paglock)
This option is the default option. When selected, SQL server uses the share page lock.
Tablockx (exclusive table lock)
When this option is selected, SQL Server will lock the entire table until the command or transaction ends. This prevents other processes from reading or modifying table data.

Function Description

Holdlock

The shared lock is retained until the transaction is completed, instead of releasing the lock immediately when the corresponding table, row, or data page is no longer needed. Holdlock is equivalent to serializable.

Nolock

Do not issue a shared lock, and do not provide an exclusive lock. When this option is enabled, it may read uncommitted transactions or a group of pages that are rolled back during reading. Dirty reads may occur. Applies only to select statements.

Paglock

A page lock is usually used where a single table lock is used. Readcommitted performs scanning with the same lock semantics as transactions running at the committed read isolation level. By default, SQL Server 2000 operates at this isolation level.

Readpast

Skip the lock row. This option causes the transaction to skip the rows locked by other transactions (these rows are usually displayed in the result set), rather than blocking the transaction, wait for other transactions to release the locks on these rows.

Readpast

The lock prompt is only applicable to transactions that run at the committed read isolation level and read only after the row-Level Lock. Only applicable to select statements.

Readuncommitted

It is equivalent to nolock. Repeatableread performs scanning with the same lock semantics as transactions running at the repeatableread isolation level.

Rowlock

Use row-level locks instead of coarse-grained page-level locks and table-level locks. Serializable performs scanning with the same lock semantics as transactions running at the serializable read isolation level. Equivalent to holdlock.

Tablock

Use table locks instead of finer-grained row-level locks or page-level locks. Before the statement ends, SQL Server keeps holding the lock. However, if both holdlock is specified, the lock will be held until the transaction ends.

Tablockx

Use the exclusive lock of the table. This lock prevents other transactions from reading or updating tables and holding them until the statement or transaction ends.

Updlock

The update lock is used when reading the table, instead of the shared lock, and the lock is kept until the statement or transaction ends. Updlock allows you to read data (without blocking other transactions) and update data later. It also ensures that the data has not been changed since the last read.

Xlock

Use the exclusive lock until the end of the transaction on all data processed by the statement. You can use paglock or tablock to specify the lock. In this case, the exclusive lock applies to an appropriate level of granularity. As for the number of records locked, the default locking behavior of SQL is Row-level locking, so you can use top 1 to specify to lock only one record/

Select top 1 * from TB with (updlock, readpast)