问题描述
我试图模拟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*/
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*/
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
这篇关于如何在SQL Server上模拟DEADLOCK？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！