記憶體最佳化資料表的交易Transactions with Memory-Optimized Tables

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本文說明記憶體最佳化資料表和原生編譯預存程序特定的所有交易層面。This article describes all the aspects of transactions that are specific to memory-optimized tables and natively compiled stored procedures.

SQL Server 中的交易隔離等級會分別套用到記憶體最佳化資料表與硬碟資料表,而基礎機制並不相同。The transaction isolation levels in SQL Server apply differently to memory-optimized tables versus disk-based tables, and the underlying mechanisms are different. 了解這些差異,有助於程式設計人員設計高輸送量系統。An understanding of the differences helps the programmer design a high throughput system. 所有案例的共同目標都是要達成交易完整性。The goal of transaction integrity is shared in all cases.

如需記憶體最佳化資料表上的交易特定錯誤狀況,請跳至 衝突偵測和重試邏輯一節。For error conditions specific to transactions on memory-optimized tables, jump to the section Conflict Detection and Retry Logic.

如需一般資訊,請參閱 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)For general information, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

封閉式與開放式Pessimistic versus Optimistic

因為要達成交易完整性的方法分為封閉式與開放式,所以在功能上所有差異。The functional differences are due to pessimistic versus optimistic approaches to transaction integrity. 記憶體最佳化資料表會使用開放式方法:Memory-optimized tables use the optimistic approach:

  • 封閉式方法使用鎖定,在潛在衝突發生前加以封鎖。Pessimistic approach uses locks to block potential conflicts before they occur. 會在執行陳述式時採用鎖定,並在認可交易時放開解除鎖定。Lock are taken when the statement is executed, and released when the transaction is committed.

  • 衝突一發生,開放式方法會立即偵測到,並於認可時執行驗證檢查。Optimistic approach detects conflicts as they occur, and performs validation checks at commit time.

    • 記憶體最佳化資料表不得發生錯誤 1205,也就是死結。Error 1205, a deadlock, cannot occur for a memory-optimized table.

開放式方法的額外負荷較少,而且通常更有效率,部分原因是大多數應用程式中不常發生交易衝突。The optimistic approach is less overhead and is usually more efficient, partly because transaction conflicts are uncommon in most applications. 封閉式和開放式方法的主要功能差異,是在發生衝突時,封閉式方法會讓您等待,而開放式方法則是其中一筆交易失敗,且必須由用戶端重試。The main functional difference between the pessimistic and optimistic approaches is that if a conflict occurs, in the pessimistic approach you wait, while in the optimistic approach one of the transactions fails and needs to be retried by the client. 強制使用 REPEATABLE READ 隔離等級時,功能差異較大,在 SERIALIZABLE 等級則差異最大。The functional differences are bigger when the REPEATABLE READ isolation level is in force, and are biggest for the SERIALIZABLE level.

交易初始模式Transaction Initiation Modes

