$30 off During Our Annual Pro Sale. View Details »

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.

    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