Миф про то, что вложенные транзакции реальны

Автор: Paul Randal, A SQL Server DBA myth a day: (26/30) nested transactions are real

Вложенные транзакции — это злое изобретение, предназначенное для того, чтобы позволить разработчикам сделать жизнь администраторов баз данных невыносимой. В SQL Server они ещё более злы…

Миф №26: вложенные транзакции в SQL Server реальны.

ЛОЖЬ!!!

Вложенные транзакции на самом деле ведут себя не так, как можно было бы предположить, глядя на синтаксис. Я понятия не имею, почему они были реализованы именно так в SQL Server — всё, что приходит мне в голову, это что кто-то из далёкого прошлого постоянно показывает нос сообществу SQL Server и говорит: «ха — обманул вас!!».

Позвольте мне объяснить. SQL Server позволяет запускать транзакции внутри других транзакций — они называются вложенными транзакциями. Он позволяет их фиксировать (commit) и откатывать (roll back).

Фиксация вложенной транзакции не имеет абсолютно никакого эффекта — поскольку единственная транзакция, которая действительно существует с точки зрения SQL Server, — это внешняя. Можете сказать «неконтролируемый рост журнала транзакций»? Вложенные транзакции — частая причина проблем с ростом журнала транзакций, потому что разработчик думает, что вся работа выполняется во внутренних транзакциях, поэтому проблемы нет.

Откат вложенной транзакции откатывает весь набор транзакций — поскольку такой вещи, как вложенная транзакция, не существует.

Ваши разработчики не должны использовать вложенные транзакции. Они злы.

Если вы мне не верите, вот код, который покажет вам, что я имею в виду. Во-первых, создайте базу данных с таблицей, где каждая вставка будет занимать 8 КБ в журнале.

CREATE DATABASE [NestedXactsAreNotReal];
GO
USE [NestedXactsAreNotReal];
GO
ALTER DATABASE [NestedXactsAreNotReal] SET RECOVERY SIMPLE;
GO
CREATE TABLE [t1] ([c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'a');
CREATE CLUSTERED INDEX [t1c1] ON [t1] ([c1]);
GO
SET NOCOUNT ON;
GO

Тест №1: Откатывает ли откат вложенной транзакции только эту вложенную транзакцию?

BEGIN TRAN OuterTran;
GO
 
INSERT INTO [t1] DEFAULT VALUES;
GO 1000
 
BEGIN TRAN InnerTran;
GO
 
INSERT INTO [t1] DEFAULT Values;
GO 1000
 
SELECT @@TRANCOUNT, COUNT (*) FROM [t1];
GO

Я получаю результаты 2 и 2000. Теперь я откачу вложенную транзакцию, и она должна откатить только 1000 строк, вставленных внутренней транзакцией…

ROLLBACK TRAN InnerTran;
GO

Msg 6401, Level 16, State 1, Line 1
Cannot roll back InnerTran. No transaction or savepoint of that name was found.

Хм… из Books Online следует, что я могу использовать только имя внешней транзакции или не использовать имя вообще. Попробую без имени:

ROLLBACK TRAN;
GO
 
SELECT @@TRANCOUNT, COUNT (*) FROM [t1];
GO

И я получаю результаты 0 и 0. Как объясняется в Books Online, ROLLBACK TRAN откатывает изменения к началу внешней транзакции и устанавливает @@TRANCOUNT в 0. Все изменения откатываются. Единственный способ сделать то, что я хочу, — это использовать SAVE TRAN и ROLLBACK TRAN с указанием имени точки сохранения.

Тест №2: Действительно ли фиксация вложенной транзакции фиксирует сделанные изменения?

BEGIN TRAN OuterTran;
GO
 
BEGIN TRAN InnerTran;
GO
 
INSERT INTO [t1] DEFAULT Values;
GO 1000
 
COMMIT TRAN InnerTran;
GO
 
SELECT COUNT (*) FROM [t1];
GO

Я получаю результат 1000, как и ожидалось. Теперь я откачу внешнюю транзакцию, и вся работа, выполненная внутренней транзакцией, должна сохраниться…

ROLLBACK TRAN OuterTran;
GO
 
SELECT COUNT (*) FROM [t1];
GO

И я получаю обратно результат 0. Ой — фиксация вложенной транзакции не сделала её изменения долговечными.

Тест №3: Позволяет ли фиксация вложенной транзакции хотя бы очистить журнал?

Я воссоздал базу данных перед запуском этого теста, чтобы журнал изначально был минимального размера, а вывод DBCC SQLPERF ниже был отредактирован, чтобы включить только базу данных NestedXactsAreNotReal.

BEGIN TRAN OuterTran;
GO
 
BEGIN TRAN InnerTran;
GO
 
INSERT INTO [t1] DEFAULT Values;
GO 1000
 
DBCC SQLPERF ('LOGSPACE');
GO

Database Name         Log Size (MB) Log Space Used (%) Status
--------------------- ------------- ------------------ -----------
NestedXactsAreNotReal 12.05469      95.81983           0

Теперь я зафиксирую вложенную транзакцию, запущу контрольную точку (которая очистит весь возможный журнал транзакций в модели восстановления SIMPLE) и снова проверю пространство журнала:

COMMIT TRAN InnerTran;
GO
 
CHECKPOINT;
GO
 
DBCC SQLPERF ('LOGSPACE');
GO

Database Name         Log Size (MB) Log Space Used (%) Status
--------------------- ------------- ------------------ -----------
NestedXactsAreNotReal 12.05469      96.25324           0

Хм — никаких изменений — на самом деле Log Space Used (%) немного увеличился из-за записи в журнале контрольной точки (см. Незавершённые контрольные точки и восстановление). Фиксация вложенной транзакции не позволила очистить журнал. И, конечно же, нет, потому что откат может быть выполнен в любой момент, и он откатит все изменения до начала внешней транзакции — поэтому все записи журнала требуются до тех пор, пока внешняя транзакция не будет зафиксирована или откачена.

И чтобы доказать это, я зафиксирую внешнюю транзакцию и запущу контрольную точку:

COMMIT TRAN OuterTran;
GO
 
CHECKPOINT;
GO
 
DBCC SQLPERF ('LOGSPACE');
GO

Database Name         Log Size (MB) Log Space Used (%) Status
--------------------- ------------- ------------------ -----------
NestedXactsAreNotReal 12.05469      26.4339            0

И он резко падает.

Вложенным транзакциям — скажите «нет»! (социальная реклама от хороших ребят из SQLskills.com :-)