In sqlserver, how can I lock tables, unlock tables, and view sale tables? I will introduce three different instances to my friends for details.
In sqlserver, how can I lock tables, unlock tables, and view sale tables? I will introduce three different instances to my friends for details.
In sqlserver, how can I lock tables, unlock tables, and view sale tables? I will introduce three different instances to my friends for details.
Lock a table in the database
The Code is as follows: |
|
SELECT * FROM table WITH (HOLDLOCK) |
Note: What is the difference between locking a database table?
The Code is as follows: |
|
SELECT * FROM table WITH (HOLDLOCK) |
Other transactions can read tables, but cannot update or Delete tables.
The 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:
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: name of the table locked
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 (1, 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
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) |