SQL Server lock table, unlock, view pin table

There are a few friends to suggest a combination of examples to illustrate, the previous article has said several types of locks, you can use the system dynamic view sys.dm_tran_locks see, the important fields are as follows:

Resource_type the type of resource being locked (Database, FILE, Object,page,key,extent,rid,application,metadata,hobt,appocation_unit)

Type of Request_mode lock (shared lock, update lock, exclusive lock, schema lock, etc.)

Resource_description Resource Description

Request_session_idrequest Session ID

One: The following is a brief description of the sample database in AdventureWorks2008,

To filter out the shared locks of the general database, as an example must see the lock, so use with (HOLDLOCK) to keep the lock.

1. Shared locks (S) share lock

Use AdventureWorks2008 BEGIN TRAN select * to Sales.SalesOrderHeader with (HOLDLOCK) where salesorderid= ' 43662 ' select re Source_type, Request_mode, resource_description,request_session_id, Db_name (resource_database_id) as Resource_ Database from sys.dm_tran_locks WHERE resource_type <> ' database '--rollback Tran

Before a transaction is rolled back, view the type of lock:

Other sessions on table are read-only, cannot be updated, and a new session test is opened:

SELECT * from Sales.SalesOrderHeader where salesorderid= ' 43662 ' Go update sales.salesorderheader set orderdate=getdate () where salesorderid= ' 43662 '

The select works correctly, and the UPDATE statement is always waiting, waiting for the session above to release the lock.

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

Use AdventureWorks2008 BEGIN TRAN select * to Sales.SalesOrderHeader with (UPDLOCK) where salesorderid= ' 43662 ' select res Ource_type, Request_mode, Resource_description,request_session_id,db_name (resource_database_id) as Resource_ Database from sys.dm_tran_locks WHERE resource_type <> ' database ' ROLLBACK Tran

To view information about a lock:

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

Use AdventureWorks2008 BEGIN TRAN update 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_locks WHERE resource_type <> ' database ' ROLLBACK Tran

To view locks:

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

The database engine uses intent locks to protect shared (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 before a lower-level lock and therefore are notified of the intention to place the lock at a lower level.

There are two purposes of intent locks:

Prevents other transactions from modifying higher-level resources in a way that makes lower-level locks invalid.

Increase the efficiency of the database engine in detecting lock conflicts at a higher granularity level.

5. Schema Locks (Sch): Schema lock

Schema stability Lock (SCH-S): Maintains schema stability and does not block access to data when generating execution plans.

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

Use AdventureWorks2008 BEGIN TRAN CREATE TABLE MyTable (ID INT, NAME VARCHAR (), COUNTRY VARCHAR ()) SELECT Resource_type , Request_mode, resource_description from sys.dm_tran_locks WHERE resource_type <> ' DATABASE ' ORDER by Request_mode ROLLBACK TRAN

6. Bulk Update Locks (BU)

The database engine used bulk update (BU) locks when data was bulk copied into a table, and specified a TABLOCK hint or set the table lock on bulk load table option with sp_tableoption. Bulk update locks (BU locks) allow 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 serializable transaction isolation levels, key-range locks implicitly protect the range of rows that are contained in the recordset for the recordset read by Transact-SQL statements. Key-range locks prevent Phantom reads. By protecting the range of keys between rows, it also prevents phantom insertions or deletions of the recordset accessed by the transaction.

Two: Deadlock and deadlock cancellation

1. Deadlock

Using or managing a database inevitably involves deadlocks. Once a deadlock occurs, data is waiting to be freed from each other's resources, blocking access to the data and causing DB to hang up. When a resource is locked and cannot be accessed, the session that accesses 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 attempting to lock, the task is permanently blocked and a deadlock occurs. For example:

Transaction A gets the shared lock for row 1.

Transaction B gets the shared lock for row 2.

Transaction A now requests an exclusive lock of line 2, but is blocked before transaction B completes and releases its shared lock on row 2.

Transaction B now requests an exclusive lock on line 1, but is blocked before transaction A completes and releases its shared lock on row 1.

Transaction A cannot be completed after transaction B completes, but transaction B is blocked by transaction a. This condition is also referred to as a circular dependency: Transaction a relies on transaction B, and transaction B closes the loop by dependency on transaction A.

Unless an external process disconnects a deadlock, the two transactions in the deadlock will wait indefinitely. The Microsoft SQL Server Database Engine deadlock monitor periodically checks for tasks that fall into deadlock. If the monitor detects a circular dependency, it selects one of the tasks as a victim and then terminates its transaction and prompts for an error. In this way, other tasks can complete their transactions. It can also retry a transaction for an error-terminated application, but typically waits until another transaction with which it falls into deadlock is completed.

2. Deadlock Detection

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

2.2 View DMV:sys.dm_tran_locks

2.3 SQL Server Profiler can visually display graphical events for deadlocks.

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 an existing lock, the transaction requesting the new lock waits for the release of the existing lock or waits for the lock timeout interval to expire. For example, there is no lock mode compatible with exclusive locks. If an exclusive (x) lock is available, no other transaction can obtain a lock of any type (shared, updated, or exclusive) of that resource until an exclusive (x) lock is released. Alternatively, if a shared lock (S lock) has been applied to a resource, the other transaction can get a shared or updated lock (U lock) for that item even if the first transaction has not been completed. However, other transactions cannot acquire an exclusive lock until the shared lock is released.

Click to view larger image

Four: summary

The principle of the lock is more abstract, to the user is transparent, not too much attention. Applications generally do not request locks directly. The lock is managed internally by a part of the database engine, called the lock manager. When the 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 appropriate lock from the lock manager. If a lock held with another transaction does not collide, the lock manager will grant the lock

SQL Server provides a powerful and complete lock mechanism to help achieve concurrency and high performance in database systems. Users can use the default settings of SQL Server or use the lock option in the SELECT statement to achieve the desired effect. This article describes the "lock options" and the corresponding functional descriptions in the SELECT statement

To view the locked table:

The code is as follows

Select request_session_id spid,object_name (resource_associated_entity_id) tablename

From sys.dm_tran_locks where resource_type= ' OBJECT '

SPID Lock Table Process

TableName Locked Table name

[@more @]

Unlock:

Create a temporary table

The code is as follows

CREATE TABLE #HarveyLock

(

SPID INT,

DBID INT,

ObjID INT,

Indid INT,

TYPE VARCHAR (100),

RESOURCE VARCHAR (100),

MODE VARCHAR (100),

STATUS VARCHAR (100)

)

Save lock information to this table

INSERT into #HarveyLock EXEC sp_lock

3. Lock for query under table condition

SELECT * from #HarveyLock

4.KILL related lock

KILL @SPID

Cases

The code is as follows

DECLARE @spid int

Set @spid = 57--Lock table process

DECLARE @sql varchar (1000)

Set @sql = ' Kill ' +cast (@spid as varchar)

EXEC (@sql)