Transactions & Concurrency

A database transaction groups several reads and writes so that they commit together or not at all, while the database controls what other concurrent transactions are allowed to see of that work. Inside one transactional database that gives you atomicity on failure and a chosen degree of isolation under concurrency. It does not make a multi-service workflow atomic, it does not make every replica current, and it does not make a client request execute exactly once.

The mental model: a transaction is a private draft plus a single publishing step. Writes go into a draft only your session can see, and COMMIT publishes the whole draft at one instant. Everything else in this lesson comes from two questions about that draft. What happens to it if something fails before publishing (atomicity, durability and the ambiguity of commit acknowledgements)? What may other sessions see, and what may they overwrite, while it is open (isolation, MVCC, locks and optimistic checks)?

The failures that matter in production are specific: a transfer that debits without crediting, a counter that loses an increment, a stock level that goes negative, a rota that ends up with nobody on call, a retry that charges a card twice. Each has a different mechanism behind it and a different fix, and a recurring theme is that the cheapest correct fix is usually the narrowest one. The sections below take them in order, with timelines, runnable SQL where the example is portable, and the engine-specific behaviour labelled so you can tell a general rule from a PostgreSQL or MySQL detail. If you want the storage machinery underneath (WAL, recovery, pages), the Database Foundations lesson covers it; this one assumes only that a commit is recorded in a durable log.

Moving 500 units from account A to account B takes two writes: debit A, credit B. The business rule is that either both land or neither does. If the process dies after the debit and before the credit, 500 units have left the system. A transaction closes that gap, because the database treats the pair as one unit and discards the half-done work if no commit arrived. The figure shows the same transfer in three situations.

-- PostgreSQL. Runnable as written.
CREATE TABLE account (
  id      bigint PRIMARY KEY,
  owner   text    NOT NULL,
  balance numeric(14,2) NOT NULL CHECK (balance >= 0)   -- the database refuses an overdraft
);
INSERT INTO account VALUES (1, 'A', 1000), (2, 'B', 250);

BEGIN;
UPDATE account SET balance = balance - 500 WHERE id = 1;   -- 1000 -> 500, visible only to this session
UPDATE account SET balance = balance + 500 WHERE id = 2;   -- 250  -> 750, visible only to this session
COMMIT;                                                    -- both become visible together, or neither does

-- If the first UPDATE would take the balance below zero, the CHECK constraint raises
-- error 23514 and the transaction is left aborted: issue ROLLBACK and nothing was applied.

In the first timeline, notice the bottom lane. Until COMMIT, other sessions keep reading A = 1000 and B = 250. After it they read A = 500 and B = 750. The sum is 1250 in both states and never anything else. That is the practical meaning of atomicity from the outside: there is no intermediate state to observe. In the second timeline the crashed session never wrote a commit record, so recovery has nothing to apply. In the third, the commit record was already in the log, so recovery finishes the job even though the data pages never reached disk.

The guarantee has a boundary that this example hides: A and B are two rows in one database. If they live in separate services or separate databases, a local SQL transaction on each side gives you two atomic halves and no atomic whole. A crash between the two commits leaves one side done. That case is covered in Distributed transaction boundaries below and, in depth, in the Saga Pattern lesson.

One habit follows directly from the draft-and-publish model: keep external side effects out of the open transaction. Calling a payment API, sending an email or publishing a message while rows are locked holds locks for the duration of a network call, and if the transaction later aborts or is retried, the side effect has already happened. Write the intent to an outbox table inside the transaction and let a relay deliver it after commit; the Outbox Pattern lesson shows how.

Atomicity means a transaction commits or rolls back as a unit. Consistency means transactions preserve constraints and application invariants when those invariants are correctly enforced: it is the odd one out, because the database can only check rules you declared (keys, foreign keys, CHECK constraints, triggers) and everything else is your code's responsibility. Isolation describes what concurrent transactions can observe of each other. Durability describes what survives failure after a successful commit, under the configured guarantees.

Read the bottom row of the figure as the part that gets lost in conversation. ACID does not imply that every business workflow is globally serializable, that every replica is current, or that every client request executes exactly once. Each property is also a bundle of settings, not a switch.

