SQL Server supports transaction-based access to distributed data by using the provider’s ITransactionLocal (for local transaction) and ITransactionJoin (for distributed transactions) OLE DB interfaces. By starting a local transaction against the provider, SQL Server guarantees atomic write operations. By using distributed transactions, SQL Server ensures that a transaction that involves multiple nodes has the same result (either commit or abort) in all the nodes. If the provider does not support the requisite OLE DB transaction-related interfaces, update operations against that provider are not allowed depending on the local transaction context.
The following table describes what happens when the user executes a distributed query. A read operation against a provider involves either a SELECT statement or reading a remote table into the input side of a SELECT INTO, INSERT, UPDATE, or DELETE statement. A write operation against a provider refers to an INSERT, UPDATE, or DELETE statement with a remote table as the destination table.
Distributed query occurs |
Provider does not support ITransactionLocal | Provider supports ITransactionLocal but not ITransactionJoin | Provider supports both ITransactionLocal and ITransactionJoin |
|---|---|---|---|
| In a transaction by itself (no user transaction). | By default, only read operations are allowed. When the provider level option Nontransacted Updates is enabled, write operations are allowed. (When this option is enabled, SQL Server cannot guarantee atomicity and consistency on the provider’s data. This can cause partial effects of a write operation to be reflected in the remote data source without the ability to undo them.) | All statements are allowed against remote data. Keyset cursors are read-only. The local transaction is started on the provider with the current SQL Server session’s isolation level and is committed at the end of successful statement evaluation. (The default isolation level for a SQL Server session is READ COMMITTED unless it is modified with the SET TRANSACTION ISOLATION LEVEL statement. The provider must support the requested isolation level.) |
All statements are allowed. Keyset cursors are read-only. The local transaction is started on the provider with the current SQL Server session’s isolation level and is committed at the end of a successful statement evaluation. |
| In a user transaction (that is, between BEGIN TRAN or BEGIN DISTRIBUTED TRAN and COMMIT). | If the isolation level of the transaction is READ COMMITTED (the default) or below, read operations are allowed.
If the isolation level is higher, no distributed queries are allowed. |
Only read operations are allowed.
New distributed transactions are started on the provider with the current SQL Server session’s isolation level. |
All statements are allowed. New distributed transaction is started on the provider with the current SQL Server session’s isolation level and committed when the user transaction commits. For data modification statements, by default SQL Server starts a nested transaction under the distributed transaction, so that the data modification statement can be stopped under certain error conditions without stopping the surrounding transaction. If the XACT_ABORT SET option is on, SQL Server does not require nested transaction support and stops the surrounding transaction in the case of errors during the data modification statement. |