Lock in $30 Savings on PRO—Offer Ends Soon! ⏳

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

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

     Eloquent.
 //          (I  picked  a  popular  ORM.
 //            It  doesn't  really  matter  
 //            which  one.)
  4. An example. SELECT  *
 FROM  accounts
 WHERE  id  =  123;


    $a  =
  Account::find(123);
 
 //  Grabs  a  record:
 //  "id"  =>  123
 //  "name"  =>  ...
 //  "balance"  =>  5
  5. 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;
  6. An example. Something  can   happen  in  here SELECT  *


    FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123;
  7. B 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =

     123;
 
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 A
  8. B 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =

     123;
 
 
 SELECT  *
 FROM  accounts
 WHERE  id  =  123;
 
 
 UPDATE  accounts
 SET  balance  =  10
 WHERE  id  =  123; A
  9. 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;
  10. 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
  11. [    "id"            =>  123,

       "balance"  =>  9,    "name"        =>  "...",
  ...   ] Winner: B
  12. 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();
  13. 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();
  14. $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
  15. 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.
  16. 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.
  17. 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.
  18. $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.
  19. 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.
  20. $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.
  21. $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.
  22. • 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.
  23. "Just Add a Transaction!" DB::transaction(function(){
 
    $a  =  Account::findOrFail(123);

     //  SELECT
    
    $a-­‐>balance  =  calculation($a-­‐>balance);
    $a-­‐>save();                                        //  UPDATE
 
 });
  24. "Just Add a Transaction!" DB::transaction(function(){
 
    $a  =  Account::findOrFail(123);

     //  SELECT
    
    $a-­‐>balance  =  calculation($a-­‐>balance);
    $a-­‐>save();                                        //  UPDATE
 
 });
  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 Still  stomping
 all  over  A.
  26. • 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
  27. DB::transaction(function(){
 
    $a  =  Account::findOrFail(123);  //  SELECT
    


       $a-­‐>balance  =  calculation($a-­‐>balance);
    $a-­‐>save();                                        //  UPDATE
 
 }); Isolation Level: Default
  28. 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
  29. • Locking a particular row. • Locking an entire table.

    • Arbitrary application-level locks. Locks
  30. $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
  31. • 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.