SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot Offspring2099's Avatar
    Join Date
    May 2005
    Location
    Los Angeles, CA
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Transactions - Rollback

    Is it necessary to call 'ROLLBACK' statement if one of your queries fails? Or is it sufficient just NOT to call 'COMMIT'.

    Seems to be working both ways for me.

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If there is an error, the engine automatically does a rollback.
    The transaction is automatically closed

  3. #3
    SitePoint Zealot Offspring2099's Avatar
    Join Date
    May 2005
    Location
    Los Angeles, CA
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tripy View Post
    If there is an error, the engine automatically does a rollback.
    The transaction is automatically closed
    I'm a little confused.
    In this simple case:

    SET autocommit=0;
    START TRANSACTION;

    INSERT 1
    INSERT 2 //fails
    INSERT 3

    COMMIT;

    Insert 1 and 3 still go through.
    So the transaction wasn't closed? Rollback not initiated implicitly?

  4. #4
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I have to say that I'm more used to Oracle, postgresql, sqlite and ms sql server than mysql.
    In fact, I haven't touched to mysql in the last 6 years...

    I don't know the way mysql handle this, and especially not which table engine plugin handle in which way, but I know that some mysql engines simply don't implements transaction.
    Start transaction and roolback/commit probably don't trigger any error, but may not do anything either.

    What I know, is that in those other engines, if you have 1 statement that fails in a sequence, then the whole transaction (even the queries that had no problems) is rolled back, and that makes plain sense.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tripy View Post
    What I know, is that in those other engines, if you have 1 statement that fails in a sequence, then the whole transaction (even the queries that had no problems) is rolled back, and that makes plain sense.
    this is not true. if the SQL server returns an error on a single query, it will NOT automatically rollback the whole transaction. rollbacks only happen automatically if the connection is closed without an explicit COMMIT or if the server crashes before a COMMIT.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SitePoint Zealot Offspring2099's Avatar
    Join Date
    May 2005
    Location
    Los Angeles, CA
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    this is not true. if the SQL server returns an error on a single query, it will NOT automatically rollback the whole transaction. rollbacks only happen automatically if the connection is closed without an explicit COMMIT or if the server crashes before a COMMIT.
    Got it. So I don't really have to use ROLLBACK explicitly if my connection closes, which it does. But it's nice to have for completeness, which probably clears up some logs?

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it's good to include it for completeness, and it might prevent a bug in the future. for example, an modular component of your application may fault and want a rollback. if your use this component inside another component that handles errors in a manner other than immediately terminating, then the transaction might get committed.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  8. #8
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    rollbacks only happen automatically if the connection is closed without an explicit COMMIT or if the server crashes before a COMMIT.
    I was rather thinking about a single statement that updates thousands of rows.
    If one of the constraints placed on a column fires, then everything is rolled back.
    Now, if you use a cursor, open a transaction, fires the query, commit it and go to the next row, then of course only the rows where an error occurred would stay unchanged.
    But at the cost of a lousy query plan and much unneeded strain on the server, if you don't know why you use it that way.

    My previous post was not explicit enough on the context.

  9. #9
    SitePoint Zealot Offspring2099's Avatar
    Join Date
    May 2005
    Location
    Los Angeles, CA
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks fellas. Really appreciate the help.

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    tripy, i understand now what you were trying to say. you're describing statement atomicity: the whole statement works, or it doesn't. however, one statement failing with an error will not roll back a transaction. transactions and statements are not the same thing.

    (and to make the distinction even muddier, innodb internally wraps every statement in a transaction to achieve statement atomicity, but that is not the only way to do it)
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •