SQL Server database table Locking Principle and how to unlock the table-Example

<Next part: SQL Server database table Locking Principle and how to unlock the table>

 

A few of my friends leave a message and suggest using examples to demonstrate the lock types mentioned in the previous article. You can view the lock types in the system dynamic view sys. dm_tran_locks. The important columns are as follows:

Resource_type	Locked resource type (Database, file, object, page, key, extent, RID, application, metadata, HOBT, appocation_unit)
Request_mode	Lock type (shared lock, update lock, exclusive lock, architecture lock, etc)
Resource_description	Resource Description
Request_session_id	Request session ID

 

I. Below we will briefly describe the example database with adventureworks2008 to filter out the shared locks of General databases. As an example, we must see the locks, so we should use with (holdlock) to keep the locks.

1. Shared locks (s) shared locks

USE AdventureWorks2008BEGIN TRANselect * from Sales.SalesOrderHeader WITH(HOLDLOCK)where SalesOrderID='43662'     SELECT resource_type, request_mode, resource_description,request_session_id, DB_NAME(resource_database_id)as resource_databaseFROM   sys.dm_tran_locksWHERE  resource_type <> 'DATABASE'--ROLLBACK TRAN

 

View the lock type before the transaction rollback:

 

Other sessions are read-only to the table and cannot be updated. In this case, start a new session to test:

select * from Sales.SalesOrderHeader  where SalesOrderID='43662'goupdate Sales.SalesOrderHeader set OrderDate=GETDATE() where SalesOrderID='43662'

 

The SELECT statement can be executed normally, and the update statement remains in the waiting state, waiting for the above session to release the lock.

 

2. Update locks (u): Update locks are a combination of shared locks and exclusive locks. Use updlock to keep update locks.

USE AdventureWorks2008BEGIN TRANselect * from Sales.SalesOrderHeader WITH(UPDLOCK)where SalesOrderID='43662'     SELECT resource_type, request_mode, resource_description,request_session_id,DB_NAME(resource_database_id)as resource_databaseFROM   sys.dm_tran_locksWHERE  resource_type <> 'DATABASE'ROLLBACK TRAN

 

Lock information:

 

 

3. exclusive locks (x): exclusive locks are used to lock the data modified by a session but cannot be modified by another session. Only nolock can be specified to read the data.

 

USE AdventureWorks2008BEGIN TRANupdate Sales.SalesOrderHeader set ShipDate=GETDATE() where SalesOrderID='43662'    SELECT resource_type, request_mode, resource_description,request_session_id,DB_NAME(resource_database_id)as resource_database--,*FROM   sys.dm_tran_locksWHERE  resource_type <> 'DATABASE'ROLLBACK TRAN

View lock:

 

 

4. intent locks (I): Intention locks are used to establish the lock hierarchy. intention locks include intention sharing (is), intention exclusive (IX), and intention exclusive sharing (six ).

The database engine uses intention locks to protect shared locks or exclusive locks (x locks) from being placed on the underlying resources of the lock hierarchy. Intention locks are named intention locks because they can be obtained before lower-level locks, so they are notified to place the locks at lower-level.

Intention lock has two purposes:

• Prevent other transactions from modifying higher-level resources in a way that invalidates lower-level locks.
• Improve the efficiency of database engines in detecting lock conflicts at a high granularity level.

 

 

5. schema locks (Sch): schema lock

• Schema stability lock (Sch-S): maintains the architecture stability. When an execution plan is generated, access to data is not blocked.
• Schema modification lock (Sch-m): Used in DDL operations. When the architecture is being changed, access to object data is blocked.

 

USE AdventureWorks2008BEGIN TRANCREATE TABLE MyTable (ID INT, NAME VARCHAR(20),COUNTRY VARCHAR(15))SELECT resource_type, request_mode, resource_descriptionFROM   sys.dm_tran_locksWHERE  resource_type <> 'DATABASE' order by request_modeROLLBACK TRAN

