Ensuring Data Consistency with Transactions

Written with a little help from AI, reviewed and edited by a human.

Sometimes you need to run multiple SQL statements together as one unit — either they all succeed, or they all fail. For example, transferring money from one bank account to another involves two steps: subtract from one account and add to another. If the first succeeds but the second fails, you’ve just lost money. Transactions solve this by treating multiple operations as a single, all-or-nothing unit.

Real life example

Imagine you’re transferring $100 from your checking account to your savings account. This involves two steps:

Subtract $100 from checking
Add $100 to savings

What if the bank’s system crashes after step 1 but before step 2? You’d lose $100 — it was removed from checking but never added to savings.

A transaction ensures this can’t happen. The bank groups both steps together and says: “Either both steps succeed, or neither happens.” If something goes wrong, it undoes step 1 (called a rollback). If everything works, it finalizes both steps (called a commit).

What is a transaction?

A transaction is a sequence of SQL statements that are executed as a single unit. The database guarantees that either:

All statements succeed (commit)
All statements are undone (rollback)

There’s no in-between state where some statements succeeded and others didn’t.

Starting a transaction

You start a transaction with BEGIN or START TRANSACTION:

BEGIN;
-- Your SQL statements go here

From this point forward, any changes you make are not permanent until you commit.

Committing a transaction

When all your statements have succeeded and you’re happy with the changes, you commit the transaction to make it permanent:

BEGIN;

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

COMMIT;

COMMIT says: “I’m done, make these changes permanent.”

After the commit, the money transfer is complete and the database is updated.

Rolling back a transaction

If something goes wrong, you can rollback the transaction to undo all changes:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Oh no, something went wrong!

ROLLBACK;

ROLLBACK says: “Undo everything since BEGIN. Pretend none of it happened.”

After the rollback, account 1 still has its original balance. Nothing changed.

A complete example

Let’s say we have an accounts table:

id	name	balance
1	Alice	500
2	Bob	300

Alice wants to send $100 to Bob:

BEGIN;

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

COMMIT;

After the commit:

id	name	balance
1	Alice	400
2	Bob	400

If something failed (like the database crashing), the transaction would automatically rollback and both accounts would stay at their original values.

Why use transactions?

Data consistency: Transactions prevent “half-done” operations. Either everything happens or nothing happens.

Error recovery: If an error occurs mid-transaction, you can rollback and leave the database in a consistent state.

Concurrency: Transactions help when multiple users are accessing the database at the same time. They prevent conflicts and ensure each user sees a consistent view of the data.

ACID properties

Transactions follow four principles called ACID:

Atomicity — All statements in a transaction succeed or all fail. No partial success.
Consistency — The database goes from one valid state to another valid state. Rules and constraints are always enforced.
Isolation — Transactions don’t interfere with each other. One transaction can’t see another’s uncommitted changes.
Durability — Once a transaction is committed, the changes are permanent (even if the system crashes immediately after).

You don’t need to memorize ACID, but it’s good to know that transactions provide strong guarantees about your data.

Transactions are automatic for single statements

Most databases automatically wrap each statement in a transaction:

UPDATE accounts SET balance = balance + 100 WHERE id = 1;

This is implicitly:

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

So single statements are already atomic. Transactions are mainly useful when you have multiple statements that need to succeed or fail together.

Handling errors in transactions

In application code, you typically use try/catch (or similar) with transactions:

Pseudo-code example:

BEGIN TRANSACTION

try:
  UPDATE accounts SET balance = balance - 100 WHERE id = 1
  UPDATE accounts SET balance = balance + 100 WHERE id = 2

  if everything_ok:
    COMMIT
  else:
    ROLLBACK

catch error:
  ROLLBACK
  throw error

This ensures that if anything goes wrong (database error, application crash, etc.), the transaction is rolled back.

Savepoints

You can create savepoints within a transaction to create “checkpoints”:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

SAVEPOINT after_debit;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Oh no, something's wrong with the second update
ROLLBACK TO SAVEPOINT after_debit;

-- The first update is still there, but the second was undone

COMMIT;

ROLLBACK TO SAVEPOINT undoes everything after the savepoint but keeps everything before it. This is advanced and not commonly used, but it’s useful for complex transactions.

Isolation levels

When multiple transactions run at the same time, isolation levels control how much they can see of each other’s changes. The levels are:

READ UNCOMMITTED — Can see other transactions’ uncommitted changes (dirty reads)
READ COMMITTED — Only sees committed changes (default in most databases)
REPEATABLE READ — Guarantees the same read twice in a transaction returns the same result
SERIALIZABLE — Strongest isolation, transactions run as if they’re the only ones

Higher isolation = safer but slower. Lower isolation = faster but more potential for conflicts.

Most of the time, the default (READ COMMITTED) is fine.

Deadlocks

A deadlock happens when two transactions are waiting for each other to release locks:

Transaction A locks row 1, waits for row 2
Transaction B locks row 2, waits for row 1
Both are stuck waiting forever

Databases detect deadlocks and automatically rollback one of the transactions to break the deadlock. Your application should catch this error and retry the transaction.

When to use transactions

Use transactions when:

You have multiple related changes that must all succeed together (like a money transfer)
You want to ensure data consistency across multiple tables
You need to rollback if any step fails

When NOT to use transactions

Don’t wrap every single query in a transaction just because. Transactions have overhead. Use them when you actually need the all-or-nothing guarantee.

Also, keep transactions short. A transaction holds locks on rows, which can block other users. Don’t do:

BEGIN;
-- Do a bunch of stuff
-- Wait for user input
-- Do more stuff
COMMIT;

This locks rows for a long time. Transactions should be fast.

Summary

Transactions group multiple SQL statements into an all-or-nothing unit
Use BEGIN to start, COMMIT to finalize, ROLLBACK to undo
Transactions ensure data consistency and error recovery
Follow ACID principles: Atomicity, Consistency, Isolation, Durability
Use transactions when multiple statements must succeed or fail together
Keep transactions short to avoid blocking other users