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).