Distributed Queries and Distributed Transactions
The SQL Server Database Engine allows you to create links to OLE DB data sources called linked servers. After linking to an OLE DB data source, you can:
Reference row sets from the OLE DB data sources as tables in Transact-SQL statements.
- Pass commands to the OLE DB data sources and include the resulting row sets as tables in Transact-SQL statements.
Each distributed query can reference multiple linked servers and can perform either update or read operations against each individual linked server. A single distributed query can perform read operations against some linked servers and update operations against other linked servers. In general, the Database Engine requires distributed transactions support from the corresponding OLE DB provider whenever data from more than one linked server are likely to be updated in a transaction. Hence, the types of queries that are supported against linked servers depend on the level of support for transactions present in the OLE DB providers. OLE DB defines two optional interfaces for transaction management:
- ITransactionLocal supports local transactions in the OLE DB data source.
- ITransactionJoin lets the provider join a distributed transaction that includes other resource managers.
Any provider that supports ITransactionJoin also supports ITransactionLocal.
If a distributed query is executed when the connection is in autocommit mode, these rules apply:
- Only read operations are allowed against providers that do not support ITransactionLocal.
- All update operations are allowed against any providers that support ITransactionLocal.
The controlling instance of the Database Engine automatically calls ITransactionLocal in each linked server participating in an update operation to start a local transaction. It commits them when the statement succeeds or rolls them back if the statement fails.
If a distributed query is against a distributed partitioned view, or if it is executed when the connection is in either an explicit or implicit transaction, these rules apply:
- Only read operations are allowed against providers that do not support ITransactionJoin. Providers that do not support any transactions or only support ITransactionLocal cannot participate in update operations.
- If SET XACT_ABORT is ON, all update operations are allowed against any providers that support ITransactionJoin. The controlling instance of the Database Engine automatically calls ITransactionJoin in each linked server participating in an update operation to enroll it in the distributed transaction. Microsoft Distributed Transaction Coordinator (MS DTC) then either commits them or rolls them back when the controlling server indicates that the transaction is either committed or rolled back.
- If SET XACT_ABORT is OFF, the linked server must also support nested transactions before update operations are allowed. Nested transactions are supported if the provider supports calling ITransactionLocal::StartTransaction when there is already an existing transaction for the session. This allows SQL Server to roll back individual statements in distributed queries without rolling back the entire transaction.
The above rules imply the following restriction for providers that do not support nested transactions: Update operations are allowed in a distributed transaction only if the XACT_ABORT option is ON.