Databases and Race Conditions (SydPHP, 2014)

"Problems that Occur when Multiple Things Use a Database at the Same Time", or how to deal with two things fighting over the same record.

(This is the second iteration of a presentation given at Rorosyd, with improvements to the examples.)

Transcript

1. Problems that Occur when Multiple Things Use a Database at the Same Time … and some suggested solutions and workarounds.
2. A Quick Caveat.
3. A Quick Caveat. No NoSQL today. Sorry.
4. The Problem
5. An example.
6. An example.
7. An example.
13. ?
14. An example.
15. An example. $a  =  Account::findOrFail(123);
 
 //  ^-­‐-­‐  Laravel's  ORM,  Eloquent.
 //          (I  picked  a  popular  ORM.
 //            It  doesn't  really  matter  
 //            which  one.)
16. An example. SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 $a  =
  Account::find(123);
17. An example. SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 $a  =
  Account::find(123);
 
 //  Grabs  a  record:
 //  "id"  =>  123
 //  "name"  =>  ...
 //  "balance"  =>  5
18. An example. $a  =
  Account::find(123);   ! ! $a-­‐>balance  =
    ($a-­‐>balance  +  5);
 $a-­‐>save(); SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123;
19. An example. Something  can   happen  in  here SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123;
20. Two examples at once.
21. B SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 A
22. B 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 A
23. B 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123; A
24. B A 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  9
 WHERE  id  =  123; SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123;
25. B 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  9
 WHERE  id  =  123; SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123; A
26. [    "id"            =>  123,    "balance"  =>  9,    "name"        =>  "...",
  ...   ] Winner: B
27. ATOMICITY
28. SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123; Atoms $a  =
  Account::find(123);   ! ! $a-­‐>balance  =
    ($a-­‐>balance  +  5);
 $a-­‐>save();
29. Atoms SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123; $a  =
  Account::find(123);   ! ! $a-­‐>balance  =
    ($a-­‐>balance  +  5);
 $a-­‐>save();
30. $a  =
  Account::find(123);   ! ! $a-­‐>balance  =
    ($a-­‐>balance  +  5);
 $a-­‐>save(); SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123; Atoms
31. Making the
 Database Interactions Atomic
32. Three Ways
33. Three Ways 1. UPDATE a column's value based on
 its current value. • Get the database to figure out the new value.
 Don't assume we know what the value is in advance.
34. Three Ways 1. UPDATE a column's value based on
 its current value. • Get the database to figure out the new value.
 Don't assume we know what the value is in advance. 2. Add conditions to the UPDATE. • Only update if our assumptions are true.
35. Three Ways 1. UPDATE a column's value based on
 its current value. • Get the database to figure out the new value.
 Don't assume we know what the value is in advance. 2. Add conditions to the UPDATE. • Only update if our assumptions are true. 3. Put everything inside a container. • Suddenly the container is the atom.
36. 1) Push it to the DB.
37. $a  =
  Account::find(123);
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 1) Push it to the DB.
38. $a  =
  Account::find(123);
 
 
 
 $a-­‐>increment(
    'balance',  5
 );
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 UPDATE  accounts
 SET  balance  =
  balance  +  5
 WHERE  id  =  123; 1) Push it to the DB.
39. 2) Add Conditions to UPDATE.
40. 2) Add Conditions to UPDATE. SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123; $a  =
  Account::find(123);   
 //  Do  something  we  can't  
 //  use  SQL  for.  Let's  
 //  call  it  calculate():   $a-­‐>balance  =
  calculate($a-­‐>balance);
 $a-­‐>save();
 
 //  ^-­‐-­‐  This  is  broken.
41. $a  =  Account::find(123);
 
 Account::whereId(123)
 -­‐>whereBalance(
  $a-­‐>balance
 )
 -­‐>update(["balance"  =>
  calculate($a-­‐>balance),
 ]);
 
 //  ^-­‐-­‐  This  is  better.
 
 //  =>  1  means  it  worked
 //  =>  0  means  it  didn't -­‐-­‐  SELECT  ...
 
 UPDATE  accounts
 SET
  balance  =  10
 WHERE
  id  =  123  AND
  balance  =  5; 2) Add Conditions to UPDATE.
42. $a  =  Account::find(123);
 
 Account::whereId(123)
 -­‐>whereVersion(
  $a-­‐>version
 )
 -­‐>update([
  "balance"  =>
      calculate($a-­‐>balance),
  "version"  =>
      $a-­‐>version  +  1
 ]);
 
 //  ^-­‐-­‐  Generalised -­‐-­‐  SELECT  ...
 
 UPDATE  accounts
 SET
  balance  =  10
  version  =  5
 WHERE
  id  =  123  AND
  version  =  4; 2) Add Conditions to UPDATE.
43. • Mostly Good Enough. • The "did it update or not?" counter is too coarse. • Can't differentiate between Stale and Gone.
 
 (Needs an extra SELECT round-trip, and we're starting to replicate features the database already gives us.) 2) Add Conditions to UPDATE.
44. • Transaction Isolation • Locking 3) Put actions inside a container.
45. "Just Add a Transaction!" DB::transaction(function(){
 
    $a  =  Account::findOrFail(123);  //  SELECT
    
    $a-­‐>balance  =  calculation($a-­‐>balance);
    $a-­‐>save();                                        //  UPDATE
 
 });
46. "Just Add a Transaction!" DB::transaction(function(){
 
    $a  =  Account::findOrFail(123);  //  SELECT
    
    $a-­‐>balance  =  calculation($a-­‐>balance);
    $a-­‐>save();                                        //  UPDATE
 
 });
49. B 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  9
 WHERE  id  =  123; SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123; A Still  stomping
 all  over  A.
50. • Read Uncommitted (MySQL default) • Read Committed (PostgreSQL default) • Repeatable Read • Serializable
 
 Good references:
 http://www.postgresql.org/docs/9.1/static/transaction-iso.html
 http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html Isolation Levels
51. DB::transaction(function(){
 
    $a  =  Account::findOrFail(123);  //  SELECT
    
    $a-­‐>balance  =  calculation($a-­‐>balance);
    $a-­‐>save();                                        //  UPDATE
 
 }); Isolation Level: Default
52. DB::transaction(function(){
    DB::execute(
        'SET  TRANSACTION  ISOLATION  LEVEL  SERIALIZABLE'
    );  //  PostgreSQL
 
    $a  =  Account::findOrFail(123);                    //  SELECT
    
    $a-­‐>balance  =  calculation($a-­‐>balance);
    $a-­‐>save();                                                          //  UPDATE
 
 }); Isolation Level: Maximum
53. • Locking a particular row. • Locking an entire table. • Arbitrary application-level locks. Locks
54. $a  =  Account::whereId(123)
                        -­‐>lockForUpdate()
                        -­‐>firstOrFail();
 //  SELECT  ...  FOR  UPDATE
 //  Locks  row;  anything  else  has  to  wait  in  
 //  line  until  COMMIT  is  called.
 
 $a-­‐>balance  =  calculation($a-­‐>balance);
 $a-­‐>save();
 //  UPDATEs,  COMMITs
 //  Other  processes  free  to  run  a  SELECT. Locks: SELECT FOR UPDATE
55. • Push to the database where possible. • Use Isolation or Locks where not. • The more restrictions, the slower it's gonna go. • More work, but makes the problem visible. Summin' up.
56. Fin. 
 Rob Howard
 @damncabbage https://speakerdeck.com/damncabbage/