Ведение журнала и восстановление в SQL Server

Одними из самых непонятных составных частей SQL Server являются механизмы ведения журнала и восстановления. Складывается впечатление, что сам факт существования журнала транзакций и то, что неправильное управление этим журналом может приводить к неполадкам, ставит в тупик многих «невольных администраторов баз данных» (DBA). Почему журнал транзакций может неограниченно увеличиваться в размере? Почему в некоторых ситуациях требуется слишком много времени для того, чтобы база данных стала доступной после сбоя системы? Почему невозможно полностью отключить ведение журнала? Почему не удается надлежащим образом восстановить базу данных? Что из себя представляет журнал транзакций, и зачем он существует?

Все эти вопросы я постоянно вижу на форумах SQL Server, поэтому в данной статье я собираюсь предоставить обзор системы ведения журнала и восстановления и объяснить, для чего существует эта неотъемлемая часть модуля хранилищ SQL Server. Будет рассмотрена архитектура журнала транзакции и то, каким образом три модели восстановления, доступные для базы данных, могут изменить поведение журнала транзакций и сам процесс ведения журнала. Заодно я предоставляю некоторые ссылки на ресурсы, описывающие оптимальные методики упрвления журналом транзакций.

Что из себя представляет ведение журнала?

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

Операции, выполняемые в RDBMS, регистрируются (или записываются) на физическом и логическом уровне в терминах событий, происходящих в структурах хранилища базы данных. Для каждого изменения в структурах хранилища имеется отдельная запись журнала, описывающая изменяемую структуру и собственно изменение. Это выполняется способом, позволяющим повторить изменение или, при необходимости, отменить изменение и вернуть все в исходное состояние. Записи журнала хранятся в специальном файле, который называется журналом транзакций — далее это будет описано подробнее, а пока его можно представлять в виде файла с последовательным доступом.

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

Транзакции в SQL Server бывают явными и неявными. При явной транзакции пользователь или приложение выдает оператор BEGIN TRANSACTION T-SQL, оповещающий о запуске данным сеансом группы связанных изменений. Явная транзакция успешно завершается, когда выдается оператор COMMIT TRANSACTION, оповещающий об успешном выполнении группы изменений. Если вместо него выдается оператор ROLLBACK TRANSACTION, все изменения, выполненные в данном сеансе с момента выдачи оператора BEGIN TRANSACTION, обращаются (откатываются), и транзакция отменяется. Откат транзакции может быть принудительно вызван внешним событием, например, нехваткой для базы данных свободного места на диске или выходом из строя сервера. Эти случаи будут рассмотрены далее.

При неявной транзакции пользователь или приложение не выдает явно оператора BEGIN TRANSACTION до выдачи оператора T-SQL. Однако, поскольку все изменения в базе данных должны быть оформлены в транзакцию, модуль хранилищ скрытым образом автоматически запускает транзакцию. По завершении выполнения оператора T-SQL модуль хранилищ автоматически фиксирует транзакцию, запущенную для создания оболочки для пользовательского оператора.

Вам может показаться, что в этом нет необходимости, поскольку один оператор T-SQL не может генерировать большое число изменений в структурах хранилища базы данных, но рассмотрите, например, оператор ALTER INDEX REBUILD. Хотя этот оператор не может содержаться в явной транзакции, он может генерировать огромное число изменений в базе данных. Поэтому необходим механизм, обеспечивающий в случае неправильного развития событий (например, если отменяется выполнение оператора) надлежащее выполнение обращения изменений.

В качестве примера рассмотрим, что происходит, если в неявной транзакции обновляется одна строка таблицы. Представим себе простую неупорядоченную таблицу, содержащую столбец c1 с целочисленными данными и столбец c2 с символьными данными. В таблице имеется 10 000 строк, и пользователь отправляет запрос на обновление следующим образом:

UPDATE SimpleTable SET c1 = 10 WHERE c2 LIKE '%Paul%'; 

Выполняются следующие операции:

• Страницы данных из SimpleTable считываются с диска в память (буферный пул), поэтому можно выполнять поиск соответствующих строк. Оказывается, что на трех страницах данных имеется пять строк, соответствующих предикату предложения WHERE.
• Модуль хранилищ автоматически запускает неявную транзакцию.
• Эти три страницы и пять строк данных блокируются для выполнения обновлений.
• Изменения вносятся в пять записей данных на трех страницах данных, находящихся в памяти.
• Изменения записываются также в записи журнала транзакций на диске.
• Модуль хранилищ автоматически фиксирует эту неявную транзакцию.
  

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

Контрольные точки существуют по двум причинам — для группирования операций ввода/вывода с целью повышения производительности и для сокращения вермени, требуемого для восстановления после сбоя. В терминах производительности, если бы страница данных вытеснялясь на диск при каждом ее обновлении, число операций ввода/вывода в активно используемой системе могло бы превысить возможности подсистемы ввода/вывода. Разумнее с некоторой периодичностью записывать на диск «грязные» страницы (те, которые были изменены с момента их считывания с диска), чем записывать на диск страницы незамедлительно после внесения в них изменений. Чуть ниже я рассмотрю контрольные точки с точки зрения восстановления.

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

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