The XA environment treats each thread or process that works on a transaction as a transaction branch. Each transaction branch is assigned a different xid and works independently of the other branches. However, all branches are committed or rolled back as a unit. This applies to MTS/COM+ environments only, and only if you are using ctlib-based drivers that are older than version 3.6.
Some TMs allow branches to be tightly coupled. Tightly coupled branches are assigned the same xid and work together on the transaction. In such cases the open string can contain the -O1 option. This option causes Adaptive Server to move the work among connections on demand and eliminates any lock that might otherwise occur between the connections. See your TM documentation to determine how the TM can be configured for tightly coupled operation.
WARNING! Set the -O1 option only when the application design is guaranteed to avoid conflicting updates. Normally this is true only when the application branches are fully serialized, (branch B operates only after branch A completes). Data inconsistency may occur if the interaction of the tightly coupled branches is not well designed.
Without the -O1 option, attempts by the branches to update the same database row can result in a deadlock internal to the transaction. The -O1 option has no practical effect when the branches are not tightly coupled through the TM and are assigned different xids.
WARNING! Cursors and dynamic SQL cannot be retained when the transaction is assigned to a different connection. Therefore, they should not be used unless the application structure guarantees that they are opened and closed during a period when no other branch will work on the transaction.
The transaction is reassigned to another connection
only between batches. A tightly
coupled application can ensure that a set of operations is completed
without conflict by performing all the operations in a single batch. This
implies that operations within a single stored procedure are also completed
without conflict.
For example, if row z in table B must contain the sum of rows x and y in table A. The following can result in an invalid value in row z:
Branch 1: Branch 2
Updates Row x -> 5
Reads Row y (= 4)
Updates Row y -> 5
Reads Row x (= 5)
Updates Row z -> 10
Updates Row z -> 9 (wrong value)
No problem occurs if the branches are performed serially:
Branch 1: Branch 2 Updates Row x -> 5 Reads Row y (= 4) Updates Row z -> 9 Updates Row y -> 5 Reads Row x (=5) Updates Row z -> 10
A control branch can also be used to resolve the problem:
Branch 0: Branch 1: Branch 2 (controller) Starts Branches 1 and 2 Waits for both to complete Updates Row x Updates Row y -> 5 Terminates Terminates Reads Row y Reads Row x Updates Row z
TM specific branch control mechanisms must be used to implement these serialization mechanisms.
