How to start and end transaction in mysqli?

Question

As far as I understood transaction starts once we call $mysqli->autocommit(FALSE); statement and ends after calling $mysqli->commit(); command like in the example below.

<?php //Start transaction  $mysqli->autocommit(FALSE); $mysqli->query('UPDATE `table` SET `col`=2'); $mysqli->query('UPDATE `table1` SET `col1`=3;'); $mysqli->commit(); //End transaction  //Executing other queries without transaction control $mysqli->query("Select * from table1"); $mysqli->query("Update table1 set col1=2"); //End of executing other queries without transaction control  //Start transaction  $mysqli->autocommit(FALSE); $mysqli->query('UPDATE `table` SET `col`=2'); $mysqli->query('UPDATE `table1` SET `col1`=3;'); $mysqli->commit(); //End transaction ?> 

Have I understood correctly? If not could you please correct me, because it is actually my first time using transactions in real life.

Thank you.

Answer 1

Update Novembre 2020: @Dharman gave a better answer with more details about transactions in mysqli, just check it instead: https://stackoverflow.com/a/63764001/569101 👇

---

Well according to the php doc, you're right.

<?php $mysqli = new mysqli("localhost", "my_user", "my_password", "world");  /* check connection */ if (mysqli_connect_errno()) {     printf("Connect failed: %s\n", mysqli_connect_error());     exit(); }  $mysqli->query("CREATE TABLE Language LIKE CountryLanguage");  /* set autocommit to off */ $mysqli->autocommit(FALSE);  /* Insert some values */ $mysqli->query("INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)"); $mysqli->query("INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");  /* commit transaction */ $mysqli->commit();  /* drop table */ $mysqli->query("DROP TABLE Language");  /* close connection */ $mysqli->close(); ?> 

In the example above:

• the CREATE TABLE is auto committed because it's the default behaviour.
• the INSERT INTO aren't auto committed because of the autocommit(FALSE).
• the DROP TABLE is auto committed because the autocommit(FALSE) was reset by the ->commit();.

Answer 2

j0k is mainly right, except in the drop table.

The auto commit is not turned on with the ->commit()

Instead, the DROP TABLE is a DDL query, and DDL queries are always implicitly committed and will commit all your previously non committed work.

So, if you did not commit the work, the DDL query would force this commit.