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

Обновлено: 05.07.2024

Мне часто приходилось сталкиваться с тем, что люди прекрасно понимают, что такое транзакции в базе данных и для чего они нужны, но при этом не всегда умеют ими правильно пользоваться. Безусловно, для достижения 80-го уровня сакрального знания нужно иметь не один год опыта и прочесть множество толстенных книг по SQL. Поэтому в этой статье я даже не буду пытаться описать всё, что может быть связано с транзакциями в MS SQL. Я хочу затронуть один простой, но очень важный вопрос, который разработчики часто упускают из вида – уровни изоляции транзакций.
Несмотря на то, что тема очень проста, во многих источниках она освящается плохо – информации либо очень мало, либо очень много. Т.е. прочитав 5-6 кратких теоретических определений невозможно их применить на практике. Для уверенного понимания предмета статьи нужно обращаться к специализированной литературе, но там информации на столько много, что далеко не каждый может уделить необходимое время для её усваивания.
Сегодня я хочу поделиться своим простым рецептом, который помог мне раз и на всегда запомнить особенности уровней изоляции транзакций и по сей день помогает без проблем принимать взвешенные решения о выборе необходимого уровня.

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

Побочные эффекты параллелизма

Потерянное обновление (lost update)
«Грязное» чтение (dirty read)
Неповторяющееся чтение (non-repeatable read)
Фантомное чтение (phantom reads)

Первым нужно запускать скрипт для транзакции №1, а затем сразу же скрипт для транзакции №2 (не позднее чем через 10 секунд после начала выполнения первого скрипта).
В базе должна существовать таблица с именем Table1 и колонками Id и Value. В ней ожидается наличие одной строки:

Для создания таблицы и наполнения её данными можно запустить следующий скрипт.

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

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

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

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

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

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

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

восстановление отдельных транзакций;
восстановление всех незавершенных транзакций при запуске SQL Server ;
накат восстановленной базы данных, файла, файловой группы или страницы до момента сбоя;
поддержка репликации транзакций;
Поддержка решений высокой уровня доступности и аварийного восстановления: Группы доступности AlwaysOn, зеркальное отображение базы данных и доставка журналов.

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

Если приложение выдает инструкцию 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. Дополнительные сведения см. в разделе Физическая архитектура журнала транзакций.

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

Журнал транзакций может располагаться в нескольких файлах. Вы можете задать для этих файлов автоматическое расширение, установив для журнала значение FILEGROWTH . Это снижает вероятность исчерпания пространства журнала транзакций, в то же самое время уменьшая административные издержки. Дополнительные сведения см. в разделе Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых групп.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Операции массового импорта (bcp, BULK INSERT и INSERT. SELECT). Дополнительные сведения о том, когда массовый импорт в таблицу подлежит минимальному протоколированию, см. в разделе Prerequisites for Minimal Logging in Bulk Import.

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

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

Частичные изменения типов данных с большими значениями с помощью предложения .WRITE инструкции UPDATE при вставке или добавлении новых данных. Обратите внимание, что минимальное протоколирование не используется при обновлении существующих значений. Дополнительные сведения о больших типах-значениях см. в статье Типы данных (Transact-SQL).

Инструкции WRITETEXT и UPDATETEXT при вставке или добавлении новых данных в столбцы с типом данных text , ntext , и image . Обратите внимание, что минимальное протоколирование не используется при обновлении существующих значений.

Инструкции WRITETEXT и UPDATETEXT являются устаревшими , поэтому старайтесь не использовать их в новых приложениях.

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

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

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

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

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

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

Related tasks

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

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

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

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

Модели восстановления и журналы транзакций

SQL Server поддерживает три модели восстановления - полную (Full), простую (Simple) и с неполным протоколированием (Bulk Logged).

