Заблуждения относительно моментальных снимков базы данных и отката транзакций

Автор: Paul Randal, Misconceptions around database snapshots and transaction rollbacks

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

Страница базы данных копируется в моментальный снимок базы данных перед её изменением в исходной базе данных. Хотя механизм обычно называют «копирование при записи» (copy-on-write), технически точнее было бы называть его «копирование до записи» (copy-before-write) (но для многих людей это немного сложнее понять). Как только страница была скопирована в моментальный снимок базы данных, она никогда не удаляется из него и никогда не будет скопирована в него снова, поскольку моментальный снимок уже содержит правильную копию обновлённой страницы на момент времени создания снимка. (Для получения дополнительной информации о моментальных снимках базы данных в целом см. запись в документации Как работают моментальные снимки базы данных).

Транзакция вносит одно или несколько изменений в базу данных, обновляя одну или несколько страниц. Эти страницы будут скопированы в моментальный снимок базы данных, если их ещё там нет, чтобы сохранить их образ до изменения (который существовал на момент создания моментального снимка).

Если транзакция откатывается, откат происходит путём генерации обратных операций, которые транзакция выполнила, и их применения к базе данных (например, вставка будет аннулирована путём генерации и применения удаления; обновление будет аннулировано путём замены обновлённых частей записи значениями до обновления). Я расскажу об этом подробнее в другой записи блога.

Эти операции отката будут выполняться на тех же страницах, на которых происходили первоначальные операции транзакции. Это означает, что никакие другие страницы не будут изменены операциями отката, и, следовательно, никакие дополнительные страницы не будут скопированы в моментальный снимок базы данных из-за отката. Страницы не могут быть удалены из моментального снимка базы данных при откате транзакции, потому что они всё равно изменились в исходной базе данных (хотя общий эффект транзакции+отката не приводит к логическим изменениям данных, заголовки страниц изменятся, получив обновлённый номер последовательности журнала LSN), и поэтому копия в моментальном снимке всё ещё требуется для сохранения представления базы данных на момент времени (на физическом уровне) с момента создания моментального снимка.

Я докажу это вам с помощью простого скрипта, с которым вы также можете поэкспериментировать, чтобы убедиться самим.

USE master;
GO

DROP DATABASE SnapRollbackTest_Snapshot;
GO
DROP DATABASE SnapRollbackTest;
GO

CREATE DATABASE SnapRollbackTest;
GO
USE SnapRollbackTest;
GO

CREATE TABLE MyTable (c1 INT);
CREATE CLUSTERED INDEX MyTable_CL ON MyTable (c1);
GO

SET NOCOUNT ON;
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 100001)
BEGIN
    INSERT INTO MyTable (c1) VALUES (@a);
    SELECT @a = @a + 1;
END;
GO

CREATE DATABASE SnapRollbackTest_Snapshot ON
    (NAME = N'SnapRollbackTest', FILENAME = N'C:\SQLskills\test\SnapRollbackTest.mdfss')
AS SNAPSHOT OF SnapRollbackTest;
GO

-- Начальный размер
SELECT size_on_disk_bytes AS [Initial Size (bytes)] FROM sys.dm_io_virtual_file_stats (DB_ID ('SnapRollbackTest_Snapshot'), 1);
GO

-- Начало транзакции
BEGIN TRAN
GO
UPDATE MyTable SET c1 = 42;
GO
CHECKPOINT; -- чтобы абсолютно всё точно было сброшено на диск
GO

SELECT size_on_disk_bytes AS [After Transaction (bytes)] FROM sys.dm_io_virtual_file_stats (DB_ID ('SnapRollbackTest_Snapshot'), 1);
GO

-- Откат
ROLLBACK TRAN;
GO

SELECT size_on_disk_bytes AS [After Rollback (bytes)] FROM sys.dm_io_virtual_file_stats (DB_ID ('SnapRollbackTest_Snapshot'), 1);
GO
    

Initial Size (bytes)
--------------------
196608

After Transaction (bytes)
-------------------------
1835008

After Rollback (bytes)
----------------------
1835008
    

Вы можете чётко видеть, что размер моментального снимка базы данных НЕ увеличился вообще из-за отката транзакции. Используя приведённый выше скрипт, вы можете попробовать это с кучей, кластерным индексом, различными комбинациями размера строк и количества строк — результат будет тем же — размер моментального снимка базы данных не увеличится из-за отката транзакции. Я попробовал кучу разных комбинаций, и все они дали одинаковый результат.

В глубине души у меня есть смутное ощущение, что существует странный, редкий, патологический случай, когда какая-то странная комбинация операций приводит к разделению страницы при откате, но я не могу его воспроизвести.

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

Надеюсь, это поможет!

P.S. Как отметил один из комментаторов, на начальный размер снимка может повлиять восстановление после сбоя, которое выполняется при создании снимка. Я подробно рассказываю об этом процессе в этой статье, так как это может быть непонятно при запуске CHECKDB.