ACID — Atomicity, Consistency, Isolation, Durability defined with real examples
Concept
ACID — the four properties that guarantee reliable transaction processing in databases.
A — Atomicity: A transaction is ALL or NOTHING. If any part fails, the entire transaction is rolled back as if it never happened. "Atomic" = indivisible.
- Example: Transferring $100 from Alice to Bob. Deduct from Alice, add to Bob. If the add fails, the deduction is rolled back. You can't end up in a state where Alice lost $100 but Bob didn't receive it.
C — Consistency: A transaction takes the database from one valid state to another valid state. Constraints (NOT NULL, UNIQUE, FK, CHECK) are satisfied before and after. An invalid transaction is rejected.
- Example: A transfer can't make Alice's balance negative if there's a
CHECK (balance >= 0)constraint.
I — Isolation: Concurrent transactions don't interfere with each other. Each transaction sees a consistent snapshot of data, as if it's running alone. The level of isolation is configurable (isolation levels).
- Example: While Alice's transfer is in progress, Bob querying his balance doesn't see a partial state.
D — Durability: Once a transaction is committed, it stays committed. Even if the server crashes immediately after, the data persists (written to disk, transaction log).
- Example: After
COMMIT, the transfer is permanent. Server crash immediately after doesn't lose the data.
Which part is most often violated: Isolation is the configurable one. Full isolation (Serializable) is expensive — most systems trade some isolation for performance. This leads to anomalies: dirty reads, non-repeatable reads, phantom reads.
In Laravel: DB::transaction() wraps all operations in a BEGIN → COMMIT/ROLLBACK block. If an exception is thrown inside, Laravel rolls back automatically.
Code Example
<?php
// ATOMICITY — all or nothing
\DB::transaction(function () {
// These two operations are ATOMIC — either both succeed or both are rolled back
\DB::table('accounts')->where('id', 1)->decrement('balance', 100); // deduct
\DB::table('accounts')->where('id', 2)->increment('balance', 100); // add
// If the second line fails (DB error, exception), the first is rolled back
// No state where Alice has less but Bob hasn't received it
});
// CONSISTENCY — constraints enforced
try {
\DB::transaction(function () {
\DB::table('accounts')->where('id', 1)->decrement('balance', 9999);
// If balance would go negative and there's a CHECK constraint,
// the DB rejects the update — transaction fails, rolled back
});
} catch (\Illuminate\Database\QueryException $e) {
// Check constraint violation — database protected consistency
}
// ISOLATION — each transaction sees a snapshot
// Thread 1: transferring money
\DB::transaction(function () {
$balance = \DB::table('accounts')->where('id', 1)->value('balance');
// Thread 2 reads balance here → sees original value, not our modified value
// (depending on isolation level)
\DB::table('accounts')->where('id', 1)->decrement('balance', 100);
// Thread 2 reads again → still sees original (REPEATABLE READ) or new value (READ COMMITTED)
});
// DURABILITY — committed = permanent
try {
\DB::beginTransaction();
\DB::table('orders')->insert(['user_id' => 1, 'total' => 99.99, 'status' => 'pending']);
\DB::commit(); // ← After this, data is durable. Server crash here = data survives.
// InnoDB writes to the redo log (WAL) before confirming COMMIT
} catch (\Throwable $e) {
\DB::rollBack();
throw $e;
}
// Laravel's DB::transaction() handles begin/commit/rollback automatically
$order = \DB::transaction(function () {
$order = Order::create(['user_id' => 1, 'total' => 99.99]);
$order->items()->createMany([
['product_id' => 1, 'quantity' => 2, 'price' => 49.99],
]);
return $order;
// COMMIT if no exception, ROLLBACK if anything throws
}, attempts: 3); // retry up to 3 times on deadlock