Free MuleSoft CONNECT Keynote & Expo Pass Available!

Register now+
Nav

Database Transactions Reference

You can execute database operations in the context of a transaction. Each operation has a transactionalAction that specifies the type of joining action that operations can take regarding the active transaction if there is one. For example, the Select Operation has the following possible actions:

always join join if possible not supported
  • ALWAYS_JOIN: Expects a transaction to be in progress when a message is received. If there is no transaction, an error is raised.

  • JOIN_IF_POSSIBLE: Joins the current transaction if one is available. Otherwise, no transaction occurs.

  • NOT_SUPPORTED: Executes outside any existent transaction.

Grouping Operations

Sometimes you need to execute several queries atomically in the context of the same transaction. For example, during a bank account transfer, you need to subtract money from one account and add it in another, but if any of the two operations fail, both should be rolled back:


         
      
1
2
3
4
5
6
7
8
9
<db:update config-ref=db>
        <db:sql>UPDATE ACCOUNT set BALANCE = BALANCE - :money where ID = :source</db:sql>
        <db:input-parameters>#[{‘money’ : payload.money, ‘source’: payload.source}]</db:input-parameters>
</db:update>

<db:update config-ref=db>
        <db:sql>UPDATE ACCOUNT set BALANCE = BALANCE + :money where ID = :target</db:sql>
        <db:input-parameters>#[{‘money’ : payload.money, ‘target’’: payload.target}]</db:input-parameters>
</db:update>

If these queries are executed in the context of an already existing transaction, the queries belong to the same transaction. If there’s no active transaction, you can start one by using the <try> scope:


         
      
1
2
3
4
5
6
7
8
9
10
11
<try transactionalAction="ALWAYS_BEGIN">
        <db:update config-ref="db">
                <db:sql>UPDATE ACCOUNT set BALANCE = BALANCE - :money where ID = :source</db:sql>
                <db:input-parameters>#[{'money' : payload.money, 'source': payload.source}]</db:input-parameters>
        </db:update>

        <db:update config-ref="db">
                <db:sql>UPDATE ACCOUNT set BALANCE = BALANCE + :money where ID = :target</db:sql>
                <db:input-parameters>#[{'money' : payload.money, 'target'': payload.target}]</db:input-parameters>
        </db:update>
</try>

In this topic: