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

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Фиксации транзакцийSQL ServerSQL Server могут быть либо полностью устойчивыми, служба SQL ServerSQL Server по умолчанию, либо отложенными устойчивыми (также известные как отложенная запись).SQL ServerSQL Server transaction commits can be either fully durable, the SQL ServerSQL Server default, or delayed durable (also known as lazy commit).

Полностью устойчивые фиксации транзакций являются синхронными и сообщают об успешной фиксации и возвращают управление клиенту только после записи журналов транзакций на диск.Fully durable transaction commits are synchronous and report a commit as successful and return control to the client only after the log records for the transaction are written to disk. Устойчивые фиксации транзакций являются асинхронными и сообщают об успешной фиксации до записи журналов транзакций на диск.Delayed durable transaction commits are asynchronous and report a commit as successful before the log records for the transaction are written to disk. Запись журнала транзакций на диск необходима, чтобы транзакция была устойчивой.Writing the transaction log entries to disk is required for a transaction to be durable. Отложенные транзакции становятся устойчивыми после записи журнала транзакций на диск.Delayed durable transactions become durable when the transaction log entries are flushed to disk.

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

Сравнение полностью устойчивых и отложенных устойчивых транзакцийFull vs. Delayed Transaction Durability

И полностью устойчивые, и отложенные устойчивые транзакции имеют свои преимущества и недостатки.Both full and delayed transaction durability have their advantages and disadvantages. Приложение может включать в себя и полностью устойчивые, и отложенные устойчивые транзакции.An application can have a mix of fully and delayed durable transactions. Необходимо тщательно учитывать бизнес-требования и соответствие транзакций этим требованиям.You should carefully consider your business needs and how each fits into those needs.

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

Полностью устойчивые транзакции записывают журнал транзакций на диск до возвращения управления клиентуFully durable transactions write the transaction log to disk before returning control to the client. Полностью устойчивые транзакции необходимо использовать в следующих случаях.You should use fully durable transactions whenever:

  • Система не может работать при потере данных.Your system cannot tolerate any data loss.
    См. раздел Когда я могу потерять данные? , чтобы узнать, когда данные могут быть потеряны.See the section When can I lose data? for information on when you can lose some of your data.

  • Причиной возникновения проблемы не является задержка записи журналов.The bottleneck is not due to transaction log write latency.

Отложенная устойчивость транзакций уменьшает задержку операций ввода-вывода журналов за счет хранения журналов транзакций в памяти и записи данных в журналы транзакций в пакетном режиме, что требует меньшего числа операций ввода-вывода.Delayed transaction durability reduces the latency due to log I/O by keeping the transaction log records in memory and writing to the transaction log in batches, thus requiring fewer I/O operations. Отложенная устойчивость транзакций потенциально уменьшает вероятность конфликтов ввода-вывода журналов, тем самым уменьшая время ожидания в системе.Delayed transaction durability potentially reduces log I/O contention, thus reducing waits in the system.

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

  • После успешного выполнения фиксации транзакции изменений, внесенные транзакцией, становятся видимыми для других транзакций в системе.Once transaction commit succeeds, the changes made by the transaction are visible to the other transactions in the system. Дополнительные сведения об уровнях изоляции транзакции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL) или Транзакции с таблицами с оптимизированной памятью.For more information about transaction isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL) or Transactions with Memory-Optimized Tables.

  • Устойчивость гарантируется при выполнении фиксации.Durability is guaranteed on commit. Соответствующие записи журнала сохраняются на диск до выполнения фиксации транзакции и возврата управления клиенту.Corresponding log records are persisted to disk before the transaction commit succeeds and returns control to the client.

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

Устойчивость отложенных транзакций реализуется при асинхронной записи журналов на диск.Delayed transaction durability is accomplished using asynchronous log writes to disk. Записи журнала транзакций содержатся в буфере и записываются на диск, когда буфер заполняется или при сбросе буфера.Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. Устойчивость отложенных транзакций уменьшает задержки и число конфликтов в системе по следующим причинам.Delayed transaction durability reduces both latency and contention within the system because:

  • При обработке фиксации транзакций система не ожидает создания журнала ввода-вывода для завершения операции и возврата управления клиенту.The transaction commit processing does not wait for log IO to finish and return control to the client.

  • При параллельных транзакциях реже возникают конфликты при ведении журналов ввода-вывода. Вместо этого может быть выполнена очистка буфера на диск большими частями, что позволяет уменьшить число конфликтов и увеличить пропускную способность.Concurrent transactions are less likely to contend for log IO; instead, the log buffer can be flushed to disk in larger chunks, reducing contention, and increasing throughput.

    Примечание

    При высокой степени параллелизма могут возникать конфликты при ведении журналов ввода-вывода, особенно в случаях, когда буфер журналов заполняется быстрее, чем выполняется его очистка.You may still have log I/O contention if there is a high degree of concurrency, particularly if you fill up the log buffer faster than you flush it.

