8

This seems like a simple enough question, yet I couldn't find any definitive answers specific for MySQL. Look at this:

$mysqli->autocommit(false); //Start the transaction
$success = true;    

/* do a bunch of inserts here, which will be rolled back and
   set $success to false if they fail */

if ($success) {
    if ($mysqli->commit()) {
        /* display success message, possibly redirect to another page */
    }
    else {
        /* display error message */
        $mysqli->rollback(); //<----------- Do I need this?
    }
}

$mysqli->autocommit(true); //Turns autocommit back on (will be turned off again if needed)

//Keep running regardless, possibly executing more inserts

The thing is, most examples I have seen just end the script if commiting failed, either by letting it finish or by explicitly calling exit(), which apparently auto rolls back the transaction(?), but what if I need to keep running and possibly execute more database-altering operations later? If this commit failed and I didn't have that rollback there, would turning autocommit back on (which according to this comment on the PHP manual's entry on autocommit does commit pending operations) or even explicitly calling another $mysqli->commit() later on, attempt to commit the previous inserts again, since it failed before and they weren't rolled back?

I hope I've been clear enough and that I can get a definitive answer for this, which has been bugging me quite a lot.

Edit: OK, maybe I phrased my question wrong in that line comment. It's not really a matter of whether or not I need the rollback, which, as it was pointed out, would depend on my use case, but really what is the effect of having or not having a rollback in that line. Perhaps a simpler question would be: does a failed commit call discards pending operations or does it just leaves them in their pending state, waiting for a rollback or another commit?

  • 1
    easy enough to test. –  Jun 30 '16 at 21:07
  • 3
    not a definitive thing, but...: not all commit failures are fatal. eg. consider a case where a commit fails due to a lock timing out. that may not be the case again if you re-commit a little bit later. auto-roll back would needlessly undo all that work. – Marc B Jun 30 '16 at 21:10
  • Completely depends on your use case – Farkie Jun 30 '16 at 21:18
  • @Dagon How can I force `commit` to fail, so I can test that? –  Jun 30 '16 at 21:18
  • @Karu Try creating a duplicate of a unique key. – Barmar Jun 30 '16 at 21:29
  • @Barmar Wouldn't that make the query itself fail rather than the commit (and $success be set to false)? –  Jun 30 '16 at 21:47
  • Yeah, you haven't gotten to commit yet – Drew Jun 30 '16 at 21:48
  • @Karu You're right, that doesn't work. Commit failures are hard to create in MySQL, they usually only happen with systemic failures, like losing the connection to the server. See http://stackoverflow.com/questions/3960189/can-a-commit-statement-in-sql-ever-fail-how – Barmar Jun 30 '16 at 21:59
  • 2
    I can't find any documentation that explicitly answers your question. I think it would be best to assume that it doesn't automatically rollback, and call `rolback()` yourself. I don't see how it can hurt. – Barmar Jun 30 '16 at 22:04
  • @Barmar Yeah, that's probably what I'll do, it's best to be safe. I came across that question you linked before, by the way, when I was searching before posting this, yet even rereading it now I can't immediately think of a practical way to simulate a commit failure, so I think I'll just add the rollback and move on. Unfortunately the PHP manual can be lacking sometimes. Thanks for your help. –  Jun 30 '16 at 22:18
  • This isn't something that should be found in the PHP manual, it should be in the MySQL manual. But its documentation on commit doesn't address this detail. – Barmar Jun 30 '16 at 23:42
  • @Barman Sure, but since PHP wraps it in its own function, you never know what extra operations it might try to perform for convenience (well, you could know, if you looked at the source, but I don't think many people have that habit), which is a good intention, but if not well documented could lead to some puzzling situations. –  Jun 30 '16 at 23:53
  • To test commit failure: 1. start transaction; 2. perform query; 3. pause while you have another process do something guaranteed to cause commit failure (e.g. shut down the DB server, sever the network connection, etc.); 4. Attempt to perform commit. – kmoser May 05 '20 at 04:16
  • Does this answer your question? [If an PHP PDO transaction fails, must I rollback() explicitely?](https://stackoverflow.com/questions/2001698/if-an-php-pdo-transaction-fails-must-i-rollback-explicitely) – Walf Feb 18 '22 at 01:37

1 Answers1

0

If you are NOT re-using the connection and it is closed immediately after the transaction fails, closing the connection would cause an implicit rollback anyway.

If you are re-using the connection, you should definitely do a rollback to avoid inconsistency with any follow-up statements.

And if you are not really re-using it but it is still in a blocking state (e.g. being left open for a couple of seconds or even minutes, depending e.g. whether you're on a website or a cronjob), please keep in mind that there can be many concurrent connections going on. So if you have a very large transaction, the server needs to hold it in a temporary state which might consume lots of memory (e.g. if you're doing a major database migration that affects lots of columns or tables) you should definitely do an explicit rollback or close the connection for an implicit rollback after it fails.

Another factor is => if you have lots of concurrent connections in different processes, they may or may not already see parts of the transaction, even if it's not committed yet, depending on the transaction isolation level that you are using. See also: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

Mario Trojan
  • 11
  • 1