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

i've read online php manual i'm still not sure of way these 2 functions work: mysqli::commit & mysqli::rollback.

the first thing have to:

$mysqli->autocommit(false); 

then make queries:

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

then commit transaction consisting of these 3 queries doing:

$mysqli->commit(); 

but in unfortunate case in 1 of these queries not work, 3 queries cancelled or have call rollback myself? want 3 queries atomic , considered 1 query. if 1 query fails 3 should fail , have no effect.

i'm asking because in comments i've seen on manual page: http://php.net/manual/en/mysqli.commit.php user lorenzo calls rollback if 1 of queries failed.

what's rollback if 3 queries atomic? don't understand.

edit: code example doubtful about:

<?php  $all_query_ok=true; // our control variable  $mysqli->autocommit(false); //we make 4 inserts, last 1 generates error  //if @ least 1 query returns error change our control variable  $mysqli->query("insert mycity (id) values (100)") ? null : $all_query_ok=false;  $mysqli->query("insert mycity (id) values (200)") ? null : $all_query_ok=false;  $mysqli->query("insert mycity (id) values (300)") ? null : $all_query_ok=false;  $mysqli->query("insert 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 code wrong because if of queries failed , $all_query_ok==false don't need rollback because transaction not processed. right?

i think code wrong because if of queries failed , $all_query_ok==false don't need rollback because transaction not processed. right?

no, transaction not keep track if single sql-statement fails.

if single sql-statement fails statement rolled (like described in @eggyal's answer) - transaction still open. if call commit now, there no rollback of successful statements , inserted "corrupted" data database. can reproduce 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 transtest (name) value ('foo'); query ok, 1 row affected (0.00 sec)  m> insert transtest (name) value ('foo'); error 1062 (23000): duplicate entry 'foo' key 'uq_transtest_name'  m> insert transtest (name) value ('bar'); query ok, 1 row affected (0.00 sec)  m> commit; query ok, 0 rows affected (0.02 sec)  m> select * transtest; +----+------+ | id | name | +----+------+ |  3 | bar  | |  1 | foo  | +----+------+ 2 rows in set (0.00 sec) 

you see insertion of 'foo' , 'bar' successful although second sql-statement failed - can see auto_increment-value has been increased faulty query.

so have check results of each query-call , if 1 fails, call rollback undo otherwise successful queries. lorenzo's code in php-manual makes sense.

the error forces mysql roll transaction "transaction deadlock" (and specific innodb, other storage engines may handle errors differently).