SQL-Ex blog

Принцип ACID

Журнал транзакций используется для поддержания целостного состояния базы данных. При выполнении транзакций они должны записываться полностью и точно в файлы данных базы данных SQL Server. Если они не на 100% завершены успешно, то всю транзакцию необходимо откатить, чтобы гарантировать содержание в базе данных только завершенных транзакций.

Когда транзакция записывается некорректно или неполностью, база данных могла бы содержать некорректные данные и/или неполную информацию. Существует много различных ситуаций, когда транзакция могла быть некорректно записана в файл данных. Вот несколько причин, почему данные в базе данных могут быть неточны или неполны:

• Введенные в систему данные являются неточными или не отвечают правилам целостности данных.


• Произошел сбой движка базы данных в критичное время, когда не все обновления были зафиксированы на диске в файлах ДАННЫХ.


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

SQL Server поддерживает целостность базы данных, следуя принципу, известному как ACID. Термин ACID это просто аббревиатура для Atomicity (атомарность), Consistency (согласованность), Isolation (изоляция) и Durability (длительность). Эти четыре свойства работают совместно для поддержания согласованности и целостности реляционной базы данных.

Атомарность

Свойство атомарности означает, что на диск будет записана завершенная транзакция, или ничего из транзакции записано не будет. Т.е. реализуется подход "все или ничего". Простой пример работы атомарности можно показать на переводе денег банком при обработке письменного чека. Обработка обналичивания чека требует как дебетовой, так и кредитной операции. Дебетовая операция удаляет средства из аккаунта, с которым связан чек. Кредитная операция заносит средства на аккаунт, на который выписан чек. Для поддержания атомарности необходимо, чтобы были успешно завершены обе операции. Если кредитная или дебетовая операция терпит неудачу по какой-либо причине, то свойство атомарности гарантирует, что никакая операция выполнена не будет. Когда выполняется только часть транзакции реализуется откат всех операций, связанных с обработкой чека, для обеспечения атомарности.

Согласованность

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

Процесс обналичивания чеков требует успешного выполнения обеих, кредитной и дебетовой, операций. Если происходит только "дебетовая" обработка процесса обналичивания чека, то только 50% операций завершено. Без выполнения кредитной операции все правила обработки чека не завершились бы успешно, и "дебетовая" операция должна быть отменена, чтобы гарантировать, что данные в базе не содержат неполной информации. Свойство согласованности обеспечивает успешное выполнение всех частей транзакции, а если нет, то будет выполнен откат незавершенных транзакций.

Изоляция

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

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

SQL Server имеет несколько различных уровней изоляции, одни из которых обеспечивают большую или меньшую изоляцию, чем другие. По поводу различных уровней изоляции обратитесь к документации Microsoft.

Длительность

Свойство длительности подразумевает, что изменения данных будут постоянны в базе данных, если транзакция зафиксирована. Поддержка длительности означает, что изменения не исчезнут в результате сбоя системы, если транзакция была успешно зафиксирована. Т.е. SQL Server гарантирует, что коль скоро операция "дебет" и "кредит" в примере обналичивания чека были завершены и зафиксированы, изменения останутся, даже если ядро базы данных даст сбой. Без длительности изменения могут исчезнуть при сбое системы, что было бы нехорошо. При использовании принципов ACID и информации, хранящейся в журнале транзакций, ядро базы данных в состоянии поддерживать целостность информации в базах данных SQL Server.

Архитектура журнала транзакций

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

Логическая архитектура

Логически журнал транзакций можно представить как последовательный файл, который содержит набор записей, содержащих различные виды модификаций, выполненных в базе данных. При всяком изменении базы данных одна или более записей записываются в логический конец журнала транзакций. Записи либо описывают выполненную логическую операцию, либо содержат образы до и после изменения фактических данных. Образы "после" - это образы данных после модификации базы данных. Логическая операция и образы до и после используются не только для обновления базы данных, но и для отмены транзакции и/или восстановления базы данных в случае краха системы.

Каждая запись журнала идентифицируется регистрационным номером транзакции (LSN). LSN определяет порядок, к котором записи записываются в журнал. Это означает, что запись журнала с LSN = 1 должна была быть записана в журнал до записи журнала с значением LSN, равным 2.

Каждый LSN связывается с транзакцией и может быть активным или неактивным. Активный LSN связан с транзакцией, которая еще не была зафиксирована. Самый старый LSN (наименьший номер LSN) среди активных номеров называется минимальным номером LSN восстановления или MinLSN. На рис.1 показано логическое представление журнала транзакций, где желтым цветом выделены активные LSN.


Рис.1: Логическая архитектура

Физическая архитектура

Физически журнал транзакций состоит из одного или более файлов журнала транзакций. Файл журнала разбит на куски, которые называются виртуальными файлами журнала (VLF). Журнал транзакций может иметь несколько или большое число VLF, в зависимости от величины журнала транзакций и интенсивности его роста. Всякий раз, когда требуется увеличить журнал транзакций, создается дополнительный VLF и добавляется к цепочке VLF. Величина пространства диска, добавляемого к журналу транзакций при каждом событии увеличения его размера, будет определяться числом вновь созданных VLF.

Журнал транзакций можно рассматривать как циркулярный файл, состоящий из связанных в цепочку VLF. Записи журнала транзакций пишутся от начала в конец, а затем возвращаются и начинают запись с самого начала. На диаграмме рис.2 показан журнал транзакций с 4-мя различными файлами VLF, которые логически связаны в циклическую цепочку. Красная пунктирная линия представляет то, как выполняется запись в журнал транзакций, начиная с первого VLF к последнему, и, возвращаясь по кругу обратно, опять начинают запись с первого VLF.


