A single business process often writes more than one table. An invoice has a header and items; a goods receipt has a document and stock movements. If some writes succeed while another fails, the database may retain an incomplete state. An atomic transaction is designed to prevent that outcome within the boundary of one database connection.
What does “atomic” mean in a database transaction?
Atomic means a set of operations is treated as one unit. The database begins a transaction, performs the writes, and commits only when every operation succeeds. If a constraint, type, or connection error occurs before commit, changes made inside that transaction are rolled back.
This is different from merely running nodes in sequence. Ordering ensures the header is attempted before items, but does not automatically remove the header when the third item fails. The rollback guarantee comes from an active database transaction on the same connection, not from the lines drawn on a workflow canvas.
When should multiple writes share one transaction?
Start with the business invariant: what state must never be partially visible? An invoice without items may be invalid. A stock transfer that deducts the origin without adding the destination is also problematic. A separate audit record, however, might be allowed to fail without cancelling the main transaction when the organization has another reconciliation mechanism.
| Question | If “yes” | Implication |
|---|---|---|
| Do records depend on each other? | Group them | Use one transaction unit |
| Are all writes on one connection? | ACID is possible | Commit or roll back together |
| Is an API or email involved? | Outside DB ACID | Use idempotency and reconciliation |
| Are databases different? | Not a local transaction | Consider a Saga |
Example: an invoice header-detail workflow
- Validate the payload.
Ensure the header and every item satisfy the contract before writes begin.
- Create execution identity.
A temporary UUID can serve as the same invoice ID for the header and child items.
- Write the header first.
Order matters when items have a foreign key to the header.
- Iterate through items.
For Each carries parent fields and the active item into detail mapping.
- Commit one batch.
Writes to the same agent and database connection are collected as an atomic batch.
NitroNet groups synchronous writes by agent and database connection. The agent executes the batch with begin, commit, or rollback. If a duplicate detail or another constraint fails, the entire batch in that group is rolled back, and error information points to the relevant item and node.
Test rollback, not only the happy path
The first test confirms the header and every item are saved. The next test should deliberately fail a middle item through a duplicate key, invalid foreign key, null required value, or check constraint. After receiving a failure response, inspect the database directly and confirm that no partial header or items remain.
Also retry the request. An atomic transaction protects one attempt but does not make the endpoint idempotent by itself. If an agent or connection is interrupted after the destination processes the operation but before status returns, a retry still needs a stable business key or idempotency key.
Common design mistakes
- Assuming every node in one workflow automatically shares a transaction.
- Including another database or external API in an ACID rollback claim.
- Generating a new UUID inside the item loop so child foreign keys differ from the header.
- Handling an item error while returning success for the overall transaction.
- Failing to inspect database state after a negative test.
Isolation and concurrency also affect outcomes. Two executions can pass an existence check and then compete for the same unique key or update one record. Database constraints remain necessary even when the workflow checked first. For sensitive updates, use a version field or condition that detects changes since reading, then return an actionable conflict instead of silently overwriting.
Keep transaction duration short. Do not hold a database transaction while waiting for an external API, email, human approval, or long delay. Perform safe non-database work before begin, then group local mutations into a short batch. After commit, run external effects through a separate delivery mechanism with idempotency. This reduces prolonged locks and makes the consistency boundary visible to operators.
Make transaction boundaries visible
Writes to other connections use Saga compensation. HTTP and email actions are at-least-once and cannot be rolled back with the database.
Atomic transactions reduce partial-data risk but do not replace validation, idempotency, and observability. Use Sandbox to verify failure paths with test data. Continue with choosing database operations and NitroNet's agent-centric flow before designing a multi-table process.