MSSQLServer's Lock mode introduction

Description of a SQL Server lock type

When locking in a SQL Server database, in addition to the different resources can be locked, you can also use different degrees of lock-up mode, there are multiple modes, the lock mode in SQL Server includes:

1. Shared lock (S) shared lock is used for the system data operation. Shared locks are non-exclusive and allow multiple concurrent transactions to read their locked resources. By default, when data is read, SQL Server immediately releases the shared lock.

For example, execute the query "SELECT * FROM dbo." Customer, first lock the first page, after reading, release the lock on the first page, and then lock the second page. This allows you to modify the first page that is not locked during a read operation. However, the Transaction Isolation level link option settings and the lock settings in the SELECT statement can change this default setting for SQL Server.

Executes the query "SELECT * FROM dbo." Customer with (HOLDLOCK) requires that the table be locked during the entire query process until the query is complete before releasing the lock.

2. Update lock (U) The update lock is used during the initialization phase of the modify operation to lock resources that might be modified, which avoids the deadlock caused by the use of shared locks (S). Because using a shared lock (s), modifying the data is a two-step operation, first obtaining a shared lock (s), reading the data, then upgrading the shared lock to an exclusive lock (X), and then performing the modify operation. This way, if two or more transactions concurrently request a shared lock on a transaction, these transactions escalate the shared lock to an exclusive lock (X) when the data is modified. At this point, these transactions do not release the shared lock but wait for the other party to release, which creates a deadlock. If a data is requested directly before the update lock (U), when the data is modified to upgrade to exclusive lock (X), you can avoid deadlocks.

3. Structure Lock (SCH) uses schema modification (SCH-M) locks when performing data definition language (DDL) operations on tables, such as adding columns or dropping tables. The schema stability (sch-s) lock is used when compiling the query. The schema stability Lock does not block any transaction locks, including exclusive locks. As a result, other transactions, including those that have an exclusive lock on the table, can continue to run when the query is compiled. However, DDL operations cannot be performed on the table.

4. Intent Lock (I) Intent lock indicates that SQL Server has the intention to acquire a shared or exclusive lock at the bottom of the resource. The database engine uses intent locks to secure shared locks or exclusive locks placed on the underlying resources of the lock hierarchy. Intent locks are named intent locks because they are available at lower-level locks, so they are notified of intent to place locks at lower levels.

For example, a table-level shared intent lock indicates that a transaction intends to tell an exclusive lock to release a page or row in a table.

An intent lock can be divided into:

Shared Intent Lock (IS): The transaction intends to read data by placing a shared lock on the underlying resource to which the shared intent lock is locked.

Exclusive Intent Lock (IX): The transaction intends to modify the data by placing an exclusive lock on the resource on which the lock is shared.

Shared Exclusive Intent Lock (SIX): Transactions allow other transactions to use shared locks to read top-level resources and intend to place an exclusive lock on the lower layer of the resource.

Two uses of intent lock:

• • • Prevents other transactions from modifying higher-level resources in such a way that they invalidate the lower-level locks.
    • Improves the efficiency of the database engine to detect lock collisions at a high level of granularity.

5. Bulk update lock (BU) A bulk update lock is used when you bulk copy data to a table and specify the TABLOCK hint or use sp_tableoption to set the table lock on bulk option. A bulk update lock allows a process to concurrently bulk replicate data to the same table, while preventing other processes that do not bulk copy data from accessing the table.

SQL Server uses the Lock feature description:

