Shulou(Shulou.com)05/31 Report--

This article will explain in detail what the types of database deadlock are, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

According to Microsoft Books online, the types of deadlocks are as follows:

Shared locks (S locks) allow concurrent transactions to read (SELECT) resources under closed concurrency control. When a shared lock (S lock) exists on a resource, no other transaction can modify the data. The shared lock (S lock) on the resource is released as soon as the read operation is complete, unless the transaction isolation level is set to repeatable read or higher, or lock prompts to retain the shared lock (S lock) for the duration of the transaction.

Update locks update locks (U locks) can prevent common deadlocks. In a repeatable or serializable transaction, the transaction reads the data [acquires the shared lock (S lock) of the resource (page or row)] and then modifies the data [this operation requires the lock to be converted to an exclusive lock (X lock)]. If two transactions acquire a shared mode lock on a resource and then try to update the data at the same time, one transaction attempts to convert the lock to an exclusive lock (X lock). The conversion from shared mode to exclusive lock must wait for some time because the exclusive lock of one transaction is not compatible with the shared mode lock of other transactions; lock waiting occurs. The second transaction attempts to acquire an exclusive lock (X lock) for update. Deadlocks occur because both transactions are converted to exclusive locks (X locks), and each transaction waits for the other transaction to release the shared mode lock. To avoid this potential deadlock problem, use an update lock (U lock). Only one transaction at a time can obtain an update lock (U lock) for a resource. If the transaction modifies the resource, the update lock (U lock) is converted to an exclusive lock (X lock).

Exclusive locks (X locks) prevent concurrent transactions from accessing resources. When using an exclusive lock (X lock), no other transaction can modify the data; read operations occur only when the NOLOCK prompt is used or the read isolation level is not committed. Data modification statements, such as INSERT, UPDATE, and DELETE, combine modification and read operations. Statement first performs a read operation to get the data before performing the required modification operation. Therefore, data modification statements usually request shared and exclusive locks. For example, a UPDATE statement might modify rows in another table based on a join with another table. In this case, in addition to requesting an update of the exclusive lock on the row, the UPDATE statement will also request a shared lock on the row read in the join table.

The intent lock database engine uses intent locks to protect shared locks (S locks) or exclusive locks (X locks) on the underlying resources of the lock hierarchy. Intention locks are named because they can be acquired before lower-level locks, so they are notified of the intention to place the lock at a lower level. Intention locks have two uses:

Prevents other transactions from modifying higher-level resources in a manner that invalidates lower-level locks.

Improve the efficiency of the database engine in detecting lock conflicts at a higher level of granularity.

For example, a shared intention lock is requested at the table level before a shared lock (S lock) is requested on the page or row of the table. Setting an intention lock at the table level prevents another transaction from subsequently acquiring an exclusive lock (X lock) on the table that contains that page. Intentional locks can improve performance because the database engine checks intentional locks only at the table level to determine whether a transaction can safely acquire locks on that table. There is no need to check the locks on each row or page in the table to determine whether the transaction can lock the entire table. Intention lock includes intention sharing (IS), intention exclusive sharing (IX) and intention exclusive sharing (SIX).

Intention sharing (IS) protects shared locks that are requested or acquired for some, but not all, lower-level resources in the hierarchy.

IX protects exclusive locks that are requested or acquired for some, but not all, lower-level resources in the hierarchy. IX, a superset of IS, also protects shared locks for low-level resource requests.

Intentional exclusive sharing (SIX) protects shared locks that are requested or acquired for some (but not all) lower-level resources in the hierarchy and those that are requested or acquired for some (but not all) lower-level resources. Top-level resources allow the use of concurrent IS locks. For example, acquiring the SIX lock on the table also acquires the intention exclusive lock on the page being modified and the exclusive lock on the modified row. Although each resource can have only one SIX lock at a time to prevent other transactions from updating the resource, other transactions can read lower-level resources in the hierarchy by acquiring table-level IS locks.

IU protects update locks that are requested or acquired for all lower-level resources in the hierarchy. Use IU locks only on page resources. If an update operation is made, the IU lock is converted to an IX lock.

Share the combination of SIU S locks and IU locks as a result of acquiring these locks separately and holding both locks at the same time. For example, a transaction executes a query with a PAGLOCK prompt, and then performs an update operation. The query with the PAGLOCK prompt acquires the S lock, and the update operation acquires the IU lock.

Update the combination of intention UIX U locks and IX locks as a result of acquiring these locks separately and holding both locks at the same time.

The schema lock database engine uses schema modification (Sch-M) locks during Table data definition language (DDL) operations, such as adding columns or deleting tables. While the lock is maintained, the Sch-M lock prevents concurrent access to the table. This means that the Sch-M lock blocks all peripheral operations before it is released. Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent concurrent operations from accessing the affected tables. The database engine uses schema stability (Sch-S) locks when compiling and executing queries. Sch-S locks do not block certain transaction locks, including exclusive (X) locks. Therefore, while the query is compiled, other transactions, including those that use X locks on the table, will continue to run. However, concurrent DDL operations and concurrent DML operations that acquire Sch-M locks cannot be performed on the table.

The bulk update lock database engine uses bulk update (BU) locks when bulk copying data into tables and specifies TABLOCK prompts or sets table lock on bulk load table options using sp_tableoption. Bulk update locks (BU locks) allow multiple threads to bulk load data into the same table concurrently while preventing other processes that do not bulk load data from accessing the table.

Key range locks when serializable transaction isolation levels are used, key range locks implicitly protect the range of rows contained in the recordset read by the Transact-SQL statement. Key range locks prevent illusions. By protecting the range of keys between rows, it also prevents phantom insertions or deletions of recordsets accessed by transactions.

About the types of database deadlock which are shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.