Поделиться через

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

  • Статья

Область применения: 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.

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

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

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

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

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

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

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

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

Следующий код помещается в атомный блок.

DELAYED_DURABILITY = { OFF | ON }

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

ON
Транзакция обладает отложенной устойчивостью, если только параметр базы данных DELAYED_DURABILITY = 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. Устойчивость в атомарных блоках

Параметр устойчивости атомарных блоков Отсутствие существующих транзакций Транзакция в процессе (полностью или отсроченная устойчивая)
DELAYED_DURABILITY = OFF Блок ATOMIC инициирует новую полностью устойчивую транзакцию. Блок ATOMIC создает точку сохранения в существующей транзакции, а затем начинает новую транзакцию.
ОТЛОЖЕННАЯ УСТОЙЧИВОСТЬ = ВКЛ Блок ATOMIC инициирует новую отложенную надёжную транзакцию. Блок ATOMIC создает точку сохранения в существующей транзакции, а затем начинает новую транзакцию.

Управление уровнем COMMIT - Transact-SQL

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

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

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

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

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

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

Параметр COMMIT/параметр настроек базы данных ОТЛОЖЕННАЯ_УСТОЙЧИВОСТЬ = ОТКЛЮЧЕНА DELAYED_DURABILITY = РАЗРЕШЕНО 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), использование отложенной устойчивости не поддерживается.

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

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

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