Two War Stories

(in which Locking appears as a primary antagonist)

Imagine, if you will...

The log

SELECT * FROM definition_table WHERE defname='foobarbaz' FOR SHARE;
SELECT * FROM definition_table WHERE defname='foobarbaz' FOR SHARE;
SELECT * FROM definition_table WHERE defname='foobarbaz' FOR SHARE;
SELECT * FROM definition_table WHERE defname='foobarbaz' FOR SHARE;
SELECT * FROM definition_table WHERE defname='foobarbaz' FOR SHARE;

The problem

The problem

• Even read-only locks need to write

The problem

• Even read-only locks need to write
• That write can have contention

The solution

The conclusion

The conclusion

• Concurrency is baaad, mmkay?

The conclusion

• Concurrency is baaad, mmkay?
• MySQL had it right all along

Questions?

Now, imagine....

Egads! Deadlocks?

The culprit(s)

SELECT * FROM sum_table WHERE sum_id = 1234
  AND sum_date BETWEEN '2011-01-01' AND '2011-01-10' FOR UPDATE;
SELECT * FROM sum_table WHERE sum_id = 1234
  AND sum_date BETWEEN '2011-01-03' AND '2011-01-13' FOR UPDATE;

The problem

The problem

• Lock acquisition is atomic

The problem

• Lock acquisition is only atomic per-lock

The problem

• Lock acquisition is only atomic per-lock
• Locks need to be acquired in a stable order

The solution

The real problem

The real problem

UPDATE sum_table SET last_update = now()
  WHERE sum_id = 1234
  AND sum_date BETWEEN '2011-01-01' and '2011-01-10';

The real solution

The real solution

UPDATE sum_table SET last_update = now()
  WHERE sum_id = 1234
  AND sum_date BETWEEN '2011-01-01' and '2011-01-10'
  ORDER BY sum_id, sum_date;

The conclusion

The conclusion

• Transactional atomicity is not the same as being deadlock-proof

The conclusion

• Transactional atomicity is not the same as being deadlock-proof
• MySQL had it right all along

The conclusion

• Transactional atomicity is not the same as being deadlock-proof
• MySQL had it right all along

The MySQL way

UPDATE t SET id = id + 1;

The MySQL way

UPDATE t SET id = id + 1 ORDER BY id DESC;

Questions?

Thank you!