Управление устойчивостью транзакций

  • Статья

Применимо к:База данныхSQL Server Azure SQL Управляемый экземпляр SQL Azure

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

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

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

Сравнение полностью устойчивых и отложенных устойчивых транзакций

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

Полная устойчивость транзакций

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

  • Система не может работать при потере данных. См. раздел Когда я могу потерять данные? , чтобы узнать, когда данные могут быть потеряны.

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

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

Гарантии полностью устойчивых транзакций

  • После успешного выполнения фиксации транзакции изменений, внесенные транзакцией, становятся видимыми для других транзакций в системе. Дополнительные сведения об уровнях изоляции транзакций см. в разделах SET TRANSACTION ISOLATION LEVEL (Transact-SQL) или Транзакции с Memory-Optimized таблицами.

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

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

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

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

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

    Примечание

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

Использование отложенных устойчивых транзакций

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

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

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

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

Гарантии отложенных устойчивых транзакций

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

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

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

    • Пользователь успешно выполняет системную хранимую процедуру sp_flush_log .

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

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

Управление устойчивостью транзакций

Управление на уровне базы данных

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

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

ВЫКЛЮЧЕНО
[по умолчанию] При использовании этой настройки все фиксируемые в базе данных транзакции являются полностью устойчивыми независимо от настроек уровня фиксации (DELAYED_DURABILITY= [ON | OFF]). Изменение и повторная компиляция хранимых процедур не требуются. Это позволяет гарантировать, что данные не будут подвергаться рискам из-за отложенной устойчивости.

РАЗРЕШЕНО
При использовании этой настройки устойчивость каждой транзакции определяется на уровне транзакций — DELAYED_DURABILITY = { OFF | ON }. Дополнительные сведения см. в разделах Atomic Block Level Control — Скомпилированные в собственном коде хранимые процедуры и Управление на уровне COMMIT .

ПРИНУДИТЕЛЬНО
Если выбран этот параметр, все транзакции, которые фиксируются в базе данных, являются отложенными устойчивыми. Независимо от того, указана ли транзакция как полностью устойчивая (DELAYED_DURABILITY = OFF) или данные не указаны, транзакция является отложенной устойчивой. Этот параметр полезен, если отложенная устойчивая транзакция используется для баз данных и не следует изменять код приложения.

Управление на уровне блока Atomic — скомпилированные в собственном коде хранимые процедуры

Ниже представлен код блока ATOMIC.

DELAYED_DURABILITY = { OFF | ON }

OFF
[по умолчанию] Транзакция является полностью устойчивой, пока действует параметр базы данных DELAYED_DURABLITY = FORCED, в этом случае фиксация является асинхронной и таким образом, устойчивой. Дополнительные сведения см. в разделе Управление на уровне базы данных.

ON
Транзакция является устойчиво отложенной, пока действует параметр базы данных DELAYED_DURABLITY = DISABLED, в этом случае фиксация является синхронной и таким образом, полностью устойчивой. Дополнительные сведения см. в разделе Управление на уровне базы данных.

Пример кода

CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
    DELAYED_DURABILITY = ON,
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
/* procedure body goes here */
END

Таблица 1: Устойчивость в блоках ATOMIC

Параметр устойчивости блоков ATOMIC Отсутствие существующих транзакций Транзакция обрабатывается (полностью или отложенная устойчивая)
DELAYED_DURABILITY = OFF Блок ATOMIC инициирует новую полностью устойчивую транзакцию. Блок ATOMIC создает точку сохранения в существующей транзакции, а затем начинает новую транзакцию.
DELAYED_DURABILITY = ON Блок ATOMIC инициирует новую отложенную устойчивую транзакцию. Блок ATOMIC создает точку сохранения в существующей транзакции, а затем начинает новую транзакцию.

Элемент управления уровня COMMIT -Transact-SQL

Синтаксис фиксации расширен, что обеспечивает возможность принудительной реализации отложенной устойчивости транзакций. Если для DELAYED_DURABILITY задано DISABLED или FORCED на уровне базы данных (см. выше), то этот параметр фиксации не учитывается.

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

OFF
[по умолчанию] Фиксация транзакции является полностью устойчивой за исключением случаев, когда применяется параметр базы данных DELAYED_DURABLITY = FORCED, в этом случае фиксация является асинхронной и, следовательно, отложенной устойчивой. Дополнительные сведения см. в разделе Управление на уровне базы данных.

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

Краткое описание параметров и их взаимодействий

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

Параметр фиксации/параметр базы данных DELAYED_DURABILITY = DISABLED DELAYED_DURABILITY = ALLOWED DELAYED_DURABILITY = FORCED
DELAYED_DURABILITY = OFF Транзакции на уровне базы данных. Транзакция является полностью устойчивой. Транзакция является полностью устойчивой. Транзакция является отложенной устойчивой.
DELAYED_DURABILITY = ON Транзакции на уровне базы данных. Транзакция является полностью устойчивой. Транзакция является отложенной устойчивой. Транзакция является отложенной устойчивой.
DELAYED_DURABILITY = OFF Межбазовая или распределенная транзакция. Транзакция является полностью устойчивой. Транзакция является полностью устойчивой. Транзакция является полностью устойчивой.
DELAYED_DURABILITY = ON Межбазовая или распределенная транзакция. Транзакция является полностью устойчивой. Транзакция является полностью устойчивой. Транзакция является полностью устойчивой.

Принудительная реализация записи журнала транзакций

Существует два способа записи журнала транзакций на диск.

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

  • Выполните системную хранимую процедуру sp_flush_log. При выполнении этой процедуры выполняется принудительная запись данных журналов всех ранее зафиксированных полностью устойчивых транзакций на диск. Дополнительные сведения см. в разделе sys.sp_flush_log (Transact-SQL).

Отложенная устойчивость и другие функции SQL Server

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

  • Для баз данных с поддержкой репликации транзакций или отслеживания измененных данных (CDC) использование отложенной устойчивости не поддерживается.

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

Начиная с SQL Server 2022 CU 2 и SQL Server 2019 CU 20, вы можете увидеть:

  • Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability set Если вы пытаетесь включить репликацию транзакций или отслеживание измененных данных в базе данных с отложенной устойчивостью.

  • Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabled При попытке включить отложенную устойчивость в базе данных, настроенной с помощью репликации транзакций или отслеживания измененных данных.

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

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

Группы доступности Always On и зеркальное отображение
Отложенные устойчивые транзакции не гарантируют устойчивость на сервере-отправителе или сервере-получателе. Кроме того, они не гарантируют получения сведений о транзакциях на сервере-получателе. После фиксации элемент управления возвращается клиенту до получения подтверждения от любого синхронного дополнительного экземпляра. Репликация на вторичные реплики прекращается, если происходит запись на диск на сервере-источнике.

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

Azure Synapse Link для SQL
Отложенные устойчивые транзакции не поддерживаются в Azure Synapse Link для SQL.

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

Резервная копия журналов транзакций
В резервные копии включаются только устойчивые транзакции.

Когда я могу потерять данные?

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

Критические события

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

SQL Server завершения работы и перезапуска

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

Дальнейшие действия