Distributed Transaction Management (DTM)

A distributed, or external, transaction is managed by an external transaction coordinator, such as XA Transaction Manager (TM).

In versions of Adaptive Server earlier than 15.0.3, user applications determined whether an external transaction was rolled back while executing DML commands. If Adaptive Server implicitly aborted an external transaction without the application’s knowledge, DML commands that would normally run inside this transaction might instead be executed inside an implicit transaction started by Adaptive Server. This behavior could result in inconsistent business data.

In versions 15.0.3 and later, Adaptive Server does not allow any DML commands to be executed on the connection attached to the external transaction until the transaction manager sends a detach request. The detach request indicates the end of a batch of commands intended for the external transaction.

In all versions, the steps Adaptive Server takes to execute an external transaction are:

The TM initiates a begin transaction.
The TM initiates an attach transaction.

The TM might perform steps 1 and 2 together.
The application executes DML commands.
The TM initiates a detach transaction.
Repeat steps 2 through 4, if necessary.
The TM initiates a prepare transaction, if the transaction is not rolled back.
The TM initiates a commit transaction or a rollback transaction.

Executing step 3 can cause the distributed transaction to roll back.

Because it is cumbersome to check the global variable before issuing every command, many user applications do not check it at all. Before version 15.0.3, if the distributed transaction rolled back, Adaptive Server allowed the user application to continue issuing SQL commands. These commands executed outside the distributed transaction as independent transactions. A SQL command that should have been included in a rollback transaction could be committed independently of that transaction, causing transactionally inconsistent data.

In versions 15.0.3 and later, Adaptive Serve automatically prevents SQL commands that are intended to execute inside a distributed transaction from executing outside it. The user application no longer has to check the global variable before every command; when a transaction is implicitly aborted, an error message (3953) appears, saying “Cannot execute the command because the external transaction has been rolled back.” This message disappears when a detach transaction command is issued.

To suppress the 3953 error messages and let Adaptive Server restore the former behavior, executing SQL commands even if the DTM transaction is not active, start Adaptive Server using trace flag -T3955.