SQL Server死锁
目录简介
死锁是一个并发问题，其中两个会话会阻止彼此的进程。第一个会话锁定了另一个会话想要访问的资源，反之亦然。
下图说明了SQL Server中的死锁：
此图中，invoices和invoices_items是两张表。
- 首先，会话一访问
invoices表并将其锁定。 - 第二，会话二锁定
invoice_items表并将其锁定。 - 第三，会话一还想要访问
invoice_items表，但需要等待会话二完成。同时，会话二想要访问invoices表，但需要等待会话二完成。
这样，两个会话都在等待对方完成，从而导致死锁。
因此，两个会话将相互等待，直到SQL Server主动终止其中一个会话。SQL Server终止的会话称为死锁牺牲者。
示例
看一个创建死锁的示例。在本例中，我们将首先创建invoices(发票)和invoice_items(发票项)表：
CREATE TABLE invoices (
id int IDENTITY PRIMARY KEY,
customer_id int NOT NULL,
total decimal(10, 2) NOT NULL DEFAULT 0 CHECK (total >= 0)
);
CREATE TABLE invoice_items (
id int,
invoice_id int NOT NULL,
item_name varchar(100) NOT NULL,
amount decimal(10, 2) NOT NULL CHECK (amount >= 0),
tax decimal(4, 2) NOT NULL CHECK (tax >= 0),
PRIMARY KEY (id, invoice_id),
FOREIGN KEY (invoice_id) REFERENCES invoices (id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
INSERT INTO invoices (customer_id, total)
VALUES (100, 0);
INSERT INTO invoice_items (id, invoice_id, item_name, amount, tax)
VALUES (10, 1, 'Keyboard', 70, 0.08),
(20, 1, 'Mouse', 50, 0.08);
UPDATE invoices
SET total = (SELECT
SUM(amount * (1 + tax))
FROM invoice_items
WHERE invoice_id = 1
);
然后，我们将创建两个会话来连接到数据库。下面是需要从每个会话执行的语句序列。
| 会话1 | 会话2 |
|---|---|
| BEGIN TRAN; | |
| BEGIN TRAN; | |
| UPDATE invoices SET customer_id = 100 WHERE id = 1; | |
| UPDATE invoice_items SET amount = 100 WHERE id = 10; | |
| UPDATE invoice_items SET item_name = ‘Cool Keyboard’ WHERE id = 10; | |
| Blocked | UPDATE invoices SET total = (SELECT SUM(amount * (1 + tax)) FROM invoice_items WHERE invoice_id = 1) WHERE id = 1; |
| Blocked |
一旦发生死锁，SQL Server将杀死死锁牺牲者。在我们的例子中，死锁牺牲者是进程ID 65。
Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.