控制交易持久性

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

SQL Server 交易認可可能具有完全持久性 (SQL Server 預設值) 或延遲持久性 (也稱為延遲認可)。

完全持久交易認可是同步認可，而且只有在交易的記錄檔記錄寫入磁碟之後，才會將認可回報為成功並將控制權傳回給用戶端。 延遲的持久交易認可是非同步認可，而且在交易的記錄檔記錄寫入磁碟之前，就會將認可回報為成功。 將交易記錄項目寫入磁碟是讓交易能夠持久的必要條件。 延遲的持久交易會在交易記錄項目排清至磁碟時變成持久。

本文將詳細說明延遲的持久交易。

完全與延遲的交易持久性

完全與延遲的交易持久性各有優缺點。 應用程式可以混合使用完全與延遲的持久交易。 您應該仔細考量業務需求，以及每種交易如何配合這些需求。

完全交易持久性

完全持久交易會先將交易記錄寫入磁碟，然後再將控制權傳回給用戶端。 只要符合下列情況，您就應該使用完全持久交易：

  • 您的系統無法容忍任何資料遺失。 如需何時會遺失部分資料的相關資訊，請參閱 我何時會遺失資料？ 一節。

  • 造成瓶頸的原因不是交易記錄寫入延遲。

延遲的交易持久性可減少記錄 I/O 造成的延遲，方法是將交易記錄檔記錄保留在記憶體中，並且批次寫入交易記錄，因此需要的 I/O 作業較少。 延遲的交易持久性可能會減少記錄 I/O 競爭，因而減少系統的等候時間。

完全交易持久性保證

延遲的交易持久性

延遲的交易持久性是使用磁碟的非同步記錄寫入來達成。 交易記錄檔記錄會保留在緩衝區中，然後在緩衝區填滿或發生緩衝區排清事件時寫入磁碟。 延遲的交易持久性會同時減少系統中的延遲和競爭，因為：

  • 交易認可處理不會等候記錄 IO 完成並將控制權傳回給用戶端。

  • 並行交易不太可能會爭用記錄 IO。不過，記錄緩衝區可能會以較大的區塊排清至磁碟，以便減少競爭並提高輸送量。

    注意

    如果並行程度很高，仍然可能會發生記錄 I/O 競爭，尤其是記錄緩衝區的填滿速度比排清速度快時。

何時使用延遲的交易持久性

可因使用延遲的交易持久性而獲益的情形如下：

您可以容忍部分資料遺失。
如果您可以容忍部分資料遺失 (只要擁有大部分資料即可，個別記錄並不重要)，延遲的持久性就值得考慮使用。 如果您無法容忍任何資料遺失，請勿使用延遲的交易持久性。

您在交易記錄寫入時遇到瓶頸。
如果您的效能問題是由於交易記錄寫入的延遲所造成，則使用延遲的交易持久性可能會讓您的應用程式從中獲益。

您的工作負載具有很高的競爭率。
如果您的系統具有高競爭層級的工作負載，就表示花很多時間在等候釋放鎖定。 延遲的交易持久性會減少認可時間並加快釋放鎖定的速度，因而提高輸送量。

延遲的交易持久性保證

  • 一旦交易認可成功之後，系統中的其他交易就可以看到該筆交易所進行的變更。

  • 只有在記憶體中的交易記錄排清至磁碟之後，才會保證交易持久性。 記憶體中的交易記錄會在下列情況中排清至磁碟：

    • 相同資料庫中的完全持久交易對資料庫進行變更並且成功認可。

    • 使用者成功執行系統預存程序 sp_flush_log 。

      如果完全持久交易或 sp_flush_log 成功認可，就表示所有先前認可的延遲持久交易保證都已經變成持久。

    • SQL Server 嘗試根據記錄產生和時間將記錄檔排清至磁碟，即使所有交易都延遲為持久。 如果 IO 裝置保持啟動，這通常會成功。 但是，除了持久交易和 sp_flush_log 之外，SQL Server 不會提供任何硬性持久性保證。

如何控制交易持久性

資料庫層級控制

身為 DBA 的您，可以控制使用者是否能使用下列陳述式，在資料庫上使用延遲的交易持久性。 您必須使用 ALTER DATABASE 來設定延遲的持久性設定。

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

已停用
[預設值] 使用這項設定時，在資料庫上認可的所有交易都是完全持久，不論認可層級設定為何 (DELAYED_DURABILITY=[ON | OFF])。 完全不需要進行預存程序變更和重新編譯。 這可讓您確保任何資料都不會因為延遲的持久性而面臨風險。

