A database transaction is a sequence of operations treated as a single unit of work: either all operations complete successfully, or none of them do. The ACID properties — Atomicity, Consistency, Isolation, and Durability — guarantee that transactions behave reliably even when hardware fails or multiple users access the same data simultaneously.
Why are transactions needed in databases?
Consider a bank transfer: £100 moves from Account A to Account B. This requires two updates:
- Deduct £100 from Account A
- Add £100 to Account B
If the system crashes after step 1 but before step 2, Account A is £100 poorer but Account B never received the money. The £100 has disappeared. Without transactions, this kind of data corruption is a real risk in any system where multiple operations must succeed or fail together.
A transaction groups these two operations so that they are treated as one atomic unit. Either both succeed, or — if anything goes wrong — both are undone.
What is Atomicity?
Atomicity means a transaction is all or nothing. Every operation within the transaction either completes fully or the entire transaction is rolled back — every change is undone, leaving the database as if the transaction never started.
This is achieved using a transaction log: the database records every intended change before applying it. If a failure occurs mid-transaction, the database reads the log and undoes any partial changes.
SQL syntax for a transaction:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
COMMIT; -- applies all changes permanently
If something fails between BEGIN and COMMIT:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
-- System crashes or error occurs here
ROLLBACK; -- undoes the first UPDATE; Account A is restored
What is Consistency?
Consistency means a transaction moves the database from one valid state to another valid state, never violating the database's rules (constraints, triggers, and referential integrity).
Example constraints:
- A bank balance cannot go below zero (a CHECK constraint)
- A foreign key must reference a real record in another table
- A column marked NOT NULL cannot be left empty
If a transaction would violate any constraint, the database refuses the transaction and rolls it back. The data is always in a consistent, rule-following state — before and after every transaction.
What is Isolation?
Isolation means concurrent transactions do not interfere with each other. Even when hundreds of users update a database simultaneously, each transaction runs as if it is the only one in progress.
Why isolation matters:
Imagine two booking agents simultaneously reserving the last seat on a flight:
- Agent 1 reads: 1 seat available
- Agent 2 reads: 1 seat available (simultaneously)
- Agent 1 writes: seat booked, count = 0
- Agent 2 writes: seat booked, count = 0 — but the seat is already booked!
Proper isolation prevents this "double booking" scenario through locking: when a transaction reads or writes a row, it holds a lock on that row, preventing other transactions from modifying it until the first transaction completes.
Isolation levels (from weakest to strongest protection):
| Level | What it prevents |
|---|---|
| Read uncommitted | Nothing — dirty reads allowed |
| Read committed | Dirty reads prevented |
| Repeatable read | Dirty reads + non-repeatable reads prevented |
| Serialisable | All concurrency anomalies prevented — highest isolation |
Higher isolation means stronger data safety but slower performance because locks are held longer. Most production databases default to "read committed" or "repeatable read" as a balance.
What is Durability?
Durability means that once a transaction has been committed, its changes are permanent — even if the system crashes immediately afterwards. The database must survive a power cut, hardware failure, or operating system crash after a COMMIT without losing the committed data.
This is achieved by writing committed transaction data to non-volatile storage (typically a persistent write-ahead log on disk) before confirming the COMMIT to the user. When the system restarts after a crash, it reads the log and replays any committed transactions that did not make it fully to disk.
How do the four ACID properties work together?
| Property | Guarantees | Key mechanism |
|---|---|---|
| Atomicity | All or nothing | Rollback log |
| Consistency | Rules always respected | Constraints, triggers |
| Isolation | Concurrent transactions do not interfere | Locking |
| Durability | Committed data persists | Write-ahead log, non-volatile storage |
Together, ACID properties make database transactions reliable. A system without ACID guarantees (called "eventually consistent" systems, used for very large-scale web applications) trades reliability for speed, accepting that data may be temporarily inconsistent in exchange for higher throughput.
Frequently asked questions
Is SQL always used with transactions?
Most relational database systems (MySQL, PostgreSQL, SQLite, SQL Server, Oracle) support ACID transactions using BEGIN TRANSACTION, COMMIT, and ROLLBACK. Some systems default to auto-commit mode, where each individual SQL statement is automatically treated as its own transaction — which is convenient but means you cannot group multiple statements into a single atomic unit without explicitly opening a transaction.
What is a deadlock?
A deadlock occurs when two or more transactions are each waiting for a lock held by the other, creating a circular dependency that neither can resolve. Transaction A holds a lock on table 1 and waits for table 2; Transaction B holds a lock on table 2 and waits for table 1. Neither can proceed. Database systems detect deadlocks automatically and resolve them by rolling back one of the waiting transactions (the "victim"), which the application can then retry.
Do all databases use ACID?
No. Traditional relational databases (RDBMS) are built around ACID guarantees. Some NoSQL databases (such as MongoDB in certain configurations, Cassandra, and DynamoDB) relax ACID properties — particularly isolation and durability — in exchange for much higher performance and scalability across distributed systems. These are appropriate for applications where a small amount of inconsistency is acceptable, such as social media feeds, where showing a slightly stale post count matters far less than serving billions of users quickly.
Is ACID covered in GCSE Computer Science exams?
ACID properties are included in some GCSE specifications, particularly OCR GCSE Computer Science. You may be asked to define each property, explain why transactions are necessary, or trace what happens to a database when a transaction is committed or rolled back. Even where not explicitly listed, understanding transactions helps you answer evaluation questions about database design and data integrity.
Want to master databases and SQL for your GCSE? Professor Turing at aitutors.me will guide you through transactions, constraints, and queries step by step.