Использование отложенных устойчивых транзакцийWhen to use delayed transaction durability

Некоторые из сценариев, в которых можно использовать преимущества, обеспечиваемые отложенными устойчивыми транзакциямиSome of the cases in which you could benefit from using delayed transaction durability are:

Допустимы потери данных. You can tolerate some data loss.
Если допустим определенный уровень потерь данных, например, если отдельные записи не имеют критического значения при условии сохранения основной части данных, рекомендуется использовать отложенные устойчивые транзакции.If you can tolerate some data loss, for example, where individual records are not critical as long as you have most of the data, then delayed durability may be worth considering. Если потеря данных недопустима, не следует использовать отложенные устойчивые транзакции.If you cannot tolerate any data loss, do not use delayed transaction durability.

При записи журналов транзакций обнаружено узкое место. You are experiencing a bottleneck on transaction log writes.
Если проблемы с производительностью связаны с задержкой записи журналов транзакций, использование отложенных устойчивых транзакций будет полезно при работе с приложением.If your performance issues are due to latency in transaction log writes, your application will likely benefit from using delayed transaction durability.

Рабочая нагрузка отличается высокой частотой конфликтов. Your workloads have a high contention rate.
Если в системе используется рабочая нагрузка с высокой частотой конфликтов, то для разблокировки требуется длительное время.If your system has workloads with a high contention level much time is lost waiting for locks to be released. Отложенные устойчивые транзакции позволяют уменьшить время фиксации, благодаря чему разблокировка выполняется быстрее, что обеспечивает более высокий уровень пропускной способности.Delayed transaction durability reduces commit time and thus releases locks faster which results in higher throughput.

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

  • После успешного выполнения фиксации транзакции изменений, внесенные транзакцией, становятся видимыми для других транзакций в системе.Once transaction commit succeeds, the changes made by the transaction are visible to the other transactions in the system.

  • Устойчивость транзакции гарантируется только после записи журналов транзакций в памяти на диск.Transaction durability is guaranteed only following a flush of the in-memory transaction log to disk. Журнал транзакций в памяти записывается на диск в следующих случаях.The in-memory transaction log is flushed to disk when:

    • Полностью устойчивая транзакция в той же базе данных вносит изменение в базу данных и фиксация завершается успешно.A fully durable transaction in the same database makes a change in the database and successfully commits.

    • Пользователь успешно выполняет системную хранимую процедуру sp_flush_log .The user executes the system stored procedure sp_flush_log successfully.

      При успешной фиксации полностью устойчивой транзакции или sp_flush_log все предыдущие отложенные устойчивые транзакции гарантированно становятся устойчивыми.If a fully durable transaction or sp_flush_log successfully commits, all previously committed delayed durability transactions are guaranteed to have been made durable.

    • SQL ServerSQL Server предпринимает попытку записать журнал на диск, основываясь как на ведении журналов, так и на времени, даже если все транзакции являются отложенными устойчивыми.does attempt to flush the log to disk both based on log generation and on timing, even if all the transactions are delayed durable. Это эта попытка завершается успешно, если устройство ввода-вывода справляется с обработкой.This usually succeeds if the IO device is keeping up. Однако служба SQL ServerSQL Server не предоставляет каких-либо надежных гарантий устойчивости, кроме устойчивых транзакций и sp_flush_log.However, SQL ServerSQL Server does not provide any hard durability guarantees other than durable transactions and sp_flush_log.

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

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

Администратор базы данных управляет тем, могут ли пользователи использовать отложенные устойчивые транзакции в базе данных, с помощью следующей инструкции.You, the DBA, can control whether users can use delayed transaction durability on a database with the following statement. Необходимо использовать для настройки параметра отложенной устойчивости инструкцию ALTER DATABASE.You must set the delayed durability setting with ALTER DATABASE.

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

ВЫКЛЮЧЕНО DISABLED
[по умолчанию] При использовании этой настройки все фиксируемые в базе данных транзакции являются полностью устойчивыми независимо от настроек уровня фиксации (DELAYED_DURABILITY= [ON | OFF]).[default] With this setting, all transactions that commit on the database are fully durable, regardless of the commit level setting (DELAYED_DURABILITY=[ON | OFF]). Изменение и повторная компиляция хранимых процедур не требуются.There is no need for stored procedure change and recompilation. Это позволяет гарантировать, что данные не будут подвергаться рискам из-за отложенной устойчивости.This allows you to ensure that no data is ever put at risk by delayed durability.