SQL Server 有下列交易初始模式:SQL Server has the following modes for transaction initiation:

  • 自動認可 :簡單查詢或 DML 陳述式一開始會隱含開啟交易,而陳述式的結尾會隱含認可交易。Autocommit - The start of a simple query or DML statement implicitly opens a transaction, and the end of the statement implicitly commits the transaction. 自動認可是預設值。Autocommit is the default.

    • 在自動認可模式中,您通常不需要使用 FROM 子句撰寫記憶體最佳化資料表交易隔離等級的資料表提示程式碼。In autocommit mode, usually you are not required to code a table hint about the transaction isolation level on the memory-optimized table in the FROM clause.
  • 明確 - 您的 Transact-SQL 包含程式碼 BEGIN TRANSACTION,以及最終的 COMMIT TRANSACTION。Explicit - Your Transact-SQL contains the code BEGIN TRANSACTION, along with an eventual COMMIT TRANSACTION. 相同交易中可以包含二或多個陳述式。Two or more statements can be corralled into the same transaction.

    • 在明確模式中,您必須使用資料庫選項 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT,或在 FROM 子句針對記憶體最佳化資料表編寫有關交易隔離等級的資料表提示程式碼。In explicit mode, you must either use the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT or code a table hint about the transaction isolation level on the memory-optimized table in the FROM clause.
  • 隱含 - 強制使用 SET IMPLICIT_TRANSACTION ON 時。Implicit - When SET IMPLICIT_TRANSACTION ON is in force. IMPLICIT_BEGIN_TRANSACTION 可能會是更適合的名稱,因為此選項的作用就只是在 0 = @@trancount 時,在每個 UPDATE 陳述式之前隱含執行明確 BEGIN TRANSACTION 的對等項目。Perhaps a better name would have been IMPLICIT_BEGIN_TRANSACTION, because all this option does is implicitly perform the equivalent of an explicit BEGIN TRANSACTION before each UPDATE statement if 0 = @@trancount. 因此,您的 T-SQL 程式碼會決定最終要不要發出明確 COMMIT TRANSACTION。Therefore it is up to your T-SQL code to eventually issue an explicit COMMIT TRANSACTION.

  • ATOMIC 區塊 - ATOMIC 區塊中的所有陳述式一律執行為單一交易的一部分。ATOMIC BLOCK - All statements in ATOMIC blocks always run as part of a single transaction. 成功時將 ATOMIC 區塊的所有動作視為一個整體認可,或失敗後復原所有動作。Either the actions of the atomic block as a whole are committed on success, or the actions are all rolled back when a failure occurs. 每個原生編譯的預存程序都需要 ATOMIC 區塊。Each natively compiled stored procedure requires an ATOMIC block.

明確模式的程式碼範例Code Example with Explicit Mode

以下解譯的 Transact-SQL 指令碼使用:The following interpreted Transact-SQL script uses:

  • 明確交易。An explicit transaction.
  • 記憶體最佳化資料表,名為 dbo.Order_mo。A memory-optimized table, named dbo.Order_mo.
  • READ COMMITTED 交易隔離等級內容。The READ COMMITTED transaction isolation level context.

因此,記憶體最佳化資料表上必須要有資料表提示。Therefore it is necessary to have a table hint on the memory-optimized table. 提示必須提供給 SNAPSHOT 或隔離程度更高的等級。The hint must be for SNAPSHOT or an even more isolating level. 在程式碼範例的案例中,提示為 WITH (SNAPSHOT)。In the case of the code example, the hint is WITH (SNAPSHOT). 如果移除此提示,指令碼就會發生錯誤 41368,亦即無法執行自動重試。If this hint is removed, the script would suffer an error 41368, for which an automated retry would be inappropriate:

錯誤 41368Error 41368

只有自動認可交易才支援使用 READ COMMITTED 隔離等級來存取記憶體最佳化的資料表。Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. 明確或隱含交易則不支援。It is not supported for explicit or implicit transactions. 請為使用 WITH (SNAPSHOT) 等資料表提示的記憶體最佳化資料表,提供支援的隔離等級。Provide a supported isolation level for the memory-optimized table using a table hint, such as WITH (SNAPSHOT).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  

BEGIN TRANSACTION;  -- Explicit transaction.  

-- Order_mo  is a memory-optimized table.  
SELECT * FROM  
           dbo.Order_mo  as o  WITH (SNAPSHOT)  -- Table hint.  
      JOIN dbo.Customer  as c  on c.CustomerId = o.CustomerId;  
COMMIT TRANSACTION;

透過使用資料庫選項 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT,即不需要 WITH (SNAPSHOT) 提示。The need for the WITH (SNAPSHOT) hint can be avoided through the use of the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. 當此選項設為 ON時,較低隔離等級的記憶體最佳化資料表存取權,會自動提升為 SNAPSHOT 隔離。When this option is set to ON, access to a memory-optimized table under a lower isolation level is automatically elevated to SNAPSHOT isolation.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

資料列版本設定Row Versioning