Рис.2: Циркулярный файл журнала

Начальным файлом циркулярного журнала может быть любой из VLF в журнале транзакций. Когда SQL Server достигает конца последовательного файла журнала при добавлении записей в журнал транзакций, он делает круг и начинает писать в журнал от начала последовательного файла журнала до тех пор, пока конец файла журнала когда-нибудь не достигнет логического начала файла журнала, как показано на Рис.3.

Если конец файла журнала когда-нибудь достигнет начала логического файла, SQL Server прекратит запись в базу данных до тех пор, пока некоторые записи не будут удалены из файла журнала транзакций в результате его усечения, или физический файл не будет расширен. Если все VLF активны, когда конец журнала совпадет с началом, то файл журнала транзакций будет расширен, и будут созданы дополнительные VLF. Если некоторые VLF не являются активными при достижении концом журнала его начала, ядро базы данных усечет журнал, очищая неактивные VLF, чтобы освободить место для записи в журнал транзакций. Освобождение неактивных VLF делается автоматически, когда база данных находится в простом режиме восстановления (simple recovery mode <ссылка на модели восстановления>), но потребует резервирования журнала транзакций, если база данных использует полную модель восстановления или модель с неполным протоколированием.


Рис.3: Частично заполненный журнал транзакций

Усечение журнала

Файл журнала должен периодически усекаться, чтобы избежать переполнения. Процесс усечения очищает файлы VLF, которые содержат только те записи журнала, которые уже были зафиксированы. Процесс очистки VLF файлов стартует с VLF, находящегося в начале файла журнала, пока не достигнет VLF, который содержит минимальный номер восстановления (MinLSN). MinLSN - это самая старая запись в журнал, которая необходима для успешного отката самой старой незафиксированной транзакции. Прежде чем журнал может быть усечен, необходимо выполнить команду контрольной точки. На рис.4 показано, как полные VLF очищаются в процессе усечения, и после очистки сбрасывается начало журнала. На рис. 4 видно, что файл журнала начинается с VLF3, а MinLSN находится в VLF4 до усечения журнала. Когда происходит процесс усечения, очищаются только те VLF, которые содержат зафиксированные записи журнала между началом файла журнала и VLF, который содержит MinLSN. В моем примере очистился VLF3, и начало файла журнала было перепозиционировано на начало VLF4.


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

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

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


• После создания резервной копии журнала, если используется модель полного восстановления или с неполным протоколированием, при условии, что была выполнена контрольная точка после предыдущего бэкапа (более подробно о моделях восстановления смотрите статью Понимание моделей восстановления SQL Server).

Что делает контрольная точка?

Изменения в базе данных сначала записываются в памяти в буферный кэш в целях повышения производительности, и фиксируются на диске, когда происходит операция контрольной точки. Когда страница обновилась в памяти, но еще не записана на диск, её называют грязной страницей. При выполнении контрольной точки грязные страницы, которые содержат только зафиксированные транзакции, записываются на диск.

Контрольные точки могут быть записаны автоматически, вручную, явно или неявно. Контрольные точки устанавливаются автоматически, когда ядро базы данных считает на основе установки конфигурационного параметра “Recovery Time Interval” (интервал времени восстановления), что имеется достаточно транзакций, которые могут быть записаны на диск. Они также могут быть выполнены вручную с помощью выполнения команды CHECKPOINT. Если у вас SQL Server 2012 или выше, могут быть выполнены неявные контрольные точки. Неявные контрольные точки записываются на основе числа грязных страниц в журнале, которые могут быть записаны на диск в указанный период времени, контролируемый установкой базы данных “target recovery time” (целевое время восстановления). Этот параметр определяет верхнюю границу того, сколько времени потребуется SQL Server для восстановления базы данных. Контрольные точки могут записываться явно при наступлении конкретных событий сервера. Этими событиями, как отмечено в документации Microsoft, являются:

• Добавлены или удалены файлы базы данных с помощью ALTER DATABASE.


• Создается резервная копия базы данных.


• Создается снимок базы данных, неявно или явно для DBCC CHECKDB.


• Деятельность, требующая выключения базы данных. Например, AUTO_CLOSE включен, и закрыто последнее подключение к базе данных, или изменяется опция базы данных, которая требует перезапуска базы данных.


• Экземпляр SQL Server останавливается в результате остановки службы SQL Server (MSSQLSERVER). Это действие вызывает контрольную точку в каждой базе данных в экземпляре SQL Server.


• Перевод экземпляра отказоустойчивого кластера SQL Server в режим офлайн.

Архитектура журнала транзакций SQL Server

Журнал транзакций - это журнал активности обновлений базы данных. SQL Server поддерживает эту информацию об обновлении базы данных до тех пор, пока она не будет постоянно зафиксирована и записана на диск. Журнал также используется для отката некорректных или незавершенных транзакций, которые могут иметь место в результате проблем в приложении или системе. Его также можно использовать для наката базы данных к определенному моменту времени, если потребуется восстановление. Если не обслуживать журнал транзакций, он может переполниться. Чтобы избежать этого, необходимо периодически выполнять контрольные точки и резервные копии журнала транзакций. В качестве администратора баз данных вам необходимо понимать архитектуру журнала транзакций, как управлять размером и ростом журнала, и периодически выполнять резервирование журнала транзакций базы данных, которая не находится в простом режиме восстановления.