Definition
Database Transaction
A single unit of work, consisting of one or more (SQL) operations which are executed atomically such that all changes take effect together (commit) or none do (rollback).
Importance of ACID Properties in a Database Transaction
- ACID is an acronym for: Atomicity, Consistency, Isolation, Durability.
- All transactions must adhere to ACID principles, which are the following:
- Atomicity is when all tasks in a transaction are performed (committed), or none are (rollback).
- This ensures there are no partial writes and corrupt data writes.
- Consistency is where the data written must be valid according to existing rules.
- Isolation indicates each transaction must not interfere with other transactions.
- This helps avoid data inconsistency among concurrent transactions.
- Durability is the concept where when a transaction is committed, the change to the database is permanent, even if system failure occurs.
- Atomicity is when all tasks in a transaction are performed (committed), or none are (rollback).
- Atomicity: A bank transfer where debit and credit must both succeed or both roll back.
- Consistency: Foreign‑key prevents an order pointing to a non‑existent customer.
- Isolation: Two shoppers don’t buy the last ticket twice.
- Durability: After reboot, confirmed bookings are still there.
Using Transactions, States and Updates to Maintain Data Consistency (and Integrity)
- As mentioned before, transactions help maintain data consistency and integrity through its adherence to ACID principles.
- Meanwhile, the current database state is the entire collection of data as it exists at a single moment.
- Consistency means every committed state obeys all keys, validation rules and business constraints.
- An update (INSERT, UPDATE or DELETE) is the individual change executed within the transaction.
- On its own, an update might violate a rule (e.g., make stock negative), but when enclosed in a transaction it either becomes part of a new consistent state or is cancelled.
- Picture an airline database with two key tables:
- Seats(flight_id, seat_no, status) where status can be FREE or BOOKED
- Passengers(flight_id, seat_no, name)
- At 09:00 the database state S₀ is consistent: seat 12A on flight BA123 is FREE and no passenger record references that seat.
- When a customer now books 12A, inside the transaction, the system executes two updates:
- UPDATE Seats SET status = 'BOOKED' WHERE flight_id = 'BA123' AND seat_no = '12A';
- INSERT INTO Passengers VALUES ('BA123', '12A', 'Aria Patel');
- Only when both statements succeed does the DBMS commit, producing state S₁ where seat 12A is BOOKED and Aria Patel is linked to that seat, again a fully valid snapshot.
- If the insert fails (e.g., duplicate key, network glitch), the transaction (update) rolls back, restoring state S₀ so no passenger ever sees a map claiming 12A is simultaneously free and booked.
- In this way transactions move the database cleanly from one consistent state to the next, never exposing impossible seat allocations.