記憶體最佳化資料表即使在最嚴格的隔離等級 SERIALIZABLE,也會使用非常複雜的資料列版本設定系統,讓開放式方法有效率。Memory-optimized tables use a highly sophisticated row versioning system that makes the optimistic approach efficient, even at the most strict isolation level of SERIALIZABLE. 如需詳細資料,請參閱 記憶體最佳化資料表簡介For details see Introduction to Memory-Optimized Tables.

當 READ_COMMITTED_SNAPSHOT 或 SNAPSHOT 隔離等級作用時,硬碟資料表會間接擁有資料列版本設定系統。Disk-based tables indirectly have a row versioning system when READ_COMMITTED_SNAPSHOT or the SNAPSHOT isolation level is in effect. 此系統是以 tempdb 為基礎,而記憶體最佳化的資料結構有資料列版本設定內建功能,可取得最高效率。This system is based on tempdb, while memory-optimized data structures have row versioning built in, for maximum efficiency.

隔離等級Isolation Levels

下表列出可能的交易隔離等級,順序從隔離程度最低到最高。The following table lists the possible levels of transaction isolation, in sequence from least isolation to most. 如需會發生的衝突以及處理這些衝突之重試邏輯的詳細資訊,請參閱 衝突偵測和重試邏輯For details about conflicts that can occur and retry logic to deal with these conflicts, see Conflict Detection and Retry Logic.

隔離等級Isolation Level DescriptionDescription
READ UNCOMMITTEDREAD UNCOMMITTED 無法使用:READ UNCOMMITTED 隔離下無法存取記憶體最佳化資料表。Not available: memory-optimized tables cannot be accessed under Read Uncommitted isolation. 如果工作階段層級的 TRANSACTION ISOLATION LEVEL 設為 READ UNCOMMITTED,使用 WITH (SNAPSHOT) 資料表提示或將資料庫設定 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 設為 ON,仍有可能存取 SNAPSHOT 隔離下的記憶體最佳化資料表。It is still possible to access memory-optimized tables under SNAPSHOT isolation if the session-level TRANSACTION ISOLATION LEVEL is set to READ UNCOMMITTED, by using the WITH (SNAPSHOT) table hint or setting the database setting MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to ON.
READ COMMITTEDREAD COMMITTED 只有在自動認可模式作用時,才受記憶體最佳化資料表支援。Supported for memory-optimized tables only when the autocommit mode is in effect. 如果工作階段層級的 TRANSACTION ISOLATION LEVEL 設為 READ COMMITTED,使用 WITH (SNAPSHOT) 資料表提示或將資料庫設定 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 設為 ON,仍有可能存取 SNAPSHOT 隔離下的記憶體最佳化資料表。It is still possible to access memory-optimized tables under SNAPSHOT isolation if the session-level TRANSACTION ISOLATION LEVEL is set to READ COMMITTED, by using the WITH (SNAPSHOT) table hint or setting the database setting MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to ON.

如果資料庫選項 READ_COMMITTED_SNAPSHOT 設為 ON,不允許存取相同陳述式中 READ COMMITTED 隔離下的記憶體最佳化和磁碟資料表。If the database option READ_COMMITTED_SNAPSHOT is set to ON, it is not allowed to access both a memory-optimized and a disk-based table under READ COMMITTED isolation in the same statement.
SNAPSHOTSNAPSHOT 受到記憶體最佳化資料表支援。Supported for memory-optimized tables.

內部 SNAPSHOT 是記憶體最佳化資料表最基本的交易隔離等級。Internally SNAPSHOT is the least demanding transaction isolation level for memory-optimized tables.

SNAPSHOT 使用的系統資源比 REPEATABLE READ 或 SERIALIZABLE 更少。SNAPSHOT uses fewer system resources than does REPEATABLE READ or SERIALIZABLE.
REPEATABLE READREPEATABLE READ 受到記憶體最佳化資料表支援。Supported for memory-optimized tables. REPEATABLE READ 隔離保證在認可時,不會有並行交易更新此交易讀取的任何資料列。The guarantee provided by REPEATABLE READ isolation is that, at commit time, no concurrent transaction has updated any of the rows read by this transaction.

