Go SQL Server table locking principle and how to unlock it

October 13, 2010 12:46 Source: Ministry of Matsumasa's blog Department Matsumasa Editor: Huminya

　　A: Below with AdventureWorks2008 for the sample database to do a brief description , filter out the general database of shared locks, as an example must see the lock, so with (HOLDLOCK) to maintain the lock.

1. Shared locks (S) share lock

 UseAdventureWorks2008BEGIN TRANSelect *  fromSales.SalesOrderHeader with(HOLDLOCK)whereSalesOrderID='43662'     SELECTResource_type, Request_mode, resource_description,request_session_id,db_name(resource_database_id) asResource_database fromsys.dm_tran_locksWHEREResource_type<> 'DATABASE'--ROLLBACK TRAN

Before the transaction is rolled back, look at the type of lock:

The other session is read-only to the table, cannot be updated, and a new session test is opened:

Select *  from Sales.SalesOrderHeader  where SalesOrderID='43662'go Update Set OrderDate=GETDATEwhere SalesOrderID='43662 '

Select can execute normally, and the UPDATE statement waits until the session above releases the lock.

　　

2. Update locks (U): Update lock is a combination of shared and exclusive locks. Keep Update lock with Updlock

 UseAdventureWorks2008BEGIN TRANSelect *  fromSales.SalesOrderHeader with(UPDLOCK)whereSalesOrderID='43662'     SELECTResource_type, Request_mode, resource_description,request_session_id,db_name(resource_database_id) asResource_database fromsys.dm_tran_locksWHEREResource_type<> 'DATABASE'ROLLBACK TRAN

To view information about a lock:

3.Exclusive Locks (X): An exclusive lock is to lock data that is modified by a session and not be modified by another session. Only NOLOCK can be specified for reading.

 UseAdventureWorks2008BEGIN TRANUpdateSales.SalesOrderHeaderSetShipDate=GETDATE()whereSalesOrderID='43662'    SELECTResource_type, Request_mode, resource_description,request_session_id,db_name(resource_database_id) asResource_database--,* fromsys.dm_tran_locksWHEREResource_type<> 'DATABASE'ROLLBACK TRAN 

To view locks:

4.Intent Locks (I): Intent lock is used to establish the hierarchy of locks. An intent lock consists of three types: intent sharing (IS), intent Exclusive (IX), and intent exclusive sharing (SIX).

The database engine uses intent locks to secure shared locks (S locks) or exclusive locks (X locks) 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.

There are two uses of intent locks:

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. Schema Locks (Sch): Schema lock

Schema stability Lock (SCH-S): Preserves schema stability and does not block access to data when the execution plan is generated.

Schema modification Lock (SCH-M): Used in DDL operations. Block access to object data when the schema is being changed.

 UseAdventureWorks2008BEGIN TRANCREATE TABLEMyTable (IDINT, NAMEVARCHAR( -), CountryVARCHAR( the))SELECTResource_type, Request_mode, resource_description fromsys.dm_tran_locksWHEREResource_type<> 'DATABASE' Order  byRequest_modeROLLBACK TRAN

6. Bulk Update Locks (BU)

The database engine uses a bulk update (BU) lock when bulk copying data to a table, and specifies the TABLOCK hint or the table lock on bulk load table option that is set with sp_tableoption. A bulk update lock (BU Lock) allows multiple threads to load data concurrently into the same table, while preventing other processes that do not bulk load data from accessing the table.

7. Key-range Locks

When using the serializable transaction isolation level, a key-range lock can implicitly protect the range of rows contained in a recordset that is read by a Transact-SQL statement. Key-range locks prevent Phantom reads. By protecting the range of keys between rows, it also prevents phantom insertions or deletions to the recordset accessed by the transaction.

　　Two: Deadlock with deadlock release

1. Deadlock

The use or management of databases is inevitably related to deadlocks. Once a deadlock occurs, the data waits for each other's resources to be released, preventing access to the data, which can cause the db to hang out. When a resource is locked and 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 a deadlock. For example:

Transaction A acquires a shared lock for row 1.

Transaction B gets the shared lock for row 2.

Transaction A now requests an exclusive lock on line 2, but is blocked until transaction B finishes and frees its shared lock on row 2.

Transaction B now requests an exclusive lock on line 1, but is blocked until transaction A finishes and frees its shared lock on row 1.

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 an external process disconnects a deadlock, two transactions in the deadlock will wait indefinitely. The Microsoft 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 an application where the transaction terminates incorrectly, it can also retry the transaction, but usually waits until the other transaction with which it is deadlocked is completed.

2. Deadlock Detection

The 2.1 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 (an error occurs) to interrupt the deadlock.

2.2 Views DMV:sys.dm_tran_locks

2.3 SQL Server Profiler is able to visually display deadlock graphics events.

Three: Lock compatibility

Lock compatibility Controls whether multiple transactions can acquire locks on the same resource at the same time. If a resource is locked by another transaction, a new lock request is granted only if the mode of the request lock is compatible with the mode of the existing lock. If the mode of the request lock is incompatible with the mode of the existing lock, the transaction requesting the new lock waits for the existing lock to be freed or the lock timeout interval to expire. For example, there is no lock mode compatible with an exclusive lock. If you have an exclusive (x) lock, no other transaction can acquire any type of (shared, updated, or exclusive) lock on the resource until the exclusive lock (x Lock) is released. Alternatively, if a shared lock (S-Lock) has been applied to a resource, other transactions can acquire a shared or update lock (U Lock) for the item, even if the first transaction has not yet completed. However, other transactions cannot acquire an exclusive lock until the shared lock is released.

Four: summary

The principle of the lock is more abstract, transparent to the user, without too much attention. Applications generally do not request locks directly. A lock is managed internally by a part of the database engine, called the lock manager. When a database engine instance processes Transact-SQL statements, the database Engine query processor determines which resources will be accessed. The query processor determines the type of lock required to protect each resource based on the access type and transaction isolation level settings. The query processor then requests the lock manager for the appropriate lock. If a lock held with another transaction does not conflict, the lock manager grants the lock.

Go SQL Server table locking principle and how to unlock it