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

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

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

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

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

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

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

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

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

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

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

Гарантии полной надежности транзакций

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

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

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

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

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

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

  Замечание

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

Когда следует использовать устойчивость отложенной транзакции

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

DELAYED_DURABILITY = { OFF | ON }  

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

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

Пример кода

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

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

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

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

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

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

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

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

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

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

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

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

См. также

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