лог транзакций что это

Лог транзакций что это

SqlCmd все о SQL технологиях.

Все что необходимо для изучения и работы с СУБД Microsoft SQL Server, MySQL, MariaDB, MongoDB. Авторские статьи, библиотека фрагментов T-SQL кода, сборник полезных инструментов.

Как перестать называть журнал транзакций SQL Server лог-файлом и прекратить борьбу за его размер. Часть 1/12.

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

Пусть подобные мысли не остановят его от тщательного изучения представленного материала, ибо ничего подобного он в нем не обнаружит. Автор данной статьи и данного блога вообще придерживается той точки зрения, что если ваша терминология понятна вам, вашим товарищам по developer team, и вашим коллегам на профессиональных форумах — вперед, называйте вы этот лог хоть «горшком». Поэтому хотя официальное название героя нашего повествования журнал транзакций (и это, несомненно, лучшее название для него) вы можете сокращать эту довольно длинную формулировку до лог-файла, или даже log-файла, или вообще ldf-файла — вас, несомненно, поймут. В очень редких случаях потребуется уточнить что вы ведете речь о том log, что именно журнал транзакций, а не о том error log что создается движком сервера при каждом его рестарте, или вообще не о логе событий OS Windows. Однако в 99% случаев подразумеваемый log-файл совершенно очевиден из контекста вашего сообщения и никаких уточнений не требуется.

Так вот, статья вовсе не будет учить вас правильно говорить по-русски, это задача иных блогов и их авторов. Вместо этого мы попробуем разобраться:

Ведение журнала транзакций. Основы.

Полагаю, для большинства читателей можно было данную «шпаргалку» и не приводить — она им прекрасно известна. В крайнем случае ее можно было бы сократить до фразы «все изменения в данных должны быть надежно зафиксированы в файле лога, прежде чем транзакция получит статус успешно проведенной». Этого вполне было бы достаточно что бы все описанные пункты возникли перед их мысленным взором. А для самых искушенных читателей и вообще можно было ограничиться термином write-ahead logging, что бы они моментально поняли суть происходящего. НО! Могут ли читатели любой из этих 3-х групп сказать что им понятны все нюансы описанного процесса? Тех кто может это заявить я искренне поздравляю (хоть и предлагаю чтение не бросать, а лишний раз убедиться в своей компетентности), а остальным спешу сообщить: 90% оставшегося объема статьи, по сути своей, сводится к разбору указанных нюансов. А так же ответу на вопрос: «как мне самому посмотреть/убедиться, что данный нюанс работает именно так, а не иначе». Потому что понимание этих самых нюансов автоматически означает получение ответов на все вопросы заданные во введении, что и является целью данного материала.

Внутреннее устройство некоторых структур хранения информации SQL Server.

Для успешного понимания означенных нюансов нам нужно «снизойти» до понимания того, как хранятся данные и записи журнала транзакций на довольно-таки низком уровне, в части случаев буквально на уровне отдельных байтов. Автор сразу предупредил, что совсем просто — не будет, хотя он и постарается смягчить «шок» от столкновения с суровой реальностью физического функционирования SQL сервера. Кроме того, автор вовсе не призывает безоглядно верить всем последующим объяснениям. Напротив, он предлагает (и даже настаивает), что бы вы проверяли их все, не пропуская ни одного. Это полезно как с целью лучшего понимания данного материала, так и с целью его дальнейшего применения на ваших реальных базах данных. А стало быть, требуется не только «знание байтов», но и знание того откуда они, байты эти, берутся и как могут быть просмотрены и проанализированы.

Эти знания, а так же наши первые эксперименты в области работы лога мы будем проводить вот с такой несложной базой:

Источник

Журнал транзакций (SQL Server)

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

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

Сведения об архитектуре и внутренних компонентах журнала транзакций см. в разделе Руководство по архитектуре журнала транзакций SQL Server и управлению им.

