ACID Properties
ACID is a set of four guarantees that a database must provide to ensure transactions are reliable, even when things go wrong (power failure, concurrent users, crashes). The acronym stands for Atomicity, Consistency, Isolation, and Durability.
These properties matter most when multiple operations need to succeed or fail together — like transferring money between two accounts, or booking a venue slot.
Atomicity
A transaction is all-or-nothing. Either every operation inside it completes, or none of them do. There is no halfway state.
Think of it like a bank transfer: deducting from account A and crediting account B must both happen together. If the credit step crashes, the debit rolls back automatically. No money vanishes.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT; -- both succeed together, or both roll back
Consistency
A transaction can only bring the database from one valid state to another valid state. All rules (constraints, foreign keys, triggers) must hold before and after. If a transaction would violate a constraint, it gets rejected entirely.
For example: a bookings row with a venue_id that references a non-existent venue will be rejected by the foreign key constraint. The database never ends up in a logically broken state.
Isolation
Concurrent transactions don't interfere with each other. From each transaction's perspective, it looks like it's running alone — even if dozens of others are running at the same time.
Without isolation, you get problems like:
- Dirty reads: reading data another transaction hasn't committed yet
- Phantom reads: a query returns different rows mid-transaction because another transaction inserted data
Databases achieve isolation through locking. SELECT ... FOR UPDATE in PostgreSQL, for example, locks matching rows so no other transaction can modify them until the first one commits. This is how the venue booking project prevents double-bookings.
Durability
Once a transaction is committed, it stays committed. A crash, power failure, or restart immediately after a COMMIT cannot undo it. The database writes transactions to a durable log (WAL — Write-Ahead Log) before confirming success.
ACID in practice
Most relational databases — PostgreSQL, MySQL, SQLite — are ACID-compliant by default. NoSQL databases (MongoDB, DynamoDB) often trade some ACID guarantees for performance and horizontal scalability, though many now offer optional transaction support.
When you use Supabase, you're using PostgreSQL and get full ACID compliance. The create_booking_atomic() stored function in the venue booking project relies on atomicity and isolation together: the check and insert happen in one transaction, and row-level locks prevent concurrent conflicts.