При полной модели восстановления ВСЕ транзакции записываются в журнал. Таким образом, база данных может быть полностью восстановлена после сбоя. Это также означает, что резервная копия базы данных может быть восстановлена к заданному моменту времени, если доступен журнал транзакций или соответствующий бэкап. При моделях восстановления Full и Bulk Logged журналы транзакций усекаются всякий раз, когда выполняется бэкап журнала.

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

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

Структура журнала транзакций

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

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

Что вызывает рост журнала?

Давайте создадим небольшую базу данных, используя код в листинге 1. Файл данных вначале имеет размер 4Мб, файл журнала - 2Мб. Ваши промышленные базы данных никогда не будут иметь такой размер, особенно при популярной практике pre-allocation (предварительное выделение). Мы выбрали такой размер просто в демонстрационных целях.

В этой базе данных мы создаем единственную таблицу (листинг 2) для последующего выполнения операторов языка манипуляции данными (DML).

Выполнив код в листинге 3, проверим, что мы сделали.

Рис.1: Результаты выполнения кода в листинге 3, но до DML

Обратите внимание на столбец File size. Приступаем к наблюдению за ростом журнала транзакций при выполнении операторов INSERT и DELETE 100000 раз (листинг 4).

В листинге 4 выполняется вставка одной строки в таблицу txn_log с последующим её удалением; это действие повторяется 100000 раз.

В целом таблица не увеличивается в результате этих действий, однако журнал транзакций растет существенно. Если повторить запрос в листинге 3 после выполнения операторов DML из листинга 4, то увидим, насколько вырос журнал транзакций:

Рис.2: Результаты выполнения кода из листинга 3 после операторов DML

Журнал транзакций вырос с 4Мб до 40Мб в результате этих манипуляций, хотя файл данных не изменился в размерах. Это ясно показывает, что размер журнала транзакций имеет мало общего с размером данных. На размер журнала оказывает влияние интенсивность, с которой происходит изменение (DML) базы данных.

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

Администраторы баз данных, которые обслуживают экземпляры SQL Server установок IaaS, должны регулярно делать резервные копии журналов транзакций. Полезно иметь конфигурации аварийного восстановления, такие как Log Shipping или AlwaysOn AG. Подобные конфигурации выполняют резервирование автоматически.

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

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

Рис. 3: Вывод кода в листинге 6

Мы можем также сжать физический журнал транзакций с помощью кода, приведенного в листинге 7. Перед сжатием проверьте, что у вас имеется резервная копия журнала транзакций. В условиях производства лучше сделать расписание создания бэкапов журнала, чтобы избежать неконтролируемого роста файла журнала транзакций и гарантировать сохранение данных. При сконфигурированной опции аварийного восстановления типа Log Shipping или AlwaysOn AG это уже гарантируется.

Вы можете обратиться к столбцу log_reuse_wait_desc представления каталога sys.databases, чтобы определить любые условия, которые препятствуют сжатию журнала транзакций. Обратите внимание на запрос этого столбца в листинге 3.

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

Рис.4: Используемое пространство после выполнения кода в листинге 7

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

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

Местоположение файла журнала транзакций должно иметь надлежащий размер, чтобы удовлетворять длительным транзакциям, которые происходят время от времени. В противном случае журнал транзакций может заполнить все дисковое пространство. На рис.4 показано, что происходит с журналом транзакций, когда делается бэкап. Обратите внимание, что физический файл по-прежнему имеет размер 40Мб, но теперь у нас есть около 37Мб свободного пространства.

Рис.5: Журнал транзакций после создания резервной копии

Что происходит при простой модели восстановления?

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

Если выполнить код, представленный в листинге 4, мы получим несколько отличное поведение.

На рис.6 показан рост журнала транзакций при простом режиме восстановления, когда мы выполняем код из листинга 4. Размер физического файла журнала всего 15Мб. Это вдвое меньше, чем он был ранее при использовании полной модели восстановления. Также заметим, что свободное пространство составляет 11,5Мб.

