$ ./guess_the_languageSQL & Databases
SQL & Databases · hard · #30

One deposit is lost. Which change prevents it?

-- two sessions run this at the same time
SELECT bal FROM acct WHERE id = 1;   -- both read 100
UPDATE acct SET bal = 110 WHERE id = 1;  -- app wrote 100+10
  1. Add an index on acct.id
  2. Read with SELECT DISTINCT
  3. Run each step in autocommit
  4. Use SET bal = bal + 10
Computing the new value in the app causes a lost update; an atomic bal = bal + 10 (or SELECT ... FOR UPDATE) serializes the writes.
Play SQL & Databases ▸