Sql transaction

Runs everything connected to its Output pin inside one database transaction. If all downstream components finish without error the transaction is committed and Continue is called; if any of them throws, the transaction is rolled back and the error is raised to the caller.

Pins

Pin	Kind	Types	Description
Input	input	any	Starts the transaction.
Output	output	same as input	Components that must run as one transaction (Table writer, Sql query, Routine call, ...).
Continue	continue output	same as input	Called with the input record after a successful commit.

Configuration

Field	Type	Default	Description
IsolationLevel	dropdown: -1 Default, 256 ReadUncommitted, 4096 ReadCommitted, 65536 RepeatableRead, 1048576 Serializable, 16777216 Snapshot	-1	Isolation level of the transaction. -1 uses the database default.

Behaviour

• On each input a transaction is opened, Output is called synchronously, and the transaction is committed when the whole downstream chain has returned. Database components in that chain get one connection per database from it, so all their statements belong to the transaction.
• On an exception anywhere downstream every database is rolled back and the exception is re-thrown; Continue is not called. If a rollback itself fails, the original error is still the one raised and the rollback failure is logged.
• Each input record gets its own transaction. Messages running in parallel in the same flow (HTTP requests, several start components) never share a transaction.
• Two database links that point to the same database (same connection string) share one connection and one transaction, so they don't block each other.

Several databases

The commit runs database by database, in the order they were first used (one phase, no prepare step). If a commit fails before anything was committed, everything is rolled back and the database error is raised. If it fails after an earlier database already committed, the rest is rolled back and a PartialCommitException is raised that names the committed links, the failed link and the rolled back links: the committed changes stay and need a manual correction. Put the database most likely to fail first in the chain.

Notes

• Only work done synchronously in the Output chain is covered. Components that buffer and write later (Bulk insert auto flush, timers) run without transaction.
• Fork branches don't wait for the transaction and run without it. For each with ParallelRun runs its items one after another inside a transaction, because a connection can't run commands in parallel.
• Nesting: an inner Sql transaction joins the outer one; the outer one commits or rolls back all of it, and the inner Continue is called when its Output chain returns. The inner isolation level must be Default or the same as the outer one, otherwise the inner component raises an error.
• Keep transactions short: long-running calls (HTTP, file I/O) inside Output hold database locks.
• Snapshot must be enabled on the database (SQL Server) before it can be used. Oracle supports only ReadCommitted and Serializable. ClickHouse has no transactions and is refused.

Example

{ "IsolationLevel": 4096 }

Http listener → Sql transaction → (Output) Table writer (order) → Table writer (order lines); (Continue) Http response