Рис.6: Рост журнала после выполнения кода в листинге 4 при простом режиме восстановления

Означает ли это меньший рост журнала?

Нет. На рис.7 показано, что в процессе выполнения сессии SQL Server установил несколько контрольных точек. Это произвело усечение журнала и дало возможность транзакциям возобновлять рост журнала через определенные промежутки времени.

Рис.7: Захват контрольных точек при помощи расширенных событий

Заключение

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

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

Заметка! T-SQL – это расширение языка SQL, реализованное в Microsoft SQL Server. Более подробно об этом можете почитать в статье – Что такое T-SQL. Подробное описание для начинающих.

Транзакции в T-SQL

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

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

По сути каждая отдельная инструкция языка T-SQL является транзакцией, это называется «Автоматическое принятие транзакций» или «Неявные транзакции», но также есть и явные транзакции, это когда мы сами явно начинаем транзакцию и также явно заканчиваем ее, т.е. делаем все это с помощью специальных команд.

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

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

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

Чтобы этого не допустить, все SQL инструкции, которые логически что-то объединяет, в данном случае все операции, связанные с переводом средств, пишут внутри транзакции, и тогда, если наступит подобная ситуация, все изменения будут отменены, т.е. деньги вернутся обратно на счет.

Транзакции – это отличный механизм обеспечения целостности данных.

Свойства транзакции

У транзакции есть 4 очень важных свойства:

Атомарность – все команды в транзакции либо полностью выполняются, и соответственно, фиксируются все изменения данных, либо ничего не выполняется и ничего не фиксируется;
Согласованность – данные, в случае успешного выполнения транзакции, должны соблюдать все установленные правила в части различных ограничений, первичных и внешних ключей, определенных в базе данных;
Изоляция – механизм предоставления доступа к данным. Транзакция изолирует данные, с которыми она работает, для того чтобы другие транзакции получали только согласованные данные;
Надежность – все внесенные изменения фиксируются в журнале транзакций и данные считаются надежными, если транзакция была успешно завершена. В случае сбоя SQL Server сверяет данные, записанные в базе данных, с журналом транзакций, если есть успешно завершенные транзакции, которые не закончили процесс записи всех изменений в базу данных, они будут выполнены повторно. Все действия, выполненные не подтвержденными транзакциями, отменяются.

Команды управления транзакциями в T-SQL

В T-SQL для управления транзакциями существуют следующие основные команды:

BEGIN TRANSACTION (можно использовать сокращённую запись BEGIN TRAN) – команда служит для определения начала транзакции. В качестве параметра этой команде можно передать и название транзакции, полезно, если у Вас есть вложенные транзакции;
COMMIT TRANSACTION (можно использовать сокращённую запись COMMIT TRAN) – с помощью данной команды мы сообщаем SQL серверу об успешном завершении транзакции, и о том, что все изменения, которые были выполнены, необходимо сохранить на постоянной основе;
ROLLBACK TRANSACTION (можно использовать сокращённую запись ROLLBACK TRAN) – служит для отмены всех изменений, которые были внесены в процессе выполнения транзакции, например, в случае ошибки, мы откатываем все назад;
SAVE TRANSACTION (можно использовать сокращённую запись SAVE TRAN) – данная команда устанавливает промежуточную точку сохранения внутри транзакции, к которой можно откатиться, в случае возникновения необходимости.

Примеры транзакций в T-SQL

Давайте рассмотрим примеры транзакций, реализованные на языке T-SQL.

Исходные данные для примеров

Но сначала нам необходимо создать тестовые данные для нашего примера.

Для этого выполните следующую инструкцию.

Простой пример транзакции в T-SQL

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

Поэтому мы решили эти инструкции объединить в одну транзакцию.

Сначала мы открываем транзакцию командой BEGIN TRANSACTION, далее пишем все необходимые инструкции, которые мы хотим объединить в транзакцию.

