#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171

    MySQL transaction


    Hello;

    By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent.

    But to disable autocommit mode implicitly for a single series of statements we can use START TRANSACTION. With START TRANSACTION autocommit remains disabled until you end the transaction with COMMIT. Right?

    Based on the above I expected this code not to make updates permenantly (unless I commit).
    Code:
    START TRANSACTION;
    UPDATE items SET price = 1 where price = 3
    But running the above, updates the database and when I restart the server the changes seem to be there still as permenant.

    Same thing with rollback. When I rollback it doesn't rollback!

    What am I missing
  2. #2
  3. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171
    Looks like it only applies to InnoDB and not MyISAM type.
  4. #3
  5. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    That is correct. MyISAM does not support transactions.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  6. #4
  7. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171
    Is it correct to put it this way:

    The only reason for transactions is to make sure either all queries grouped in the transaction run successfully or none get executed. For example withdraw money from one account and deposit it to another account.
    Code:
    SET AUTOCOMMIT =0;
    START TRANSACTION;
    UPDATE accounts SET balance = balance-500 WHERE id = 329898;
    UPDATE accounts SET balance = balance+500 WHERE id = 3428;
    COMMIT;
    Where does ROLLBACK fit in this case? Something like this maybe?
    Code:
    SET AUTOCOMMIT =0;
    START TRANSACTION;
    UPDATE accounts SET balance = balance-500 WHERE id = 329898;
    IF SUUCCESS
    THEN
    UPDATE accounts SET balance = balance+500 WHERE id = 3428;
    IF SUCCESS
    THEN
    COMMIT;
    ELSE
    ROLLBACK
    Last edited by zxcvbnm; April 17th, 2013 at 06:26 AM.

IMN logo majestic logo threadwatch logo seochat tools logo