Это миф невероятно живуч, так что самое время развеять его — и заодно показать наглядный сценарий, подтверждающий вывод.
Миф №19: операция TRUNCATE TABLE не журналируется.
ЛОЖЬ
В пользовательской базе данных не существует «нежурналируемых» операций. Единственные операции, которые SQL Server выполняет без записи пользовательских изменений в журнал, относятся к хранилищу версий в tempdb.
Операция TRUNCATE TABLE удаляет все данные в таблице «оптом». Отдельные записи не удаляются по одной; вместо этого просто снимается распределение (deallocate) со страниц данных, входящих в состав таблицы. Связи распределений «отвязываются» от таблицы и ставятся в очередь на освобождение фоновому заданию под названием «задача отложенного удаления» (deferred-drop task). Это задание выполняет освобождение страниц вместо того, чтобы делать это в рамках обычной транзакции, — так при освобождении целых экстентов не требуется удерживать блокировки. До выхода SQL Server 2000 SP3 (когда этот механизм был внедрён), при выполнении TRUNCATE TABLE можно было исчерпать память из‑за большого числа блокировок.
Вот примерный скрипт:
CREATE DATABASE TruncateTest;
GO
USE TruncateTest;
GO
ALTER DATABASE TruncateTest 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
INSERT INTO t1 DEFAULT VALUES;
GO 1280
CHECKPOINT;
GO
База данных переведена в режим простого восстановления (SIMPLE), поэтому журнал очищается на каждом CHECKPOINT (для простоты — ха‑ха :-)
Подождите минуту (в это время может пройти очистка «фантомных» записей) и проверьте, сколько строк в журнале:
SELECT COUNT (*) FROM fn_dblog (NULL, NULL);
GO
Если результат не равен 2, выполните ещё один CHECKPOINT и снова посмотрите число строк в журнале — пока не получите 2. Теперь база данных полностью «спокойна», и любые новые записи в журнале будут связаны только с нашими действиями. Выполним TRUNCATE:
TRUNCATE TABLE t1;
GO
SELECT COUNT (*) FROM fn_dblog (NULL, NULL);
GO
У меня получается 541 запись в журнале — очевидно, операция вовсе не «нежурналируемая», но так же очевидно, что и не удаляет каждую запись по одной (я вставил 1280 записей). Если заглянуть в журнал, увидим следующее:
SELECT
[Current LSN], [Operation], [Context],
[Transaction ID], [AllocUnitName], [Transaction Name]
FROM fn_dblog (NULL, NULL);
GO
Current LSN Operation Context Transaction ID AllocUnitName Transaction Name
————————— —————— —————— —————— —————————— ——————
00000081:000001a6:0016 LOP_BEGIN_CKPT LCX_NULL 0000:00000000 NULL NULL
00000081:000001a9:0001 LOP_END_CKPT LCX_NULL 0000:00000000 NULL NULL
00000081:000001aa:0001 **LOP_BEGIN_XACT** LCX_NULL 0000:00001072 NULL TRUNCATE TABLE
00000081:000001aa:0002 LOP_LOCK_XACT LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:0003 LOP_LOCK_XACT LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:0004 LOP_LOCK_XACT LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:0005 LOP_COUNT_DELTA LCX_CLUSTERED 0000:00000000 sys.sysallocunits.clust NULL
00000081:000001aa:0006 LOP_COUNT_DELTA LCX_CLUSTERED 0000:00000000 sys.sysrowsets.clust NULL
00000081:000001aa:0007 LOP_COUNT_DELTA LCX_CLUSTERED 0000:00000000 sys.sysrowsetcolumns.clust NULL
00000081:000001aa:0008 LOP_COUNT_DELTA LCX_CLUSTERED 0000:00000000 sys.sysrowsetcolumns.clust NULL
00000081:000001aa:0009 LOP_COUNT_DELTA LCX_CLUSTERED 0000:00000000 sys.sysrowsetcolumns.clust NULL
00000081:000001aa:000a LOP_HOBT_DDL LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:000b LOP_MODIFY_ROW LCX_CLUSTERED 0000:00001072 sys.sysallocunits.clust NULL
00000081:000001aa:000c LOP_MODIFY_COLUMNS LCX_CLUSTERED 0000:00001072 sys.sysallocunits.clust NULL
00000081:000001aa:000d LOP_DELETE_ROWS LCX_MARK_AS_GHOST 0000:00001072 sys.sysserefs.clust NULL
00000081:000001aa:000e LOP_MODIFY_HEADER LCX_PFS 0000:00000000 Unknown Alloc Unit NULL
00000081:000001aa:000f LOP_SET_BITS LCX_PFS 0000:00000000 sys.sysserefs.clust NULL
00000081:000001aa:0010 LOP_INSERT_ROWS LCX_CLUSTERED 0000:00001072 sys.sysserefs.clust NULL
00000081:000001aa:0011 LOP_MODIFY_ROW LCX_SCHEMA_VERSION 0000:00000000 sys.sysobjvalues.clst NULL
00000081:000001aa:0012 LOP_INSERT_ROWS LCX_CLUSTERED 0000:00001072 sys.sysallocunits.clust NULL
00000081:000001aa:0013 LOP_INSERT_ROWS LCX_CLUSTERED 0000:00001072 sys.sysserefs.clust NULL
00000081:000001aa:0014 LOP_HOBT_DDL LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:0015 LOP_MODIFY_ROW LCX_CLUSTERED 0000:00001072 sys.sysrowsets.clust NULL
00000081:000001aa:0016 LOP_IDENT_SENTVAL LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:0017 LOP_MODIFY_ROW LCX_CLUSTERED 0000:00001072 sys.syscolpars.clst NULL
00000081:000001aa:0018 LOP_COMMIT_XACT LCX_NULL 0000:00001072 NULL NULL
00000081:000001b0:0001 LOP_BEGIN_XACT LCX_NULL 0000:00001073 NULL DeferredAllocUnitDrop::Process
00000081:000001b0:0002 LOP_LOCK_XACT LCX_NULL 0000:00001073 NULL NULL
00000081:000001b0:0003 LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0004 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0005 LOP_SET_BITS LCX_SGAM 0000:00000000 Unknown Alloc Unit NULL
00000081:000001b0:0006 LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0007 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0008 LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0009 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000a LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000b LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000c LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000d LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000e LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000f LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0010 LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0011 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0012 LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0013 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0014 LOP_SET_BITS LCX_SGAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0015 LOP_SET_BITS LCX_GAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0016 LOP_SET_BITS LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0017 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0018 LOP_SET_BITS LCX_GAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0019 LOP_SET_BITS LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:001a LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:001b LOP_SET_BITS LCX_GAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:001c LOP_SET_BITS LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
etc
Транзакция с идентификатором 0000:00001072 — это неявная транзакция оператора TRUNCATE TABLE (что видно по имени транзакции). Она фиксируется на LSN 00000081:000001aa:0018, а сразу после этого начинается транзакция отложенного удаления (deferred-drop). Как видно из записей журнала, задача просто освобождает страницы и экстенты.
Правда, это не так‑то просто заметить, если не знать, что означают все типы записей в журнале, поэтому взглянем на их описания:
SELECT
[Current LSN], [Operation], [Lock Information], [Description]
FROM fn_dblog (NULL, NULL);
GO
Так вы сможете увидеть блокировки, которые протоколируются, чтобы обеспечить «быстрое восстановление» (см. мою статью «Логирование блокировок и быстрое восстановление» с подробным разбором), а также описания выполняемых операций. Ниже небольшой фрагмент из начала транзакции отложенного удаления:
Operation Lock Information Description
————– ———————————————————– ——————————————————–
LOP_BEGIN_XACT NULL DeferredAllocUnitDrop::Process
LOP_LOCK_XACT HoBt 0:ACQUIRE_LOCK_IX ALLOCATION_UNIT: 8:72057594042384384
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:0
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:152 Deallocated 0001:00000098
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:1
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:156 Deallocated 0001:0000009c
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:2
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:157 Deallocated 0001:0000009d
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:3
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:158 Deallocated 0001:0000009e
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:4
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:159 Deallocated 0001:0000009f
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:5
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:160 Deallocated 0001:000000a0
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:6
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:161 Deallocated 0001:000000a1
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:7
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:162 Deallocated 0001:000000a2
LOP_SET_BITS NULL ClearBit 0001:000000a0
LOP_SET_BITS NULL Deallocated 1 extent(s) starting at page 0001:000000a0
LOP_SET_BITS NULL
LOP_MODIFY_ROW Deallocated 0001:000000a8;Deallocated 0001:000000a9;Deallocated 0001:000000aa;Deallocated 0001:000000ab;Deallocated 0001:000000ac;Deallocated 0001:000000ad;Deallocated 0001:000000ae;Deallocated 0001:000000af
LOP_SET_BITS NULL Deallocated 1 extent(s) starting at page 0001:000000a8
LOP_SET_BITS NULL
LOP_MODIFY_ROW Deallocated 0001:000000b0;Deallocated 0001:000000b1;Deallocated 0001:000000b2;Deallocated 0001:000000b3;Deallocated 0001:000000b4;Deallocated 0001:000000b5;Deallocated 0001:000000b6;Deallocated 0001:000000b7
Первые восемь операций освобождают восемь страниц, которые были выделены из «смешанных» экстентов при первоначальном заполнении таблицы; затем переключаемся на освобождение целых экстентов разом. Повыбирайте записи — это действительно любопытно. Обратите внимание и на запись LOP_LOCK_XACT в журнале: она лишь описывает установку блокировки, а не изменение базы данных. Вы заметите, что отмена распределения экстентов не сопровождается отдельными блокировками на каждую страницу — для этого удерживается блокировка IX на единице распределения (allocation unit).
Кстати, если в таблице есть и некластерные индексы, они обрабатываются тем же способом: будет одна транзакция отложенного удаления, которая освободит страницы и у таблицы, и у всех некластерных индексов — по одной единице распределения за раз. Попробуйте — увидите сами.
Миф развенчан!
P.S. Есть ещё одно заблуждение — будто TRUNCATE TABLE нельзя откатить. Я опровергаю его в старой статье: «Когда после TRUNCATE страницы станут доступны для повторного использования?»
Комментариев нет:
Отправить комментарий