因為是開放式模型,所以不會阻止並行交易更新此交易讀取的資料列。Because of the optimistic model, concurrent transactions are not prevented from updating rows read by this transaction. 反倒是在認可時,此交易會驗證不違反 REPEATABLE READ 隔離。Instead, at commit time this transaction validated that REPEATABLE READ isolation has not been violated. 如果此交易違規,則會回復且必須重試。If it has, this transaction is rolled back and must be retried.
SERIALIZABLESERIALIZABLE 受到記憶體最佳化資料表支援。Supported for memory-optimized tables.

命名為 Serializable 的原因是隔離相當嚴格,幾乎像是讓交易接續執行,而非並行執行。Named Serializable because the isolation is so strict that it is almost a bit like having the transactions run in series rather than concurrently.

交易階段和存留期Transaction Phases and Lifetime

當涉及記憶體最佳化資料表時,交易的存留期會隨階段增加,如下圖所示:When a memory-optimized table is involved, the lifetime of a transaction progresses through the phases as displayed in the following image:

hekaton_transactions

後面接著階段描述。Descriptions of the phases follow.

正常處理:階段 1 (共 3 個)Regular Processing: Phase 1 (of 3)

  • 這個階段是由查詢中的所有查詢和 DML 陳述式的執行所組成。This phase is composed of the execution of all queries and DML statements in the query.
  • 在此階段,陳述式會將記憶體最佳化資料表的版本視為交易的邏輯開始時間。During this phase, the statements see the version of the memory-optimized tables as of the logical start time of the transaction.

驗證:階段 2 (共 3 個)Validation: Phase 2 (of 3)

  • 指定結束時間即開始驗證階段,將交易標示為邏輯方面已完成。The validation phase begins by assigning the end time, thereby marking the transaction as logically complete. 完成此作業可讓依賴這筆交易的其他交易看到交易的所有變更。This completion makes all changes of the transaction visible to other transactions which take a dependency on this transaction. 成功認可此交易前,不允許認可相依的交易。The dependent transactions are not allowed to commit until this transaction has successfully committed. 此外,不允許保留這類相依性的交易將結果集傳回用戶端,以確保用戶端只會看到已成功向資料庫認可的資料。In addition, transactions which hold such dependencies are not allowed to return result sets to the client, to ensure the client only sees data that has been successfully committed to the database.
  • 這個階段包含可重複的讀取和可序列化的驗證。This phase comprises the repeatable read and serializable validation. 針對可重複讀取驗證,會檢查是否有任何交易讀取的資料列在此之後更新。For repeatable read validation, it checks whether any of the rows read by the transaction has since been updated. 針對可序列化驗證,會檢查是否已將任何資料列插入此交易掃描的任何資料範圍。For serializable validation it checks whether any row has been inserted into any data range scanned by this transaction. 根據 隔離等級和衝突中的資料表,使用快照集隔離時,都會發生可重複讀取和可序列化驗證,以驗證唯一外部索引鍵條件約束的一致性。Per the table in Isolation Levels and Conflicts, both repeatable read and serializable validation can happen when using snapshot isolation, to validate consistency of unique and foreign key constraints.

認可處理:階段 3 (共 3 個)Commit Processing: Phase 3 (of 3)

  • 在認可階段,耐久資料表的變更會寫入記錄中,而記錄會寫入磁碟中。During the commit phase, the changes to durable tables are written to the log, and the log is written to disk. 控制項接著會傳回用戶端。Then control is returned to the client.
  • 認可處理完成之後,所有相依的交易都會收到進行認可通知。After commit processing completes, all dependent transactions are notified that they can commit.

您應該像平常一樣,在符合資料需求的前提下,盡可能使用最少且簡潔的工作交易單位。As always, you should try to keep your transactional units of work as minimal and brief as is valid for your data needs.

衝突偵測和重試邏輯Conflict Detection and Retry Logic

