#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0

    Transaction and Select For Update help


    Hello,

    Can someone tell me the differences between the following, besides the fact that with the transaction you can rollback if something bad happens? Does the transaction prevent another client from reading/writing the count until its done?

    1. Transaction way:
    start transaction;
    select count from mutable where id = 2;
    // calculate new value of count, which ends up being 100
    update mytable set count = 100 where id = 2 limit 1;
    commit;


    2. Select for Update way:
    select count from mutable where id = 2 for update;
    // calculate new value of count, which ends up being 100
    update mytable set count = 100 where id = 2 limit 1;

    Thanks!
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Actually the second option you have written is flawed. A SELECT ... FOR UPDATE doesn't work without a transaction. So basically you have written two transactional ways where you in one doesn't lock the rows in the SELECT and in the second you do.

    So your two options should have looked like:
    1. Transaction way:
    start transaction;
    select count from mutable where id = 2;
    // calculate new value of count, which ends up being 100
    update mytable set count = 100 where id = 2 limit 1;
    commit;


    2. Select for Update way:
    start transaction;
    select count from mutable where id = 2 for update;
    // calculate new value of count, which ends up being 100
    update mytable set count = 100 where id = 2 limit 1;
    commit;
    And the difference in the default transaction mode REPEATABLE READ is that the second way will lock the record earlier (already in the select and not in the update).

    But logically it doesn't matter if they both are trying to update the same value. Since in both ways the second thread will end up waiting for the first thread to commit and then the second transaction will fail and roll back.

    But if you change transaction mode some of this behavior might change.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo