sql saturday #377 kyiv 2015 (transaction log – how it works)

Transaction Log – How it works.

Виталий ПоповичРазработчик баз данныхIntapp

Say Thank you to Volunteers:

They spend their FREE time to give you this event.

Because they are love our COMMUNITY. Because they want YOU

to learn from the BEST IN THE WORLD.

If you see one of these guys in the hall – buy them a beer/wine, they deserve it.

Vitaliy Popovych

Olena Smoliak

Maksim Garnets

Yevhen Nedashkivskyi

Oksana Borysenko

3 Sponsor Sessions at 12:30

Don’t miss them, they might be providing some interesting and valuable information!

Bakotech (Dell) – Room “AE” Intapp – Room “F” DevArt – Room “Lazurny”

Our Sponsors:

Содержание

Что такое журнал транзакций и для чего он нужен?

Как работает журнал транзакций? Из чего состоит журнал транзакций? Ошибки при работе с журналом

транзакций. Лучшие практики.

Что такое журнал транзакций?

В журнале транзакций фиксируются все изменения данных, произведенные в каждой из транзакций.

Файл данных*.mdf

Файл журнала транзакций*.ldf

Транзакция

Транзакция это - набор из одной или нескольких операций с базой данных, которые обрабатываются как одно целое.

Элементарная еденица работы с базой данных

Соблюдение принципов ACID

Atomicity (атомарность транзакций) Consistency (согласованность данных) Isolation (изоляция транзакций) Durability (устойчивость транзакций)

Гарантия корректной и надежной работы транзакций.

A - Atomicity

Атомарность транзакций

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

“Все или ничего!”

C - Consistency

Согласованность данных

Обрабатываемые транзакцией данные никогда не будут в промежуточном или несогласованном состоянии.

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

I - Isolation

Изолированность

Транзакция является изолированной если она не воздействует на другие параллельные транзакции, и они на нее не воздействуют тоже.

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

D - Durability

Устойчивость

Транзакция считается устойчивой, если она может завершиться, несмотря на системный сбой, или, в случае незафиксированной транзакции, может быть полностью отменена.

Rolled forward – накатка транзакцииRolled back – откат транзакции

Для чего он нужен?

На журнал транзакций ложиться работа по обеспечению атомарности транзакций (Atomicity) и живучасти транзакций (Durability). Так же он принимает участие в обеспечении согласованности транзакций (Consistency), в случае если транзакция откатывается.

Для чего он нужен?

Журнал транзакций поддерживает следующие операции: восстановление транзакций; восстановление всех незавершенных транзакций

при запуске SQL Server; поддержка репликации транзакций; поддержка решений высокого уровня доступности

и аварийного восстановления: Группы доступности AlwaysOn, зеркальное отображение базы данных.

Как работает журнал транзакций?

UPDATE [Table] SET column1 = 10 WHERE column2 LIKE '%Text%';

База данныхЖурнал транзакций

Buffer Pool

1) Страницы данных из Table считываются с диска в память

(Buffer Pool)

Как работает журнал транзакций?

UPDATE [Table] SET column1 = 10 WHERE column2 LIKE '%Text%';

База данныхЖурнал транзакций

Buffer Pool

2) Запускается неявная транзакция.

3) Эти три страницы блокируются для выполнения обновлений.

4) Изменения вносятся в страницах данных, находящихся в памяти.

Как работает журнал транзакций?

UPDATE [Table] SET column1 = 10 WHERE column2 LIKE '%Text%';

База данныхЖурнал транзакций

Buffer Pool

5) Изменения записываются также в записи журнала транзакций на диске.

6) Автоматически закрывается неявная транзакция.

Как работает журнал транзакций?

Когда же данные попадают в файл данных?

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

SQL Server пытается запустить процесс контрольной точки всякий раз когда журнал транзакций заполняется более чем на 70 процентов, или при получении ошибки переполнения журнала транзакций, а также при останове SQL Server (если используется SHUTDOWN WITH NOWAIT) 

Записываются все измененные (грязные) страницы!

Checkpoint

UNDO – откат всех изменений до первоначального состояния.

REDO – повтор всех изменений

Контрольная точка создает надежную точку, с которой SQL Server Database Engine может начать применение изменений, содержащихся в журнале, после непредвиденного отключения или аварии.

Из чего он состоит?

Физически, файл журнала транзакций, состоит из виртуальных файлов - Virtual Log Files (VLF’s)

Размер журнала транзакций

Кол-во VLF

64Mb 4 VLF’s

>64Mb <=1Gb 8 VLF’s

>1Gb 16 VLF’s

Как работают VLF

VLF1 VLF2 VLF3 VLF4 VLF5

Начало лога Конец лога

Checkpoint

Когда файл VLF заполняется, процедура ведения журнала автоматически переходит к следующему VLF в журнале транзакций.

Как работают VLF

VLF1 VLF2 VLF3 VLF4 VLF5

Начало логаКонец лога

Неактивные VLF отбрасываются и могут быть перезаписанны.

Транзакция, частью которой является эта запись, зафиксирована. Все страницы базы данных, которые она изменила, записаны на диск

процедурой контрольной точки. Данная запись журнала не требуется для резервного копирования (полного,

выборочного или журнала). Эта запись журнала не требуется никакому компоненту, читающему журнал

(например, средству зеркального отображения базы данных или репликации)

Как работают VLF

VLF1 VLF2 VLF3 VLF4 VLF5

Конец логаНачало лога

Усечение журнала транзакций

Процесс усечения журнала освобождает место в файле журнала для повторного использования журналом транзакций.

Усечение журнала необходимо для предотвращения переполнения журнала.

Когда выполняется усечение?

Усечение журнала выполняется автоматически после следующих событий:

В простой модели восстановления — после контрольной точки. Для моделей полного восстановления и моделей восстановления с

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

Усечение журнала не приводит к уменьшению размера физического файла журнала 

Демонстрация

Virtual Log Files

Логический вид

…

Log Sequence Numbers (LSN)

00000021:00000018:0001VLF number: Log Block Offset: Slot Number

4 байта : 4 байта : 2 байта

VLF number – номер виртуального файлаLog Block Offset – сдвиг внутри VLFSlot Number – номер слота

Общие ошибки

Журнал транзакций растет и заполняет диск!

Если выбрана модель востановления FULL и не делаются бекапы логов;

Старые (долгие) незафиксированные транзакции;

Демонстрация

Full Transaction Log Long Transaction

Лучшие практики

Не создавайте несколько файлов журналов, поскольку это не приводит к выигрышу в производительности.

Файл журнала транзакций это файл с последовательным доступом, и SQL Server не пишет в несколько файлов одновременно.

Лучшие практики

Храните файл журнала транзакций на отдельном диске.

В этом случае есть больше шансов востановления БД после сбоя.Так же это эффективнее с точки знения I/O (нагрузки на дисковую подсистему)

Вопросы?

Спасибо за внимание!

Email: vitaliy.popovych@outlook.comSkype: vitalik.popovych

www.facebook.com/vitaliy.popovich.911www.twitter.com/PopovychVitaliy