• NOLOCK (UNLOCKED): SQL Server does not add any locks when reading data. In this case, the user may read the data in the incomplete transaction or rollback, the so-called "dirty data".　　　　Applies only to select statements.
• HOLDLOCK (Hold lock): SQL Server holds this shared lock to the end of the entire transaction and no longer is released on the way. In other words, a shared lock is retained until the transaction completes, rather than releasing the lock immediately when the corresponding table, row, or data page is no longer needed. Equivalent to Serializable.
• Paglock (page Lock): page locks are used where a single table lock is normally used. The readcommitted performs the scan with the same lock semantics as the transaction running at the Read isolation level of the commit.
• READPAST: Skips locking a row, which causes the transaction to skip rows that are locked by other transactions (which normally appear in the result set) instead of blocking the transaction so that it waits for other transactions to release locks on those rows. Use only with the SELECT statement.
• readuncommitted: Equivalent to NOLOCK, performing a scan with the same lock semantics as a transaction running at the REPEATABLE read isolation level.
• Rowlock: Use row-level locks, rather than page-level and table-level locks with coarser granularity. Serializable performs a scan with the same lock semantics as transactions running at the serializable isolation level. Equivalent to Holdlock.
• TABLOCK: Use table locks instead of finer-grained row-level or page-level locks. SQL Server holds the lock until the end of the statement. However, if you make holdlock at the same time, the lock will be held until the end of the transaction.
• UPDLOCK: Use an update lock when reading a table instead of a shared lock and keep the lock until the end of the statement or transaction. The advantage of Updlock is that it allows you to read data (without blocking other transactions) and update the data at a later time, while ensuring that the data has not been changed since the last time the data was read.
• XLOCK: Use exclusive locks and persist until the end of a transaction on all data that is processed by the statement. The lock can be specified using Paglock or TABLOCK, in which case the exclusive lock applies to the granularity of the appropriate level. As for how many records are locked, the SQL default lock line is the row level lock, so you can use top 1 to specify that only one record is locked. SELECT TOP 1 * FROM dbo. customer  with (uplock,readpast)

Two dead locks with deadlock release

1. Deadlock

The use or management of the database is inevitably related to deadlocks, once a deadlock occurs, the data waits for each other's resources to be released, it will prevent access to the data, seriously causing the db to hang up, when the resource is locked, cannot be accessed, The session that accesses the DB can be terminated to achieve the purpose of unlocking (that is, killing the process that caused the lock).

In two or more tasks, if each task locks a resource that other tasks are trying to lock, it causes these tasks to become permanently blocked, resulting in deadlocks. For example:

• • Transaction a acquires a shared lock of row 1
  • Transaction B acquires a shared lock of row 2
  • Transaction a now requests row 2 for exclusive locks, but is blocked until transaction B finishes and frees its shared lock on row 2.
  • Transaction B now requests an exclusive lock on row 1, but is blocked until transaction a finishes and frees its row 1-owned shared lock.

Transaction A can complete after transaction B is complete, but transaction B is blocked by transaction a. This condition is also known as a circular dependency: Transaction a relies on transaction B, and transaction B closes the loop by its dependency on transaction a.

Unless you touch an external process to disconnect a deadlock, the two transactions in the deadlock will wait for the wireless period. The SQL Server Database engine deadlock monitor periodically checks for tasks that fall into a deadlock. If the monitor detects a circular dependency, it selects one of the tasks as the victim, then terminates its transaction and prompts for an error. In this way, other tasks can complete their transactions. For applications in which a transaction terminates with a lock fog, it can also retry the transaction, but it is usually done after other transactions that have fallen into a deadlock are completed.

2. Deadlock Detection

• The SQL Server database engine automatically detects deadlock loops in SQL Server. The database engine chooses a session as the deadlock victim, and then terminates the current transaction to interrupt the deadlock.
• View DMV:sys.dm_tran_locks (SELECT Resource_type,resource_description,resource_associated_entity_id,request_mode, Request_status,request_owner_type from sys.dm_tran_locks WHERE resource_type!= ' DATABASE ')
• SQL Server profile can visually display deadlock graphics events.

　　

Deadlock Example:

The first connection executes:

　　　　

BEGIN TRANUPDATEDbo. CustomerSETNRIC=' +' WHERETransactionnumber=6WAITFORDELAY'00:00:30'UPDATEDbo. EmployeeSETTs=1111 WHERETransactionnumber=1COMMIT

The second connection executes

BEGIN TRANUPDATEDbo. EmployeeSETTs=1111 WHERETransactionnumber=1WAITFORDELAY'00:00:10'UPDATEDbo. CustomerSETNRIC=' +' WHERETransactionnumber=6COMMIT

If two connections execute the database at the same time (SQL Server2008), a deadlock is automatically detected and one of the processes is terminated.

MSSQLServer's Lock mode introduction