有兩種導致交易失敗及回復的交易相關錯誤狀況。There are two kinds of transaction-related error conditions that cause a transaction to fail and roll back. 在大多數的情況下,一旦發生這類失敗,就要重試交易,類似於發生死結時的狀況。In most cases, once such a failure occurs, the transaction needs to be retried, similar to when a deadlock occurs.

  • 並行交易間的衝突。Conflicts between concurrent transactions. 這些是更新衝突和驗證失敗,而且可能是因為交易隔離等級違規或條件約束違規。These are update conflicts and validation failures, and can be due to transaction isolation level violations or constraint violations.
  • 相依性失敗。Dependency failures. 這是因為您依賴的交易無法認可,或相依性數目變得太大。These result from transactions that you depend on failing to commit, or from the number of dependencies growing too large.

以下是當交易存取記憶體最佳化資料表時,會導致交易失敗的錯誤狀況。The following are the error conditions that can cause transactions to fail when they access memory-optimized tables.

錯誤碼Error Code DescriptionDescription 原因Cause
4130241302 嘗試更新目前交易開始後,已在其他交易中更新的資料列。Attempted to update a row that was updated in a different transaction since the start of the present transaction. 如果兩筆並行交易同時嘗試更新或刪除相同的資料列,就會發生這個錯誤狀況。This error condition occurs if two concurrent transactions attempt to update or delete the same row at the same time. 其中一筆交易會收到這個錯誤訊息,且必須重試。One of the two transactions receives this error message and will need to be retried.

4130541305 可重複的讀取驗證失敗。Repeatable read validation failure. 這筆交易完成認可前,從記憶體最佳化資料表讀取的資料列已為另一筆認可的交易更新。A row read from a memory-optimized table this transaction has been updated by another transaction that has committed before the commit of this transaction. 使用 REPEATABLE READ 或 SERIALIZABLE 隔離時,如果並行交易的動作又造成 FOREIGN KEY 條件約束違規,就會發生此錯誤。This error can occur when using REPEATABLE READ or SERIALIZABLE isolation, and also if the actions of a concurrent transaction cause violation of a FOREIGN KEY constraint.

這種外部索引鍵條件約束的並行違規很少見,通常是應用程式邏輯或資料項目的問題。Such concurrent violation of foreign key constraints is rare, and typically indicates an issue with the application logic or with data entry. 不過,如果和 FOREIGN KEY 條件約束有關的資料行沒有索引,也會發生此錯誤。However, the error can also occur if there is no index on the columns involved with the FOREIGN KEY constraint. 因此,指引是一律在記憶體最佳化資料表中,建立外部索引鍵資料行的索引的上。Therefore, the guidance is to always create an index on foreign key columns in a memory-optimized table.

如需外部索引鍵違規所致驗證失敗的詳細考量,請參閱 SQL Server 客戶諮詢小組的 部落格文章For more detailed considerations about validation failures caused by foreign key violations, see this blog post by the SQL Server Customer Advisory Team.
4132541325 可序列化的驗證失敗。Serializable validation failure. 目前交易稍早掃描的範圍中插入了新的資料列。A new row was inserted into a range that was scanned earlier by the present transaction. 我們將這種資料列稱為虛設項目列。We call this a phantom row. 使用 SERIALIZABLE 隔離時,如果並行交易的動作又造成 PRIMARY KEY、UNIQUE 或 FOREIGN KEY 條件約束違規,就會發生此錯誤。This error can occur when using SERIALIZABLE isolation, and also if the actions of a concurrent transaction cause violation of a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint.

