使用 Multiple Active Result Sets (MARS)Using Multiple Active Result Sets (MARS)

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

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 在存取 Database EngineDatabase Engine 的應用程式中導入了對 Multiple Active Result Set (MARS) 的支援。introduced support for multiple active result sets (MARS) in applications accessing the Database EngineDatabase Engine. 在舊版的 SQL ServerSQL Server 中,資料庫應用程式無法在連接上維持多個作用中陳述式。In earlier versions of SQL ServerSQL Server, database applications could not maintain multiple active statements on a connection. 當使用 SQL ServerSQL Server 預設結果集時,應用程式必須從一個批次處理或取消所有結果集,然後才能夠在該連接上執行任何其他批次。When using SQL ServerSQL Server default result sets, the application had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 (9.x)SQL Server 2005 (9.x) 導入了新的連接屬性,好讓應用程式在每個連接上可以有一個以上的暫止要求,而且特別是每個連接上可以有一個以上的使用中預設結果集。introduced a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection.

MARS 會使用以下的新功能來簡化應用程式設計:MARS simplifies application design with the following new capabilities:

  • 應用程式可以開啟多個預設結果集,而且可以交錯讀取這些結果集。Applications can have multiple default result sets open and can interleave reading from them.

  • 當開啟預設結果集時,應用程式可以執行其他陳述式 (例如 INSERT、UPDATE、DELETE 和預存程序呼叫)。Applications can execute other statements (for example, INSERT, UPDATE, DELETE, and stored procedure calls) while default result sets are open.

以下的指導方針對於使用 MARS 的應用程式非常有用:Applications using MARS will find the following guidelines beneficial:

  • 預設結果集應該用於單一 SQL 陳述式 (SELECT、DML with OUTPUT、RECEIVE、READ TEXT 等等) 所產生的短期或簡短結果集。Default results sets should be used for short lived or short result sets generated by single SQL statements (SELECT, DML with OUTPUT, RECEIVE, READ TEXT, and so on).

  • 伺服器資料指標應該用於單一 SQL 陳述式所產生的較長期或大型結果集。Server cursors should be used for longer lived or large result sets generated by single SQL statements.

  • 一定要針對程序要求 (不論它們是否會傳回結果) 以及可傳回多個結果的批次讀取到結果結尾。Always read to the end of results for procedural requests regardless of whether they return results or not, and for batches that return multiple results.

  • 盡可能使用 API 呼叫來變更連接屬性,並優先管理交易,而非 Transact-SQLTransact-SQL 陳述式。Wherever possible, use API calls to change connection properties and manage transactions in preference to Transact-SQLTransact-SQL statements.

  • 在 MARS 中,當執行並行批次時會禁止工作階段範圍的模擬。In MARS, session-scoped impersonation is prohibited while concurrent batches are running.

注意

根據預設, 驅動程式不會啟用 MARS 功能。By default, MARS functionality is not enabled by the driver. 若要在使用SQL ServerSQL Server SQL ServerSQL Server Native Client 連接到時使用 mars, 您必須在連接字串中特別啟用 mars。To use MARS when connecting to SQL ServerSQL Server with SQL ServerSQL Server Native Client, you must specifically enable MARS within a connection string. 不過, 如果應用程式偵測到驅動程式支援 MARS, 某些應用程式可能會預設啟用 MARS。However, some applications may enable MARS by default, if the application detects that the driver supports MARS. 針對這些應用程式, 您可以視需要停用連接字串中的 MARS。For these applications, you can disable MARS in the connection string as needed. 如需詳細資訊,請參閱本主題稍後的「SQL ServerSQL Server Native Client OLE DB 提供者」和「SQL ServerSQL Server Native Client ODBC 驅動程式」章節。For more information, see the SQL ServerSQL Server Native Client OLE DB provider and SQL ServerSQL Server Native Client ODBC driver sections, later in this topic.

