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.
   

   View Slide

2. A Quick Caveat.
   

   View Slide

3. A Quick Caveat.
   No NoSQL today. Sorry.
   

   View Slide

4. The Problem
   

   View Slide

5. An example.
   

   View Slide

6. An example.
   

   View Slide

7. An example.
   

   View Slide

8. View Slide

9. View Slide

10. View Slide

11. View Slide

12. View Slide

13. ?
    

    View Slide

14. An example.
    

    View Slide

15. An example.
    $a  =  Account::findOrFail(123);

    

    //  ^-­‐-­‐  Laravel's  ORM,  Eloquent.

    //          (I  picked  a  popular  ORM.

    //            It  doesn't  really  matter  

    //            which  one.)
    

    View Slide

16. An example.
    SELECT  *

    FROM  accounts

    WHERE  id  =  123;

    $a  =

     Account::find(123);
    

    View Slide

17. An example.
    SELECT  *

    FROM  accounts

    WHERE  id  =  123;

    $a  =

     Account::find(123);

    

    //  Grabs  a  record:

    //  "id"  =>  123

    //  "name"  =>  ...

    //  "balance"  =>  5
    

    View Slide

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;
    

    View Slide

19. An example.
    Something  can  
    happen  in  here
    SELECT  *

    FROM  accounts

    WHERE  id  =  123;

    

    

    UPDATE  accounts

    SET  balance  =  10

    WHERE  id  =  123;
    

    View Slide

20. Two examples at once.
    

    View Slide

21. B
    SELECT  *

    FROM  accounts

    WHERE  id  =  123;

    A
    

    View Slide

22. B
    

    

    SELECT  *

    FROM  accounts

    WHERE  id  =  123;

    

    SELECT  *

    FROM  accounts

    WHERE  id  =  123;

    A
    

    View Slide

23. B
    

    

    SELECT  *

    FROM  accounts

    WHERE  id  =  123;

    

    

    SELECT  *

    FROM  accounts

    WHERE  id  =  123;

    

    

    UPDATE  accounts

    SET  balance  =  10

    WHERE  id  =  123;
    A
    

    View Slide

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;
    

    View Slide

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
    

    View Slide

26. [  
     "id"            =>  123,  
     "balance"  =>  9,  
     "name"        =>  "...",

     ...  
    ]
    Winner: B
    

    View Slide

27. ATOMICITY
    

    View Slide

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();
    

    View Slide

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();
    

    View Slide

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
    

    View Slide

31. Making the

    Database Interactions
    Atomic
    

    View Slide

32. Three Ways
    

    View Slide

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.
    

    View Slide

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.
    

    View Slide

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.
    

    View Slide

36. 1) Push it to the DB.
    

    View Slide

37. $a  =

     Account::find(123);

    SELECT  *

    FROM  accounts

    WHERE  id  =  123;

    1) Push it to the DB.
    

    View Slide

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.
    

    View Slide

39. 2) Add Conditions to UPDATE.
    

    View Slide

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.
    

    View Slide

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.
    

    View Slide

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.
    

    View Slide

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.
    

    View Slide

44. • Transaction Isolation
    
    • Locking
    3) Put actions inside a container.
    

    View Slide

45. "Just Add a Transaction!"
    DB::transaction(function(){

    

       $a  =  Account::findOrFail(123);  //  SELECT

       

       $a-­‐>balance  =  calculation($a-­‐>balance);

       $a-­‐>save();                                        //  UPDATE

    

    });
    

    View Slide

46. "Just Add a Transaction!"
    DB::transaction(function(){

    

       $a  =  Account::findOrFail(123);  //  SELECT

       

       $a-­‐>balance  =  calculation($a-­‐>balance);

       $a-­‐>save();                                        //  UPDATE

    

    });
    

    View Slide

47. View Slide

48. View Slide

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.
    

    View Slide

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
    

    View Slide

51. DB::transaction(function(){

    

       $a  =  Account::findOrFail(123);  //  SELECT

       

       $a-­‐>balance  =  calculation($a-­‐>balance);

       $a-­‐>save();                                        //  UPDATE

    

    });
    Isolation Level: Default
    

    View Slide

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
    

    View Slide

53. • Locking a particular row.
    
    • Locking an entire table.
    • Arbitrary application-level locks.
    Locks
    

    View Slide

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
    

    View Slide

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.
    

    View Slide

56. Fin.
    

    Rob Howard

    @damncabbage
    
    https://speakerdeck.com/damncabbage/
    

    View Slide