如何在 SQL Server 上模拟死锁？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22825147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to simulate DEADLOCK on SQL Server?
提问by user3388473
I am trying to simulate a deadlock on SQL Server.
我正在尝试模拟 SQL Server 上的死锁。
_|worker_id|salary|
1|1 |100 |
2|2 |300 |
Transaction 1 completed in 5 seconds.
事务 1 在 5 秒内完成。
/* TRANSACTION 1*/
Use dbmcw;
DECLARE @sal1 INT, @sal2 int;
BEGIN TRAN;
SELECT @sal1 = salary
FROM dbo.deadlock_demonstration WITH(UPDLOCK)
WHERE worker_id = 1;
WAITFOR DELAY '00:00:05.000';
SELECT @sal2 = salary
FROM dbo.deadlock_demonstration WITH(UPDLOCK)
WHERE worker_id = 2;
COMMIT TRAN;
Transaction 2 finished in 3 seconds.
事务 2 在 3 秒内完成。
/* TRANSACTION 2*/
Use dbmcw;
DECLARE @sal1 INT, @sal2 int;
BEGIN TRAN;
SELECT @sal2 = salary
FROM dbo.deadlock_demonstration WITH(UPDLOCK)
WHERE worker_id = 2;
SELECT @sal1 = salary
FROM dbo.deadlock_demonstration WITH(UPDLOCK)
WHERE worker_id = 1;
COMMIT TRAN;
SQL Server is not giving any error. Deadlock did not occur. What should I change in order to simulate a deadlock?
SQL Server 没有给出任何错误。没有发生死锁。为了模拟死锁，我应该改变什么？
回答by Dave Mason
You can create a deadlock by using the steps shown below. First, create the global temp tables with sample data.
您可以使用下面显示的步骤创建死锁。首先，使用示例数据创建全局临时表。
--Two global temp tables with sample data for demo purposes.
CREATE TABLE ##Employees (
EmpId INT IDENTITY,
EmpName VARCHAR(16),
Phone VARCHAR(16)
)
GO
INSERT INTO ##Employees (EmpName, Phone)
VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')
GO
CREATE TABLE ##Suppliers(
SupplierId INT IDENTITY,
SupplierName VARCHAR(64),
Fax VARCHAR(16)
)
GO
INSERT INTO ##Suppliers (SupplierName, Fax)
VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')
GO
Now open two empty query windows in SSMS. Place the code for session 1 in one query window and the code for session 2 in the other query window. Then execute each of the two sessions step by step, going back and forth between the two query windows as required. Note that each transaction has a lock on a resource that the other transaction is also requesting a lock on.
现在在 SSMS 中打开两个空的查询窗口。将会话 1 的代码放在一个查询窗口中，将会话 2 的代码放在另一个查询窗口中。然后逐步执行这两个会话中的每一个，根据需要在两个查询窗口之间来回切换。请注意，每个事务都锁定了另一个事务也请求锁定的资源。
Session 1 | Session 2
===========================================================
BEGIN TRAN; | BEGIN TRAN;
===========================================================
UPDATE ##Employees
SET EmpName = 'Mary'
WHERE EmpId = 1
===========================================================
| UPDATE ##Suppliers
| SET Fax = N'555-1212'
| WHERE SupplierId = 1
===========================================================
UPDATE ##Suppliers
SET Fax = N'555-1212'
WHERE SupplierId = 1
===========================================================
<blocked> | UPDATE ##Employees
| SET Phone = N'555-9999'
| WHERE EmpId = 1
===========================================================
| <blocked>
===========================================================
A deadlock results; one transaction finishes and the other transaction is aborted and error message 1205 is sent to client.
结果出现死锁；一个事务完成，另一个事务被中止，错误消息 1205 被发送到客户端。
Close the SSMS query windows for "Session 1" and "Session 2" to commit (or rollback) any open transactions. Lastly, cleanup the temp tables:
关闭“会话 1”和“会话 2”的 SSMS 查询窗口以提交（或回滚）任何打开的事务。最后，清理临时表：
DROP TABLE ##Employees
GO
DROP TABLE ##Suppliers
GO