Durability shows this most clearly, and it is engine-specific. In PostgreSQL with fsync = on and the default synchronous_commit = on, COMMIT returns after the commit record has been flushed to the local WAL. Setting synchronous_commit = off returns earlier and can lose the most recently acknowledged commits in a crash, without corrupting the database. Setting it to wait for a synchronous standby adds a second copy before the acknowledgement, at the cost of commit latency and a dependency on that standby. MySQL InnoDB has the equivalent knob in innodb_flush_log_at_trx_commit. None of these settings is a replacement for backups; a replica faithfully replays an accidental DELETE as well. See Database Foundations for the log mechanics and Database Scaling for replication and recovery objectives.

Isolation is where the most engineering judgement goes, so the next sections spend their time there.

Database isolation levels define which anomalies a transaction may observe. An isolation anomaly is a schedule of interleaved statements whose outcome could not have happened if the transactions had run one at a time. The standard names are worth knowing, but they are best learned as timelines. A dirty read observes uncommitted data. A non-repeatable read observes a changed row on a later read in the same transaction. A phantom changes the set of rows matching a predicate. A lost update overwrites another transaction's work. Write skew, covered next, is the subtle one. Step through the first four:

The lost update is the one most often written by accident, because the SQL for each transaction looks reasonable. The trouble is the read-modify-write cycle: the application reads 1000 into memory, does arithmetic, and writes back an absolute value. The database has no way to know the number was derived from a read that went stale. Whether it can protect you depends on how the write is expressed and on the engine, which is why the answer is never just "use a higher isolation level".

-- PostgreSQL. Choosing an isolation level for one transaction.
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;   -- or READ COMMITTED (the default), SERIALIZABLE
SELECT balance FROM account WHERE id = 1;
-- ...
COMMIT;

-- Lost update, application read-modify-write: unsafe at READ COMMITTED.
--   SELECT balance FROM account WHERE id = 1;                  -- app reads 1000, computes 1100
--   UPDATE account SET balance = 1100 WHERE id = 1;            -- overwrites whatever committed meanwhile
--
-- Safe forms of the same intent:
UPDATE account SET balance = balance + 100 WHERE id = 1;      -- computed inside the row's lock

BEGIN;
SELECT balance FROM account WHERE id = 1 FOR UPDATE;          -- competing writers wait here
UPDATE account SET balance = 1100 WHERE id = 1;               -- safe because nobody else can have changed it
COMMIT;

Isolation level names are not portable

The SQL standard defines levels by which anomalies they permit, and engines implement them with different machinery, so the same name can mean different guarantees. Define the anomaly you must prevent, then validate the engine-specific behaviour from its documentation and, for anything important, with a test that interleaves two real sessions.

• PostgreSQL READ COMMITTED (the default) gives each statement a fresh snapshot. It prevents dirty reads and permits non-repeatable reads, phantoms and lost updates from read-modify-write. An UPDATE that hits a row another transaction has just changed waits, then re-evaluates its WHERE clause against the newest version, which is why balance = balance + 100 is safe. READ UNCOMMITTED behaves as READ COMMITTED.
• PostgreSQL REPEATABLE READ is snapshot isolation: one snapshot for the whole transaction, taken at its first query. It prevents non-repeatable reads and, stricter than the standard requires, phantoms. If you try to update a row that another transaction changed after your snapshot, you get error 40001 instead of silently overwriting, so it also prevents the lost update above. It does not prevent write skew.
• PostgreSQL SERIALIZABLE adds serializable snapshot isolation: the database tracks read-write dependencies between concurrent transactions and aborts one when it detects a dangerous cycle. It can abort transactions that would in fact have been fine (false positives), and for the guarantee to hold, the transactions touching the same data should all run at this level.
• MySQL InnoDB REPEATABLE READ (its default) behaves differently. Plain SELECT reads come from a snapshot established by the first read, but locking reads and UPDATE/DELETE operate on the latest committed rows and take next-key locks that block phantom inserts into the scanned range. A read-then-write of an absolute value can therefore still lose an update, and a transaction can see its own writes alongside snapshot data. InnoDB SERIALIZABLE is lock-based: it turns plain reads into shared locking reads when autocommit is off.

Write skew occurs when two transactions read a shared condition but update different rows, violating a cross-row invariant that neither violates alone. Take a hospital rota with the rule that at least one doctor stays on call. Alice and Bob are both on call. Each opens a transaction, counts the doctors on call, sees two, and marks herself or himself off call. Under snapshot isolation both counts come from snapshots taken before either commit, the two writes touch different rows so there is no write-write conflict, and both commit. Nobody is on call.

