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

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

  • Статья

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

DELAYED_DURABILITY = { OFF | ON }

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

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

Пример кода

CREATE PROCEDURE <procedureName> ...  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  
    DELAYED_DURABILITY = ON,  
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
    LANGUAGE = N'English'  
    ...  
)  
END

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

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

Элемент управления уровня COMMIT — (T-SQL)

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

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

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

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

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

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

Параметр фиксации/параметр базы данных 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).

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

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

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

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

Репликация транзакций
Отложенно-устойчивые транзакции не поддерживается при репликации транзакций.

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

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

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

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

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

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

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

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

См. также:

Уровни изоляции транзакций
Рекомендации для уровней изоляции транзакций с таблицами, оптимизированными для памяти