Home >Backend Development >PHP Tutorial >Distributed query and distributed transaction_PHP tutorial
Distributed query and distributed transaction_PHP tutorial
- WBOYOriginal
- 2016-07-13 17:01:151017browse
Microsoft® SQL Server™ allows you to create links to OLE DB data sources called linked servers. After linking to an OLE DB data source, you can:
Reference rowsets from the OLE DB data source as tables in Transact-SQL statements.
Pass the command to an OLE DB data source and include the resulting rowset as a table in a Transact-SQL statement.
Each distributed query can reference multiple linked servers, and each linked server can perform update or read operations separately. A single distributed query can perform read operations on some linked servers and update operations on other linked servers. Typically, whenever a transaction may update data in multiple linked servers, Microsoft SQL Server requires that the corresponding OLE DB provider supports distributed transactions. Therefore, the types of queries supported on a linked server depend on the level of support for transactions in the OLE DB provider. OLE DB defines two optional interfaces for transaction management:
ITransactionLocal supports local transactions in OLE DB data sources.
ITransactionJoin allows providers to join distributed transactions involving other resource managers.
All providers that support ITransactionJoin also support ITransactionLocal.
If a distributed query is executed when the connection is in autocommit mode, the following rules apply:
For providers that do not support ItransactionLocal, only read operations are allowed.
All update operations are allowed for providers that support ITransactionLocal.
The master SQL Server will automatically call ITransactionLocal in each linked server participating in the update operation to start a local transaction and commit it if the statement execution succeeds or roll back if the statement execution fails.
If a distributed query is executed against a distributed partitioned view or when the join is an explicit or implicit transaction, the following rules apply:
For providers that do not support ITransactionJoin, only read operations are allowed. Providers that do not support any transactions or only support ITransactionLocal cannot participate in update operations.
If SET XACT_ABORT is set to ON, all update operations are allowed for any provider that supports ITransactionJoin. The master SQL Server automatically calls ITransactionJoin on each linked server participating in the update operation to enlist the server in the distributed transaction. Then when the master server indicates that it wants to commit or rollback the transaction, MS DTC will commit or rollback.
If SET XACT_ABORT is set to OFF, the linked server must also support nested transactions before update operations can be performed on it. Nested transactions are supported if the provider supports calling ITransactionLocal::StartTransaction when the session already has an existing transaction. This enables SQL Server to roll back individual statements within a distributed query rather than rolling back the entire transaction.
The above rules mean that nested transactions are not supported by the following limitations of the provider: Update operations are only allowed in distributed transactions when the XACT_ABORT option is set to ON.