SQL-Ex blog

Настоящая статья не ставит себе целью просто поговорить о транзакциях. Напротив, цель - пролить свет на некоторые малоизвестные факты о транзакциях в SQL Server, о чем и сообщает название статьи.

Явная транзакция включает следующие три стадии:

• Начало транзакции: Это первая стадия явной транзакции. Транзакция начинается с оператора BEGIN TRANSACTION (или BEGIN TRAN)


• Сохранение транзакции (или установка точки сохранения в пределах транзакции): Это необязательный, хотя и важный этап. Оператор SAVE TRANSACTION используется для установки точки сохранения в пределах транзакции. Без SAVE TRANSACTION не существует способа управлять вложенными транзакциями. Он помогает установить точку сохранения в транзакции, к которой позднее может быть выполнен откат (если потребуется), используя ту же точку сохранения. Для того, чтобы установить точку сохранения с помощью SAVE TRANSACTION, транзакция должна быть активной.


• Завершение транзакции: Транзакция завершается либо с помощью фиксации, либо отката. Фиксация может быть сделана с помощью COMMIT TRANSACTION (или COMMIT TRAN, или просто COMMIT). Но если откат должен быть сделан к точке сохранения, то она должна быть указана наряду с командой ROLLBACK. Например, ROLLBACK TRANSACTION <точка сохранения>.

Давайте рассмотрим явные транзакции более подробно в форме вопросов и ответов.

Что на самом деле делает commit?

Commit уменьшает значение @@TRANCOUNT на 1 всякий раз, когда выполняется. @@TRANCOUNT возвращает число активных транзакций. Если значение @@TRANCOUNT больше 1, то реальное влияние произойдет, когда @@ TRANCOUNT станет 1. Это означает, что активное влияние фиксации будет иметь место для самой верхней транзакции. Активное влияние означает, что изменения станут постоянной частью базы данных, а задействованные ресурсы (включая блокирование) освободятся.

Давайте рассмотрим следующий пример.

BEGIN TRANSACTION ParentTran;

SELECT * FROM sys.dm_tran_active_transactions WHERE name = 'ParentTran';

SELECT @@TRANCOUNT AS [ParentTran_Begin_TranCount];

BEGIN TRANSACTION ChildTran;

SELECT * FROM sys.dm_tran_active_transactions WHERE name = 'ChildTran';

SELECT @@TRANCOUNT AS [ChildTran_Begin_TranCount];

COMMIT TRANSACTION ChildTran;

SELECT @@TRANCOUNT AS [ChildTran_Commit_TranCount];

SELECT * FROM sys.dm_tran_active_transactions WHERE name IN ('ParentTran', 'ChildTran');

COMMIT TRANSACTION ParentTran

SELECT @@TRANCOUNT AS [ParentTran_Commit_TranCount];

SELECT * FROM sys.dm_tran_active_transactions WHERE name IN ('ParentTran', 'ChildTran');

Вывод вышеприведенного кода должен быть похожим на представленный на рисунке ниже.



Если вы посмотрите вывод, то не обнаружите транзакции с именем ‘ChildTran’ в DMV sys.dm_tran_active_transactions. Однако @@TRANCOUNT стал равным 2, когда выполнился оператор ‘BEGIN TRANSACTION ChildTran’. С каждым commit @@TRANCOUNT уменьшается на единицу. Наконец, в последнем commit имеет место активное влияние.

Поскольку мы не выполняли никакой модификации данных в транзакции, то реально не можем увидеть влияния. Но в модифицирующих транзакциях, содержащих INSERT, UPDATE и DELETE, активное влияние будет иметь место на последнем commit. Следует заметить, что число операторов COMMIT TRANSACTION должно быть равным числу операторов BEGIN TRANSACTION.

Может иметь место несколько операторов rollback, если есть несколько транзакций?

Нет. Не может быть нескольких операторов отката. Вы можете иметь несколько операторов BEGIN TRANSACTION, но не может быть нескольких операторов ROLLBACK TRANSACTION. Откат может быть только один. ROLLBACK TRANSACTION откатывает все активные транзакции в рамках родительской транзакции в сессии (SPID).

Рассмотрим следующий пример.

BEGIN TRANSACTION ParentTran;

SELECT * FROM sys.dm_tran_active_transactions WHERE name = 'ParentTran';

SELECT @@TRANCOUNT AS [ParentTran_Begin_TranCount];

BEGIN TRANSACTION ChildTran;

SELECT * FROM sys.dm_tran_active_transactions WHERE name = 'ChildTran';

SELECT @@TRANCOUNT AS [ChildTran_Begin_TranCount];

ROLLBACK TRANSACTION ChildTran;

SELECT @@TRANCOUNT AS [ChildTran_Commit_TranCount];

SELECT * FROM sys.dm_tran_active_transactions WHERE name IN ('ParentTran', 'ChildTran');

ROLLBACK TRANSACTION ParentTran

