Skip to content

Intermediate · SQL in depth · topic 25 of 54 · 20 min

Transactions and ACID

Explanation

A transaction groups several SQL statements so they all happen or none of them happen. A UPI transfer debits one account and credits another; if the server crashes in between, the money must not vanish. You start with BEGIN, save with COMMIT, or undo everything with ROLLBACK.

All or nothing

Sending 750 rupees from Ravi to Meera needs two updates: take 750 from Ravi, add 750 to Meera. If the server crashes after the first update, Ravi has lost money and Meera got nothing. A transaction makes the two updates one unit: either both are saved, or neither is.

Think of it like this: Think of buying a railway ticket at the counter. You hand over the money and the clerk hands you the ticket. If the printer jams, the clerk gives your money back; you never end up with no money and no ticket. A transaction is that promise inside the database.
sql
BEGIN;
UPDATE accounts SET balance = balance - 750 WHERE id = 1;  -- debit Ravi
UPDATE accounts SET balance = balance + 750 WHERE id = 2;  -- credit Meera
COMMIT;
Nothing is final until COMMIT. A crash before it undoes both updates.

ROLLBACK cancels everything since BEGIN, as if it never happened. Your backend code sends ROLLBACK when anything goes wrong: a check fails, an error is thrown, or the balance is too low.

sql
BEGIN;
UPDATE accounts SET balance = 0;  -- oops, forgot WHERE
ROLLBACK;                          -- all balances are back
SELECT holder, balance FROM accounts;
ROLLBACK is your undo button, but only inside a transaction.

ACID in simple words

LetterMeansIn the UPI example
AAtomic: all or nothingDebit and credit both happen, or neither
CConsistent: rules always holdA balance never goes below zero
IIsolated: transactions do not see each other's half-done workAnother query never sees money "in the air"
DDurable: once committed, it survives a crash or power cutAfter "Payment successful", the money stays moved

Constraints guard the data

A CHECK constraint is a rule the database tests on every insert and update. With CHECK (balance >= 0), an update that would make a balance negative fails with an error. Your code catches the error and sends ROLLBACK, so the debit is undone too. The rule lives in the database, so no bug in any app can break it.

sql
CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  holder  TEXT NOT NULL,
  upi_id  TEXT UNIQUE NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
Store money as whole paise or rupees in INTEGER (or NUMERIC), never as a float.

Isolation, briefly

Many users hit the database at once. Isolation decides what one transaction can see of another. PostgreSQL by default uses READ COMMITTED: you never see uncommitted changes. For the "last seat on the train" problem, lock the row you are about to change with SELECT ... FOR UPDATE (PostgreSQL), or use the stricter SERIALIZABLE level, so two people cannot both book it.

Deadlocks

A deadlock happens when transaction A has locked Ravi's row and waits for Meera's, while transaction B has locked Meera's and waits for Ravi's. Neither can move. The database notices, cancels one of them with an error, and the app should simply retry it. You avoid most deadlocks by always locking rows in the same order, for example lower account id first, and by keeping transactions short.

Tip: Keep transactions short: BEGIN, a few quick statements, COMMIT. Long transactions hold locks and make other users wait.
Careful: Never call a slow outside service (send an SMS, call a payment gateway) while a transaction is open. Commit first, then do the slow work, or record a "to do" row and let a background job handle it.

Watch the lesson

Transactions and ACID · 1/7

Transactions and ACID

Moving money is two steps: a debit and a credit. A crash in the middle must never lose money.

Prefer a video by a teacher? Find free videos on “database transactions acid explained commit rollback” on YouTube ↗

In real life: A wallet transfer in Node.js

A wallet app moves money with a transaction. The backend takes one database connection, sends BEGIN, runs the debit and the credit, inserts a row into a transfers log, and sends COMMIT. If any step throws (for example the CHECK constraint fails because the balance is too low), the catch block sends ROLLBACK and the user sees "Insufficient balance" with nothing changed.

javascript
async function transfer(pool, fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
    await client.query('INSERT INTO transfers (from_id, to_id, amount) VALUES ($1, $2, $3)', [fromId, toId, amount]);
    await client.query('COMMIT');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}
The BEGIN / COMMIT / ROLLBACK pattern with the pg library for PostgreSQL.

Practice (0/3 done)

Task 1 · Code · SQL

Table: accounts(id, holder, upi_id, balance). Ravi Kumar (id 1) sends 750 rupees to Meera Joshi (id 2). Inside the transaction, write the debit and the credit. The final SELECT shows the balances.

Task 2 · Code · SQL

Someone started a transaction and ran an UPDATE without WHERE, setting every balance to 0. Before the final SELECT, add the one statement that cancels the whole transaction, so the original balances come back.

Task 3 · Question

In a transfer transaction, the debit succeeds but the credit fails with an error. What should the backend do?

Quick quiz

Question 1

1.What does ROLLBACK do?

Question 2

2.Which ACID property means "after the app shows Payment successful, a power cut cannot undo it"?

Question 3

3.A table has CHECK (balance >= 0). An UPDATE would make a balance -200. What happens?

Question 4

4.What is a deadlock?

Question 5

5.Why should you not send an SMS from inside an open transaction?

Read more (free)

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: Normalisation and table design →

← Previous: Indexes: making queries fast