如何在 SQL Server 上模拟 DEADLOCK?
我正在尝试模拟 SQL Server 上的死锁.
I am trying to simulate a deadlock on SQL Server.
_|worker_id|salary|
1|1 |100 |
2|2 |300 |
交易 1 在 5 秒内完成.
Transaction 1 completed in 5 seconds.
/* 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;
交易 2 在 3 秒内完成.
Transaction 2 finished in 3 seconds.
/* 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 没有给出任何错误.没有发生死锁.为了模拟死锁，我应该改变什么?
SQL Server is not giving any error. Deadlock did not occur. What should I change in order to simulate a deadlock?
推荐答案
您可以使用下面显示的步骤创建死锁.首先，使用示例数据创建全局临时表.
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
现在在 SSMS 中打开两个空的查询窗口.将会话 1 的代码放在一个查询窗口中，将会话 2 的代码放在另一个查询窗口中.然后逐步执行这两个会话中的每一个，根据需要在两个查询窗口之间来回切换.请注意，每个事务都锁定了另一个事务也请求锁定的资源.
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.
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>
===========================================================
导致死锁；一个事务完成，另一个事务中止，并向客户端发送错误消息 1205.
A deadlock results; one transaction finishes and the other transaction is aborted and error message 1205 is sent to client.
关闭会话 1"和会话 2"的 SSMS 查询窗口以提交(或回滚)任何打开的事务.最后，清理临时表:
Close the SSMS query windows for "Session 1" and "Session 2" to commit (or rollback) any open transactions. Lastly, cleanup the temp tables:
DROP TABLE ##Employees
GO
DROP TABLE ##Suppliers
GO
相关文章