# Two War Stories
### (in which Locking appears as a primary antagonist)

<div><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/></div>

<div class="credit">John Melesky<br/>
(PDX PUG, February 2012)</div>
---
# 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](http://dev.mysql.com/doc/refman/5.5/en/update.html)

---
# 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!