После этого командой COMMIT TRANSACTION мы сохраняем все внесенные изменения.

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

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

При этом, стоит помнить о том, что ошибки с определённым уровнем серьезности, например, ошибки, связанные с нарушением ограничений, не влекут за собой автоматический откат всех изменений внесенных текущей транзакцией, поэтому всегда необходимо использовать или инструкцию SET XACT_ABORT ON, или обработку ошибок (допускается и совместное использование).

Например, если во второй инструкции мы попытаемся записать в столбец Price какое-нибудь текстовое значение, то у нас возникнет ошибка, и изменения, внесённые первой инструкцией, не зафиксируются на постоянной основе.

Пример транзакции в T-SQL с обработкой ошибок

В языке T-SQL существует механизм перехвата и обработки ошибок – конструкция TRY… CATCH.

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

Сначала мы открываем блок для обработки ошибок, затем открываем транзакцию командой BEGIN TRANSACTION, далее пишем наши инструкции, например, те же самые две инструкции UPDATE.

После этого закрываем блок TRY, открываем блок CATCH, в котором в случае возникновения ошибки мы откатываем все изменения командой ROLLBACK TRANSACTION. Также мы принудительно завершаем нашу инструкцию командой RETURN.

Если ошибок нет, то в блок CATCH мы, соответственно, не попадаем и у нас выполнится команда COMMIT TRANSACTION, которая сохранит все изменения.

В этом примере нет ошибок, поэтому транзакция завершена успешно.

А в этом примере мы намерено допускаем ошибку во второй инструкции. Поэтому управление передается в блок CATCH, где мы откатываем все изменения, возвращаем номер и описание ошибки и принудительно завершаем всю инструкцию командой RETURN.

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

Уровни изоляции транзакций в T-SQL

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

Давайте подробней рассмотрим уровни изоляции.

READ UNCOMMITTED

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

READ COMMITTED

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

REPEATABLE READ

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

Также уровень REPEATABLE READ, как и остальные, запрещает «Потерянное обновление» – это когда две транзакции сначала считывают одни и те же данные, а затем изменяют их на основе неких вычислений, в результате обе транзакции выполнятся, но данные будут те, которая зафиксировала последняя операция обновления. Это происходит потому, что данные в операциях чтения в начале этих транзакций не были заблокированы.

SERIALIZABLE

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

SNAPSHOT и READ COMMITTED SNAPSHOT

Также существуют уровни изоляции, алгоритм которых основан на версиях строк, это

Иными словами, SQL Server делает снимок и хранит последние версии подтвержденных строк. В данном случае, клиенту не нужно ждать снятия блокировок, пока одна транзакция изменит данные, он сразу получает последнюю версию подтвержденных строк. Следует отметить, что уровни изоляции, основанные на версиях строк, замедляют операции обновления и удаления, так как перед этими операциями сервер делает и копирует снимок строк во временную базу данных.

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

READ COMMITTED SNAPSHOT – этот уровень изоляции работает практически так же, как уровень SNAPSHOT, с одним отличием, он хранит снимок строк, которые подтверждены на момент запуска команды, а не транзакции, как в SNAPSHOT.

Побочные эффекты параллелизма

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

Потерянное обновление (LostUpdate) – при одновременном изменении данных разными транзакциями одно из изменений будет потеряно;
Грязное чтение (DirtyRead) – чтение неподтвержденных данных;
Неповторяющееся чтение (Non-Repeatable Read) – чтение измененных данных в рамках одной транзакции;
Фантомное чтение (Phantom Reads) – чтение записей, которые появились между началом и завершением транзакции.

Каждый из уровней изоляции устраняет определенные побочные эффекты. В таблице ниже приведены сводные данные.