SELECT @@TRANCOUNT AS [ParentTran_Commit_TranCount];

SELECT * FROM sys.dm_tran_active_transactions WHERE name IN ('ParentTran', 'ChildTran');

Тем не менее, вы можете иметь несколько операторов ROLLBACK TRANSACTION, если использовали оператор SAVE TRANSACTION для установки точки сохранения ROLLBACK TRANSACTION. Вы можете использовать ROLLBACK TRANSACTION с именем точки сохранения для выполнения отката к конкретной точке сохранения.

Вывод вышеприведенного запроса будет выглядеть как на рисунке ниже.



Однако тут также будет получена ошибка, что показано ниже. Эта ошибка возникает на строке 9, где в нашем коде находится “ROLLBACK TRANSACTION ChildTran;”. Ошибка возникает потому, что транзакции с именем ‘ChildTran’ не существует, как это видно в DMV sys.dm_tran_active_transactions.



Давайте рассмотрим еще один пример отката с точкой сохранения.

DECLARE @Table	TABLE

(

	[ID]			INT

	, [Name]		VARCHAR(50)

);

BEGIN TRANSACTION ParentTran;

INSERT INTO @Table (ID, Name) VALUES (1, NULL);

SELECT @@TRANCOUNT AS [ParentTran_Begin_TranCount];

SELECT * FROM @Table;

SAVE TRANSACTION ChildTran;

UPDATE @Table SET NAME = 'ChildTran' WHERE ID = 1;

SELECT @@TRANCOUNT AS [ChildTran_Begin_TranCount];

SELECT * FROM @Table;

ROLLBACK TRANSACTION ChildTran;

SELECT @@TRANCOUNT AS [ChildTran_Commit_TranCount];

SELECT * FROM @Table;

ROLLBACK TRANSACTION ParentTran;

SELECT @@TRANCOUNT AS [ParentTran_Commit_TranCount];

SELECT * FROM @Table;

Вывод показан на рисунке ниже. Если вы заметили, оператор ‘ROLLBACK TRANSACTION ChildTran;’ ничего не откатил. Спрашивается почему?

Это еще один важный факт о транзакции. Табличные переменные и Identity не отменяются при откате.



Попробуем еще один пример. Расширим рассмотренный ранее пример, но вместо табличной переменной будем использовать временную таблицу.

DROP TABLE IF EXISTS #Table;

CREATE TABLE #Table

(

	[ID]			INT

	, [Name]		VARCHAR(50)

);

BEGIN TRANSACTION ParentTran;

INSERT INTO #Table (ID, Name) VALUES (1, NULL);

SELECT @@TRANCOUNT AS [ParentTran_Begin_TranCount];

SELECT * FROM #Table;

SAVE TRANSACTION ChildTran;

UPDATE #Table SET NAME = 'ChildTran' WHERE ID = 1;

SELECT @@TRANCOUNT AS [ChildTran_Begin_TranCount];

SELECT * FROM #Table;

ROLLBACK TRANSACTION ChildTran;

SELECT @@TRANCOUNT AS [ChildTran_Commit_TranCount];

SELECT * FROM #Table;

ROLLBACK TRANSACTION ParentTran;

SELECT @@TRANCOUNT AS [ParentTran_Commit_TranCount];

SELECT * FROM #Table;

На рисунке ниже видно, что откат отработал с точкой сохранения, и отменил её тоже.

Имеет ли значение именование транзакции?

Нет. Это не имеет значения. Если имеются вложенные транзакции, каждой из них дается имя. Только первая транзакция (или родительская транзакция) обнаруживается в DMV sys.dm_tran_active_transactions по указанному имени. Другие транзакции не отображаются.

Однако именование транзакций делает ваш код читабельным.

Мы можем иметь вложенные транзакции?

Вложенные транзакции - это миф. Вы можете иметь одну транзакцию внутри другой, но вы не можете зафиксировать или откатить именно её. Так в чем смысл иметь транзакцию внутри другой транзакции?

Вместо вложенных транзакций вы можете установить точку сохранения внутри активной транзакции, к которой вы сможете сделать откат (если потребуется). Но заметьте, что вы не можете зафиксировать заданную точку сохранения. Фактически commit имеет место, когда @@TRANCOUNT становится 1. Поэтому технически commit имеет место, когда фиксируется самая верхняя транзакция.

Можем мы непосредственно зафиксировать или откатить транзакцию?

Да, можем.

Однако рекомендуется использовать глобальную переменную @@TRANCOUNT и функцию XACT_STATE() , чтобы проверить число активных транзакций, и может ли транзакция быть зафиксированной до её фактической фиксации или отката.

@@TRANCOUNT возвращает число активных транзакций, а функция XACT_STATE() дает нам знать, может ли транзакция быть зафиксирована. Эти функции могут уберечь нас от ошибок, которые могут произойти в незапланированных ситуациях, например, когда нет активных транзакций или транзакции существуют, но не могут быть зафиксированы.