Bakhtiyor Bakhtiyor - 4 months ago 29
PHP Question

How to start and end transaction in mysqli?

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 the real life.

Thank you.

j0k j0k
Answer

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 behavior.
  • the INSERT INTO aren't auto committed because of the autocommit(FALSE)
  • the DROP TABLE is auto committed because the autocommit(FALSE) was resetted by the ->commit();.