Побочный эффект / Уровень изоляции	Потерянное обновление	Грязное чтение	Неповторяющееся чтение	Фантомные записи
READ UNCOMMITTED	Устраняет	Не устраняет	Не устраняет	Не устраняет
READ COMMITTED	Устраняет	Устраняет	Не устраняет	Не устраняет
REPEATABLE READ	Устраняет	Устраняет	Устраняет	Не устраняет
SERIALIZABLE	Устраняет	Устраняет	Устраняет	Устраняет
SNAPSHOT	Устраняет	Устраняет	Устраняет	Устраняет
READ COMMITTED SNAPSHOT	Устраняет	Устраняет	Устраняет	Устраняет

Включение уровня изоляции в T-SQL

Для того чтобы включить тот или иной уровень изоляции для всей сессии, необходимо выполнить команду SET TRANSACTION ISOLATION LEVEL и указать название уровня изоляции.

Также для уровней SNAPSHOT и READ COMMITTED SNAPSHOT предварительно необходимо включить параметр базы данных ALLOW_SNAPSHOT_ISOLATION для уровня изоляции SNAPSHOT и READ_COMMITTED_SNAPSHOT для уровня READ COMMITTED SNAPSHOT.

Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.

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

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

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

Методы, чтобы узнать, как проверить журнал транзакций SQL Server

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

В третьем решении вы должны использовать SQL Server Management Studio, который может решить ту же проблему за вас. Пока это бесплатный метод, он сложен и может занять некоторое время, чтобы выполнить это упражнение.

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

Как проверить журнал транзакций SQL Server с помощью интеллектуального решения?

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

Приступим к работе с этим инструментом.

Как инструмент помогает проверить журнал транзакций SQL Server?

1. Запустите инструмент и щелкните значок Открыть кнопку, чтобы вставить файлы в панель программного обеспечения.

2. Выберите Онлайн БД вариант и выберите Имя сервера из списка или введите его. Выберите Тип аутентификации и ударил ОК.

3. Отметьте таблицы, которые вы хотите проанализировать, и нажмите на Экспорт кнопка.

4. Выберите Вставлять, Обновлять, и / или Удалить варианты и применить Дата-фильтры.

5. Перейти к База данных SQL Server в Экспорт в / как поле. Введите необходимую информацию в Учетные данные базы данных вариант.

6. Задайте место назначения или Создать новую базу данных и ударил Экспорт чтобы узнать, как проверить журнал транзакций SQL Server.

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

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

Ручной метод открытия файлов журнала с помощью fn_dblog ()

Эта функция может использоваться в целях криминалистики для извлечения данных из файлов транзакций для их тщательного анализа. Пользователи могут применять эту функцию в версиях SQL 2005, 2008 R2 до 2017 для проверки журнала транзакций SQL Server. Ниже приведены шаги для этой функции:

1. Здесь вы должны просмотреть записи, используя T-SQL и использовать Обновлять команда.

2. Выберите Выбирать запрос для просмотра значений таблицы.

3. После этого вы можете увидеть измененные данные.

4. Затем запустите fn_dblog () функционировать согласно вашим требованиям.

5. Теперь вы можете просмотреть все детали внесенных изменений в журнал на вашем дисплее.

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

Как проверить журнал транзакций службы SQL с помощью альтернативного ручного метода?

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

Теперь перейдем к рабочим этапам этой техники:

1. Прежде всего откройте приложение SQL Server Management Studio, чтобы запустить этот процесс.

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

3. Нажмите Соединять после заполнения данных и переходите к проверке журнала транзакций SQL Server.

4. На этом шаге перейдите к Обозреватель объектов окно и выберите Управление вариант.

5. Там выберите Журналы SQL Server вариант из расширенного меню.

6. Теперь щелкните правой кнопкой мыши Журналы SQL Server возможность открыть другой расширенный список и нажать Вид.

7. Здесь выберите Журнал SQL Server кнопку из меню.

8. Наконец, вы сможете увидеть журналы на Окно просмотра файлов журнала.

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

Это должно быть все

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

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

Читайте также: