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

How does a database like PostgreSQL resolve this?

T1: UPDATE acct SET bal = 0 WHERE id = 1;
T2: UPDATE acct SET bal = 0 WHERE id = 2;
T1: UPDATE acct SET bal = 0 WHERE id = 2;  -- waits
T2: UPDATE acct SET bal = 0 WHERE id = 1;  -- waits
  1. Both commit; the last write wins
  2. It merges the two transactions
  3. It aborts one of them as victim
  4. Both wait forever until restart
Each holds a lock the other needs: a deadlock. The detector cancels one transaction so the other can proceed.
Play SQL & Databases ▸