6. Bulk update locks (BU)

The database engine uses the large-capacity Update (BU) lock when copying large data volumes to tables, and specifiesTablockTips or usageSp_tableoptionSetTable lock on bulk LoadTable options. The large-capacity update lock (Bu lock) allows multiple threads to concurrently load data to the same table in large capacity, and prevents other processes that do not load data in large capacity from accessing the table.

 

7. Key-range locks

When the serializable transaction isolation level is used, the key range lock can implicitly protect the row range contained in the record set read by the transact-SQL statement. the key range lock prevents Phantom reads. by protecting the range of keys between rows, it also prevents phantom insertion or deletion of transaction access record sets.

 

Ii. deadlock and deadlock relief

1. deadlock

The use or management of databases inevitably involves deadlocks. in the event of a deadlock, data waits for the other party to release resources, which will block data access and seriously cause the database to crash. when the resource is locked and cannot be accessed, You can terminate the session accessing the database to unlock the database (that is, kill the process that causes the lock ).

In two or more tasks, if each task locks the resources that other tasks attempt to lock, this will cause permanent blocking of these tasks and lead to deadlocks. For example:

• Transaction A obtains the share lock of Row 1.
• Transaction B obtains the shared lock of Row 2.
• Now, transaction a requests the exclusive lock of Row 2, but it is blocked before transaction B completes and releases its shared lock held on Row 2.
• Currently, transaction B requests the exclusive lock of Row 1, but it is blocked before transaction a completes and releases its share lock held on Row 1.

Transaction A can be completed only after transaction B is completed, but transaction B is blocked by transaction. This condition is also known as cyclic dependency: transaction a depends on transaction B, and transaction B Closes the loop through the dependency on transaction.

Unless an external process is disconnected from the deadlock, both transactions in the deadlock will wait for an indefinite period of time. The Microsoft SQL Server database engine deadlock monitor regularly checks deadlocked tasks. If the monitor detects a circular dependency, it selects one of the tasks as the victim, terminates its transactions, and prompts an error. In this way, other tasks can complete their transactions. For an application that terminates a transaction with an error, it can also retry the transaction, but it usually needs to wait until other transactions that are deadlocked with it are completed.

 

2. Deadlock Detection

2.1 The SQL Server database engine automatically detects the deadlock loop in SQL Server. The database engine selects a session as the deadlock victim and terminates the current transaction (an error occurs) to interrupt the deadlock.

2.2 view DMV: SYS. dm_tran_locks

2.3 SQL Server Profiler can intuitively display deadlocked graphical events.

 

Iii. Lock compatibility

Lock compatibility controls whether multiple transactions can simultaneously obtain the locks on the same resource. If the resource has been locked by another transaction, a new lock request is granted only when the request lock mode is compatible with the existing lock mode. If the request lock mode is not compatible with the existing lock mode, the transaction requesting the new lock will wait for the release of the existing lock or wait for the lock timeout interval to expire. For example, there is no lock mode compatible with exclusive locks. If an exclusive lock (x lock) exists, no other transaction can obtain any type of lock (SHARE, update, or exclusive) for the resource before the exclusive lock (x lock) is released. In another case, if the shared lock (s lock) has been applied to the resource, even if the first transaction has not been completed, other transactions can also obtain the shared lock or update lock (U Lock) of the item ). However, before the shared lock is released, other transactions cannot obtain the exclusive lock.

 

 

 

Iv. Summary

The lock principle is abstract and transparent to users. generally, applications do not directly request locks. locks are managed internally by a part of the database engine (called the lock manager. when a database engine instance processes a Transact-SQL statement, the Database Engine Query processor determines the resources to be accessed. the query processor determines the lock types required to protect each Resource Based on the access type and transaction isolation level settings. then, the query processor will request the appropriate lock from the lock manager. if it does not conflict with the lock held by other firms, the lock manager will grant the lock.