The figure shows the failing schedule and two of the fixes. The annotated bands are the visibility windows: T2's snapshot still shows two doctors on call after T1 has committed, which is exactly the isolation boundary doing what it promises and exactly why the check is stale. There are three ways to make the rule hold:

• SERIALIZABLE, with retries. The unmodified check-then-write is correct again, but any transaction may fail with SQLSTATE 40001 and the application must re-run it. In PostgreSQL the failure can appear on a statement or at COMMIT, so the retry must wrap the entire transaction. Index the predicates you read; without a usable index the database falls back to coarser predicate tracking and aborts more often.
• Take a lock that covers the invariant. Lock the rows the rule depends on, or a parent row that stands for them, before reading. It serializes only the transactions that share that parent, and the second one waits instead of failing. Two details matter: lock the shared row rather than just the doctor row you are about to change, and run it at READ COMMITTED so the post-lock read sees the first commit (a REPEATABLE READ snapshot taken before the wait would still be stale).
• Materialize the constraint. Keep a counter on the shift row with CHECK (on_call_count >= 1), and decrement it in the same transaction. The rule becomes a single-row constraint that the database enforces under any isolation level, at the price of keeping the counter in step on every code path that changes on_call.

-- PostgreSQL. Invariant: every shift keeps at least one doctor on call.
CREATE TABLE shift  (id int PRIMARY KEY);
CREATE TABLE doctor (
  id       int PRIMARY KEY,
  shift_id int     NOT NULL REFERENCES shift (id),
  name     text    NOT NULL,
  on_call  boolean NOT NULL
);
INSERT INTO shift VALUES (1);
INSERT INTO doctor VALUES (1, 1, 'Alice', true), (2, 1, 'Bob', true);

-- Option 1: SERIALIZABLE. Run the unmodified check-then-write; be ready to retry on SQLSTATE 40001.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctor WHERE shift_id = 1 AND on_call;       -- application: refuse if this is 1 or less
UPDATE doctor SET on_call = false WHERE id = 1;
COMMIT;                                                           -- may fail with 40001: retry the whole transaction

-- Option 2: serialize the rota with a row lock on the parent. Use READ COMMITTED (the default),
-- so that the count after the lock sees the other transaction's committed change.
BEGIN;
SELECT 1 FROM shift WHERE id = 1 FOR UPDATE;                      -- the second caller waits here
SELECT count(*) FROM doctor WHERE shift_id = 1 AND on_call;       -- application: refuse if this is 1 or less
UPDATE doctor SET on_call = false WHERE id = 1;
COMMIT;

-- Option 3: materialize the invariant as one row the database can check.
ALTER TABLE shift ADD COLUMN on_call_count int;
UPDATE shift SET on_call_count = (SELECT count(*) FROM doctor WHERE shift_id = shift.id AND on_call);
ALTER TABLE shift ALTER COLUMN on_call_count SET NOT NULL;
ALTER TABLE shift ADD CONSTRAINT on_call_count_min CHECK (on_call_count >= 1);
-- Add the CHECK only after the backfill: existing rows must already satisfy it, so a shift with no one
-- on call would need fixing first. Every code path that flips on_call must also move the counter
-- (one code path, or a trigger).

BEGIN;
UPDATE doctor SET on_call = false WHERE id = 1 AND on_call;       -- application: expect exactly 1 row
UPDATE shift  SET on_call_count = on_call_count - 1 WHERE id = 1; -- CHECK fails (23514) if this was the last doctor
COMMIT;
-- Concurrent callers queue on the shift row. At READ COMMITTED the second sees the new count and its
-- CHECK fails; at REPEATABLE READ or SERIALIZABLE it gets 40001 and retries. Both are safe.

The same shape shows up wherever a rule spans rows: two bookings for overlapping times on one resource, a username that must be unique across several tables, a budget shared by several line items. When the rule is a uniqueness rule, a unique index (or an exclusion constraint in PostgreSQL for overlaps) is better than any of the above, because it makes the database the single enforcement point.

Multi-version concurrency control keeps several versions of a row and gives each transaction visibility rules over them. An UPDATE does not overwrite in place; it creates a new version and marks the old one as ended by the updating transaction. A reader looks for the version that was committed as of its snapshot and ignores versions that were created later or that belong to a transaction that has not committed. That is why, in many engines, readers do not block writers and writers do not block readers. Drag the snapshot in the figure to see which version it reads.

