Одним из фундаментальных понятий в любой реляционной системе управления базами данных (СУБД), такой как SQL Server, является транзакция. За свою консультационную карьеру я видел множество случаев проблем с производительностью, вызванных тем, что разработчики не понимают, как работают транзакции в SQL Server, поэтому в этом учебном руководстве я объясню, что такое транзакции и почему они необходимы, а также некоторые детали их работы в SQL Server. В использовании всего этого есть нюансы, когда задействовано Ускоренное восстановление базы данных (ADR) — темы для будущих статей.
Что такое транзакция?
Транзакция — это единица работы в базе данных. Каждая транзакция имеет определённую начальную точку и определённую конечную точку. Конечной точкой может быть фиксация транзакции (т.е. успешное завершение) или завершение транзакции откатом (т.е. неуспешное завершение), и я расскажу о значении этих терминов чуть позже.
Базовый синтаксис для транзакций выглядит следующим образом:
BEGIN TRANSACTION(илиBEGIN TRAN) запускает транзакцию.COMMIT TRANSACTION(илиCOMMIT TRAN) успешно завершает транзакцию.ROLLBACK TRANSACTION(илиROLLBACK TRAN) приводит к неуспешному завершению транзакции, так что все операции, выполненные в транзакции, отменяются.
Вы также можете указать имя транзакции, но это не требуется, и я не часто вижу их использование.
Важно задуматься о том, что такое единица работы. Это означает все изменения в базе данных в пределах транзакции, обычно операции изменения данных DML, такие как инструкции insert, update и delete. Это может быть одна инструкция T-SQL или несколько инструкций, в зависимости от типа используемой транзакции. Если это одна инструкция, это не обязательно означает одно изменение. Рассмотрим таблицу с 1000 строк, и кто-то выполняет инструкцию UPDATE с предложением WHERE. Это одна инструкция, но она приведёт к изменению всех строк в таблице — по крайней мере 1000 изменений в базе данных в рамках транзакции.
Даже если инструкция UPDATE работает только с одной строкой в таблице, всё равно происходит как минимум два изменения в базе данных: обновление самой строки на странице файла данных и обновление страницы дифференциальной битовой карты, чтобы отметить эту часть базы данных как изменённую, чтобы следующая дифференциальная резервная копия скопировала экстент, частью которого является эта страница. За годы я видел множество других примеров того, как одна инструкция может вызвать множество изменений в базе данных в зависимости от типов данных, таких как varchar, форматов таблиц, наличия некластерных индексов и так далее.
Почему транзакции необходимы?
Транзакции являются частью того, как SQL Server реализует свойства ACID базы данных (Атомарность, Согласованность, Изоляция и Долговечность), наряду с такими механизмами, как блокировки и журналирование.
Транзакция гарантирует, что её единица работы либо полностью присутствует в базе данных, либо полностью отсутствует. Это атомарность из свойств ACID, и я объясню, как это делается, позже. Это означает, что транзакции очень полезны для разработчиков SQL Server, чтобы контролировать, полностью ли успешен набор операций (например, реализующий некоторую бизнес-логику) или совсем не успешен, так чтобы не было частично выполненных наборов операций, которые оставили бы базу данных несогласованной с бизнес-точки зрения.
Классический пример — перевод денег с расчётного счёта на депозитный счёт. Это включает списание с расчётного счёта и зачисление на депозитный счёт. Это должно быть реализовано как одна транзакция, чтобы в случае успеха списания и неудачи зачисления вся транзакция в целом завершилась неудачей, и списание было отменено при откате транзакции.
Во всех случаях изменения базы данных выполняются под блокировками, удерживаемыми транзакцией, и эти блокировки не снимаются до завершения транзакции. Используя уровень изоляции по умолчанию, который называется read committed (подтверждённое чтение), другие транзакции не смогут увидеть эти изменения, пока транзакция не будет зафиксирована (завершена), отсюда и название уровня изоляции. Это изоляция из свойств ACID.
Например, изменение строки будет включать монопольную блокировку строки транзакцией. Другая транзакция, которая хочет прочитать эту строку, обычно потребует разделяемую блокировку на строку и, следовательно, также будет заблокирована. Это поведение можно изменить, если читающая транзакция переключится на уровень изоляции read uncommitted (чтение неподтверждённых данных) (или использует опцию NOLOCK в инструкции SELECT), который не требует разделяемых блокировок для чтения строк, но вносит возможность возникновения аномалий.
Типы транзакций в SQL Server
В SQL Server существуют три основных типа транзакций:
- Явные транзакции, как следует из названия, должны быть явно запущены инструкцией
BEGIN TRANSACTIONи явно завершены либо инструкциейCOMMIT TRANSACTION, либо инструкциейROLLBACK TRANSACTION. Другими словами, разработчик SQL Server контролирует, когда единица работы фиксируется или нет. - Автофиксируемые транзакции — это тип, при котором разработчик не контролирует начальную и конечную точки транзакции. Каждая инструкция T-SQL является собственной транзакцией, которую SQL Server запускает и фиксирует автоматически под капотом. Не существует понятия возможности изменить базу данных SQL Server без запуска транзакции, поскольку SQL Server должен иметь возможность откатить изменение, если что-то пойдёт не так.
- Неявные транзакции — это когда транзакция автоматически запускается SQL Server, как только вносится изменение в базу данных, но остаётся активной до тех пор, пока не будет явно завершена. В этот момент автоматически запускается новая транзакция. Это поведение не является поведением по умолчанию и должно быть специально включено с помощью инструкции
SET IMPLICIT_TRANSACTIONS, что обычно не делается, за исключением случаев обеспечения совместимости поведения с другой СУБД, для которой это поведение по умолчанию. Я видел, как это становилось проблемой, когда разработчики не осознавали, что неявные транзакции включены, и не думали, что им нужно явно фиксировать транзакцию. Подробнее об этом в разделе «распространённые ошибки» ниже.
Для всех этих трёх типов транзакций, если SQL Server сталкивается с проблемой, вся транзакция будет автоматически откачена.
В SQL Server также есть два более продвинутых вида транзакций, которые выходят за рамки этой статьи:
- Транзакции в области пакета, которые используются только во время сеансов с несколькими активными наборами результатов (MARS).
- Распределённые транзакции, которые используются, когда локальной транзакции необходимо координироваться с несколькими экземплярами SQL Server, например, для выполнения хранимых процедур с бизнес-логикой на разных серверах. Это делается с помощью координатора распределённых транзакций (DTC) или самой службой в Azure Managed Instance.
Как работает фиксация в SQL Server?
Рассмотрим очень простой пример явной транзакции, которая вставляет запись в таблицу, используя код:
BEGIN TRANSACTION;
INSERT INTO
MyDatabase.dbo.MyTable
VALUES (1, 'Paul', 'Randal');
COMMIT TRANSACTION;Инструкция insert вызывает получение некоторых блокировок, что обеспечивает часть изоляции свойств ACID базы данных. Они будут сняты только после того, как транзакция будет зафиксирована. Когда выполняется инструкция COMMIT TRANSACTION, я знаю, что вставка теперь долговечна. Как это на самом деле происходит?
Все изменения базы данных журналируются. Проще говоря, это означает, что когда вносится изменение на страницу файла данных, генерируется описание изменения, называемое записью журнала, и оно заносится в журнал транзакций базы данных. Также, когда транзакция начинается, генерируется запись журнала, и ещё одна — когда транзакция фиксируется. Это означает, что наша простая явная транзакция будет иметь три записи журнала в журнале транзакций, все с одним и тем же идентификатором транзакции, по одной для каждой из трёх выполненных инструкций. На самом деле, если бы я использовал автофиксируемую транзакцию вместо явной (выполнив только инструкцию insert), SQL Server автоматически запустил бы и зафиксировал транзакцию, и в журнале транзакций для этой транзакции всё равно было бы три записи журнала. Один интересный факт, который вы, возможно, не знаете: SQL Server обычно даёт имена транзакциям, которые он запускает; в этом случае она была бы названа просто 'INSERT'.
Когда транзакция фиксируется, SQL Server должен убедиться, что все записи журнала для транзакции находятся в журнале транзакций на диске, а не только в памяти, чтобы в случае сбоя транзакция могла быть воспроизведена, гарантируя её долговечность. Он делает это, обеспечивая сброс на диск всего журнала транзакций в памяти вплоть до записи журнала для COMMIT TRANSACTION до того, как фиксация будет подтверждена пользователю или приложению. Последовательность операций при фиксации следующая:
- Убедиться, что журнал сброшен на диск
- Если есть синхронное зеркало базы данных или синхронная реплика группы доступности, убедиться, что журнал также записан на диск для их файлов журнала на удалённых серверах
- Снять блокировки, удерживаемые транзакцией
- Подтвердить, что фиксация произошла
Нет необходимости также сбрасывать на диск изменённые страницы файлов данных на этом этапе, так как транзакция уже стала долговечной благодаря тому, что описание всех изменений находится на диске. Страницы файлов данных будут записаны позже операцией контрольной точки — тема для будущей статьи.
Как работает откат в SQL Server?
Когда транзакция должна быть откачена, все операции, которые были частью транзакции, должны быть по существу обращены вспять, чтобы ни одно из изменений данных от транзакции не присутствовало в базе данных. Это делается с помощью журнала транзакций, поскольку записи журнала для транзакции связаны вместе в обратном порядке, и это позволяет отменить изменения транзакции в обратном порядке.
Рассмотрим другой простой пример:
BEGIN TRANSACTION;
INSERT INTO
MyDatabase.dbo.MyTable
VALUES (1, 'Paul', 'Randal');
INSERT INTO
MyDatabase.dbo.MyTable
VALUES (2, 'Kimberly', 'Tripp');На этом этапе для транзакции есть три записи журнала. Если затем я решу выполнить команду отката, SQL Server делает следующее:
- Находит самую последнюю запись журнала для «прямой» части транзакции, определяет, какая операция отменит изменение, описанное записью журнала, выполняет операцию и генерирует запись журнала.
- Находит предыдущую запись журнала, на которую указывает LSN «предыдущей записи журнала».
- Повторяет, пока не будет достигнута начальная запись журнала. На этом этапе откат завершён, поэтому генерируется ещё одна запись журнала, указывающая, что транзакция успешно прервана.
В моём примере это приведёт к созданию ещё трёх записей журнала. Как видите, откат транзакции требует большой работы под капотом.
Также возможно определить точку сохранения с помощью инструкции SAVE TRANSACTION и выполнить откат к этой именованной точке в транзакции, а не откатывать всю транзакцию целиком.
Распространённые ошибки, которые могут вызвать проблемы с журналом транзакций
Первая ошибка — забыть зафиксировать транзакцию. Это означает, что всё, что впоследствии происходит в этом подключении, является частью той же транзакции. По мере внесения дополнительных изменений генерируется больше записей журнала и требуется больше места в журнале транзакций. Пространство, используемое для хранения записей журнала из более ранней части транзакции, не может быть повторно использовано (т.е. позволить журналу усекаться), поскольку эти записи журнала должны оставаться на случай отката транзакции (и они нужны для механизма, который я описал выше). Журнал транзакций, скорее всего, будет расти... и расти... и расти, пока кто-нибудь наконец не зафиксирует долго выполняющуюся транзакцию и не позволит вернуть журнал под контроль.
Вторая ошибка — непреднамеренное выполнение кода, который делает гораздо больше работы, чем вы думали, например, выполнение обновления очень большой таблицы (например, миллиард строк) и забывание предложения WHERE. Для каждой обновлённой строки генерируется по крайней мере одна запись журнала, поэтому для транзакции будет сгенерировано по крайней мере миллиард записей журнала, и это, скорее всего, вызовет взрывной рост журнала транзакций. Администратор баз данных, не знающий, как работает откат, может быть искушён немедленно отменить обновление. Но знающий администратор будет знать, что откат очень долго выполняющейся транзакции приведёт к генерации по крайней мере такого же количества записей журнала, которое уже было сгенерировано, что займёт гораздо больше времени, и может решить, что благоразумным образом действий будет позволить обновлению завершиться.
Если у вас есть журнал транзакций, который, казалось бы, выходит из-под контроля, вы можете увидеть причину, выполнив этот код:
SELECT
[log_reuse_wait_desc]
FROM [master].[sys].[databases]
WHERE
[name] = N'MyDatabase';Если виноват один из этих двух сценариев ошибок, вывод будет выглядеть так:
log_reuse_wait_desc
-------------------
ACTIVE_TRANSACTIONЕсли нет, вы можете прочитать о других возможных значениях и их значении в документации Microsoft здесь, в разделе "Факторы, которые могут вызвать задержку усечения журнала".
Важность понимания транзакций в SQL Server
Важно не только понимать, что такое транзакции, и проектировать свой код так, чтобы он надлежащим образом реализовывал вашу бизнес-логику, но также важно понимать некоторые внутренние механизмы, которые я описал, поскольку ошибки могут вызывать проблемы у администраторов баз данных. По моему опыту, администраторам баз данных часто нужно знать, как работают транзакции и какие потенциальные ошибки могут быть допущены, чтобы они могли устранять неполадки, связанные с журналом транзакций.
Существует гораздо больше аспектов использования транзакций, таких как указание уровней изоляции и написание эффективного кода, но я надеюсь, что это начальное введение дало вам хорошую основу для понимания того, зачем нужны транзакции и как они работают. Знаю, это звучит как клише, но с SQL Server определённо работает принцип: чем больше вы знаете, тем дальше вы продвинетесь!
Комментариев нет:
Отправить комментарий