控制交易持久性

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體

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

完全持久化的交易提交是同步的，只有在與交易相關的記錄已寫入磁碟後，才會回報提交成功，並將控制權傳回用戶端。 延遲的持久交易認可是非同步的，在交易記錄寫入磁碟之前，就會將認可回報為成功。 將交易記錄項目寫入磁碟是讓交易能夠持久的必要條件。 延遲的持久性交易會在交易日誌項目被寫入磁碟時變得持久。

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

完全與延遲的交易持久性

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

完全交易持久性

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

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

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

延後交易的持久性透過將交易日誌記錄保持在記憶體中並批次寫入交易日誌，從而減少由日誌 I/O 引起的延遲，並減少所需的 I/O 作業次數。 延遲的交易持久性可能會減少記錄 I/O 競爭，因而減少系統的等候時間。

完全交易持久性保證

• 當交易提交成功時，系統中的其他交易就可以看到該筆交易所進行的變更。 如需交易隔離等級的詳細資訊，請參閱 SET TRANSACTION ISOLATION LEVEL (Transact-SQL) 或交易與經記憶體最佳化的資料表。

• 持久性是在認可時獲得保證。 在交易認可成功並將控制權傳回給用戶端之前，對應的記錄檔記錄都會保存到磁碟。

延遲的交易耐久性

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

• 交易提交處理不會等候日誌輸入/輸出完成後再將控制權傳回給客戶端。

• 並行交易不太可能會爭用記錄 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
[default] 交易是完全持久的，除非資料庫選項 DELAYED_DURABILITY = FORCED 生效。在這種情況下，提交是以異步方式進行的，因此是延遲持久的。 如需相關資訊，請參閱資料庫層級控制。

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（延遲耐久性 = 關閉）	原子區塊啟動新的完全持久化交易。	原子區塊在現有的交易中建立儲存點，然後開始新的交易。
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
[default]除非資料庫選項DELAYED_DURABILITY = FORCED 有效，否則交易 COMMIT 是完全持久的，在此情況下，COMMIT 是異步的，因此會延遲持久。 如需相關資訊，請參閱資料庫層級控制。

ON
除非資料庫選項DELAYED_DURABILITY = DISABLED 有效，否則交易 COMMIT 會延遲持久，在此情況下 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
Azure Synapse Link for SQL 不支援延遲的持久交易。

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

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

我何時會遺失資料？

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

重大事件

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

SQL Server 關機及重新啟動

針對延遲持久性，SQL Server 的意外關機與預期的關機/重新啟動之間沒有任何不同。 就像重大事件一樣，您應該針對資料遺失進行規劃。 在規劃好的關機/重新啟動中，部分尚未寫入磁碟的交易可能會在關機前先儲存到磁碟，但您不應該規劃相關事項。 務必規劃好，認為不論是計劃內或計劃外的關機/重新啟動都會像災難性事件一樣遺失資料。

下一步

• 使用記憶體優化資料表的交易