SQL ServerSQL Server Native Client 不會限制連接上作用中陳述式的數目。Native Client does not limit the number of active statements on a connection.

不需要同時執行多個單一多重語句批次或預存程式的一般應用程式, 將受益于 MARS, 而不必瞭解如何實作為 MARS。Typical applications which do not need to have more than a single multi-statement batch or stored procedure executing at the same time will benefit from MARS without having to understand how MARS is implemented. 但是,具有更複雜需求的應用程式確實需要考量這件事。However, applications with more complex requirements do need to take account of this.

MARS 可啟用單一連接內多個要求的交錯執行。MARS enables the interleaved execution of multiple requests within a single connection. 也就是說,它可允許批次執行,而且當它執行時,可允許其他要求執行。That is, it allows a batch to run, and within its execution, it allows other requests to execute. 但是請注意,MARS 是以交錯來定義,而不是以平行執行來定義。Note, however, that MARS is defined in terms of interleaving, not in terms of parallel execution.

MARS 基礎結構可讓多個批次以交錯方式執行,但是只能在定義良好的點上切換執行。The MARS infrastructure allows multiple batches to execute in an interleaved fashion, though execution can only be switched at well defined points. 此外,大多數的陳述式都必須在批次內自動執行。In addition, most statements must run atomically within a batch. 傳回資料列給用戶端的語句 (有時也稱為「產生點」), 可以在資料列傳送到用戶端時, 于完成前交錯執行, 例如:Statements which return rows to the client, which are sometimes referred to as yield points, are allowed to interleave execution before completion while rows are being sent to the client, for example:

  • SELECTSELECT

  • FETCHFETCH

  • RECEIVERECEIVE

當執行可以切換到其他 MARS 要求之前,當做預存程序或批次的一部分執行的其他任何陳述式都必須執行到完成為止。Any other statements that are executed as part of a stored procedure or batch must run to completion before execution can be switched to other MARS requests.

批次交錯執行的確切方式會受到一些因素的影響,而且很難預測包含產生點之多個批次中將要執行命令的確切順序。The exact manner in which batches interleave execution is influenced by a number of factors, and it is difficult to predict the exact sequence in which commands from multiple batches that contain yield points will be executed. 請小心避免因為這類複雜批次的交錯執行所產生之不必要的副作用。Be careful to avoid unwanted side effects due to interleaved execution of such complex batches.

若要避免問題的發生,請使用 API 呼叫 (而非 Transact-SQLTransact-SQL 陳述式) 來管理連接狀態 (SET、USE) 和交易 (BEGIN TRAN、COMMIT、ROLLBACK),其方式是不要將這些陳述式併入同樣包含產生點的多重陳述式批次內,以及取用或取消所有結果來序列化這類批次的執行。Avoid problems by using API calls rather than Transact-SQLTransact-SQL statements to manage connection state (SET, USE) and transactions (BEGIN TRAN, COMMIT, ROLLBACK) by not including these statements in multi-statement batches that also contain yield points, and by serializing execution of such batches by consuming or canceling all results.

注意

在啟用 MARS 時啟動手動或隱含交易的批次或預存程序必須先完成交易,然後才能結束批次。A batch or stored procedure which starts a manual or implicit transaction when MARS is enabled must complete the transaction before the batch exits. 如果不是這樣的話,SQL ServerSQL Server 會在批次完成時回復交易所做的所有變更。If it does not, SQL ServerSQL Server rolls back all changes made by the transaction when the batch finishes. 這類交易是由 SQL ServerSQL Server 當做批次範圍的交易來管理。Such a transaction is managed by SQL ServerSQL Server as a batch-scoped transaction. SQL Server 2005 (9.x)SQL Server 2005 (9.x) 中導入了新類型的交易,好讓現有行為良好的預存程序在啟用 MARS 時可以使用。This is a new type of transaction introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x) to enable existing well-behaved stored procedures to be used when MARS is enabled. 如需批次範圍交易的詳細資訊, 請參閱Transaction 語句(transact-sql)For more information about batch-scoped transactions, see Transaction Statements (Transact-SQL).