РАЗРЕШЕНО ALLOWED
При использовании этой настройки устойчивость каждой транзакции определяется на уровне транзакций — DELAYED_DURABILITY = { OFF | ON }.With this setting, each transaction's durability is determined at the transaction level - DELAYED_DURABILITY = { OFF | ON }. Дополнительные сведения см. в разделах Управление на уровне блока Atomic — скомпилированные в собственном коде хранимые процедуры и Управление на уровне ФИКСАЦИИ —Transact-SQL.See Atomic block level control - Natively Compiled Stored Procedures and COMMIT level control -Transact-SQL for more information.

ПРИНУДИТЕЛЬНО FORCED
Если выбран этот параметр, все транзакции, которые фиксируются в базе данных, являются отложенными устойчивыми.With this setting, every transaction that commits on the database is delayed durable. Независимо от того, указана ли транзакция как полностью устойчивая (DELAYED_DURABILITY = OFF) или данные не указаны, транзакция является отложенной устойчивой.Whether the transaction specifies fully durable (DELAYED_DURABILITY = OFF) or makes no specification, the transaction is delayed durable. Этот параметр полезен, если отложенная устойчивая транзакция используется для баз данных и не следует изменять код приложения.This setting is useful when delayed transaction durability is useful for a database and you do not want to change any application code.

Управление на уровне блока Atomic — скомпилированные в собственном коде хранимые процедурыAtomic block level control - Natively Compiled Stored Procedures

Ниже представлен код блока ATOMIC.The following code goes inside the atomic block.

DELAYED_DURABILITY = { OFF | ON }    

OFF OFF
[по умолчанию] Транзакция является полностью устойчивой, пока действует параметр базы данных DELAYED_DURABLITY = FORCED, в этом случае фиксация является асинхронной и таким образом, устойчивой.[default] The transaction is fully durable, unless the database option DELAYED_DURABLITY = FORCED is in effect, in which case the commit is asynchronous and thus delayed durable. Подробнее см. в разделе Database level control .See Database level control for more information.

ON ON
Транзакция является устойчиво отложенной, пока действует параметр базы данных DELAYED_DURABLITY = DISABLED, в этом случае фиксация является синхронной и таким образом, полностью устойчивой.The transaction is delayed durable, unless the database option DELAYED_DURABLITY = DISABLED is in effect, in which case the commit is synchronous and thus fully durable. Подробнее см. в разделе Database level control .See Database level control for more information.

Пример кодаExample Code:

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: Устойчивость в блоках ATOMICTable 1: Durability in Atomic Blocks

Параметр устойчивости блоков ATOMICAtomic block durability option Отсутствие существующих транзакцийNo existing transaction Транзакция обрабатывается (полностью или отложенная устойчивая)Transaction in process (fully or delayed durable)
DELAYED_DURABILITY = OFFDELAYED_DURABILITY = OFF Блок ATOMIC инициирует новую полностью устойчивую транзакцию.Atomic block starts a new fully durable transaction. Блок ATOMIC создает точку сохранения в существующей транзакции, а затем начинает новую транзакцию.Atomic block creates a save point in the existing transaction, then begins the new transaction.
DELAYED_DURABILITY = ONDELAYED_DURABILITY = ON Блок ATOMIC инициирует новую отложенную устойчивую транзакцию.Atomic block starts a new delayed durable transaction. Блок ATOMIC создает точку сохранения в существующей транзакции, а затем начинает новую транзакцию.Atomic block creates a save point in the existing transaction, then begins the new transaction.

Управление на уровне ФИКСАЦИИ — Transact-SQLTransact-SQLCOMMIT level control -Transact-SQLTransact-SQL

Синтаксис фиксации расширен, что обеспечивает возможность принудительной реализации отложенной устойчивости транзакций.The COMMIT syntax is extended so you can force delayed transaction durability. Если для DELAYED_DURABILITY задано DISABLED или FORCED на уровне базы данных (см. выше), то этот параметр фиксации не учитывается.If DELAYED_DURABILITY is DISABLED or FORCED at the database level (see above) this COMMIT option is ignored.

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

