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

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  Примечание.

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

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

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

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

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

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

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

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

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

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

  • Пользователь успешно выполняет системную хранимую процедуру 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 }. Дополнительные сведения см . в разделе "Элемент управления уровня атомарного блока" — скомпилированные хранимые процедуры и элемент управления уровня COMMIT.

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

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

Ниже представлен код блока 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. Устойчивость в атомарных блоках

Элемент управления уровня 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, в этом случае фиксация является синхронной и, следовательно, полностью устойчивой. Дополнительные сведения см. в разделе "Управление уровнем базы данных".

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

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

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

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

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

• Выполните системную хранимую процедуру 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. Как и в случае с критическими событиями, вы должны быть готовы к потере данных. В запланированном завершении или перезапуске некоторые транзакции, которые не были записаны на диск, могут быть сохранены на диск перед завершением работы, но не следует планировать его. При запланированном или незапланированном выключении/перезагрузке данные теряются так же, как и при критических событиях.

Следующие шаги

• Transactions with Memory-Optimized Tables