One Transaction or Two
Splitting a unit of work trades an atomicity guarantee for shorter locks — and buys you an intermediate state you now have to design.
The requirement, the obvious build, and why it breaks
Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.
Should this be one long transaction or two short ones, and what do I owe the state in between?
Importing a supplier catalogue writes 50,000 product rows. It currently runs as one transaction, and while it runs, everything else that touches products slows down.
Keep it as one transaction. It is a single logical operation, and splitting it means the import can end up half-applied.
A transaction that writes 50,000 rows holds every one of those row locks until it commits, so any concurrent update to a touched row waits for the whole import (Low CPU, High Latency: Lock Contention).
- A transaction that writes 50,000 rows holds every one of those row locks until it commits, so any concurrent update to a touched row waits for the whole import (Low CPU, High Latency: Lock Contention).
- It holds a pooled connection for the entire duration, removing capacity from request traffic (Connection Pools).
- On Postgres it holds back the vacuum horizon for its whole lifetime, so dead row versions accumulate across the database and unrelated queries get slower (MVCC: Multi-Version Concurrency Control).
- If it fails at row 49,000, all the work is discarded and the retry starts from zero — and takes just as long to fail again.
- The undo/WAL volume for one enormous transaction is a replication and disk problem as well as a locking one (Replication Lag: Reads That Are Correct and Stale).
What is actually happening
- One transaction gives you a single atomic outcome and holds all its resources until the end. Cost grows with duration and with the number of rows touched.
- Two or more transactions release locks between them, so other work proceeds — and create an intermediate state that is visible to everyone else.
- Splitting therefore converts an atomicity guarantee into a design obligation: the intermediate state must be legal, observable and recoverable.
- Recovery for a split unit of work is either resumability (record progress, continue where you stopped) or compensation (undo what was done, with a write that can itself fail) (Saga Pattern).
- Batched work is the common middle: N small transactions with a recorded high-water mark, so a failure loses one batch rather than all of them.
- Some operations cannot be split at all, because their invariant spans them. Moving money between two accounts is the canonical example: one transaction, and no negotiation.
The test: does the invariant span the parts?
Splitting is not a performance technique you apply everywhere. It is legitimate exactly when no rule is violated by the intermediate state. That is a question about the domain, not about the database.
A transfer between accounts fails the test: at every moment, total balance must be unchanged, so debit and credit are one transaction. A catalogue import passes it: a partially imported catalogue is a normal state that can be named, observed and resumed. Same mechanism, opposite answers, and the deciding input is the invariant.
| Unit of work | Invariant spans the parts? | Shape |
|---|---|---|
| Debit account A, credit account B | Yes — totals must always balance | One transaction, no exceptions |
| Create order + its line items | Yes — an order with no items is invalid | One transaction |
| Create order + send confirmation | No — a pending notification is normal | Transaction, then a durable job |
| Import 50,000 products | No — partial import is a nameable state | Batched transactions with a cursor |
| Create user + provision workspace | No, if a user without a workspace is legal | Two transactions plus a state column |
| Reserve stock + capture payment | Cannot — different systems | Reservation with expiry, then capture (Saga Pattern) |
Batching with a cursor
session.begin() as a context manager commits on clean exit and rolls back on exception. Django's equivalent is transaction.atomic() with select_for_update(). The structural point — cursor written inside the same bracket — is framework-independent.For bulk work the useful shape is neither one transaction nor N independent ones. It is N bounded transactions with the progress marker written inside the same transaction as the work, so progress and work can never disagree.
That single detail — cursor in the same bracket as the rows — is what makes the job resumable. If the cursor were updated separately, a crash between the two would either replay work or skip it.
1BATCH = 5002 3while True:4 with session.begin(): # one transaction per batch5 job = session.get(ImportJob, job_id, with_for_update=True)6 rows = fetch_batch(job.cursor, BATCH)7 if not rows:8 job.status = 'done'9 break10 11 for r in rows:12 upsert_product(session, r) # idempotent by natural key13 14 job.cursor = rows[-1].supplier_sku # progress committed WITH the work15 16 time.sleep(0.05) # yield connections to request trafficThree properties: the batch is bounded so locks are held briefly, the cursor commits atomically with the rows it covers so a crash resumes exactly once, and the upsert is idempotent so replaying the last batch is harmless. The with_for_update lease stops two runners processing the same job.
What the intermediate state owes you
The moment you split, the system gains a state it did not have. Treating that state as an implementation detail is how a rare crash becomes a support ticket nobody can explain six weeks later.
Give it a name in the schema, a meaning in the API, an expected maximum age, and a path out — resume or clean up. Then monitor the age. A split unit of work with no monitoring on its intermediate state is a background failure waiting to be discovered by a customer.
How to build it
Most important first.
- Split when the invariant does not span the parts. Do not split when it does — a debit and a matching credit are one transaction, however long the queue is.
- For bulk work, batch: a bounded number of rows per transaction, a persisted cursor, and a pause between batches so other traffic gets connections.
- Design the intermediate state as a real state, with a name and a column:
import_status = 'in_progress',order_status = 'awaiting_payment'. Unnamed intermediate states are how "impossible" rows appear (Resources Have State Machines). - Make every step idempotent so a resumed or retried run cannot double-apply (Idempotency in Backends).
- Prefer resumability over compensation. Undoing is a write that can fail, which means a compensation needs a compensation.
- Bound the intermediate state in time and monitor it: rows stuck in
in_progressfor longer than expected are the alert that a split unit of work needs.
What can go wrong
- A crash between the two transactions leaving a state no code handles, because the state was never named.
- Compensation that fails, leaving the system in the state that both the forward path and the undo path were supposed to prevent.
- Batches that are individually fine but collectively unbounded, so the import runs for hours and the intermediate state is normal for most of the day.
- A reader that sees the half-finished import and treats it as complete — a report, an export, a sync to another system.
- Splitting something whose invariant genuinely spanned both parts, discovered as a data integrity bug months later.
- Retrying a batch that partially committed because it was not idempotent, doubling rows.
- Another request reads the intermediate state and acts on it. Not a bug in itself — a case that must be specified.
- Two runs of the same split unit of work overlapping, each resuming from its own view of the cursor. Guard with a lease or an advisory lock (Pessimistic Locking).
- Compensation racing the forward path when a slow step finally completes after the timeout that triggered the undo (Timeouts).
- Intermediate states can be permission-relevant. A user half-added to an organisation must be treated as not-a-member, never as a member (Object-Level Authorization).
- Fail closed at every intermediate state: an incomplete authorization change must deny, not allow (Defence in Depth).
- Long-lived intermediate rows are an audit obligation — who started it, when, and what was applied so far (Audit Logs for Privileged Actions).
- "Shorter transactions are always better." Not when the invariant spans them. A transfer split in two is a system that can lose money.
- "If we split it we lose atomicity." You lose atomicity across the parts and keep it within each. Often that is exactly enough.
- "We will just compensate on failure." Compensation is a forward write that can fail; it is not an undo. Prefer resumability where you can get it.
- "The intermediate state is temporary, so it does not need handling." Under load and after crashes it is not temporary at all — it is a state the system spends real time in.
Operating it
- Age of the oldest row in each intermediate state. This is the health metric for any split unit of work.
- Progress metrics for batched jobs: batches completed, rows remaining, last cursor position.
- Longest-running transaction on the database, alerted above a threshold. It catches both the unsplit bulk job and the leaked bracket (Low CPU, High Latency: Lock Contention).
- Compensation execution count, which should be near zero and is worth an alert when it is not.
- The bigger the dataset, the less viable one transaction becomes — this is one of the few decisions that genuinely flips as a pure function of row count.
- At higher concurrency, shorter transactions win even when the total work is the same, because contention is superlinear in duration (Queueing: Why Systems Get Slow Before They Get Broken).
- At very large scale bulk work often stops being transactional at all: write to a new table, then swap it in with a rename, which is one fast metadata change.
- Two transactions buy throughput and cost you a state machine, monitoring and recovery code. That is the trade, stated plainly.
- Batching costs total wall-clock time — it is slower end to end and much friendlier to everything else running.
- Named intermediate states cost schema and API surface: every consumer now has to handle a status they did not have before (Resources Have State Machines).
Where this applies
Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.
- GENERALThe atomicity-versus-duration trade and the obligation to design the intermediate state hold everywhere.
- SCALE-SPECIFICBelow a few thousand rows, one transaction is almost always right and the split is premature complexity. The advice inverts as row count and concurrent traffic rise; there is no fixed threshold, which is why "longest transaction" is a metric worth having.
- DATABASE-SPECIFICThe penalty for a long transaction differs in kind. Postgres holds back the vacuum horizon, so bloat and slower scans appear database-wide, not just on the touched table. InnoDB grows its undo history, which lengthens the version chains that consistent reads must walk. Both punish duration; the graphs you watch are different (MVCC: Multi-Version Concurrency Control).
Where the depth lives
This domain teaches the application-side mechanism and hands the rest off.