這種並行條件約束違規很少見,通常是應用程式邏輯或資料項目的問題。Such concurrent constraint violation is rare, and typically indicates an issue with the application logic or data entry. 不過,與可重複讀取驗證失敗相似,如果相關資料行的 FOREIGN KEY 條件約束不含任何索引,也會發生此錯誤。However, similar to repeatable read validation failures, this error can also occur if there is a FOREIGN KEY constraint with no index on the columns involved.
4130141301 相依性失敗︰相依性建立在稍後無法認可的另一個交易上。Dependency failure: a dependency was taken on another transaction that later failed to commit. 這筆交易 (Tx1) 藉由讀取 Tx2 寫入的資料相依於另一筆交易 (Tx2),而後者 (Tx2) 當時處於其驗證或認可處理階段。This transaction (Tx1) took a dependency on another transaction (Tx2) while that transaction (Tx2) was in its validation or commit processing phase, by reading data that was written by Tx2. 接下來 Tx2 認可失敗。Tx2 subsequently failed to commit. Tx2 認可失敗最常見的原因是可重複讀取 (41305) 和可序列化 (41325) 驗證失敗,較不常見的原因則是記錄 IO 失敗。Most common causes for Tx2 to fail to commit are repeatable read (41305) and serializable (41325) validation failures; a less common cause is log IO failure.
418234184041823 and 41840 已達到使用者資料在經記憶體最佳化的資料表和資料表變數中的配額。Quota for user data in memory-optimized tables and table variables was reached. 錯誤 41823 適用於 SQL Server Express/Web/Standard Edition,以及 Azure SQL DatabaseAzure SQL Database 中的單一資料庫。Error 41823 applies to SQL Server Express/Web/Standard Edition, as well as single databases in Azure SQL DatabaseAzure SQL Database. 錯誤 41840 適用於 Azure SQL DatabaseAzure SQL Database 中的彈性集區。Error 41840 applies to elastic pools in Azure SQL DatabaseAzure SQL Database.

在大部分情況下,這些錯誤會指出已達到最大使用者資料大小,而解決此錯誤的方法是從經記憶體最佳化的資料表刪除資料。In most cases these errors indicate that the maximum user data size was reached, and the way to resolve the error is to delete data from memory-optimized tables. 不過,在少數情況下,這是暫時性錯誤。However, there are rare cases where this error is transient. 因此,我們建議您在第一次發生這些錯誤時重試。We therefore recommend to retry when first encountering these errors.

與此清單中的其他錯誤一樣,錯誤 41823 和 41840 會造成使用中交易中止。Like the other errors in this list, errors 41823 and 41840 cause the active transaction to abort.
4183941839 交易超過認可相依性的數目上限。Transaction exceeded the maximum number of commit dependencies. 適用於: SQL Server 2016 (13.x)SQL Server 2016 (13.x)Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x). 較新版本的 SQL ServerSQL ServerAzure SQL DatabaseAzure SQL Database 對於認可相依性的數目沒有限制。Later versions of SQL ServerSQL Server and Azure SQL DatabaseAzure SQL Database do not have a limit on the number of commit dependencies.

給定的交易 (Tx1) 能夠相依的交易數目有限制。There is a limit on the number of transactions a given transaction (Tx1) can depend on. 這些交易是連出的相依性。Those transactions are the outgoing dependencies. 此外,能夠相依於指定交易 (Tx1) 的交易數目也有限制。In addition, there is a limit on the number of transactions that can depend on a given transaction (Tx1). 這些交易是連入的相依性。These transactions are the incoming dependencies. 兩者的限制皆為 8。The limit for both is 8.

發生此錯誤的最常見情況,是大量的讀取交易存取由單一寫入交易寫入的資料。The most common case for this failure is where there is a large number of read transactions accessing data written by a single write transaction. 如果讀取交易全都執行相同資料的大型掃描,以及如果寫入交易長時間處理驗證或認可,例如寫入交易在可序列化隔離下執行大型掃描 (延長驗證階段) 或交易記錄檔位於慢速記錄 IO 裝置 (延長認可處理的時間),觸發這個狀況的可能性就會增加。The likelihood of hitting this condition increases if the read transactions are all performing large scans of the same data and if validation or commit processing of the write transaction takes long, for example the write transaction performs large scans under serializable isolation (increases length of the validation phase) or the transaction log is placed on a slow log IO device (increases length of commit processing). 如果讀取交易正在執行大型掃描,且應該只存取少數資料列,可能會遺漏索引。If the read transactions are performing large scans and they are expected to access only few rows, an index might be missing. 同樣地,如果寫入交易使用可序列化隔離且正在執行大型掃描,原本只想存取少數資料列,這也是沒有指示索引所致。Similarly, if the write transaction uses serializable isolation and is performing large scans but is expected to access only few rows, this is also an indication of a missing index.

