ACID vs BASE: the Lost Update Neither Engine Prevents by Default

The same naive bug — read a value, wait, write based on what you read — exists in both engines. The real difference is what each one gives you to fix it.

The bug, in Redis: two processes, one lost update

redis-cli SET balance 1000

Two processes run concurrently, each reading the current balance, waiting briefly (simulating real work between the read and the write), then writing back the old value plus their own amount:

# redis_naive_increment.sh
AMOUNT=$1
OLD=$(redis-cli GET balance)
sleep 0.3
NEW=$((OLD + AMOUNT))
redis-cli SET balance $NEW
echo "read $OLD, wrote $NEW"

sh redis_naive_increment.sh 100 &
sh redis_naive_increment.sh 50 &
wait
redis-cli GET balance

--- process A (100) ---
read 1000, wrote 1100
--- process B (50) ---
read 1000, wrote 1050
--- final balance ---
1100

Both processes read 1000 before either had written anything back. Process A wrote 1100; process B — reading the same stale 1000 — wrote 1050, silently overwriting A’s result. The correct answer (both increments applied) is 1150. 50 is gone, with no error anywhere.

The exact same bug, in PostgreSQL

UPDATE accounts SET balance = 1000 WHERE id = 1;

# pg_naive_increment.sh
AMOUNT=$1
OLD=$(psql -U postgres -t -A -c "SELECT balance FROM accounts WHERE id = 1;")
sleep 0.3
NEW=$((OLD + AMOUNT))
psql -U postgres -c "UPDATE accounts SET balance = $NEW WHERE id = 1;"
echo "read $OLD, wrote $NEW"

sh pg_naive_increment.sh 100 &
sh pg_naive_increment.sh 50 &
wait
psql -U postgres -t -A -c "SELECT balance FROM accounts WHERE id = 1;"

--- process A (100) ---
read 1000, wrote 1100
--- process B (50) ---
read 1000, wrote 1050
--- final balance ---
1100

Identical failure, identical final answer. SELECT followed by a separate UPDATE, with no locking, has exactly the same race in Postgres as GET followed by SET has in Redis. Being an ACID database doesn’t protect a client that never asks for that protection.

The fix: SELECT … FOR UPDATE

# pg_locked_increment.sh
AMOUNT=$1
psql -U postgres <<SQL
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE \gset old_
SELECT pg_sleep(0.3);
UPDATE accounts SET balance = :old_balance + $AMOUNT WHERE id = 1;
COMMIT;
SELECT balance FROM accounts WHERE id = 1;
SQL

psql -U postgres -c "UPDATE accounts SET balance = 1000 WHERE id = 1;"
sh pg_locked_increment.sh 100 &
sh pg_locked_increment.sh 50 &
wait
psql -U postgres -t -A -c "SELECT balance FROM accounts WHERE id = 1;"

--- process A (100) ---
balance
---------
    1100
--- process B (50) ---
balance
---------
    1150
--- final balance ---
1150

1150 — correct. FOR UPDATE takes a row lock the instant the SELECT runs. Process B’s SELECT ... FOR UPDATE blocked until process A’s transaction committed, so by the time B actually read the balance, it saw A’s already-applied 1100 — not the stale 1000 it would have seen without the lock. This is Postgres’s built-in answer to the exact bug demonstrated above: a row lock taken as part of the read, not bolted on afterward.

What Redis needs instead

A raw GET has no equivalent to FOR UPDATE — there’s no way to say “lock this key, I’m about to write it” as part of a plain read. Fixing this in Redis means reaching for a different feature entirely: WATCH/MULTI/EXEC (an optimistic lock that aborts the transaction if the key changed — see this site’s own redis/transactions.md for a full worked example of exactly that abort happening), or simply using INCRBY, which is atomic by construction and never has this race at all because there’s no separate read step to race against. Postgres bundles read-then-lock into one clause; Redis requires picking the specific tool the operation actually needs.

Practice

redis-cli SET balance 1000
redis-cli INCRBY balance 100 &
redis-cli INCRBY balance 50 &
wait
redis-cli GET balance

1050
1150
1150

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE query LIKE '%accounts%' AND state != 'idle';

 pid | state  | wait_event_type |  wait_event   |                        query
-----+--------+-----------------+---------------+------------------------------------------------------
 166 | active | Lock            | transactionid | SELECT balance FROM accounts WHERE id = 1 FOR UPDATE

Next: scaling patterns compared — what “scale this up” actually means differently in each engine.