Transactions & Concurrency

So far every statement you wrote ran on its own. In real applications, though, a single business action often needs several statements that must all succeed together or not at all. Moving money between two accounts is the classic case: one row goes down, another goes up, and you can never afford to apply only half of that.

A transaction is the tool for exactly this. It groups one or more statements into a single all-or-nothing unit of work. Either every statement takes effect, or none of them do. On top of that, a busy database has many transactions running at the same time, which is what we mean by concurrency. This module is about both: how to define units of work, and how PostgreSQL keeps them from corrupting each other’s data.

The four guarantees: ACID

A transaction in PostgreSQL gives you four guarantees, remembered by the acronym ACID.

Letter	Property	What it promises
A	Atomicity	All statements commit together or roll back together; never half-done
C	Consistency	The database moves from one valid state to another, honouring constraints
I	Isolation	Concurrent transactions do not see each other’s unfinished work
D	Durability	Once committed, the change survives a crash or power loss

Atomicity is the part you feel most directly when you type COMMIT or ROLLBACK. Isolation is the part that gets interesting once more than one client is connected, and it is where most of this module’s depth lies.

How the pieces fit together

The next four lessons build on each other. You start a transaction, you choose how isolated it should be, and underneath both of those PostgreSQL uses two mechanisms to make concurrency safe: keeping multiple versions of each row, and taking locks when it must.

flowchart TD
  A[Transaction: BEGIN to COMMIT] --> B[Isolation level: how much it sees]
  B --> C[MVCC: each transaction reads a snapshot]
  B --> D[Locking: serialize conflicting writes]
  C --> E[Safe concurrency for many clients]
  D --> E

From a single transaction to safe concurrency

What this module covers

Transaction basics — BEGIN, COMMIT, ROLLBACK, atomicity, and SAVEPOINT for partial rollbacks, demonstrated with an account transfer.
Isolation levels — Read Committed, Repeatable Read, and Serializable, and the read anomalies each one prevents.
MVCC — Multi-Version Concurrency Control, where writers do not block readers and readers do not block writers because each transaction reads a consistent snapshot.
Locking and deadlocks — explicit row locks with SELECT ... FOR UPDATE, why two transactions can deadlock, and how PostgreSQL breaks the tie.

A first look

You will meet these commands properly in the next lesson, but here is the shape of a transaction so the rest of the module reads naturally.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 2]);
  await client.query('COMMIT');
} finally {
  client.release();
}

with conn.transaction():
    cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (100, 1))
    cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (100, 2))

tx, _ := conn.Begin(ctx)
tx.Exec(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", 100, 1)
tx.Exec(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", 100, 2)
tx.Commit(ctx)

let mut tx = pool.begin().await?;
sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
    .bind(100_i64).bind(1_i64).execute(&mut *tx).await?;
sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
    .bind(100_i64).bind(2_i64).execute(&mut *tx).await?;
tx.commit().await?;

In pgAdmin: the Query Tool runs each statement in its own auto-committed transaction by default. To group statements, type BEGIN; yourself at the top and COMMIT; at the end, then run the whole block at once.

Tips / gotchas

A connection that is not inside an explicit transaction runs each statement in its own implicit transaction, committed immediately. This is called auto-commit.
Isolation is a spectrum, not a switch. Higher levels prevent more anomalies but make conflicts more likely to abort.
PostgreSQL never asks readers to wait for writers for ordinary SELECT queries; that is the headline benefit of MVCC.
Deadlocks are a normal possibility in any concurrent system, not a bug; well-written applications expect them and retry.