OFF OFF
[по умолчанию] Фиксация транзакции является полностью устойчивой за исключением случаев, когда применяется параметр базы данных DELAYED_DURABLITY = FORCED, в этом случае фиксация является асинхронной и, следовательно, отложенной устойчивой.[default] The transaction COMMIT is fully durable, unless the database option DELAYED_DURABLITY = FORCED is in effect, in which case the COMMIT is asynchronous and thus delayed durable. Подробнее см. в разделе Database level control .See Database level control for more information.

ON ON
Фиксация транзакции является отложенной устойчивой за исключением случаев, когда применяется параметр базы данных DELAYED_DURABLITY = DISABLED, в этом случае фиксация является синхронной и, следовательно, полностью устойчивой.The transaction COMMIT is delayed durable, unless the database option DELAYED_DURABLITY = DISABLED is in effect, in which case the COMMIT is synchronous and thus fully durable. Подробнее см. в разделе Database level control .See Database level control for more information.

Краткое описание параметров и их взаимодействийSummary of options and their interactions

В следующей таблице перечислены взаимодействия параметров отложенной устойчивости на уровне базы данных и параметров на уровне фиксации.This table summarizes the interactions between database level delayed durability settings and commit level settings. Параметры уровня базы данных всегда имеют более высокий приоритет, чем параметры уровня фиксации.Database level settings always take precedence over commit level settings.

Параметр фиксации/параметр базы данныхCOMMIT setting/Database setting DELAYED_DURABILITY = DISABLEDDELAYED_DURABILITY = DISABLED DELAYED_DURABILITY = ALLOWEDDELAYED_DURABILITY = ALLOWED DELAYED_DURABILITY = FORCEDDELAYED_DURABILITY = FORCED
DELAYED_DURABILITY = OFF Транзакции на уровне базы данных.DELAYED_DURABILITY = OFF Database level transactions. Транзакция является полностью устойчивой.Transaction is fully durable. Транзакция является полностью устойчивой.Transaction is fully durable. Транзакция является отложенной устойчивой.Transaction is delayed durable.
DELAYED_DURABILITY = ON Транзакции на уровне базы данных.DELAYED_DURABILITY = ON Database level transactions. Транзакция является полностью устойчивой.Transaction is fully durable. Транзакция является отложенной устойчивой.Transaction is delayed durable. Транзакция является отложенной устойчивой.Transaction is delayed durable.
DELAYED_DURABILITY = OFF Межбазовая или распределенная транзакция.DELAYED_DURABILITY = OFF Cross database or distributed transaction. Транзакция является полностью устойчивой.Transaction is fully durable. Транзакция является полностью устойчивой.Transaction is fully durable. Транзакция является полностью устойчивой.Transaction is fully durable.
DELAYED_DURABILITY = ON Межбазовая или распределенная транзакция.DELAYED_DURABILITY = ON Cross database or distributed transaction. Транзакция является полностью устойчивой.Transaction is fully durable. Транзакция является полностью устойчивой.Transaction is fully durable. Транзакция является полностью устойчивой.Transaction is fully durable.

Принудительная реализация записи журнала транзакцийHow to force a transaction log flush

Существует два способа записи журнала транзакций на диск.There are two means to force flush the transaction log to disk.

  • Выполните любую полностью устойчивую транзакцию, которая изменяет одну и ту же базу данных.Execute any fully durable transaction that alters the same database. При этом выполняется принудительная запись данных журналов всех ранее зафиксированных отложенных устойчивых транзакций на диск.This forces a flush of the log records of all preceding committed delayed durability transactions to disk.

  • Выполните системную хранимую процедуру sp_flush_log.Execute the system stored procedure sp_flush_log. При выполнении этой процедуры выполняется принудительная запись данных журналов всех ранее зафиксированных полностью устойчивых транзакций на диск.This procedure forces a flush of the log records of all preceding committed delayed durable transactions to disk. Дополнительные сведения см. в разделе sys.sp_flush_log (Transact-SQL).For more information see sys.sp_flush_log (Transact-SQL).

Отложенная устойчивость и другие функции службы SQL ServerSQL ServerDelayed durability and other SQL ServerSQL Server features

Отслеживание изменений и отслеживание измененных данных Change tracking and change data capture
Все транзакции с отслеживанием изменений являются полностью устойчивыми.All transactions with change tracking are fully durable. Транзакция имеет свойство отслеживания изменений, если она выполняет какую-либо операцию записи в таблицы, для которой включено отслеживание изменений.A transaction has the change tracking property if it does any write operations to tables that are enabled for change tracking. Использование отложенной устойчивости не поддерживается для баз данных, которые используют систему отслеживания измененных данных (CDC).The use of delayed durability is not supported for databases which use change data capture (CDC).

