Transactions and Threads of Control

SAP ASE provides native support for the “suspend” and “join” semantics used by X/Open XA-compliant transaction managers such as Encina and TUXEDO. Transactions may be shared among different threads of execution, or may have no associated thread at all.

When a transaction has no thread associated with it, it is said to be “detached.” Detached transactions are assigned a spid value 0. You can see the transaction spid value in the new master.dbo.systransactions table, or in output from the new sp_transactions procedure.

Detached transactions are meant to persist in SAP ASE, since the client application may want to reattach the original thread, or attach a new thread to the transaction. The system administrator can no longer roll back a transaction by killing its associated spid, as a thread is not attached to the transaction.

Transactions in a detached state may also prevent the log from being truncated with the dump transaction command. In extreme circumstances, detached transactions can be rolled back by using the new dbcc complete_xact command to heuristically complete a transaction.

The system administrator can also specify a server-wide interval after which SAP ASE automatically rolls back transactions that are in the detached state. dtm detach timeout period sets the amount of time, in minutes, that a distributed transaction branch can remain in the detached state. After this time has passed, SAP ASE rolls back the detached transaction.

For example, to automatically rollback detached transactions after 30 minutes, use:

sp_configure 'dtm detach timeout period', 30