ACID vs BASE

A user clicks "Pay Now" on a checkout page. Their browser is slow — they click again. Two identical payment requests hit your server at the same millisecond. Your database processes both. The customer is charged £340 twice. Their bank account is short £340. Your support queue explodes. This is not a theoretical problem — it happens to real companies every week. The difference between a system where this is impossible and one where it happens daily comes down to one choice: ACID or BASE.

ACID — Four Guarantees, Each One Critical

ACID is a set of four properties that a database transaction must have to be considered reliable. Each letter protects you from a specific class of failure. Lose any one of them and data corruption becomes possible.

ACID in Code — A Real Bank Transfer

The scenario: You're building the transfer feature for a fintech app. User A sends £200 to User B. This requires two database writes — debit A, credit B. Here's how ACID protects this in PostgreSQL:

-- Start the transaction — nothing is saved until COMMIT
BEGIN;

-- Step 1: Debit the sender
UPDATE accounts
SET balance = balance - 200
WHERE user_id = 'user_A'
  AND balance >= 200;  -- consistency check: can't go negative

-- Step 2: Check that Step 1 actually updated a row
-- If 0 rows were updated, the sender didn't have enough balance
-- ROLLBACK undoes everything — atomicity in action
GET DIAGNOSTICS row_count = ROW_COUNT;
IF row_count = 0 THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient balance';
END IF;

-- Step 3: Credit the receiver
UPDATE accounts
SET balance = balance + 200
WHERE user_id = 'user_B';

-- Step 4: Make it permanent — both writes saved atomically
COMMIT;

-- Before transfer:
user_A balance: £500
user_B balance: £100

-- After COMMIT:
user_A balance: £300   (-£200)
user_B balance: £300   (+£200)

-- Total money in system: £600 → £600   ✓ (no money created or destroyed)

What Happens Without ACID — The Race Condition

The scenario: Same transfer — but this time there's no transaction. Two checkout requests for the same item arrive at exactly the same millisecond. Watch what breaks:

-- Request 1 reads stock: 1 item left
SELECT stock FROM products
WHERE id = 'ITEM_99';
-- Returns: 1

-- Request 2 ALSO reads stock: 1 item left
SELECT stock FROM products
WHERE id = 'ITEM_99';
-- Returns: 1

-- Both think 1 item is available
-- Both proceed to place the order
-- Both decrement stock
UPDATE products SET stock = stock - 1
WHERE id = 'ITEM_99';
-- stock is now: -1

BEGIN;

-- SELECT FOR UPDATE locks this row
-- Request 2 must WAIT until Request 1 finishes
SELECT stock FROM products
WHERE id = 'ITEM_99'
FOR UPDATE;
-- Returns: 1 (locked)

UPDATE products
SET stock = stock - 1
WHERE id = 'ITEM_99'
  AND stock > 0;

COMMIT;
-- Lock released. Request 2 now runs.
-- Sees stock = 0. Zero rows updated.
-- ROLLBACK. "Out of stock" returned.

BASE — The Alternative Contract

BASE is not a weakness — it's a deliberate design choice made by NoSQL databases that prioritise scale and availability over strict consistency. It stands for three things:

BASE in Code — The Same Transfer, Different World

The scenario: You're tracking user wallet balances in Cassandra for a gaming platform. Points are awarded constantly — thousands per second. Perfect consistency isn't required — if a player sees 4,820 points instead of 4,821 for half a second, nothing breaks. But the system must never slow down or refuse a write.

from cassandra.cluster import Cluster
from cassandra import ConsistencyLevel
from cassandra.query import SimpleStatement

session = Cluster(['node1','node2','node3']).connect('gaming')

# Add 50 points to a player's balance
# ONE = only one node needs to confirm — fastest possible write
add_points = SimpleStatement(
  "UPDATE wallets SET points = points + 50 WHERE player_id = %s",
  consistency_level=ConsistencyLevel.ONE
)

session.execute(add_points, ('player_7721',))

-- Write acknowledged immediately by node1
-- node2 and node3 will sync within ~50ms

Result: Applied
Latency: 1.2ms

-- If player reads balance right now from node2:
-- Might see: 4,820  (old value — node2 not synced yet)
-- 50ms later from node2:
-- Will see:  4,870  (synced — eventually consistent)

The ACID vs BASE Spectrum

It's not a binary switch — it's a dial. Different databases and different settings land at different points on this spectrum:

When Each Model Wins — 4 Real Scenarios

The Consequences of Choosing Wrong

Practice Questions — Spot the Bug

Quiz — Production Incident. ACID or BASE Failure?