How does mysqli::commit & mysqli::rollback work?

Question

I've read the online php manual but I'm still not sure of the way these two functions work: mysqli::commit & mysqli::rollback.

The first thing I have to do is to:

$mysqli->autocommit(FALSE);

Then I make some queries:

$mysqli->query("...");
$mysqli->query("...");
$mysqli->query("...");

Then I commit the transaction consisting of these 3 queries by doing:

$mysqli->commit();

BUT in the unfortunate case in which one of these queries does not work, do all 3 queries get cancelled or do I have to call a rollback myself? I want all 3 queries to be atomic and be considered as only one query. If one query fails then all 3 should fail and have no effect.

I'm asking this because in the comments I've seen on the manual page: http://php.net/manual/en/mysqli.commit.php the user Lorenzo calls a rollback if one of the queries failed.

What's a rollback good for if the 3 queries are atomic? I don't understand.

EDIT: This is the code example I am doubtful about:

<?php 
$all_query_ok=true; // our control variable 
$mysqli->autocommit(false);
//we make 4 inserts, the last one generates an error 
//if at least one query returns an error we change our control variable 
$mysqli->query("INSERT INTO myCity (id) VALUES (100)") ? null : $all_query_ok=false; 
$mysqli->query("INSERT INTO myCity (id) VALUES (200)") ? null : $all_query_ok=false; 
$mysqli->query("INSERT INTO myCity (id) VALUES (300)") ? null : $all_query_ok=false; 
$mysqli->query("INSERT INTO myCity (id) VALUES (100)") ? null : $all_query_ok=false; //duplicated PRIMARY KEY VALUE 

//now let's test our control variable 
$all_query_ok ? $mysqli->commit() : $mysqli->rollback(); 

$mysqli->close(); 
?>

I think this code is wrong because if any of the queries failed and $all_query_ok==false then you don't need to do a rollback because the transaction was not processed. Am I right?

Answer 1

I think this code is wrong because if any of the queries failed and $all_query_ok==false then you don't need to do a rollback because the transaction was not processed. Am I right?

No, the transaction does not keep track if a single SQL-Statement fails.

If a single SQL-Statement fails the statement is rolled back (like it is described in @eggyal's Answer) - but the transaction is still open. If you call commit now, there is no rollback of the successful statements and you just inserted "corrupted" data into your database. You can reproduce this easily:

m> CREATE TABLE transtest (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL DEFAULT '',
 CONSTRAINT UNIQUE KEY `uq_transtest_name` (name)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

m> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

m> INSERT INTO transtest (name) VALUE ('foo');
Query OK, 1 row affected (0.00 sec)

m> INSERT INTO transtest (name) VALUE ('foo');
ERROR 1062 (23000): Duplicate entry 'foo' for key 'uq_transtest_name'

m> INSERT INTO transtest (name) VALUE ('bar');
Query OK, 1 row affected (0.00 sec)

m> COMMIT;
Query OK, 0 rows affected (0.02 sec)

m> SELECT * FROM transtest;
+----+------+
| id | name |
+----+------+
|  3 | bar  |
|  1 | foo  |
+----+------+
2 rows in set (0.00 sec)

You see that the insertion of 'foo' and 'bar' were successful although the second SQL-statement failed - you can even see that the AUTO_INCREMENT-value has been increased by the faulty query.

So you have to check the results of each query-call and if one fails, call rollback to undo the otherwise successful queries. So Lorenzo's code in the PHP-manual makes sense.

The only error which forces MySQL to roll back the transaction is a "transaction deadlock" (and this is specific to InnoDB, other storage engines may handle those errors differently).

Answer 2

As documented under InnoDB Error Handling:

Error handling in InnoDB is not always the same as specified in the SQL standard. According to the standard, any error during an SQL statement should cause rollback of that statement. InnoDB sometimes rolls back only part of the statement, or the whole transaction. The following items describe how InnoDB performs error handling:

• If you run out of file space in a tablespace, a MySQL Table is full error occurs and InnoDB rolls back the SQL statement.

• A transaction deadlock causes InnoDB to roll back the entire transaction. Retry the whole transaction when this happens.

  A lock wait timeout causes InnoDB to roll back only the single statement that was waiting for the lock and encountered the timeout. (To have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout option.) Retry the statement if using the current behavior, or the entire transaction if using --innodb_rollback_on_timeout.

  Both deadlocks and lock wait timeouts are normal on busy servers and it is necessary for applications to be aware that they may happen and handle them by retrying. You can make them less likely by doing as little work as possible between the first change to data during a transaction and the commit, so the locks are held for the shortest possible time and for the smallest possible number of rows. Sometimes splitting work between different transactions may be practical and helpful.

  When a transaction rollback occurs due to a deadlock or lock wait timeout, it cancels the effect of the statements within the transaction. But if the start-transaction statement was START TRANSACTION or BEGIN statement, rollback does not cancel that statement. Further SQL statements become part of the transaction until the occurrence of COMMIT, ROLLBACK, or some SQL statement that causes an implicit commit.

• A duplicate-key error rolls back the SQL statement, if you have not specified the IGNORE option in your statement.

• A row too long error rolls back the SQL statement.

• Other errors are mostly detected by the MySQL layer of code (above the InnoDB storage engine level), and they roll back the corresponding SQL statement. Locks are not released in a rollback of a single SQL statement.