Contact Free trial Login

Database Transactions Reference - Mule 4

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.

Group 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, roll back both:

<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:

<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>

We use cookies to make interactions with our websites and services easy and meaningful, to better understand how they are used and to tailor advertising. You can read more and make your cookie choices here. By continuing to use this site you are giving us your consent to do this.