SQL Server database table locking principle and how to unlock a table

1. database table Locking principle

1.1 The current C/S,B/S structure is multi-user access to the database, each time there will be thousands of users to access the DB, which will also access the same data, will cause data inconsistencies or read dirty data.

1.2 Acid principle of business

1.3 Locks are an important part of a relational database, and the database must have a locking mechanism to ensure the integrity and consistency of the data.

1.3.1 resources that can be locked in SQL Server:

1.3.2 size of the lock:

Upgrade of 1.3.3 Lock:

Lock escalation thresholds and lock escalation are determined automatically by the system and do not require user settings.

Types of 1.3.4 Locks:

(1) Shared lock:

Shared locks are used for all read-only data operations.

(2) Modify the Lock:

Modify locks are used during the initialization phase of the modify operation to lock resources that might be modified, which avoids the deadlock caused by shared locks

(3) Exclusive lock:

An exclusive lock is reserved for modifying data. The resources it locks, other transactions cannot be read, and cannot be modified. An exclusive lock cannot be compatible with other locks.

(4) Architecture lock

Structural locks are divided into structural modification locks (sch-m) and structural stability Locks (sch-s). When you perform a table definition language operation, SQL Server takes a SCH-M lock and SQL Server uses a sch-s lock when compiling the query.

(5) Intent lock

Intent locks indicate that SQL Server has the intention of acquiring a shared or exclusive lock on the lower level of the resource.

(6) Batch modification lock

Use bulk Modify locks when copying data in bulk

1.3.4 SQL Server lock type

(1) HOLDLOCK: holds a shared lock on the table until the entire transaction ends, rather than releasing the added lock immediately after the statement is executed.

(2) NOLOCK: Do not add the shared lock and exclusive lock, when this option takes effect, may read the uncommitted read data or "dirty data", this option only applies to the SELECT statement.

(3) Paglock: Specifies that page locks are added (otherwise, table locks may be added).

(4) The readcommitted performs the scan with the same lock semantics as the transaction that is running at the read-committed isolation level. By default, SQL Server 2000 operates at this isolation level.

(5) READPAST: Skips data rows that have been locked, this option will cause the transaction to read data when the data rows that have been locked by other transactions are skipped, instead of blocking until other transactions release the lock.

Readpast is only applied to the SELECT statement operation in a transactional operation under the Read Committed isolation level.

(6) ReadUncommitted: equivalent to Nolock.

(7) RepeatableRead: SET TRANSACTION to REPEATABLE read isolation level.

(8) Rowlock: Use row-level locks instead of coarser-grained page-level and table-level locks.

(9) SERIALIZABLE: Performs a scan with the same lock semantics as the transaction running at the serializable isolation level. Equivalent to HOLDLOCK.

TABLOCK: Specifies that table-level locks are used instead of row-level or page-level locks, and SQL Server releases the lock when the statement is finished, and if Holdlock is specified at the same time, the lock remains until the end of the transaction. Tablockx: Specifies that an exclusive lock is used on the table, which prevents other transactions from reading or updating the table's data until the end of the statement or the entire transaction.

UPDLOCK: Specifies that the update lock is set when reading data in the table (update lock) instead of the shared lock, which remains until the end of the statement or the entire transaction, and uses UPDLOCK to allow the user to read the data first (without blocking other users from reading the data). And it is guaranteed that the data will not be modified by other users for a period of time when the data is later updated.

(This paragraph is excerpted from CSDN blog: http://blog.csdn.net/zp752963831/archive/2009/02/18/3906477.aspx)

2. How to unlock the table, unlock the link to terminate the lock, or wait for the linked transaction to be released.

2.1 Activity Monitor

You can see by the wait Type, Blocked by field, that SPID 54 is blocked by SPID 53. You can right-click Details to find detailed SQL statements, or kill the process.

2.2 SQL Server provides several DMV to view locks

Sys.dm_exec_requests

Sys.dm_tran_locks

Sys.dm_os_waiting_tasks

Sys.dm_tran_database_transactions

(1)

SELECT * from sys.dm_tran_locks where resource_type<> ' DATABASE '--and resource_database_id=db_id ()

(2)

SELECT session_id, blocking_session_id,*
From sys.dm_exec_requests
WHERE blocking_session_id > 0

(3)

Code

SQL Server database table locking principle and how to unlock a table