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

    Problems that Occur when Multiple Things Use a Database at

    the Same Time … and some suggested solutions and workarounds.
  2. 8.
  3. 9.
  4. 10.
  5. 11.
  6. 12.
  7. 13.

    ?

  8. 15.

    An example. $a  =  Account::findOrFail(123);
 
 //  ^-­‐-­‐  Laravel's  ORM,

     Eloquent.
 //          (I  picked  a  popular  ORM.
 //            It  doesn't  really  matter  
 //            which  one.)
  9. 17.

    An example. SELECT  *
 FROM  accounts
 WHERE  id  =  123;


    $a  =
  Account::find(123);
 
 //  Grabs  a  record:
 //  "id"  =>  123
 //  "name"  =>  ...
 //  "balance"  =>  5
  10. 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;
  11. 19.

    An example. Something  can   happen  in  here SELECT  *


    FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123;
  12. 22.

    B 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =

     123;
 
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 A
  13. 23.

    B 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =

     123;
 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123; A
  14. 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;
  15. 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
  16. 26.

    [    "id"            =>  123,

       "balance"  =>  9,    "name"        =>  "...",
  ...   ] Winner: B
  17. 27.
  18. 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();
  19. 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();
  20. 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
  21. 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.
  22. 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.
  23. 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.
  24. 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.
  25. 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.
  26. 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.
  27. 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.
  28. 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.
  29. 45.

    "Just Add a Transaction!" DB::transaction(function(){
 
    $a  =  Account::findOrFail(123);

     //  SELECT
    
    $a-­‐>balance  =  calculation($a-­‐>balance);
    $a-­‐>save();                                        //  UPDATE
 
 });
  30. 46.

    "Just Add a Transaction!" DB::transaction(function(){
 
    $a  =  Account::findOrFail(123);

     //  SELECT
    
    $a-­‐>balance  =  calculation($a-­‐>balance);
    $a-­‐>save();                                        //  UPDATE
 
 });
  31. 47.
  32. 48.
  33. 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.
  34. 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
  35. 51.

    DB::transaction(function(){
 
    $a  =  Account::findOrFail(123);  //  SELECT
    


       $a-­‐>balance  =  calculation($a-­‐>balance);
    $a-­‐>save();                                        //  UPDATE
 
 }); Isolation Level: Default
  36. 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
  37. 53.

    • Locking a particular row. • Locking an entire table.

    • Arbitrary application-level locks. Locks
  38. 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
  39. 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.