啟用 Multiple Active Result SetsEnabling Multiple Active Result Sets

Multiple Active Result Set (MARS) 是與 SQL Server 搭配使用的功能,它允許在單一連接中執行多個批次作業。Multiple Active Result Sets (MARS) is a feature that works with SQL Server to allow the execution of multiple batches on a single connection. 啟用 MARS 以與 SQL Server 搭配使用時,使用的每個命令物件都會在連接中加入工作階段。When MARS is enabled for use with SQL Server, each command object used adds a session to the connection.

注意

單一的 MARS 工作階段會開啟一個邏輯連接供 MARS 使用,然後再針對每個使用中的命令開啟邏輯連接。A single MARS session opens one logical connection for MARS to use and then one logical connection for each active command.

在連接字串中啟用及停用 MARSEnabling and Disabling MARS in the Connection String

注意

下列連接字串使用範例AdventureWorks隨附於 SQL Server 的資料庫。The following connection strings use the sample AdventureWorks database included with SQL Server. 提供的連接字串會假設伺服器上已安裝名為 MSSQL1 的資料庫。The connection strings provided assume that the database is installed on a server named MSSQL1. 視環境需要修改連接字串。Modify the connection string as necessary for your environment.

預設會停用 MARS 功能。The MARS feature is disabled by default. 藉由將 "MultipleActiveResultSets=True" 關鍵字配對加入連接字串,可啟用該功能。It can be enabled by adding the "MultipleActiveResultSets=True" keyword pair to your connection string. "True" 是啟用 MARS 的唯一有效值。"True" is the only valid value for enabling MARS. 下列範例會說明如何連接至 SQL Server 的執行個體,以及如何指定應該啟用 MARS。The following example demonstrates how to connect to an instance of SQL Server and how to specify that MARS should be enabled.

Dim connectionString As String = "Data Source=MSSQL1;" & _  
    "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" & _  
    "MultipleActiveResultSets=True"  
string connectionString = "Data Source=MSSQL1;" +   
    "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +  
    "MultipleActiveResultSets=True";  

藉由將 "MultipleActiveResultSets=False" 關鍵字配對加入連接字串,可停用 MARS。You can disable MARS by adding the "MultipleActiveResultSets=False" keyword pair to your connection string. "False" 是停用 MARS 的唯一有效值。"False" is the only valid value for disabling MARS. 下列連接字串會示範如何停用 MARS。The following connection string demonstrates how to disable MARS.

Dim connectionString As String = "Data Source=MSSQL1;" & _  
    "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" & _  
    "MultipleActiveResultSets=False"  
string connectionString = "Data Source=MSSQL1;" +   
    "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +  
    "MultipleActiveResultSets=False";  

使用 MARS 時的特殊考量Special Considerations When Using MARS

一般而言,現有應用程式不需修改即可使用啟用 MARS 的連接。In general, existing applications should not need modification to use a MARS-enabled connection. 但是,如果您要在應用程式中使用 MARS 功能,應了解下列特殊考量。However, if you wish to use MARS features in your applications, you should understand the following special considerations.

陳述式交錯Statement Interleaving

MARS 作業會在伺服器上同步執行。MARS operations execute synchronously on the server. 允許 SELECT 及 BULK INSERT 陳述式的陳述式交錯。Statement interleaving of SELECT and BULK INSERT statements is allowed. 但是,資料管理語言 (DML) 及資料定義語言 (DDL) 陳述式會以原子方式執行。However, data manipulation language (DML) and data definition language (DDL) statements execute atomically. 嘗試於原子批次執行時執行的任何陳述式都會被封鎖。Any statements attempting to execute while an atomic batch is executing are blocked. 伺服器的平行執行不是 MARS 功能。Parallel execution at the server is not a MARS feature.

如果在 MARS 連接下送出兩個批次作業,其中一個包含 SELECT 陳述式,另一個包含 DML 陳述式,則可以在執行 SELECT 陳述式時開始執行 DML。If two batches are submitted under a MARS connection, one of them containing a SELECT statement, the other containing a DML statement, the DML can begin execution within execution of the SELECT statement. 不過,DML 陳述式必須先完成執行,才能繼續執行 SELECT 陳述式。However, the DML statement must run to completion before the SELECT statement can make progress. 如果兩個陳述式同時在同一個異動下執行,則 DML 陳述式在 SELECT 陳述式已開始執行後所進行的任何變更,對於讀取作業都是不可見的。If both statements are running under the same transaction, any changes made by a DML statement after the SELECT statement has started execution are not visible to the read operation.

SELECT 陳述式內的 WAITFOR 陳述式在等待期間,即產生第一個資料列之前,不會產生異動。A WAITFOR statement inside a SELECT statement does not yield the transaction while it is waiting, that is, until the first row is produced. 這表示在 WAITFOR 陳述式等待期間,無法在同一連接內執行其他批次作業。This implies that no other batches can execute within the same connection while a WAITFOR statement is waiting.

MARS 工作階段快取MARS Session Cache

開啟啟用 MARS 的連接時,會建立邏輯工作階段,如此會增加額外負荷。When a connection is opened with MARS enabled, a logical session is created, which adds additional overhead. 若要最小化負荷並提高效能, SqlClient快取連接內的 MARS 工作階段。To minimize overhead and enhance performance, SqlClient caches the MARS session within a connection. 快取包含最多 10 個 MARS 工作階段。The cache contains at most 10 MARS sessions. 使用者不可調整此值。This value is not user adjustable. 如果達到工作階段限制,則會建立新的工作階段而不會產生錯誤。If the session limit is reached, a new session is created—an error is not generated. 其中包含的快取及工作階段是以每個連接為基礎的,不可跨連接共用。The cache and sessions contained in it are per-connection; they are not shared across connections. 釋放工作階段時,會將其傳回集區,直至達到集區上限為止。When a session is released, it is returned to the pool unless the pool's upper limit has been reached. 如果快取集區已滿,則工作階段會關閉。If the cache pool is full, the session is closed. MARS 工作階段不會過期。MARS sessions do not expire. 只會在處置連接物件時清除它們。They are only cleaned up when the connection object is disposed. MARS 工作階段快取不會預先載入。The MARS session cache is not preloaded. 應用程式需要更多工作階段時會將其載入。It is loaded as the application requires more sessions.

執行緒安全Thread Safety

MARS 作業不是安全執行緒。MARS operations are not thread-safe.

連接共用Connection Pooling

啟用 MARS 的連接共用方式與其他連接一樣。MARS-enabled connections are pooled like any other connection. 如果應用程式開啟兩個連接,一個啟用 MARS,另一個停用 MARS,則兩個連接會位於不同的集區中。If an application opens two connections, one with MARS enabled and one with MARS disabled, the two connections are in separate pools. 如需詳細資訊,請參閱 SQL Server 連共用ADO.NET)For more information, see SQL Server Connection Pooling (ADO.NET).

SQL Server 批次執行環境SQL Server Batch Execution Environment

連接開啟時,會定義預設的環境。When a connection is opened, a default environment is defined. 然後會將此環境複製到邏輯 MARS 工作階段。This environment is then copied into a logical MARS session.

批次執行環境包含下列元件:The batch execution environment includes the following components:

  • 設定選項 (例如,ANSI_NULLS、DATE_FORMAT、LANGUAGE、TEXTSIZE)Set options (for example, ANSI_NULLS, DATE_FORMAT, LANGUAGE, TEXTSIZE)

  • 安全性內容 (使用者/應用程式角色)Security context (user/application role)

  • 資料庫內容 (目前資料庫)Database context (current database)

  • 執行狀態變數 (例如,@@ERROR,@@ROWCOUNT,@@FETCH_STATUS @@IDENTITY)Execution state variables (for example, @@ERROR, @@ROWCOUNT, @@FETCH_STATUS @@IDENTITY)

  • 最上層暫存資料表Top-level temporary tables

使用 MARS,預設的執行環境可與連接產生關聯。With MARS, a default execution environment is associated to a connection. 在指定連接下開始執行的每個新批次作業,都會收到預設環境的複本。Every new batch that starts executing under a given connection receives a copy of the default environment. 每當程式碼在指定批次作業下執行時,對環境所做的所有變更都只限於該特定批次作業。Whenever code is executed under a given batch, all changes made to the environment are scoped to the specific batch. 執行一旦完成,便會將執行設定複製到預設環境中。Once execution finishes, the execution settings are copied into the default environment. 當單一批次作業發出要在同一異動下循序執行的數個命令時,其語意與舊版用戶端或伺服器相關的連接所公開的語意相同。In the case of a single batch issuing several commands to be executed sequentially under the same transaction, semantics are the same as those exposed by connections involving earlier clients or servers.

平行執行Parallel Execution

MARS 未設計為在應用程式內移除對多重連接的所有需求。MARS is not designed to remove all requirements for multiple connections in an application. 如果應用程式確實需要針對伺服器平行執行命令,則應使用多重連接。If an application needs true parallel execution of commands against a server, multiple connections should be used.

例如,請考量下列案例。For example, consider the following scenario. 建立兩個命令物件,一個用於處理結果集,另一個用於更新資料,它們透過 MARS 共用通用連接。Two command objects are created, one for processing a result set and another for updating data; they share a common connection via MARS. 在此案例中, TransactionCommitIn this scenario, the Transaction.Commit 更新失敗,直到在第一個命令物件,進而產生下列例外狀況已讀取所有結果:fails on the update until all the results have been read on the first command object, yielding the following exception:

訊息:其他工作階段正在使用交易內容。Message: Transaction context in use by another session.

來源:.NET SqlClient 資料提供者Source: .NET SqlClient Data Provider

預期:(null)Expected: (null)

已接收:System.Data.SqlClient.SqlExceptionReceived: System.Data.SqlClient.SqlException

處理此案例有三個選項:There are three options for handling this scenario:

  1. 在建立讀取器後啟動交易,使其不會成為交易的一部分。Start the transaction after the reader is created, so that it is not part of the transaction. 然後每個更新會成為其自身的交易。Every update then becomes its own transaction.

  2. 在讀取器關閉後認可所有工作。Commit all work after the reader is closed. 如此可能會發生大量批次的更新。This has the potential for a substantial batch of updates.

  3. 請勿針對每個命令物件使用 MARS,而是使用 MARS 之前的單獨連接。Don't use MARS; instead use a separate connection for each command object as you would have before MARS.

偵測 MARS 支援Detecting MARS Support

應用程式可以藉由讀取 SqlConnection.ServerVersion 值來檢查 MARS 支援。An application can check for MARS support by reading the SqlConnection.ServerVersion value. SQL Server 2005 和 SQL Server 2008 的主版本號碼應為分別為 9 和 10。The major number should be 9 for SQL Server 2005 and 10 for SQL Server 2008.

另請參閱See also