SQL Server lock table, unlock, view pin table method _mssql

Lock a table in a database

Copy Code code as follows:

SELECT * from table with (HOLDLOCK)

Note: The difference between a table that locks a database

Copy Code code as follows:

SELECT * from table with (HOLDLOCK)

Other transactions can read the table, but cannot update the deletion

Copy Code code as follows:

SELECT * from table with (TABLOCKX)

Other transactions cannot read tables, update and delete
Feature description of the lock options in the SELECT statement
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:

Copy Code code 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

CREATE TABLE #HarveyLock
(
SPID int,
DBID int,
objid int,
indid int,
TYPE VARCHAR (),
RESOURCE VARCHAR (MB),
MODE VARCHAR (m),
STATUS VARCHAR (MB)

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

 declare @spid int Set @spid = 57--Lock table process declare @sql varchar (1000) Set @sql = ' Kill ' +cast (@spid as varchar) EXEC (@sql)