Database Transactions: ACID, Isolation and Deadlocks

Quick Answer

A transaction groups several statements into one all-or-nothing unit. You open it with BEGIN, make it permanent with COMMIT, and throw it away with ROLLBACK. Without an explicit BEGIN, most clients run in autocommit mode, so every statement commits on its own and a failure halfway through leaves partial data behind. Isolation levels control what one transaction can see of another, and the price of stronger isolation is more locking, more waiting and occasional deadlocks you must retry.

Why transactions exist

Say a student pays ₹5,000 for a course. Your code debits a wallet row and credits a payouts row. Two UPDATE statements, a few milliseconds apart. If the process is killed between them, or the second statement violates a constraint, the first one has already been written. The money is simply gone. Nobody stole it, and there is nothing in the logs except the error you already caught.

A transaction turns those two statements into one unit. Either both land or neither does.

Here is the part that catches almost everybody. Nearly every database client runs in autocommit mode by default. Each statement you send is its own tiny transaction, committed the instant it succeeds. So this code contains no transaction at all, however much it looks like one:

// there is no transaction anywhere in sight
await db.query('UPDATE wallets SET balance = balance - 5000 WHERE user_id = 1');
await sendReceiptEmail(user);   // this throws
await db.query('UPDATE payouts SET balance = balance + 5000 WHERE id = 9');

Wrapping that block in try/catch does not save you. Catching an exception in your language does not undo a write the database has already committed. The first UPDATE became permanent the moment it returned, and the second one never runs at all.

This is also why "I use an ORM, it handles that" is usually wrong. Calling save() three times is three separate commits unless you explicitly opened a transaction around them. The ORM will not guess that the three belong together.

BEGIN, COMMIT and ROLLBACK

The SQL itself is short:

BEGIN;
UPDATE wallets  SET balance = balance - 5000 WHERE user_id = 1 AND balance >= 5000;
UPDATE payouts  SET balance = balance + 5000 WHERE id = 9;
COMMIT;

PostgreSQL uses BEGIN. MySQL accepts both BEGIN and START TRANSACTION. In both, everything you do between the start and the COMMIT is invisible to other sessions at any normal isolation level, and ROLLBACK throws the whole lot away as if it never happened.

Notice balance = balance - 5000 rather than reading the balance into a variable, subtracting in your code and writing it back. Read-modify-write in application code is how two concurrent requests both read ₹5,000, both write ₹0, and one debit vanishes. Let the database do the arithmetic on the row it is holding.

Also notice the AND balance >= 5000 guard. If the wallet is short, that UPDATE matches zero rows. It does not raise an error. You have to check the affected-row count yourself and roll back:

