Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Databases and Race Conditions (SydPHP, 2014)

Rob Howard
November 27, 2014

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.)

Rob Howard

November 27, 2014
Tweet

More Decks by Rob Howard

Other Decks in Technology

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.

  8. None
  9. None
  10. None
  11. None
  12. None
  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
 
 });
  47. None
  48. None
  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/