Удаляя или перемещая этот журнал, вы должны понимать все последствия этого действия.

Известные рабочие точки, от которых следует начинать применение журналов транзакций при восстановлении базы данных, создаются контрольными точками. Дополнительные сведения см. в статье Контрольные точки базы данных (SQL Server).

Операции, поддерживаемые журналом транзакций

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

Восстановление отдельных транзакций

Если приложение выдает инструкцию ROLLBACK или Компонент Database Engine обнаруживает ошибку, такую как потеря связи с клиентом, записи журнала используются для отката изменений, выполненных в результате незавершенной транзакции.

Восстановление всех незавершенных транзакций при запуске SQL Server

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

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

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

При восстановлении каждой резервной копии журнала, Компонент Database Engine повторно применяет все изменения, записанные в журнале, для наката всех транзакций. После восстановления последней резервной копии журнала Компонент Database Engine затем использует данные журнала для отката всех транзакций, которые не были завершены на момент сбоя. Дополнительные сведения см. в статье Обзор процессов восстановления (SQL Server).

Поддержка репликации транзакций

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

Поддержка решений высокого уровня доступности и аварийного восстановления

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

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

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

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

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

Ниже приведены характеристики журнала транзакций Компонент SQL Server Database Engine.

Журнал транзакций выполнен как отдельный файл или набор файлов в базе данных. Кэш журнала управляется отдельно от буферного кэша для страниц данных, что приводит к простому, быстрому и устойчивому коду в пределах компонента Компонент SQL Server Database Engine. Дополнительные сведения см. в разделе Физическая архитектура журнала транзакций.

Формат записей журнала и страниц не обязан следовать формату страниц данных.

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

Сведения об архитектуре и внутренних компонентах журнала транзакций см. в разделе Руководство по архитектуре журнала транзакций SQL Server и управлению им.

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

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

Усечение журнала удаляет неактивные виртуальные файлы журнала (VLF) из логического журнала транзакций базы данных SQL Server, освобождая в нем место для повторного использования физическим журналом транзакций. Если усечение журнала транзакций не выполняется, со временем он заполняет все доступное место на диске, отведенное для файлов физического журнала.

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

Дополнительные сведения см. в разделе Факторы, которые могут вызвать задержку усечения журнала далее в этой статье.

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

Факторы, которые могут вызвать задержку усечения журнала

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

Дополнительные сведения о том, что нужно делать при переполнении журнала транзакций, см. в разделе Troubleshoot a Full Transaction Log (SQL Server Error 9002).

На самом деле усечение журнала может быть задержано из-за множества причин. Чтобы узнать причину, препятствующую усечению журнала транзакций в конкретном случае, выполните запрос по столбцам log_reuse_wait и log_reuse_wait_desc представления каталога sys.database. В следующей таблице описаны значения этих столбцов.

Операции, допускающие минимальное протоколирование

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

Минимальное протоколирование не поддерживается для оптимизированных для памяти таблиц.

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

Следующие операции, выполняемые с полным протоколированием в модели полного восстановления, осуществляются с минимальным протоколированием в простой модели восстановления и модели восстановления с неполным протоколированием:

Если включена репликация транзакций, операции BULK INSERT протоколируются полностью даже в модели восстановления с неполным протоколированием.

Если включена репликация транзакций, операции SELECT INTO протоколируются полностью даже в модели восстановления с неполным протоколированием.

Если в базе данных используется простая модель восстановления или модель восстановления с неполным протоколированием, некоторые DDL-операции с индексом протоколируются в минимальном объеме при их выполнении как режиме «вне сети», так и в режиме «в сети». Минимально протоколируются следующие операции с индексами.

ОперацииCREATE INDEX (включая индексированные представления).

ОперацииALTER INDEX REBUILD или DBCC DBREINDEX.

Инструкция DBCC DBREINDEX является устаревшей. Не используйте ее в новых приложениях.

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

