Concept
A database transaction is a unit of work that is treated as a single, indivisible operation. Either every change inside the transaction succeeds and is committed to the database, or none of them are — the database rolls back to the state it was in before the transaction started.
Transactions give you a way to execute multiple SQL statements as an atomic group. This matters any time a logical operation requires more than one write to the database.
Problem
Consider a bank transfer: you need to deduct money from one account and credit it to another. If the deduction succeeds but the credit fails — due to a crash, a network error, or a constraint violation — you now have an inconsistent database. Money has left one account but not arrived anywhere.
Without transactions, you would need to write manual recovery logic for every possible failure point. Transactions handle this at the database level, automatically.
How it works
A transaction typically begins with a BEGIN statement, followed by
one or more SQL statements, and closes with either COMMIT (apply all changes)
or ROLLBACK (discard all changes).
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;If the database crashes between the two UPDATE statements, the transaction is automatically rolled back when the database restarts. Both accounts return to their original balances.
This behaviour is guaranteed through a mechanism called a write-ahead log (WAL). The database records every intended change to a log before applying it. On recovery, it uses the log to either complete committed transactions or discard uncommitted ones.
Transactions in relational databases are defined by four properties, collectively known as ACID:
- Atomicity — all changes succeed, or none do.
- Consistency — the database moves from one valid state to another.
- Isolation — concurrent transactions don't see each other's incomplete work.
- Durability — committed changes survive crashes.
Trade-offs
Transactions are not free. They hold locks on the rows or pages they modify, which can block other transactions from reading or writing the same data. Long-running transactions increase lock contention and can reduce throughput significantly.
The level of isolation between concurrent transactions is also configurable. Stronger isolation prevents more anomalies but increases contention. Weaker isolation improves performance but can allow reads of inconsistent data. See the note on Isolation Levels for a detailed breakdown.
Practical application
In Simple Bank, every transfer operation runs inside a transaction. The transfer logic debits one account and credits another: if either operation fails, the whole transfer is rolled back and no money moves.
In Go with the database/sql package, you begin a transaction with
db.BeginTx(ctx, opts), run your queries, and defer a rollback as a
safety net before committing:
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback() // no-op if tx.Commit() is called first
// ... execute queries using tx ...
return tx.Commit()
The deferred Rollback() is a common Go pattern. If the function returns
early due to an error, the rollback fires. If Commit() has already been
called, the rollback is a no-op.
My understanding
Before building Simple Bank, I thought of transactions primarily as a way to group writes. What I came to understand is that isolation is the harder, more interesting problem. Atomicity is relatively straightforward — either everything commits or nothing does. But isolation involves trade-offs about what concurrent transactions are allowed to see, and those trade-offs have real consequences for both correctness and performance.
The most important lesson: keep transactions short. Long-running transactions hold locks and prevent other work from proceeding. If a transaction needs to do expensive computation, do the computation first, then open the transaction and execute only the writes.