如需從 ADO 使用 MARS 的範例, 請參閱搭配使用 ado 與 SQL Server Native ClientFor an example of using MARS from ADO, see Using ADO with SQL Server Native Client.

記憶體內部 OLTPIn-Memory OLTP

記憶體內部 OLTP 支援使用查詢和原生編譯預存程式的 MARS。In-memory OLTP supports MARS using queries and natively compiled stored procedures. MARS 可讓您從多個查詢要求資料, 而不需要在傳送要求以從新的結果集提取資料列之前, 完全取得每個結果集。MARS enables requesting data from multiple queries without the need to completely retrieve each result set before sending a request to fetch rows from a new result set. 若要成功讀取多個開啟的結果集, 您必須使用已啟用 MARS 的連接。To successfully read from multiple open result sets you must use a MARS enabled connection.

MARS 預設為停用, 因此您必須藉由將加入MultipleActiveResultSets=True至連接字串來明確加以啟用。MARS is disabled by default so you must explicitly enable it by adding MultipleActiveResultSets=True to a connection string. 下列範例示範如何連接到 SQL Server 的實例, 並指定啟用 MARS:The following example demonstrates how to connect to an instance of SQL Server and specify that MARS is enabled:

Data Source=MSSQL; Initial Catalog=AdventureWorks; Integrated Security=SSPI; MultipleActiveResultSets=True  

具有記憶體內部 OLTP 的 MARS 基本上與其他 SQL 引擎中的 MARS 相同。MARS with In-Memory OLTP is essentially the same as MARS in the rest of the SQL engine. 以下列出在記憶體優化資料表和原生編譯的預存程式中使用 MARS 時的差異。The following lists the differences when using MARS in memory-optimized tables and natively compiled stored procedures.

MARS 和記憶體優化資料表MARS and memory-optimized tables

以下是使用 MARS 啟用的連接時, 磁片型和記憶體優化資料表之間的差異:The following are the differences between disk-based and memory-optimized tables when using a MARS enabled connection:

  • 兩個語句可以修改相同目標物件中的資料, 但如果兩者都嘗試修改相同的記錄, 則寫入寫入衝突會導致新的作業失敗。Two statements can modify data in the same target object but if they both attempt to modify the same record a write-write conflict will cause the new operation to fail. 不過, 如果這兩個作業都修改不同的記錄, 作業將會成功。However, if both operations modify different records, the operations will succeed.

  • 每個語句都會在快照集隔離下執行, 因此新的作業無法看到現有語句所做的變更。Each statement runs under SNAPSHOT isolation so new operations cannot see changes made by the existing statements. 即使並行語句是在相同的交易下執行, SQL 引擎還是會針對每個彼此隔離的語句, 建立批次範圍的交易。Even if the concurrent statements are executed under the same transaction the SQL engine creates batch-scoped transactions for each statement that are isolated from each other. 不過, 批次範圍的交易仍會系結在一起, 因此, 回復一個批次範圍的交易會影響相同批次中的其他交易。However, batch-scoped transactions are still bound together so rollback of one batch-scoped transaction affects other ones in the same batch.

  • 使用者交易中不允許使用 DDL 作業, 因此它們會立即失敗。DDL operations are not allowed in user transactions so they will immediately fail.

MARS 和原生編譯的預存程序MARS and natively compiled stored procedures

原生編譯的預存程式可以在已啟用 MARS 的連接中執行, 而且只有在遇到產生點時, 才會產生另一個語句的執行。Natively compiled stored procedures can run in MARS enabled connections and can yield execution to another statement only when a yield point is encountered. 「產生點」需要 SELECT 語句, 這是原生編譯預存程式內的唯一語句, 可以產生對另一個語句的執行。A yield point requires a SELECT statement, which is the only statement within a natively compiled stored procedure that can yield execution to another statement. 如果 SELECT 語句不存在於程式中, 就不會產生它, 而是在其他語句開始之前執行到完成。If a SELECT statement is not present in the procedure it will not yield, it will run to completion before other statements begin.

