SQL Server lock table, unlock, and view sale table,

SQL Server lock table, unlock, and view sale table,

Lock a table in the database

Copy codeThe Code is as follows:
SELECT * FROM table WITH (HOLDLOCK)

Note: What is the difference between locking a database table?

Copy codeThe Code is as follows:
SELECT * FROM table WITH (HOLDLOCK)

Other transactions can read tables, but cannot update or Delete tables.

Copy codeThe Code is as follows:
SELECT * FROM table WITH (TABLOCKX)

Other transactions cannot read, update, or delete tables.
Description of the "Lock options" function in the SELECT statement
SQL Server provides a powerful and complete locking mechanism to help achieve database system concurrency and high performance. You can use the default settings of SQL Server or the "Lock option" in the select statement to achieve the expected results. This article introduces the "Lock options" in the SELECT statement and related functions.

View the locked table:

Copy codeThe 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: name of the table locked
[@ More @]

Unlock:

Create a temporary Table

CREATE TABLE #HarveyLock(SPID INT,DBID INT,OBJID INT,INDID INT,TYPE VARCHAR(100),RESOURCE VARCHAR(100),MODE VARCHAR(100),STATUS VARCHAR(100))

Store the Lock information to the Table.
Insert into # HarveyLock EXEC SP_LOCK

3. The LOCK obtained by the condition query in the Table
SELECT * FROM # HarveyLock

4. KILL LOCK
KILL @ SPID
Example

Declare @ spid int Set @ spid = 57 -- lock table process declare @ SQL varchar (1000) set @ SQL = 'Kill '+ cast (@ spid as varchar) exec (@ SQL)