# Two War Stories

### (in which Locking appears as a primary antagonist)

John Melesky

(PDX PUG, February 2012)

# 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 MySQL way

```
UPDATE t SET id = id + 1;
```

# The MySQL way

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

# Questions?

# Thank you!