Databases and Race Conditions (SydPHP, 2014)

E34acb847338523dc088f03f0eedd1eb?s=47 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.)

E34acb847338523dc088f03f0eedd1eb?s=128

Rob Howard

November 27, 2014
Tweet

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/