MARS 和記憶體內部 OLTP 交易MARS and In-memory OLTP transactions

語句所做的變更, 以及交錯式的不可部分完成區塊會彼此隔離。Changes made by statements and atomic blocks that are interleaved are isolated from each other. 例如, 如果一個語句或不可部分完成的區塊會進行一些變更, 然後產生另一個語句的執行, 新的語句就不會看到第一個語句所做的變更。For example, if one statement or atomic block makes some changes, and then yields execution to another statement, the new statement will not see changes made by the first statement. 此外, 當第一個語句繼續執行時, 不會看到任何其他語句所做的任何變更。In addition, when first statement resumes execution, it will not see any changes made by any other statements. 語句只會在語句啟動之前, 看到已經完成和認可的變更。Statements will only see changes that are finished and committed before the statement starts.

使用 BEGIN TRANSACTION 語句, 可以在目前的使用者交易內啟動新的使用者交易-這只在 interop 模式中受到支援, 因此只能從 T-sql 語句呼叫 BEGIN TRANSACTION, 而不是從原生編譯的預存步.您可以使用 SAVE TRANSACTION 或對交易的 API 呼叫, 在交易中建立儲存點。儲存 (save_point_name), 以回復至儲存點。A new user transaction can be started within the current user transaction using the BEGIN TRANSACTION statement - this is supported only in interop mode so the BEGIN TRANSACTION can only be called from a T-SQL statement, and not from within a natively compiled stored procedure.You can create a save point in a transaction using SAVE TRANSACTION or an API call to transaction.Save(save_point_name) to rollback to the savepoint. 這項功能也只能從 T-sql 語句啟用, 而不是從原生編譯的預存程式中啟用。This feature is also enabled only from T-SQL statements, and not from within natively compiled stored procedures.

MARS 和資料行存放區索引MARS and columnstore indexes

SQL Server (從2016開始) 支援 MARS 搭配資料行存放區索引。SQL Server (starting with 2016) supports MARS with columnstore indexes. SQL Server 2014 使用 MARS 來與具有資料行存放區索引的資料表進行唯讀連線。SQL Server 2014 uses MARS for read-only connections to tables with a columnstore index. 不過,SQL Server 2014 不支援 MARS 在具備資料行存放區索引的資料表上,進行並行資料操作語言 (DML) 作業。However, SQL Server 2014 does not support MARS for concurrent data manipulation language (DML) operations on a table with a columnstore index. 發生這種情況時,SQL Server 會終止連接並中止交易。When this occurs, SQL Server will terminate the connections and abort the transactions. SQL Server 2012 具有唯讀的資料行存放區索引, 而 MARS 則不適用。SQL Server 2012 has read-only columnstore indexes and MARS does not apply to them.

SQL Server Native Client OLE DB 提供者SQL Server Native Client OLE DB Provider

SQL ServerSQL Server Native Client OLE DB 提供者透過加入 SSPROP_INIT_MARSCONNECTION 資料來源初始化屬性 (在 DBPROPSET_SQLSERVERDBINIT 屬性集內執行) 來支援 MARS。The SQL ServerSQL Server Native Client OLE DB provider supports MARS through the addition of the SSPROP_INIT_MARSCONNECTION data source initialization property, which is implemented in the DBPROPSET_SQLSERVERDBINIT property set. 此外,也已經加入新的連接字串關鍵字 MarsConnIn addition, a new connection string keyword, MarsConn, as been added. 它接受truefalse值;預設值為falseIt accepts true or false values; false is the default.

