Question 1387

Minus One Or Zero

stock holds one row: sku = 'A-40', qty = 1. Both sessions run at PostgreSQL's default READ COMMITTED.

time Session 1 Session 2
t1 BEGIN;
t2 BEGIN;
t3 UPDATE stock SET qty = qty - 1 WHERE sku = 'A-40' AND qty > 0; reports UPDATE 1
t4 UPDATE stock SET qty = qty - 1 WHERE sku = 'A-40' AND qty > 0; blocks
t5 COMMIT;
t6 statement returns

What does session 2 report at t6, and what is qty after both sessions commit?

A

UPDATE 1, and qty ends at 0. The re-evaluated statement re-reads qty as 1 from the snapshot session 2 took at BEGIN.

B

UPDATE 0, and qty stays at 0. Session 2 re-evaluates its WHERE against the new row version, qty > 0 is false, and the row is skipped.

C

UPDATE 1, and qty ends at -1. Session 2 read qty as 1 in its own snapshot and applies its arithmetic to that value.

D

Session 2 aborts with ERROR: could not serialize access due to concurrent update and has to retry the whole transaction.