使用追蹤旗標 9926 可以提高認可相依性的數目限制。The limit on number of commit dependencies can be lifted by using Trace Flag 9926. 只有在確認未曾遺漏任何索引後,仍然發生這個錯誤狀況時,才使用此追蹤旗標,因為在前列案例中,它可能會遮罩這些問題。Use this trace flag only if you are still hitting this error condition after confirming that there are no missing indexes, as it could mask these issues in the above-mentioned cases. 另一個警告是,每筆交易都有大量連入及連出相依性且個別交易都有多層相依性的複雜相依性圖表,可能會造成系統沒有效率。Another caution is that complex dependency graphs, where each transaction has a large number of incoming as well as outgoing dependencies, and individual transactions have many layers of dependencies, can lead to inefficiencies in the system.

重試邏輯Retry Logic

當交易因為上述任一情況而失敗時,就應該重試交易。When a transaction fails due to any of the above-mentioned conditions, the transaction should be retried.

您可以在用戶端或伺服器端實作重試邏輯。Retry logic can be implemented at the client or server side. 一般是建議在用戶端實作重試邏輯,因為它更有效率,且可讓您在失敗發生之前處理交易傳回的結果集。The general recommendation is to implement retry logic on the client side, as it is more efficient, and allows you to deal with result sets returned by the transaction before the failure occurs.

重試 T-SQL 程式碼範例Retry T-SQL Code Example

使用 T-SQL 的伺服器端重試邏輯,應該只用於不會將結果集傳回給用戶端的交易。Server-side retry logic using T-SQL should only be used for transactions that do not return result sets to the client. 否則,重試會在預期要傳回給用戶端的結果集外,還可能產生其他結果集。Otherwise, retries can potentially result in additional result sets beyond those anticipated being returned to the client.

以下解譯的 T-SQL 指令碼將說明,對於涉及記憶體最佳化資料表的交易衝突相關錯誤,重試邏輯可能會是什麼樣子。The following interpreted T-SQL script illustrates what retry logic can look like for the errors associated with transaction conflicts involving memory-optimized tables.

-- Retry logic, in Transact-SQL.
DROP PROCEDURE If Exists usp_update_salesorder_dates;
GO

CREATE PROCEDURE usp_update_salesorder_dates
AS
BEGIN
    DECLARE @retry INT = 10;

    WHILE (@retry > 0)
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
                set OrderDate = GetUtcDate()
                where CustomerId = 42;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
                set OrderDate = GetUtcDate()
                where CustomerId = 43;

            COMMIT TRANSACTION;

            SET @retry = 0;  -- //Stops the loop.
        END TRY

        BEGIN CATCH
            SET @retry -= 1;

            IF (@retry > 0 AND
                ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41823, 41840, 41839, 1205)
                )
            BEGIN
                IF XACT_STATE() = -1
                    ROLLBACK TRANSACTION;

                WAITFOR DELAY '00:00:00.001';
            END
            ELSE
            BEGIN
                PRINT 'Suffered an error for which Retry is inappropriate.';
                THROW;
            END
        END CATCH

    END -- //While loop
END;
GO

--  EXECUTE usp_update_salesorder_dates;

跨容器交易Cross-Container Transaction

如果交易執行以下動作,即稱為跨容器交易:A transaction is called a cross-container transaction if it:

  • 從解譯的 Transact-SQL 存取記憶體最佳化資料表;或Accesses a memory-optimized table from interpreted Transact-SQL; or
  • 在交易已經開啟時執行原生程序 (XACT_STATE() = 1)。Executes a native proc when a transaction is already open (XACT_STATE() = 1).

