SQL Server lock table, unlock, view pin table

Lock a table in a database

The code is as follows	Copy Code
SELECT * from table with (HOLDLOCK)

Note: The difference between a table that locks a database

The code is as follows	Copy Code
SELECT * from table with (HOLDLOCK)

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

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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

Unlock:

Create a temporary table

The code is as follows	Copy Code
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

code is as follows	copy code
DECLARE @ spid  int Set @spid   = 57-Lock table process Declare @sql varchar (1000) Set @sql = ' Kill ' +cast (@spid   as VA Rchar) Exec (@sql)