---
id: database--database-transaction
title: "Sql transaction"
summary: "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."
audience: user
keywords: "input, output, continue, isolationlevel"
---
# 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

```json
{ "IsolationLevel": 4096 }
```

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