允許
使用這項設定時，每筆交易的持久性都是在交易層級上決定的 - DELAYED_DURABILITY = { OFF | ON }。 如需詳細資訊，請參閱 ATOMIC 區塊等級控制 - 原生編譯的預存程序和 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：ATOMIC 區塊的持久性

不可部分完成的區塊持久性選項 無現有的交易 交易處理中 (完全或延遲的持久)
DELAYED_DURABILITY = OFF 不可部分完成的區塊會啟動新的完全持久交易。 不可部分完成的區塊會在現有的交易中建立儲存點，然後開始新的交易。
DELAYED_DURABILITY = ON 不可部分完成的區塊會啟動新的延遲持久交易。 不可部分完成的區塊會在現有的交易中建立儲存點，然後開始新的交易。

COMMIT 層級控制 - Transact-SQL

COMMIT 語法已擴充，因此您可以強制延遲的交易持久性。 如果資料庫層級的 DELAYED_DURABILITY 是 DISABLED 或 FORCED (請參閱上述說明)，就會忽略這個 COMMIT 選項。

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

OFF
[預設值] 交易 COMMIT 是完全持久，除非資料庫選項 DELAYED_DURABLITY = FORCED 作用中，此時 COMMIT 就是非同步，因而成為延遲的持久。 如需相關資訊，請參閱資料庫層級控制。

ON
交易 COMMIT 是延遲的持久，除非資料庫選項 DELAYED_DURABLITY = DISABLED 作用中，此時 COMMIT 就是同步，因而成為完全持久。 如需相關資訊，請參閱資料庫層級控制。

選項及其互動的摘要

下表將摘要說明資料庫層級延遲的持久性設定與認可層級設定之間的互動。 資料庫層級設定一律優先於認可層級設定。

COMMIT 設定/資料庫設定 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) 的資料庫，不支援使用延遲持久性。

  • 支援具有延遲持久性的變更追蹤。 所有具有變更追蹤的交易都是完全持久。 如果某筆交易會對啟用變更追蹤的資料表進行任何寫入作業，就是具有變更追蹤屬性。

從 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
如果某筆交易是跨資料庫或分散式交易，不論任何資料庫或交易認可設定為何，它都是完全持久。

AlwaysOn 可用性群組和鏡像
延遲的持久交易無法在主要或任何次要資料庫上保證任何持久性。 此外，它們無法保證任何關於次要資料庫上交易的知識。 認可之後，從任何同步的次要收到任何確認之前，就會將控制權傳回給用戶端。 因為主要伺服器上會發生排清到磁碟機，所以不會繼續複寫到次要複本。

容錯移轉叢集
某些延遲的持久交易寫入可能會遺失。

適用於 SQL 的 Azure Synapse Link
SQL Azure Synapse Link 不支援延遲的持久交易。

記錄傳送
只有已經變成持久的交易才會包含在傳送的記錄中。

交易記錄備份
只有已經變成持久的交易才會包含在備份中。

我何時會遺失資料？

如果您在任何資料表上實作延遲持久性，您應該了解特定環境會導致資料遺失。 如果您無法容忍任何資料遺失，就不應該在資料表中使用延遲持久性。

重大事件

發生重大事件 (例如伺服器當機) 時，您將遺失所有尚未儲存到磁碟的已認可交易資料。 每當針對資料表中的任何資料表 (持久性記憶體最佳化或以磁碟為基礎的資料表) 執行完全持久交易，或呼叫 sp_flush_log 時，即會將延遲的持久交易儲存到磁碟。 如果您正在使用延遲的持久交易，您可以在資料庫中建立小型資料表，以便定期更新或定期呼叫 sp_flush_log 來儲存所有尚未認可完畢的交易。 交易記錄也會在它已滿時排清，但這很難預期且無法控制。

SQL Server 關機及重新啟動

針對延遲的持久性，SQL Server 的意外關機與預期的關機/重新啟動之間並無差異。 就像重大事件一樣，您應該針對資料遺失進行規劃。 在規劃好的關機/重新啟動中，部分尚未寫入磁碟的交易可能會在關機前先儲存到磁碟，但您不應該規劃相關事項。 對於類似關機/重新啟動的規劃 (不論是規劃或未規劃的) 都會像重大事件一樣遺失資料。

下一步