Managing Transactions (XMLA)
Every XML for Analysis (XMLA) command sent to an instance of Microsoft SQL Server SQL Server Analysis Services runs within the context of a transaction on the current implicit or explicit session. To manage each of these transactions, you use the BeginTransaction, CommitTransaction, and RollbackTransaction commands. By using these commands, you can create implicit or explicit transactions, change the transaction reference count, as well as start, commit, or roll back transactions.
Implicit and Explicit Transactions
A transaction is either implicit or explicit:
Implicit transaction
SQL Server Analysis Services creates an implicit transaction for an XMLA command if the BeginTransaction command does not specify the start of a transaction. SQL Server Analysis Services always commits an implicit transaction if the command succeeds, and rolls back an implicit transaction if the command fails.
Explicit transaction
SQL Server Analysis Services creates an explicit transaction if the BeginTransaction command starts of a transaction. However, SQL Server Analysis Services only commits an explicit transaction if a CommitTransaction command is sent, and rolls back an explicit transaction if a RollbackTransaction command is sent.
In addition, SQL Server Analysis Services rolls back both implicit and explicit transactions if the current session ends before the active transaction completes.
Transactions and Reference Counts
SQL Server Analysis Services maintains a transaction reference count for each session. However, SQL Server Analysis Services does not support nested transactions in that only one active transaction is maintained per session. If the current session does not have an active transaction, the transaction reference count is set to zero.
In other words, each BeginTransaction command increments the reference count by one, while each CommitTransaction command decrements the reference count by one. If a CommitTransaction command sets the transaction count to zero, SQL Server Analysis Services commits the transaction.
However, the RollbackTransaction command rolls back the active transaction regardless of the current value of the transaction reference count. In other words, a single RollbackTransaction command rolls back the active transaction, no matter how many BeginTransaction commands or CommitTransaction commands were sent, and sets the transaction reference count to zero.
Beginning a Transaction
The BeginTransaction command begins an explicit transaction on the current session and increments the transaction reference count for the current session by one. All subsequent commands are considered to be within the active transaction, until either enough CommitTransaction commands are sent to commit the active transaction or a single RollbackTransaction command is sent to roll back the active transaction.
Committing a Transaction
The CommitTransaction command commits the results of commands that are run after the BeginTransaction command was run on the current session. Each CommitTransaction command decrements the reference count for active transactions on a session. If a CommitTransaction command sets the reference count to zero, SQL Server Analysis Services commits the active transaction. If there is no active transaction (in other words, the transaction reference count for the current session is already set to zero), a CommitTransaction command results in an error.
Rolling Back a Transaction
The RollbackTransaction command rolls back the results of commands that are run after the BeginTransaction command was run on the current session. The RollbackTransaction command rolls back the active transaction, regardless of the current transaction reference count, and sets the transaction reference count to zero. If there is no active transaction (in other words, the transaction reference count for the current session is already set to zero), a RollbackTransaction command results in an error.