「跨容器」一詞衍生自跨兩個交易管理容器執行交易的事實,一個用於磁碟資料表,另一個用於記憶體最佳化資料表。The term "cross-container" derives from the fact that the transaction runs across the two transaction management containers, one for disk-based tables and one for memory-optimized tables.

在單一跨容器交易內,不同的隔離等級可用於存取磁碟和記憶體最佳化資料表。Within a single cross-container transaction, different isolation levels can be used for accessing disk-based and memory-optimized tables. 這項差異透過明確的資料表提示表示,例如 WITH (SERIALIZABLE),或透過資料庫選項 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 表示,如果 TRANSACTION ISOLATION LEVEL 設定為 READ COMMITTED 或 READ UNCOMMITTED,則以隱含的方式將記憶體最佳化資料表的隔離等級提高到快照集。This difference is expressed through explicit table hints such as WITH (SERIALIZABLE) or through the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, which implicitly elevates the isolation level for memory-optimized table to snapshot if the TRANSACTION ISOLATION LEVEL is configured as READ COMMITTED or READ UNCOMMITTED.

在下列 Transact-SQL 程式碼範例中:In the following Transact-SQL code example:

  • 磁碟資料表 Table_D1,是使用 READ COMMITTED 隔離等級存取。The disk-based table, Table_D1, is accessed using the READ COMMITTED isolation level.
  • 記憶體最佳化資料表 Table_MO7,是使用 SERIALIZABLE 隔離等級存取。The memory-optimized table Table_MO7 is accessed using the SERIALIZABLE isolation level. Table_MO6 沒有特定的關聯隔離層級,因為插入永遠一致且基本在可序列化隔離下執行。Table_MO6 does not have a specific associated isolation level, since inserts are always consistent and executed essentially under serializable isolation.
-- Different isolation levels for
-- disk-based tables versus memory-optimized tables,
-- within one explicit transaction.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
go

BEGIN TRANSACTION;

    -- Table_D1 is a traditional disk-based table, accessed using READ COMMITTED isolation.

    SELECT * FROM Table_D1;


    -- Table_MO6 and Table_MO7 are memory-optimized tables.
    -- Table_MO7 is accessed using SERIALIZABLE isolation,
    --   while Table_MO6 does not have a specific isolation level.

    INSERT Table_MO6
        SELECT * FROM Table_MO7 WITH (SERIALIZABLE);

COMMIT TRANSACTION;
go

限制Limitations

  • 記憶體最佳化資料表不支援跨資料庫的交易。Cross-database transactions are not supported for memory-optimized tables. 如果交易存取記憶體最佳化資料表,該交易即無法存取其他任何資料庫,除了If a transaction accesses a memory-optimized table, the transaction cannot access any other database, except for:

    • tempdb 資料庫。tempdb database.
    • 從 master 資料庫唯讀。Read-only from the master database.
  • 不支援分散式交易:使用 BEGIN DISTRIBUTED TRANSACTION 時,交易無法存取記憶體最佳化資料表。Distributed transactions are not supported: When BEGIN DISTRIBUTED TRANSACTION is used, the transaction cannot access a memory-optimized table.

原生編譯的預存程序Natively Compiled Stored Procedures

  • 在原生程序中,ATOMIC 區塊必須宣告整個區塊的交易隔離等級,像是:In a native proc, the ATOMIC block must declare the transaction isolation level for the whole block, such as:

    • ... BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, ...) ...
  • 原生程序的主體中不允許任何明確交易控制陳述式。No explicit transaction control statements are allowed within the body of a native proc. BEGIN TRANSACTION、ROLLBACK TRANSACTION 等均不允許。BEGIN TRANSACTION, ROLLBACK TRANSACTION, and so on, are all disallowed.

  • 如需使用 ATOMIC 區塊之交易控制的詳細資訊,請參閱 ATOMIC 區塊For more information about transaction control with ATOMIC blocks, see Atomic Blocks