The figure uses abstract commit times; PostgreSQL stores the creating and ending transaction ids on each tuple (xmin and xmax) and InnoDB keeps older versions in undo logs, but the visibility reasoning is the same. The second readout is the cost side. A version can only be removed once no snapshot can see it, so the oldest active snapshot decides what is reclaimable. A long-running report, a forgotten idle in transaction session or, in PostgreSQL, a stalled replication slot pins that horizon, dead versions pile up, tables and indexes bloat and scans slow down. Long snapshots delay version cleanup and increase storage pressure; they do not block anyone directly, which is why the damage appears gradually.

MVCC does not remove locks. Four kinds still matter:

• Row locks. Two writers to the same row serialize: the second waits for the first to commit or abort. SELECT … FOR UPDATE takes the same lock without writing, which is how you make competing read-modify-write cycles on existing rows queue up.
• Predicate and range locks. InnoDB takes gap and next-key locks that block inserts into a scanned range. PostgreSQL's SERIALIZABLE tracks predicate reads without blocking and aborts instead. The engine's choice decides whether you see waits or retries.
• Unique constraint checks. If two transactions insert the same key, the second waits for the first to finish, even under MVCC, because it cannot know whether the key will exist.
• Metadata locks. A schema change needs a strong table lock. In PostgreSQL a migration that waits behind one long transaction also queues every later query behind itself, so a harmless-looking ALTER TABLE can stall a service. Run migrations with a short lock_timeout and retry.

SELECT … FOR UPDATE serializes competing updates to existing rows, but it does not universally protect an absent row or an arbitrary cross-row invariant. If the row you are checking does not exist yet, the query returns nothing and locks nothing, so two sessions both proceed to insert. The snippet shows that trap and the fix, which is to let a unique constraint decide, plus the lock modifiers you will reach for in queue-style workloads.

-- PostgreSQL. Row-level locking reads.
SELECT * FROM job WHERE status = 'queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;  -- take work nobody else holds
SELECT * FROM account WHERE id = 1 FOR UPDATE NOWAIT;                                  -- error 55P03 instead of waiting
SET lock_timeout = '2s';                                                               -- bound every wait for a lock

-- "Insert if absent" with FOR UPDATE does NOT work: there is no row to lock.
--   SELECT 1 FROM username WHERE name = 'ana' FOR UPDATE;   -- returns 0 rows, locks nothing
--   INSERT INTO username (name) VALUES ('ana');             -- two sessions can both get here
-- Let a unique constraint arbitrate instead:
CREATE TABLE username (name text PRIMARY KEY);
INSERT INTO username (name) VALUES ('ana') ON CONFLICT (name) DO NOTHING;   -- 1 row inserted, or 0: someone else owns it

Indexes affect concurrency as well as speed. An update whose predicate has no useful index scans more rows, and in lock-based engines such as InnoDB it may lock every row it examines, not only the ones it changes, so an unindexed WHERE turns a point update into a table-wide contention source. See Database Indexing for how to check that the predicate uses an index.

Pessimistic locking prevents conflicts by making writers wait. Optimistic concurrency assumes conflicts are rare, does the work without a lock, and checks at write time that nothing changed. For a versioned record the check is a compare-and-swap: update only if the version matches the value you previously read.

-- Portable SQL (tested in SQLite; also valid PostgreSQL).
CREATE TABLE inventory (
  sku       text PRIMARY KEY,
  available integer NOT NULL CHECK (available >= 0),
  version   integer NOT NULL DEFAULT 0
);
INSERT INTO inventory VALUES ('A-42', 1, 12);

-- Compare-and-swap: apply only if nothing changed since this client read version 12.
UPDATE inventory
SET available = available - 1, version = version + 1
WHERE sku = 'A-42' AND available > 0 AND version = 12;
-- Affected rows = 1: done. Affected rows = 0: ambiguous, so diagnose with a read:
SELECT available, version FROM inventory WHERE sku = 'A-42';
--   no row                         -> unknown SKU
--   available = 0                  -> out of stock
--   available > 0, version <> 12   -> someone else won the race: retry with the new version

-- When the new value does not depend on anything the client read, drop the version check.
-- The guard alone is atomic and never fails for a reason you would have to retry:
UPDATE inventory SET available = available - 1 WHERE sku = 'A-42' AND available > 0;

If the affected row count is zero, the request must distinguish out-of-stock, version conflict and missing SKU as needed, and the zero alone cannot tell them apart. The diagnostic read does, as the figure shows. That read is advice for choosing a response, not a lock: the state can change again before you act on it, so the next write must carry its own guard.

