18.2 SQL Server死锁

SQL Server死锁

目录

• 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.