What Is a Transaction?
A transaction is a set of database operations that must be treated as a single logical unit — either all succeed or all fail together.
Classic example: Bank transfer
- Debit $100 from Account A
- Credit $100 to Account B
If step 1 succeeds but step 2 fails, Account A loses money with nothing going anywhere. Transactions prevent this.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT; -- Both UPDATEs succeed, changes are permanent
-- Or ROLLBACK; -- On error, both changes are undone
The ACID Properties
Transactions must guarantee four core properties.
A — Atomicity
All operations in a transaction are all or nothing. If any operation fails, all previously completed operations in that transaction are rolled back.
C — Consistency
The database must move from one valid state to another valid state. All constraints (NOT NULL, UNIQUE, FK checks) must hold before and after the transaction.
I — Isolation
Concurrent transactions must not see each other's intermediate, uncommitted state. Each transaction should appear to execute as if it were the only one running.
D — Durability
Once a transaction commits, its changes are permanent — even if the system crashes immediately afterward. Databases use WAL (Write-Ahead Log) to guarantee this.
Concurrency Anomalies
When isolation is imperfect, these problems can occur:
Dirty Read
Reading uncommitted changes from another transaction.
Non-Repeatable Read
The same query returns different results within a single transaction because another transaction committed a change in between.
Phantom Read
A range query returns a different number of rows when executed twice because another transaction inserted or deleted rows in between.
Isolation Levels
The SQL standard defines four isolation levels. Higher isolation eliminates more anomalies but reduces concurrency and performance.
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | possible | possible | possible |
| READ COMMITTED | prevented | possible | possible |
| REPEATABLE READ | prevented | prevented | possible |
| SERIALIZABLE | prevented | prevented | prevented |
-- Set isolation level in PostgreSQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- your queries here
COMMIT;
PostgreSQL defaults to READ COMMITTED; MySQL InnoDB defaults to REPEATABLE READ.
Practical Tips
- Keep transactions short: Minimize lock contention by ending transactions quickly
- READ COMMITTED: Appropriate for most OLTP applications
- SERIALIZABLE: Reserve for financial settlement or strict consistency requirements
- Explicit transactions: Even with ORMs, define transaction boundaries explicitly
Conclusion
ACID is the foundation of reliability in relational databases. When designing your schema, clarify transaction boundaries clearly, and use Erdmates to visualize the related table structures — this makes choosing the right isolation level and designing effective locking strategies much more straightforward.