資料來源屬性 DBPROP_MULTIPLECONNECTIONS 預設為 VARIANT_TRUE。The data source property DBPROP_MULTIPLECONNECTIONS defaults to VARIANT_TRUE. 這表示,為了支援多個並行命令和資料列集物件,此提供者將會繁衍多個連接。This means the provider will spawn multiple connections in order to support multiple concurrent command and rowset objects. 當 MARS 啟用時, SQL ServerSQL Server Native Client 可以在單一連接上支援多個命令和資料列集物件, 因此 MULTIPLE_CONNECTIONS 預設會設定為 VARIANT_FALSE。When MARS is enabled, SQL ServerSQL Server Native Client can support multiple command and rowset objects on a single connection, so MULTIPLE_CONNECTIONS is set to VARIANT_FALSE by default.

如需 DBPROPSET_SQLSERVERDBINIT 屬性集之增強功能的詳細資訊, 請參閱初始化和授權屬性For more information about enhancements made to the DBPROPSET_SQLSERVERDBINIT property set, see Initialization and Authorization Properties.

SQL Server Native Client OLE DB 提供者範例SQL Server Native Client OLE DB Provider Example

在此範例中, 會使用SQL ServerSQL Server原生 OLE DB 提供者建立資料來源物件, 而且在建立會話物件之前, 會使用 DBPROPSET_SQLSERVERDBINIT 屬性集來啟用 MARS。In this example, a data source object is created using the SQL ServerSQL Server Native OLE DB provider, and MARS is enabled using the DBPROPSET_SQLSERVERDBINIT property set before the session object is created.

#include <sqlncli.h>  
  
IDBInitialize *pIDBInitialize = NULL;  
IDBCreateSession *pIDBCreateSession = NULL;  
IDBProperties *pIDBProperties = NULL;  
  
// Create the data source object.  
hr = CoCreateInstance(CLSID_SQLNCLI10, NULL,  
   CLSCTX_INPROC_SERVER,  
   IID_IDBInitialize,   
    (void**)&pIDBInitialize);  
  
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void**)&pIDBProperties);  
  
// Set the MARS property.  
DBPROP rgPropMARS;  
  
// The following is necessary since MARS is off by default.  
rgPropMARS.dwPropertyID = SSPROP_INIT_MARSCONNECTION;  
rgPropMARS.dwOptions = DBPROPOPTIONS_REQUIRED;  
rgPropMARS.dwStatus = DBPROPSTATUS_OK;  
rgPropMARS.colid = DB_NULLID;  
V_VT(&(rgPropMARS.vValue)) = VT_BOOL;  
V_BOOL(&(rgPropMARS.vValue)) = VARIANT_TRUE;  
  
// Create the structure containing the properties.  
DBPROPSET PropSet;  
PropSet.rgProperties = &rgPropMARS;  
PropSet.cProperties = 1;  
PropSet.guidPropertySet = DBPROPSET_SQLSERVERDBINIT;  
  
// Get an IDBProperties pointer and set the initialization properties.  
pIDBProperties->SetProperties(1, &PropSet);  
pIDBProperties->Release();  
  
// Initialize the data source object.  
hr = pIDBInitialize->Initialize();  
  
//Create a session object from a data source object.  
IOpenRowset * pIOpenRowset = NULL;  
hr = IDBInitialize->QueryInterface(IID_IDBCreateSession, (void**)&pIDBCreateSession));  
hr = pIDBCreateSession->CreateSession(  
   NULL,             // pUnkOuter  
   IID_IOpenRowset,  // riid  
  &pIOpenRowset ));  // ppSession  
  
// Create a rowset with a firehose mode cursor.  
IRowset *pIRowset = NULL;  
DBPROP rgRowsetProperties[2];  
  
// To get a firehose mode cursor request a   
// forward only read only rowset.  
rgRowsetProperties[0].dwPropertyID = DBPROP_IRowsetLocate;  
rgRowsetProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;  
rgRowsetProperties[0].dwStatus = DBPROPSTATUS_OK;  
rgRowsetProperties[0].colid = DB_NULLID;  
VariantInit(&(rgRowsetProperties[0].vValue));  
rgRowsetProperties[0].vValue.vt = VARIANT_BOOL;  
rgRowsetProperties[0].vValue.boolVal = VARIANT_FALSE;  
  