Перестроение новой кучи DROP INDEX (если применимо). Освобождение страниц индексов при выполнении операции DROP INDEX всегда протоколируется полностью.

Related tasks

Управление журналом транзакций

Резервное копирование журнала транзакций (модель полного восстановления)

Восстановление журнала транзакций (модель полного восстановления)

Источник

Резервное копирование Microsoft SQL Server

Отказы системы

Некоторые из отказов могут носить умеренный характер; некоторые могут оказаться разрушительными. Чтобы понять важность резервного копирования, вы должны знать о трех основных категориях отказов: отказы оборудования, отказы программного обеспечения и человеческие ошибки.

Отказы оборудования

Отказы оборудования – это, видимо, наиболее распространенный тип отказов, с которыми вы будете сталкиваться. Хотя эти отказы возникают менее часто по мере роста надежности компьютерного оборудования, но компоненты компьютеров все равно со временем становятся подвержены износу. Вот следующие типичные отказы оборудования.

Отказы программного обеспечения

Человеческие ошибки

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

Журнальное протоколирование в SQL Server

Журнал транзакций

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

Поток откладываемой записи (Lazywriter Thread)

Кроме потока откладываемой записи, поток контрольной точки а также выполняет запись ожидающих страниц на диск. (Мы рассмотрим более подробно поток контрольной точки ниже в этом разделе.)

Последовательная запись в журнал

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

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

Воспроизведение с помощью журнала транзакций

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

Источник

Транзакции и механизмы их контроля

Транзакции

Транзакцией называется последовательность операций над данными имеющая начало и конец

Транзакция это последовательное выполнение операций чтения и записи. Окончанием транзакции может быть либо сохранение изменений (фиксация, commit) либо отмена изменений (откат, rollback). Применительно к БД транзакция это нескольких запросов, которые трактуются как единый запрос.

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

Атомарность. Транзакция либо выполняется полностью либо не выполняется вовсе.

Согласованность. При завершении транзакции не должны быть нарушены ограничения накладываемые на данные (например constraints в БД). Согласованность подразумевает, что система будет переведена из одного корректного состояния в другое корректное.

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

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

Журнал транзакций

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

Журнал содержит значения, которые данные имели до и после их изменения транзакцией. Write-ahead log strategy обязывает добавлять в журнал запись о предыдущих значениях до начала, а о конечных после завершения транзакции. В случае внезапной остановки системы БД читает лог в обратном порядке и отменяет изменения сделанные транзакциями. Встретив прерванную транзакцию БД выполняет ее и вносит изменения о ней в журнал. Находясь в состоянии на момент сбоя, БД читает лог в прямом порядке и возвращает изменения сделанные транзакциями. Таким образом сохраняется устойчивость транзакций которые уже были зафиксированы и атомарность прерванной транзакции.

Простое повторное выполнение ошибочных транзакций недостаточно для восстановления.

Пример. На счету у пользователя 500$ и пользователь решает снять их через банкомат. Выполняются две транзакции. Первая читает значение баланса и если на балансе достаточно средств выдает деньги пользователю. Вторая вычитает из баланса нужную сумму. Допустим, произошел сбой системы и первая операция не выполнилась, а вторая выполнилась. В этом случае мы не можем повторно выдать деньги пользователю без возврата системы в изначальное состояние с положительным балансом.

Уровни изоляции

Чтение фиксированных данных (Read Committed)

Проблема грязного чтения (Dirty Read) заключается в том, что транзакция может прочесть промежуточный результат работы другой транзакции.

Пример. Начальное значение баланса 0$. Т1 добавляет к балансу 50$. Т2 считывает значение баланса (50$). Т1 отменяет изменения и завершается. T2 продолжает выполнение располагая неверными данными о балансе.

Решением является чтение фиксированных данных (Read Committed) запрещающее читать данные, измененные транзакцией. Если транзакция A изменила некоторый набор данных, то транзакция B при обращении за этими данными вынуждена ожидать завершения транзакции A.