Восстановление после сбоя Crash recovery
Гарантируется согласованность, но некоторые изменения отложенных фиксированных транзакций могут быть потеряны.Consistency is guaranteed, but some changes from delayed durable transactions that have committed may be lost.

Межбазовые и DTC Cross-database and DTC
Если транзакция является межбазовой или распределенной, она является полностью устойчивой независимо от настроек фиксации транзакций.If a transaction is cross-database or distributed, it is fully durable, regardless of any database or transaction commit setting.

Группы доступности Always On и зеркальное отображение Always On Availability Groups and Mirroring
Отложенные устойчивые транзакции не гарантируют устойчивость на сервере-отправителе или сервере-получателе.Delayed durable transactions do not guarantee any durability on either the primary or any of the secondaries. Кроме того, они не гарантируют получения сведений о транзакциях на сервере-получателе.In addition, they do not guarantee any knowledge about the transaction at the secondary. После фиксации, управление возвращается клиенту до того, как любое подтверждение получено от любого синхронного сервера-получателя.After commit, control is returned to the client before any acknowledgement is received from any synchronous secondary. Репликация на вторичные реплики прекращается, если происходит запись на диск на сервере-источнике.Replication to secondary replicas does continue to happen as flush to disk on the primary happens.

Отказоустойчивая кластеризация Failover clustering
Некоторые записи отложенных устойчивых транзакций могут быть утеряны.Some delayed durable transaction writes might be lost.

Репликация транзакций Transaction Replication
Отложенно-устойчивые транзакции не поддерживается при репликации транзакций.Delayed durable transactions is not supported with Transactional Replication.

Доставка журналов Log shipping
В доставляемых журналах регистрируются только устойчивые транзакции.Only transactions that have been made durable are included in the log that is shipped.

Резервная копия журналов Log Backup
В резервные копии включаются только устойчивые транзакции.Only transactions that have been made durable are included in the backup.

Когда я могу потерять данные?When can I lose data?

Если вы применяете отложенную устойчивость на любой вашей таблице, вы должны понимать, что определенные обстоятельства могут привести к потере данных.If you implement delayed durability on any of your tables, you should understand that certain circumstances can lead to data loss. Если вы не допускаете любую потерю данных, вам не следует использовать отложенную устойчивость на ваших таблицах.If you cannot tolerate any data loss, you should not use delayed durability on your tables.

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

В случае критического события, например, выход сервера из строя, вы потеряете данные всех фиксированных транзакций ,которые не были сохранены на диск.In the case of a catastrophic event, like a server crash, you will lose the data for all committed transactions that have not been saved to disk. Отложенные устойчивые транзакции сохраняются на диск всякий раз, когда полностью устойчивая транзакция выполняется для любой таблицы (устойчивой и оптимизированной для памяти или находящейся на диске) в базе данных, или вызывается sp_flush_log .Delayed durable transactions are saved to disk whenever a fully durable transaction is executed against any table (durable memory-optimized or disk-based) in the database, or sp_flush_log is called. Если вы используете устойчивые отложенные транзакции, вы, возможно, хотите создать маленькую таблицу в базе данных, которую вы можете периодически обновлять, или периодически вызывать sp_flush_log для сохранения всех невыполненных фиксированных транзакций.If you are using delayed durable transactions, you may want to create a small table in the database that you can periodically update or periodically call sp_flush_log to save all outstanding committed transactions. Журнал транзакции также сбрасывается всякий раз, когда он заполняется, но это сложно предсказать и невозможно контролировать.The transaction log also flushes whenever it becomes full, but that is hard to predict and impossible to control.

Выключение и перезагрузкаSQL ServerSQL ServerSQL ServerSQL Server shutdown and restart

Для отложенной устойчивости нет разницы между неожиданным выключением или неожиданным выключением/перезагрузкой службы SQL ServerSQL Server.For delayed durability, there is no difference between an unexpected shutdown and an expected shutdown/restart of SQL ServerSQL Server. Как и в случае с критическими событиями, вы должны быть готовы к потере данных.Like catastrophic events, you should plan for data loss. При запланированном выключении/перезагрузке некоторые транзакции, которые не были записаны на диск, могут сначала быть сохранены на диск, но вам не стоит рассчитывать на это.In a planned shutdown/restart some transactions that have not been written to disk may first be saved to disk, but you should not plan on it. При запланированном или незапланированном выключении/перезагрузке данные теряются так же, как и при критических событиях.Plan as though a shutdown/restart, whether planned or unplanned, loses the data the same as a catastrophic event.

См. также:See Also

Transactions with Memory-Optimized TablesTransactions with Memory-Optimized Tables