This conditional update prevents overselling for a single counter row. It is not a general replacement for multi-row transactions: the moment the invariant spans two rows (a stock level and an order table, a budget and its line items), a single-row check cannot see the other row. Use a transaction, a lock on a row that covers the rule, or SERIALIZABLE, as in the write-skew section.

A version column is not always needed. In the stock example the guard available > 0 is already atomic: the engine evaluates it against the row it holds locked, and at READ COMMITTED a waiter re-evaluates it against the latest version. The version predicate adds nothing for correctness and makes the statement fail whenever any other change happened since your read, including a decrement that left plenty of stock. Version checks earn their place when the new value is computed in the application from what was read (an edited document, a price recalculated from several fields) so that a stale read must be rejected rather than merged.

The failure mode is contention. When many clients hammer one row, each round has one winner and everyone else rereads and retries. The model below is the worst case with perfectly synchronized clients, so treat the exact numbers as an upper-bound illustration; the quadratic shape is what to remember.

Beyond that, a retry has to reread state and preserve any request-level idempotency guarantee. Rereading matters because the decision (is there still stock? is this still the same price?) must be remade from current data, not replayed from the first attempt. Idempotency matters because a retried request that already partly succeeded must not be applied twice; the Idempotency lesson covers the keys and storage patterns. Both are the application's job; no isolation level supplies them.

Transactions can deadlock when they acquire locks in conflicting orders. Two transfers in opposite directions are the textbook case: one locks account 1 then wants 2, the other locks 2 then wants 1. Neither can proceed, and waiting longer does not help.

The database detects the wait-for cycle and aborts one participant, depending on the engine. PostgreSQL waits deadlock_timeout (one second by default) before running its detector, then aborts the transaction that found the cycle with SQLSTATE 40P01. InnoDB detects deadlocks immediately by default and rolls back the transaction it judges cheapest, reporting error 1213. The survivor continues; the victim must be re-run. The engine cannot fix the schedule for you, so prevention is the first line of defence:

• Keep transactions short, and never hold one open across a network call or user interaction.
• Acquire locks in a consistent order. For transfers, lock the lower account id first regardless of direction, as the second timeline shows.
• Index the predicates used by updates and deletes so the statement touches only the rows it means to.
• Bound the waiting that remains: lock_timeout, statement_timeout and, for forgotten sessions, idle_in_transaction_session_timeout.

Deadlocks, serialization failures and lock timeouts can still happen, so write the retry once, in one place. Retry only operations that are safe and bounded: re-run the whole transaction from BEGIN, on fresh reads, a small number of times, with jittered backoff so that the victims do not collide again in lock step (Retry & Backoff covers why). Do not retry constraint violations or syntax errors; they will fail identically. And do not retry a transaction that already performed an external side effect, because the retry repeats it. Use an outbox or another durable handoff so that the side effect is published only after the commit that owns it.

// Node.js with node-postgres ("pg"). Assumes the account table above plus:
//   CREATE TABLE transfer (operation_id text PRIMARY KEY, from_id bigint NOT NULL,
//                          to_id bigint NOT NULL, amount numeric(14,2) NOT NULL);
const { Pool } = require('pg');
const pool = new Pool();

const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

// 40001 serialization_failure and 40P01 deadlock_detected: the database aborted the transaction,
// so running it again from BEGIN is safe.
const RETRYABLE = new Set(['40001', '40P01']);
// No SQLSTATE: the connection died and the outcome is unknown. Retrying is safe here only
// because every attempt carries the same operationId.
const isConnectionLoss = (e) =>
  e.code === 'ECONNRESET' || e.code === 'EPIPE' || /terminated unexpectedly/i.test(e.message);

