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

1. Database Table Locking Principle

1.1 currently, the C/S and B/S structures all involve multiple users accessing the database. Each time point there are thousands of users accessing the database, and the same data is also accessed, this may cause data inconsistency or dirty Data Reading.

 

1.2 acid principles of transactions

 

1.3 locks are an important part of relational databases. The database must have a locking mechanism to ensure data integrity and consistency.

1.3.1 resources that can be locked in SQL Server:

 

1.3.2 lock granularity:

 

1.3.3 lock upgrade:

The lock upgrade threshold and lock upgrade are automatically determined by the system and do not need to be set.

1.3.4 lock type:

(1) shared lock:

The shared lock is used for all read-only data operations.

(2) modify the lock:

The modification lock is used to lock resources that may be modified in the initialization phase of the modification operation, so as to avoid deadlock caused by the use of shared locks.

(3) exclusive lock:

An exclusive lock is retained to modify data. The resources it locks. Other transactions cannot be read or modified. The exclusive lock cannot be compatible with other locks.

(4) architecture lock

Structural locks are divided into schema modification locks (Sch-m) and schema stability locks (Sch-S ). When the table definition language operation is executed, SQL server uses the sch-M lock. When compiling and querying, SQL server uses the sch-s lock.

(5) Intention lock

Intention lock indicates that SQL Server has the intention to obtain a shared lock or an exclusive lock at the lower layer of the resource.

(6) Modify locks in batches

Use batch modification locks when copying data in batches

1.3.4 SQL Server lock type

(1) holdlock: Keep the shared lock on the table until the entire transaction ends, instead of releasing the added lock immediately after the statement is executed.

(2) nolock: no shared or exclusive locks are added. When this option takes effect, it may read uncommitted data or "dirty data". This option is only applicable to select statements.

(3) paglock: Specify to add a page lock (otherwise, a table lock may be added ).

(4) readcommitted performs scanning with the same lock semantics as transactions running at the committed read isolation level. By default, SQL Server 2000 operates at this isolation level.

(5) readpast: Skip the data rows that have been locked. This option will allow the transaction to skip the data rows that have been locked by other transactions when reading data, rather than blocking until other transactions release the lock,

Readpast is only applicable to select statement operations in transaction operations at the Read committed isolation level.

(6) readuncommitted: equivalent to nolock.

(7) repeatableread: sets the transaction to a read-only isolation level.

(8) rowlock: Use row-level locks instead of page-level locks and table-level locks with a higher granularity.

(9) serializable: scan with the same lock semantics as transactions running at the serializable read isolation level. Equivalent to holdlock.

(10) tablock: Table-level locks are used instead of Row-level or page-level locks. After the statement is executed, SQL Server releases the lock. If both holdlock is specified, the lock remains until the transaction ends. (11) tablockx: Specifies the use of the exclusive lock on the table. This lock can prevent other transactions from reading or updating data in the table until the statement or the entire transaction ends.

(12) updlock: Specify to set the update lock when reading data in the table instead of the shared lock. The lock remains until this statement or the entire transaction ends, updlock is used to allow users to read data first (and does not block other users from reading data), and ensure that when the data is updated later, during this period, the data was not modified by other users.

(This section from csdn blog: http://blog.csdn.net/zp752963831/archive/2009/02/18/3906477.aspx)

 

2. to unlock a table, unlock the link to terminate the lock or wait for the link to be released.

2.1 activity monitor

 

You can see through the wait type and blocked by columns that spid 54 is blocked by spid 53. You can right-click details to find the detailed SQL statement 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 * FromSYS. dm_tran_locksWhereResource_type<>'Database' --And resource_database_id = db_id ()
 

 

(2)

 
  SelectSession_id, blocking_session_id,*
FromSYS. dm_exec_requests
WhereBlocking_session_id> 0
 

 

 

 

(3)

Code

  Select  
Request_session_id  As  Spid,
  Coalesce  (S. Name  +     '  .  '     +  O. Name  +    Isnull  (  '  .  '     +  I. Name,  ''  ),
S2.name  +     '  .  '     + O2.name,
DB. Name)  As  Object,
L. resource_type  As  Type,
Request_mode  As  Mode,
Request_status  As  Status
  From  SYS. dm_tran_locks L
  Left     Join  SYS. partitions P
  On L. resource_associated_entity_id  =  P. hobt_id
  Left     Join  SYS. Indexes I
  On  P.  Object_id     =  I.  Object_id  
  And  P. index_id =  I. index_id
  Left     Join  SYS. Objects o
  On  P.  Object_id     =  O.  Object_id  
  Left     Join SYS. schemas s
  On  O. schema_id  =  S. schema_id
  Left     Join  SYS. Objects O2
  On  L. resource_associated_entity_id  =  O2.  Object_id  
  Left    Join  SYS. schemas S2
  On  O2.schema _ id  =  S2.schema _ id
  Left     Join  SYS. Databases DB
  On  L. resource_database_id  =  DB. database_id
  Where  Resource_database_id =     Db_id  ()
  Order     By  Spid, object,  Case  L. resource_type
  When     '  Database  '     Then    1  
  When     '  Object  '     Then     2  
  When     '  Page  '    Then     3  
  When     '  Key  '     Then     4  
  Else     5    End  
 

Address: http://www.cnblogs.com/changbluesky/archive/2010/06/10/1753021.html