TodoMVCC

Two users editing the same todo simultaneously:

-- User A starts editing todo #123
BEGIN;
SELECT title FROM todos WHERE id = 123;  -- "Buy groceries"

-- User B also starts editing the same todo
BEGIN;
SELECT title FROM todos WHERE id = 123;  -- "Buy groceries"

-- User A updates the title
UPDATE todos SET title = 'Buy groceries and milk' WHERE id = 123;
COMMIT;

-- User B updates based on stale data
UPDATE todos SET title = 'Buy groceries and bread' WHERE id = 123;
COMMIT;

-- Result: User A's changes are lost!

In READ COMMITTED: User B's update overwrites User A's changes.

In SERIALIZABLE: One transaction would be aborted and retry with fresh data.

Maintaining a business constraint across multiple todos:

-- Rule: At most 3 todos can be marked "urgent" priority
-- Currently 2 urgent todos exist

-- Transaction A: Mark todo #456 as urgent
BEGIN;
SELECT COUNT(*) FROM todos WHERE priority = 'urgent';  -- Returns 2
-- User thinks: "OK, I can add one more"
UPDATE todos SET priority = 'urgent' WHERE id = 456;

-- Transaction B: Mark todo #789 as urgent
BEGIN;
SELECT COUNT(*) FROM todos WHERE priority = 'urgent';  -- Returns 2
-- User thinks: "OK, I can add one more"
UPDATE todos SET priority = 'urgent' WHERE id = 789;

-- Both transactions commit successfully
-- Result: 4 urgent todos! Business rule violated.

In READ COMMITTED: Both transactions see consistent snapshots but create inconsistent final state.

In SERIALIZABLE: One transaction would be aborted to maintain the constraint.

For a deep dive into consistency models and isolation levels, check out Jepsen's Consistency Models - an excellent interactive guide to understanding how different databases handle consistency.

Key resources:

• Jepsen on Serializability
• Snapshot Isolation vs Serializability
• Interactive Consistency Explorer

Manual versioning with locking-based concurrency control.

Characteristics:

• Manual version columns and triggers
• Read committed isolation issues
• Lock contention under load
• Complex audit trail implementation
• Lost update and phantom read anomalies

Example Schema:

CREATE TABLE todos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    completed BOOLEAN NOT NULL DEFAULT false,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    version BIGINT NOT NULL DEFAULT 1
);

CREATE TABLE todos_history (
    id UUID NOT NULL,
    title TEXT NOT NULL,
    completed BOOLEAN NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL,
    version BIGINT NOT NULL,
    txid BIGINT NOT NULL DEFAULT txid_current(),
    PRIMARY KEY (id, version)
);

📖 View Complete PostgreSQL Example →

Built-in time dimensions with optimistic concurrency control.

Characteristics:

• Automatic bitemporal versioning
• Serializable transactions by default
• Lock-free optimistic concurrency
• Complete audit trail built-in
• No anomalies, perfect consistency

Example Query:

-- Time travel query (built-in)
SELECT * FROM todos
FOR SYSTEM_TIME AS OF '2024-01-01T12:00:00Z'
WHERE completed = false;

-- No manual versioning needed!
INSERT INTO todos (id, title, completed)
VALUES (1, 'Learn XTDB', false);

XTDB implementation coming soon...