async function transferOnce({ operationId, from, to, amount }) {
  if (from === to) throw new Error('cannot transfer to the same account');
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // The unique primary key makes the operation id the arbiter of "has this already happened?".
    // A concurrent duplicate waits here until the first attempt commits or aborts.
    const claim = await client.query(
      'INSERT INTO transfer (operation_id, from_id, to_id, amount) VALUES ($1, $2, $3, $4) ' +
        'ON CONFLICT (operation_id) DO NOTHING',
      [operationId, from, to, amount]
    );
    if (claim.rowCount === 0) {
      await client.query('ROLLBACK');
      return { status: 'ALREADY_APPLIED' };
    }

    // Lock both rows in id order, whatever the direction of the transfer.
    await client.query('SELECT id FROM account WHERE id = ANY($1::bigint[]) ORDER BY id FOR UPDATE', [[from, to]]);

    const debit = await client.query(
      'UPDATE account SET balance = balance - $1 WHERE id = $2 AND balance >= $1',
      [amount, from]
    );
    const credit = await client.query('UPDATE account SET balance = balance + $1 WHERE id = $2', [amount, to]);
    if (debit.rowCount !== 1 || credit.rowCount !== 1) {
      await client.query('ROLLBACK'); // also removes the transfer row, so the id is not consumed
      return { status: 'REJECTED' }; // insufficient funds or unknown account
    }

    await client.query('COMMIT');
    return { status: 'APPLIED' };
  } catch (err) {
    try { await client.query('ROLLBACK'); } catch (_) { /* the connection may already be gone */ }
    throw err;
  } finally {
    client.release();
  }
}

async function transfer(args, { attempts = 4 } = {}) {
  for (let attempt = 1; ; attempt++) {
    try {
      return await transferOnce(args);
    } catch (err) {
      const retryable = RETRYABLE.has(err.code) || isConnectionLoss(err);
      if (!retryable || attempt >= attempts) throw err;
      await sleep(Math.random() * 20 * 2 ** attempt); // jittered, bounded backoff
    }
  }
}

// transfer({ operationId: 'op-7f3', from: 1, to: 2, amount: 500 })
//   .then(console.log) // { status: 'APPLIED' }, or { status: 'ALREADY_APPLIED' } on a repeat

Three choices in that code are deliberate. The operation id is claimed first, inside the transaction, so a repeat is answered from the database rather than re-applied. The rows are locked in id order before any write. And only SQLSTATEs that mean "the database aborted this transaction" are retried by default; connection loss is retried too, which is safe only because of the operation id, and the next section explains why that case needs special care. A rejected transfer rolls back the claim row, so the id is not consumed; if you want a rejection to be a stored, repeatable answer, persist a status row instead.

A client sends COMMIT, the server commits, and the connection breaks before the acknowledgement reaches the client. The client sees an error, and a transport error does not say whether the transaction committed: the same symptom also arises when the COMMIT never reached the server. The client cannot infer the outcome from the error. This is the transactional version of the timeout ambiguity that appears in every network protocol, and it is the point where a retry becomes dangerous.

The first timeline is what a blind retry does: it double-charges, duplicates orders or sends a second email, because the database sees two unrelated requests. The fix is to make the outcome queryable. Give each logical operation a stable operation id generated by the caller before the first attempt, and record it in a column under a unique constraint in the same transaction as the business change. A retry then either applies the operation (the first one never committed) or hits the constraint and reads back the stored result (it did). Both outcomes are right, which is the property you want, and it is what the transfer function above implements. Otherwise reconcile against authoritative state some other way, such as reading the balances or querying the downstream system by reference.

Some engines can also report on a specific transaction. PostgreSQL 13 and later let you read the transaction id inside the transaction and ask later whether it committed, with the limitation that you must have captured the id before the failure and that very old ids are no longer answerable. Treat it as a diagnostic aid, not a substitute for an operation id, since it does not exist in other databases.

-- PostgreSQL 13+. Engine-specific: ask the server what happened to a transaction you identified before COMMIT.
BEGIN;
SELECT pg_current_xact_id();             -- returns an xid8; save it on the client before sending COMMIT
-- ... statements ...
COMMIT;                                  -- connection drops before the reply arrives

-- On a new connection:
SELECT pg_xact_status('12345678'::xid8); -- 'committed', 'aborted', 'in progress', or NULL if too old

Replication adds a variant. With synchronous replication, a COMMIT can be flushed locally while the primary waits for a standby acknowledgement; if that wait is cancelled or the connection drops, the client again cannot tell whether the commit is durable on the standby, and the local state may already show it. The discipline is the same: the retry must be safe to apply whether or not the first attempt succeeded, and any decision that depends on the outcome must read authoritative state. When the same idea has to cross service boundaries, the idempotency key travels with the request, as in the Idempotency lesson.

