Referenced from: http://jengting.blogspot.tw/2012/06/sql.html
Based on MSDN to minimize the number of deaths in the graphics simulation die ~ ~
Open two T-SQL inquiries (two executive) in SSMS and use the following language to execute the T-SQL Enquiry 1, and then execute T-SQL Enquiry 2.
The steps are simple and easy to say:
- Step 1: Create a T-SQL enquiry 1, open a transaction, and update the Supplier information.
- Step 2: Create another T-SQL Enquiry 2, open a transaction and update part
- Step 3: Back to T-SQL Enquiry 1, update part
- Step 4: Return to T-SQL Enquiry 2, update Supplier
To produce a dying knot.
- Building Test Information
12345678910111213141516171819202122 |
IF OBJECT_ID(
‘Supplier‘
)
IS NOT NULL
DROP TABLE Supplier
IF OBJECT_ID(
‘Part‘
)
IS NOT NULL
DROP TABLE Part
CREATE TABLE Supplier
(
SPLNO
char
(5),
SPLName
char
(10),
CONSTRAINT [PK_Supplier]
PRIMARY KEY CLUSTERED ([SPLNO]
ASC
)
) CREATE TABLE Part
(
SPLNO
char
(5),
Product
char
(50),
CONSTRAINT [PK_Part]
PRIMARY KEY CLUSTERED ([SPLNO]
ASC
,[Product]
ASC
)
)
INSERT INTO Supplier
VALUES
(
‘00000‘
,
‘Microsoft‘
)
INSERT INTO Part
VALUES
(
‘00000‘
,
‘SQL Server 2012‘
)
|
12345 |
BEGIN TRANSACTION
UPDATE Supplier
SET SPLName =
‘Microsoft‘ WHERE SPLNO =
‘00000‘ -- Step 1
WAITFOR DELAY
‘00:00:05‘
UPDATE Part
SET Product =
‘SQL Server 2012‘ WHERE Product =
‘SQL Server 2012‘ -- Step 3
COMMIT TRANSACTION
|
12345 |
BEGIN TRANSACTION
UPDATE Part
SET Product =
‘SQL Server 2012‘ WHERE Product =
‘SQL Server 2012‘ -- Step 2
WAITFOR DELAY
‘00:00:05‘
UPDATE Supplier
SET SPLName =
‘Microsoft‘ WHERE SPLNO =
‘00000‘ -- Step 4
COMMIT TRANSACTION
|
Message 1205, Level 13, State 45, Line 1 trading (Process Awareness 52) is locked in another order on the lock resource and has been chosen as the sacrifices of the dead end. Please re-execute the transaction.
Reference
Information:
- MSDN minimizes the number of dead ends
- How to troubleshoot Deadlock in SQL Server 2005
- Example of Deadlock | SQL Server Deadlock Script
- How can I simulate a deadlock for testing purposes?
SQL Simulation dead End product