Повторяемое чтение (Repeatable Read)

Проблема потерянных изменений (Lost Updates). Т1 сохраняет изменения поверх изменений Т2.

Пример. Начальное значение баланса 0$ и две транзакции одновременно пополняют баланс. T1 и T2 читают баланс равный 0$. Затем T2 прибавляет 200$ к 0$ и сохраняет результат. T1 прибавляет 100$ к 0$ и сохраняет результат. Итоговый результат 100$ вместо 300$.

Проблема неповторяемого чтения (Unrepeatable read). Повторное чтение одних и тех же данных возвращает разные значения.

Пример. Т1 читает значение баланса равное 0$. Затем Т2 добавляет к балансу 50$ и завершается. Т1 повторно читает данные и обнаруживает несоответствие с предыдущим результатом.

Повторяемое чтение (Repeatable Read) гарантирует что повторное чтение вернет тот же результат. Данные прочитанные одной транзакцией запрещено менять в других до завершения транзакции. Если транзакция A прочла некоторый набор данных, то транзакция B при обращении за этими данными вынуждена ожидать завершения транзакции A.

Упорядоченное чтение (Serializable)

Проблема фантомного чтения (Phantom Reads). Два запроса выбирающие данные по некоему условию возвращают разные значения.

Пример. T1 запрашивает количество всех пользователей баланс которых больше 0$ но меньше 100$. T2 вычитает 1$ у пользователя с балансом 101$. T1 повторно выполняет запрос.

Упорядоченное чтение (Serializable). Транзакции выполняются как полностью последовательные. Запрещается обновлять и добавлять записи, подпадающие под условия запроса. Если транзакция A запросила данные всей таблицы, то таблица целиком замораживается для остальных транзакций до завершения транзакции A.

Планировщик (Scheduler)

Устанавливает очередность в которой должны выполняться операции при параллельно протекающих транзакциях

Обеспечивает заданный уровень изолированности. Если результат выполнения операций не зависит от их очередности, то такие операции коммутативны (Permutable). Коммутативны операции чтения и операции над разными данными. Операции чтения-записи и записи-записи не коммутативны. Задача планировщика чередовать операции выполняемые параллельными транзакциями так, чтобы результат выполнения был эквивалентен последовательному выполнению транзакций.

Механизмы контроля параллельных заданий (Concurrency Control)

Оптимистический основан на обнаружении и разрешении конфликтов, пессимистический на предотвращении возникновения конфликтов

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

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

Блокировка (Locking)

Если одна транзакция заблокировала данные, то остальные транзакции при обращении к данным обязаны ждать разблокировки

Блок может накладываться на базу данных, таблицу, ряд или аттрибут. Совместный захват (Shared Lock) может быть наложен на одни данные несколькими транзакциями, разрешает всем транзакциям (включая наложившую) чтение, запрещает изменение и монопольный захват. Монопольный захват (Exclusive Lock) может быть наложен только одной транзакцией, разрешает любые действия наложившей транзакции, запрещает любые действия остальным.

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

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

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

С определенной периодичностью производится поиск взаимоблокировок. Один из способов обнаружения — по времени, то есть считать что взаимоблокировка произошла если транзакция выполняется слишком долго. Когда взаимоблокировка найдена, то одна из транзакций откатывается, что дает возможность другим транзакциям участвующим во взаимоблокировке завершиться. Выбор жертвы может быть основан на стоимости транзакций или их старшинстве (Wait-Die и Wound-wait схемы).

Каждой транзакции T присваивается временная метка TS содержащая время начала выполнения транзакции.

Если TS(Ti) = W-TS(Q), то чтение выполняется и R-TS(Q) становится MAX(R-TS(Q), TS(T)).

Когда транзакция T запрашивает изменение данных Q возможны два варианта.

Источник