多重集表示合json数据_使用多重活动结果集 (MARS) - SQL Server Native Client | Microsoft Docs...

在 SQL Server Native Client (MARS) 使用多个活动的结果集Using Multiple Active Result Sets (MARS) in SQL Server Native Client

08/08/2017

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics 并行数据仓库Parallel Data Warehouse并行数据仓库Parallel Data Warehouse适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics 并行数据仓库Parallel Data Warehouse并行数据仓库Parallel Data Warehouse

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 在访问 数据库引擎Database Engine 的应用程序中引入了对多重活动结果集 (MARS) 的支持。introduced support for multiple active result sets (MARS) in applications accessing the 数据库引擎Database 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、带 OUTPUT 的 DML、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. 若要在通过 Native Client 连接到时使用 MARS SQL ServerSQL Server SQL ServerSQL Server ，必须在连接字符串中专门启用 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. For more information about batch-scoped transactions, see Transaction Statements (Transact-SQL).

内存中 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.

每个语句都是在 SNAPSHOT 隔离下运行，因此新操作看不到现有语句所做的更改。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

由交错的语句和 ATOMIC 块所做的更改相互隔离。Changes made by statements and atomic blocks that are interleaved are isolated from each other. 例如，如果一个语句或 ATOMIC 块进行了一些更改，然后将执行机会转让给另一个语句，那么新语句就看不到第一个语句所做的更改。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 语句可以在当前用户事务中启动新的用户事务-此操作仅在互操作模式下受支持，因此只能从 T-sql 语句调用 BEGIN TRANSACTION，而不能从本机编译的存储过程中调用。您可以使用 SAVE TRANSACTION 或对 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 ServerNative 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. 此外，还添加了新的连接字符串关键字 MarsConn 。In addition, a new connection string keyword, MarsConn, as been added. 它接受值 true 或 false ；默认值为 false 。It 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 提供程序创建的，并且在创建 session 对象之前使用 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

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 驱动程序通过向SQLSetConnectAttr和SQLGetConnectAttr函数添加内容支持 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_Connection。In 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

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