A database is not only for storing data, but for keeping it correct even when something goes wrong: a program that crashes halfway through, two users editing the same record, a power cut. The tool that makes all this possible is the transaction, and the guarantees it offers are summed up in the acronym ACID.
The bank transfer example
The textbook case is moving money between two accounts. To move 100 euros from account A to account B, two operations are needed:
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
If the system stops between the first and second statement, 100 euros vanish into thin air. If the two statements are wrapped in a transaction, this cannot happen:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;
Until COMMIT, the changes are provisional. If something fails, you run ROLLBACK and the database returns exactly to its previous state.
A for atomicity
A transaction is indivisible: either all its operations take effect, or none do. There is no visible intermediate state in which account A has been debited and account B not yet credited. If the server shuts down before the commit, the transaction is found cancelled on restart.
C for consistency
A transaction takes the database from one valid state to another valid state, respecting every defined constraint: primary keys, foreign keys, CHECK, NOT NULL. If, for example, the table has the constraint CHECK (balance >= 0) and account A holds only 50 euros, the first UPDATE fails and the whole transaction is cancelled.
It is important to understand one limit: the database guarantees only the rules that have been declared to it. If the rule «the total of all balances does not change with a transfer» exists only in the head of whoever wrote the program, the database cannot enforce it. The more rules are turned into constraints, the more automatic consistency becomes.
I for isolation
Many transactions run at the same time. Isolation determines how far each one sees the not-yet-committed changes of the others. Ideally every transaction would behave as if it were the only one running, but that has a performance cost. That is why databases offer several isolation levels, which let you balance correctness and speed. Another transaction reading the balances during our transfer, at the most common levels, sees either the state before or the state after, never the one halfway through.
D for durability
Once the database has replied «commit done», the change survives even a crash immediately afterwards. To achieve this, databases write changes to a sequential log, often called a write-ahead log or redo log, and force it to disk before confirming. On restart after an interruption, the log is used to rebuild committed transactions and discard incomplete ones.
Some configurations let you partly give up this guarantee in exchange for speed, for example by confirming before the data is really on disk. It is a legitimate choice for data that can be rebuilt, but it should be made deliberately.
Transactions in application code
In programming languages, transactions are usually handled with a block that commits if all goes well and rolls back on an exception. In Python, with the standard sqlite3 library:
import sqlite3
conn = sqlite3.connect("bank.db")
try:
with conn: # automatic commit if no exception is raised
conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (100, "A"))
conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (100, "B"))
except sqlite3.Error as error:
print("Transfer cancelled:", error)
The with block applied to the connection commits at the end and rolls back if an exception occurs.
The most frequent mistakes
Forgetting autocommit mode
Many tools work in autocommit mode: each statement is its own transaction. If you run two UPDATE statements in a row without BEGIN, they are not atomic together.
Transactions that are too long
An open transaction holds locks on the modified rows and prevents the database from cleaning up old versions of the data. Opening a transaction, waiting for a reply from an external service or for a user action, and only then closing it, is a classic mistake. Transactions must be short: prepare the data first, open, write, close.
External actions inside the transaction
Sending an email or calling a payment service inside a transaction creates a problem: if the transaction is then cancelled, the email has already gone. External actions should run after the commit, or be recorded in a table of pending tasks that a separate process works through.
Ignored errors
If a statement fails and the code carries on and commits anyway, the guarantees are lost. In PostgreSQL a transaction in which an error has occurred rejects every subsequent statement until a rollback, precisely to prevent this scenario.
In short
- A transaction groups several operations that must succeed or fail together.
- Atomicity: all or nothing. Consistency: declared constraints are always respected.
- Isolation: concurrent transactions do not see intermediate states. Durability: what is committed stays.
- In code, commit at the end and roll back on error, keeping transactions short.
- Emails and calls to external services belong after the commit.