Everything above holds inside one transactional database. Once a business operation spans two databases or two services, you have to choose a coordination model, and it is worth saying which one you are using. Local ACID covers one database. Two-phase commit can coordinate participants that support the protocol, but it introduces availability and operational trade-offs. Sagas coordinate local transactions with compensating actions; compensation is a business operation, not automatic rollback. Eventual consistency makes no cross-participant promise at commit time and converges later.

In the protocol, the coordinator first asks every participant to prepare. A participant that votes yes has written enough to commit durably and has given up the right to abort on its own, so it keeps its locks. The coordinator then records its decision and tells everyone. The third timeline is the cost: between voting yes and hearing the decision, a participant is in doubt. If the coordinator is gone, those locks stay held, other transactions queue behind them, and in PostgreSQL a forgotten prepared transaction also holds back vacuum. Operators sometimes have to resolve them by hand. Some distributed databases make the coordinator itself replicated so that its failure is survivable, but that is a property of the system, not of the protocol.

-- PostgreSQL. One participant's side of two-phase commit.
-- Requires max_prepared_transactions > 0 (the default is 0) and a coordinator that drives it.
BEGIN;
UPDATE account SET balance = balance - 500 WHERE id = 1;
PREPARE TRANSACTION 'tx-8841';           -- durable; locks stay held; the session is now free of the transaction

-- Later, from any session, when the coordinator has decided:
COMMIT PREPARED 'tx-8841';               -- or: ROLLBACK PREPARED 'tx-8841';

-- Find transactions left in doubt:
SELECT gid, prepared, owner FROM pg_prepared_xacts ORDER BY prepared;

Two-phase commit also needs every participant to speak the protocol (XA in many databases, prepared transactions in PostgreSQL), which excludes most third-party HTTP APIs, including the card networks. That is the usual reason teams move to a saga: each step is a local transaction, failures are handled by compensations that you design, and the in-between states are visible to other transactions. The price is weaker isolation: you trade the single atomic commit for eventual business consistency and explicit state. The Saga Pattern lesson works through that model, and the Outbox Pattern lesson covers the local-transaction-plus-message handoff that makes each saga step reliable. How much freshness you can promise across replicas and regions is a separate question, treated in Distributed Data.

Start from the invariant, not from the isolation level. A rule that lives in one row, such as a stock counter or a balance floor, is best enforced by a guarded UPDATE; it is the cheapest mechanism and has no retry path to get wrong. A rule across several rows in one database needs a transaction, and then a choice: take a lock on a row that covers the rule when contention is high and the critical section is short, materialize the rule as a constrained single row when you can, or run at SERIALIZABLE and accept a retry loop when the rule is awkward to express any other way and you can tolerate some aborts. A rule across services needs a saga and an outbox, or two-phase commit only where every participant supports it and blocking is acceptable. All of these need an operation id so that retries are safe.

Isolation level is a separate dial. Staying on READ COMMITTED and writing the narrow fixes above gives the fewest surprises and the least overhead for most workloads. Raise the level for specific transactions that need it rather than the whole service, and prove the behaviour with a two-session test rather than by reading level names.

In production, what you watch determines what you find out. These are the signals worth having, with the PostgreSQL queries behind them:

• Transaction age and idle-in-transaction time. The oldest open transaction sets the cleanup horizon and tells you about leaked connections and slow handlers.
• Lock waits. Count and duration of blocked sessions, and who is blocking, to spot hot rows and migrations queued behind long transactions.
• Deadlocks, serialization failures and retry rate. The database counts deadlocks; serialization failures and retries are best counted in the application by SQLSTATE, with the share of operations that needed more than one attempt.
• Conflict and zero-row rates on conditional updates, split by cause (conflict, out of stock, missing), since a rising conflict rate on one key is contention you can see coming.
• Commit latency and rollback ratio. Commit latency reflects WAL and replication settings; rollbacks include normal business rejections, so read them with the retry data.

-- PostgreSQL. The queries behind the metrics in this section.

-- Oldest open transactions, and whether they are idle inside a transaction:
SELECT pid, state, now() - xact_start AS xact_age, wait_event_type, wait_event, left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

-- Who is blocked, and by whom:
SELECT pid, pg_blocking_pids(pid) AS blocked_by, now() - query_start AS waiting, left(query, 80) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- Deadlocks and rollbacks since the stats reset:
SELECT datname, deadlocks, xact_commit, xact_rollback
FROM pg_stat_database
WHERE datname = current_database();

-- Guard rails (values are examples):
--   SET idle_in_transaction_session_timeout = '30s';
--   SET lock_timeout = '2s';