Блокировка SQL-сервера

В этом руководстве вы узнаете о взаимоблокировке SQL Server и о том, как ее имитировать.

Содержание

Введение в взаимоблокировку SQL Server
Пример взаимоблокировки SQL Server
Краткое содержание

Введение в взаимоблокировку SQL Server

Взаимная блокировка — это проблема параллелизма, при которой два сеанса блокируют ход выполнения друг друга. Первый сеанс блокирует ресурс, к которому другой сеанс хочет получить доступ, и наоборот.

На следующем рисунке показана взаимоблокировка в SQL Server:

На этом рисунке инвойсы и инвойсы_элементы представляют собой таблицы.

Сначала сеанс 1 обращается к таблице счетов-фактур и блокирует ее.
Во-вторых, сеанс два блокирует таблицу invoice_items и блокирует ее.
В-третьих, сеанс один хочет получить доступ к таблице invoice_items, но должен дождаться завершения сеанса два. В то же время сеанс два хочет получить доступ к таблице invoices, но должен дождаться завершения сеанса два.

В результате два сеанса ждут друг друга, пока SQL Server проактивно не завершит один из них. Сеанс, который завершается 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
НАЧАТЬ ПЕРЕХОД;
	НАЧАТЬ ПЕРЕХОД;
ОБНОВЛЕНИЕ счетов-фактур УСТАНОВИТЬ идентификатор_клиента = 100 ГДЕ идентификатор = 1;
	ОБНОВЛЕНИЕ invoice_items УСТАНОВЛЕННАЯ сумма = 100 ГДЕ идентификатор = 10;
ОБНОВЛЕНИЕ invoice_items SET item_name = 'Крутая клавиатура' ГДЕ идентификатор = 10;
Заблокировано	ОБНОВЛЕНИЕ счетов-фактур УСТАНОВИТЬ общую сумму =(ВЫБРАТЬ СУММА(сумма *(1 + налог)) ИЗ счет-фактуры_элементов ГДЕ invoice_id = 1) ГДЕ идентификатор = 1;
	Заблокировано

Как только происходит взаимоблокировка, SQL Server убивает жертву взаимоблокировки. В нашем случае жертвой взаимоблокировки является процесс с идентификатором 65.

Transaction(Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Краткое содержание

Взаимная блокировка SQL Server — это проблема, при которой два сеанса блокируют ресурс, к которому другой сеанс хочет получить доступ, и наоборот.