const client = await pool.connect();     // one dedicated connection
try {
  await client.query('BEGIN');
  const res = await client.query(
    'UPDATE wallets SET balance = balance - $1 WHERE user_id = $2 AND balance >= $1',
    [5000, 1]
  );
  if (res.rowCount === 0) throw new Error('insufficient balance');
  await client.query('UPDATE payouts SET balance = balance + $1 WHERE id = $2', [5000, 9]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();
}

The dedicated client matters. If you send BEGIN to a connection pool instead of a checked-out connection, the pool is free to run your next statement on a different connection. Your COMMIT then lands on a connection that never began anything, while the connection holding your open transaction goes back into the pool for some unrelated request to inherit.

One platform difference worth knowing: PostgreSQL supports transactional DDL, so CREATE TABLE or ALTER TABLE inside a transaction can be rolled back. MySQL does not. DDL there triggers an implicit commit, which silently ends the transaction you thought you were still inside.

What ACID actually buys you

ACID is four separate promises, and interviewers like asking which one you are relying on.

• Atomicity is the all-or-nothing part. A failed transaction leaves no trace of its half-finished writes.
• Consistency means the database will not let a committed transaction break the rules you declared: foreign keys, unique indexes, check constraints, not-null. It does not mean your data is correct. If you never declared a constraint, there is nothing to enforce. This is the letter people most often misdescribe.
• Isolation controls how much of another in-flight transaction you can see. This is the interesting one, and it has settings.
• Durability means that once COMMIT returns, the change survives the server losing power.

Durability has a catch worth naming. The database only guarantees it if it is configured to flush the write-ahead log to disk on every commit. In MySQL that is innodb_flush_log_at_trx_commit = 1, and in PostgreSQL it is synchronous_commit = on. Both default to the safe setting. If somebody turned one down to make a benchmark look better, you have traded durability for throughput, and a power cut can lose transactions the application was told had committed.

The other practical point: constraints do real work for you. A UNIQUE index on an email column will reject a duplicate signup even when two requests arrive at the same instant, which no amount of "check first, then insert" application code can do reliably. Checking and then inserting is two operations with a gap in the middle; the constraint is one atomic decision made by the engine.

Isolation levels and the anomalies they prevent

The SQL standard names four anomalies and four levels. Each level rules out more of the anomalies and costs more in locking or version tracking.

• Dirty read: you see data another transaction wrote but has not committed. It may still roll back, so you acted on data that never existed. Prevented from READ COMMITTED upwards.
• Non-repeatable read: you read the same row twice in one transaction and get two different values, because someone committed in between. Prevented from REPEATABLE READ upwards.
• Phantom read: you run the same WHERE twice and the second run returns extra rows that someone inserted. Prevented at SERIALIZABLE.
• Lost update: two transactions read the same value, both write, one write disappears. This one is not covered by READ COMMITTED at all, and it is the one that actually bites in web apps.

Defaults differ by engine, which is why the same code behaves differently on two projects. MySQL with InnoDB defaults to REPEATABLE READ. PostgreSQL and SQL Server default to READ COMMITTED. PostgreSQL has no real READ UNCOMMITTED; ask for it and you get READ COMMITTED.

Here is the classic lost update, selling the last seat in a batch:

-- session A and session B run this at the same time
BEGIN;
SELECT seats_left FROM batches WHERE id = 7;   -- both read 1
UPDATE batches SET seats_left = 0 WHERE id = 7;
COMMIT;
-- two students, one seat

The fix is to take a lock on the row you read, or to make the decision inside a single statement:

BEGIN;
SELECT seats_left FROM batches WHERE id = 7 FOR UPDATE;  -- other sessions wait here
UPDATE batches SET seats_left = seats_left - 1
  WHERE id = 7 AND seats_left > 0;
COMMIT;

Raising the level to SERIALIZABLE also works, but in PostgreSQL that means transactions can be aborted with a serialisation failure (SQLSTATE 40001) when the engine cannot order them safely. Choosing SERIALIZABLE without writing retry logic just moves the bug.

Deadlocks and long transactions

A deadlock is two transactions holding what the other wants. Transaction A locks row 1 then asks for row 2; transaction B locked row 2 and now asks for row 1. Neither can move. The engine detects the cycle and kills one of them: MySQL raises error 1213, PostgreSQL raises SQLSTATE 40P01.

Deadlocks are normal in a busy system, not a sign that something is broken. What is broken is an application that does not retry. The victim transaction was rolled back cleanly, so retrying is safe:

async function withRetry(fn, attempts = 3) {
  for (let i = 0; i < attempts; i++) {
    try {
      return await fn();
    } catch (err) {
      const retryable = err.code === '40P01' || err.code === '40001' || err.errno === 1213;
      if (!retryable || i === attempts - 1) throw err;
      await new Promise(r => setTimeout(r, 50 * (i + 1)));
    }
  }
}

You can also make deadlocks rarer by always taking locks in the same order. If a transfer locks the two account rows sorted by id, two opposite transfers can never form a cycle. Sorting the ids before you touch them is one line of code and removes a whole class of incident.

Long transactions are the other half of the problem. Everything you hold a lock on, other requests queue behind. Two rules follow. First, never do slow work inside a transaction: no HTTP calls to a payment gateway, no sending email, no waiting for user input across requests. Open the transaction, write, commit, then do the slow thing. Second, always commit or roll back on every path, including error paths, or you leave a connection sitting idle in transaction.

Those idle transactions are expensive beyond locking. PostgreSQL cannot clean up old row versions that an open transaction might still need, so tables bloat. InnoDB keeps undo history for the same reason. A single forgotten ROLLBACK in a rarely hit error branch can degrade a database for hours before anyone connects the two.

Frequently Asked Questions