Brief introduction to several common locks of SQL

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.