Транзакция SQL-сервера

В этом руководстве вы узнаете о транзакциях SQL Server и о том, как использовать T-SQL для выполнения транзакций.

Введение в транзакцию SQL Server

Транзакция — это отдельная единица работы, которая обычно содержит несколько операторов T-SQL.

Если транзакция прошла успешно, изменения фиксируются в базе данных. Однако если транзакция имеет ошибку, изменения необходимо откатить.

При выполнении одного оператора, такого как INSERT, UPDATE и DELETE, SQL Server использует транзакцию autocommit. В этом случае каждый оператор является транзакцией.

Чтобы явно начать транзакцию, сначала используйте оператор BEGIN TRANSACTION или BEGIN TRAN:

BEGIN TRANSACTION;

Затем выполните один или несколько операторов, включая INSERT, UPDATE и DELETE.

Наконец, зафиксируйте транзакцию с помощью оператора COMMIT:

COMMIT;

Или откатите транзакцию с помощью оператора ROLLBACK:

ROLLBACK;

Вот последовательность операторов для явного начала транзакции и ее подтверждения:

-- start a transaction
BEGIN TRANSACTION;
-- other statements
-- commit the transaction
COMMIT;

Пример транзакции 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
);

Таблица invoices хранит заголовок счета, а таблица invoice_items хранит позиции. Поле total в таблице invoices рассчитывается из позиций.

В следующем примере для создания транзакции используются операторы BEGIN TRANSACTION и COMMIT:

BEGIN TRANSACTION;
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);
COMMIT;

В этом примере:

Сначала явно запустите транзакцию с помощью оператора BEGIN TRANSACTION:

BEGIN TRANSACTION;

Далее вставьте строку в таблицу счетов-фактур и верните идентификатор счета-фактуры:

DECLARE @invoice TABLE(
  id int
);
DECLARE @invoice_id int;
INSERT INTO invoices(customer_id, total)
OUTPUT INSERTED.id INTO @invoice
VALUES(100, 0);
SELECT
  @invoice_id = id
FROM @invoice;

Затем вставьте две строки в таблицу invoice_items:

INSERT INTO invoice_items(id, invoice_id, item_name, amount, tax)
VALUES(10, @invoice_id, 'Keyboard', 70, 0.08),
      (20, @invoice_id, 'Mouse', 50, 0.08);

После этого рассчитайте общую сумму, используя таблицу invoice_items, и обновите ее в таблице invoices:

UPDATE invoices
SET total =(
    SELECT SUM(amount *(1 + tax))
    FROM invoice_items
    WHERE invoice_id = @invoice_id
);

Наконец, зафиксируйте транзакцию с помощью оператора COMMIT:

COMMIT;

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

• Используйте оператор BEGIN TRANSACTION для явного начала транзакции.
• Используйте оператор COMMIT для подтверждения транзакции и оператор ROLLBACK для отката транзакции.