Brief introduction to several common locks of SQL

SELECT * FROM dbo. Aa
SELECT * FROM dbo. B
--1. Exclusive lock
--no changes or additions to the search.
-This is the database itself, in order to appear data confusion, dirty data processing mechanism of their own
--Connection 1
BEGIN Tran

UPDATE dbo. B

Set Name= ' BB '

where id=1

WAITFOR DELAY ' 00:00:30 '--wait 30 seconds

Commit Tran

--Executes the following statement in a second connection

BEGIN Tran

SELECT * FROM B

Commit Tran

--If both statements are executed at the same time, the select query must wait for 30 seconds for the update to complete before executing

--2) shared lock
--holdlock: Can query but cannot be added or deleted
--Executes the following statement in the first connection

BEGIN Tran

SELECT * FROM dbo. B (HOLDLOCK)--holdlock artificial locking

WAITFOR DELAY ' 00:00:30 '--wait 30 seconds

Commit Tran

--Executes the following statement in a second connection

BEGIN Tran

SELECT * FROM dbo. B

UPDATE dbo. B

Set name= ' FF '

where id= ' 1 '

Commit Tran

--If both statements are executed at the same time, the select query in the second connection can execute

--While update must wait 30 seconds for the first transaction to release a shared lock to an exclusive lock before it can execute

--3) Tablockx (exclusive lock)
--tablockx: Other business additions and deletions can not be changed
--Executes the following statement in the first connection

BEGIN Tran

SELECT * FROM dbo. B (Tablockx)--holdlock artificial locking

WAITFOR DELAY ' 00:00:30 '--wait 30 seconds

Commit Tran

--Executes the following statement in a second connection

BEGIN Tran

SELECT * FROM dbo. B

Commit Tran

--If both statements are executed at the same time, the select query in the second connection waits 30 seconds

--3) deadlock

--Executes the following statement in the first connection

BEGIN Tran

UPDATE dbo. A

Set name= ' AA '

where id= ' 1 '

WAITFOR DELAY ' 00:00:30 '

UPDATE dbo. B

Set name= ' AA '

where id= ' 1 '

Commit Tran

--Executes the following statement in a second connection

BEGIN Tran

Update DBO.B

Set name= ' AA '

where id= ' 1 '

WAITFOR DELAY ' 00:00:10 '

UPDATE dbo. A

Set name= ' AA '

where id= ' 1 '

Commit Tran

--Simultaneous execution, the system detects the deadlock and aborts the process

--View Deadlocks
Select
request_session_id spid,
object_name (resource_associated_entity_id) tableName
From
Sys.dm_tran_locks
where
Resource_type= ' OBJECT '

--Kill the deadlock process
Kill spid

Brief introduction to several common locks of SQL