rgRowsetProperties[1].dwPropertyID = DBPROP_IRowsetChange;  
rgRowsetProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;  
rgRowsetProperties[1].dwStatus = DBPROPSTATUS_OK;  
rgRowsetProperties[1].colid = DB_NULLID;  
VariantInit(&(rgRowsetProperties[1].vValue));  
rgRowsetProperties[1].vValue.vt = VARIANT_BOOL;  
rgRowsetProperties[1].vValue.boolVal = VARIANT_FALSE;  
  
DBPROPSET rgRowsetPropSet[1];  
rgRowsetPropSet[0].rgProperties = rgRowsetProperties  
rgRowsetPropSet[0].cProperties = 2  
rgRowsetPropSet[0].guidPropertySet = DBPROPSET_ROWSET;  
  
hr = pIOpenRowset->OpenRowset (NULL,  
   &TableID,  
   NULL,  
   IID_IRowset,  
   1,  
   rgRowsetPropSet  
   (IUnknown**)&pIRowset);  

SQL Server Native Client ODBC 驅動程式SQL Server Native Client ODBC Driver

SQL ServerSQL ServerNative Client ODBC 驅動程式透過 SQLSetConnectAttrSQLGetConnectAttr函式的新增功能支援 MARS。The SQL ServerSQL Server Native Client ODBC driver supports MARS through additions to the SQLSetConnectAttr and SQLGetConnectAttr functions. 已經加入 SQL_COPT_SS_MARS_ENABLED 來接受 SQL_MARS_ENABLED_YES 或 SQL_MARS_ENABLED_NO,而預設值為 SQL_MARS_ENABLED_NO。SQL_COPT_SS_MARS_ENABLED has been added to accept either SQL_MARS_ENABLED_YES or SQL_MARS_ENABLED_NO, with SQL_MARS_ENABLED_NO being the default. 此外, 也加入了新的連接字串關鍵字Mars_ConnectionIn addition, a new connection string keyword, Mars_Connection, as been added. 它可接受 "yes" 或 "no" 值;預設值是 "no"。It accepts "yes" or "no" values; "no" is the default.

SQL Server Native Client ODBC 驅動程式範例SQL Server Native Client ODBC Driver Example

在此範例中, SQLSetConnectAttr函數是用來在呼叫SQLDriverConnect函式來連接資料庫之前啟用 MARS。In this example, the SQLSetConnectAttr function is used to enable MARS before calling the SQLDriverConnect function to connect the database. 一旦建立連接, 就會呼叫兩個SQLExecDirect函數, 以在相同的連接上建立兩個不同的結果集。Once the connection is made, two SQLExecDirect functions are called to create two separate result sets on the same connection.

#include <sqlncli.h>  
  
SQLSetConnectAttr(hdbc, SQL_COPT_SS_MARS_ENABLED, SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);  
SQLDriverConnect(hdbc, hwnd,   
   "DRIVER=SQL Server Native Client 10.0;  
   SERVER=(local);trusted_connection=yes;", SQL_NTS, szOutConn,   
   MAX_CONN_OUT, &cbOutConn, SQL_DRIVER_COMPLETE);  
  
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt1);  
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt2);  
  
// The 2nd execute would have failed with connection busy error if  
// MARS were not enabled.  
SQLExecDirect(hstmt1, L"SELECT * FROM Authors", SQL_NTS);  
SQLExecDirect(hstmt2, L"SELECT * FROM Titles", SQL_NTS);  
  
// Result set processing can interleave.  
SQLFetch(hstmt1);  
SQLFetch(hstmt2);  

另請參閱See Also

SQL Server Native Client 功能 SQL Server Native Client Features
使用 QL Server 預設結果集Using SQL Server Default Result Sets