Solutions to SQL Server table deadlock

I wrote a stored procedure the other day and used transactions in the stored procedure. Later I commented out some code for debugging and found that a table was locked, I forgot to comment out the code for creating a transaction. Solution to table lock in this article.

I wrote a stored procedure the other day and used transactions in the stored procedure. Later I commented out some code for debugging and found that a table was locked, I forgot to comment out the code for creating a transaction. Solution to table lock in this article.

In fact, not only will the table be locked as described above, but there are also many scenarios that will cause a deadlock in the Table. Unlocking is actually very simple. The following example is used to explain:

1. First create a test table:
The Code is as follows:
Create table Test
(
Tid int identity (1, 1)
)

2. Execute the following SQL statement to lock the table:
The Code is as follows:
SELECT * FROM Test WITH (TABLOCKX)

3. Use the following statement to check which tables in the current database have deadlocks:
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'

4. The execution result of the preceding statement is as follows:

Spid: the ID of the locked process.
TableName: name of the table in which a deadlock occurs.

5. You only need to use the kill keyword to kill the locked process ID to unlock the table:

KILL 52