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.

Answer 3

How to use transactions in mysqli?

Prerequisite

In order for the transactions to behave properly you should enable exception error reporting. Otherwise mysqli will not report errors and the transaction will not be performed correctly. Alternatively, you could manually check each query, but that is not recommended. To connect properly with mysqli use the following 3 lines:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'user', 'pass', 'dbname');
$mysqli->set_charset('utf8mb4'); // always set the charset

Transactions only work with transactional tables. Make sure that your table storage engine supports transactions. For example, MyISAM ignores the commit/rollback.

Transactions

There are two possible ways to create a transaction using mysqli. By default all queries/statements are committed as soon as they are performed. You can either switch autocommit off or use a one-time-only transaction.

Transactions are committed to the database in the following situations:

• when calling commit
• after setting autocommit=1
• when starting another transaction
• when performing DDL query
• and in a few other situations. For more information see Statements That Cause an Implicit Commit

Using autocommit(false)

If you turn autocommit off, you decide when you want to commit, but calling commit() does not switch autocommit back on.

//Start transaction
$mysqli->autocommit(false);

$mysqli->query('INSERT INTO director(name) VALUE("Steven Spielberg")');

$directorId = $mysqli->insert_id;
$movieTitle = 'Jurassic Park';
$stmt = $mysqli->prepare('INSERT INTO movie(title, directorId) VALUE(?,?)');
$stmt->bind_param('ss', $movieTitle, $directorId);
$stmt->execute();

$mysqli->commit();
// Changes are committed, but autocommit is not switched back on

// Following queries are still transactional.
// They will not be committed unless you call commit or switch autocommit back on
$mysqli->query('INSERT INTO director(name) VALUE("James Cameron")');

$directorId = $mysqli->insert_id;
$movieTitle = 'Titanic';
$stmt = $mysqli->prepare('INSERT INTO movie(title, directorId) VALUE(?,?)');
$stmt->bind_param('ss', $movieTitle, $directorId);
$stmt->execute();

$mysqli->autocommit(true);
// All queries are committed and everything that follows will be immediately committed.

Using begin_transaction()

You can start a one-time-only transaction using begin_transaction(). This does not set autocommit=false so when you call commit() you end the transaction without starting a new one.

//Start transaction 
$mysqli->begin_transaction();

$mysqli->query('INSERT INTO director(name) VALUE("Steven Spielberg")');

$directorId = $mysqli->insert_id;
$movieTitle = 'Jurassic Park';
$stmt = $mysqli->prepare('INSERT INTO movie(title, directorId) VALUE(?,?)');
$stmt->bind_param('ss', $movieTitle, $directorId);
$stmt->execute();

$mysqli->commit();
// Changes are committed and the transaction has ended

// Following queries will be committed one by one as soon as they are peformed.
$mysqli->query('INSERT INTO director(name) VALUE("James Cameron")');

$directorId = $mysqli->insert_id;
$movieTitle = 'Titanic';
$stmt = $mysqli->prepare('INSERT INTO movie(title, directorId) VALUE(?,?)');
$stmt->bind_param('ss', $movieTitle, $directorId);
$stmt->execute();

Performing DDL statements

Some SQL statements trigger an explicit commit but do not affect the value of autocommit.

//Start transaction 
$mysqli->autocommit(false);

$mysqli->query('INSERT INTO director(name) VALUE("Steven Spielberg")');

$directorId = $mysqli->insert_id;
$movieTitle = 'Jurassic Park';
$stmt = $mysqli->prepare('INSERT INTO movie(title, directorId) VALUE(?,?)');
$stmt->bind_param('ss', $movieTitle, $directorId);
$stmt->execute();

// The following will call commit but it will not set autocommit=true
$mysqli->query('TRUNCATE TABLE movie_genre');
// if you want to switch autocommit back on, you have to call: 
$mysqli->autocommit(true);

Rollback

If an exception occurs then PHP will end execution of the script and the code will never reach the commit statement. However, in some situations, you might want to roll back the transaction explicitly, for example to avoid calling commit accidentally somewhere else in the code.

Here is an example of what such a transaction would look like. The second query tries to insert into a non-existent table which means that mysqli will throw an exception. Instead of letting PHP script die, we catch the exception and roll back the transaction. The value 4 will never be inserted into the database because both queries were rolled back.

try {
    // Start transaction
    $mysqli->begin_transaction();

    $mysqli->query('INSERT INTO some_table(col2) VALUE(4)');
    $mysqli->query('INSERT INTO does_not_exist(col2) VALUE(4)');

    // Commit changes
    $mysqli->commit();
} catch (\Throwable $e) {
    // Something went wrong. Rollback
    $mysqli->rollback();
    // Rethrow the exception so that PHP does not continue
    // with the execution and the error can be logged in the error_log
    throw $e;
}

Answer 4

Prepare SQL statement ONCE, and then execute it SEVERAL times:

<?php
$Mysqli = new mysqli("host","user","pass","base");

// check connection
if(mysqli_connect_errno())
{
  printf("Connect failed: %s\n",mysqli_connect_error());
  exit();
}

// some data for db insertion
$countries=['Austria','Belgia','Croatia','Denmark','Estonia'];

// explicitly begin DB transaction
$Mysqli->begin_transaction();

// prepare statement (for multiple inserts) only once
$stmt=$Mysqli->prepare("INSERT INTO table(column) VALUES(?)");

// bind (by reference) prepared statement with variable $country
$stmt->bind_param('s',$country);

// load value from array into referenced variable $country
foreach($countries as $country)
{
  //execute prep stat more times with new values
  //$country is binded (referenced) by statement
  //each execute will get new $country value
  if(!$stmt->execute())
  {
    // rollback if prep stat execution fails
    $Mysqli->rollback();
    // exit or throw an exception
    exit();
  }
}

// close prepared statement
$stmt->close();

// commit transaction
$Mysqli->commit();

// close connection
$Mysqli->close();

?>

Answer 5

You think that commit automatically switches autocommit back to true? A comment in the PHP Doc says NO!

Answer 6

In MySQL, transactions begin with the statement BEGIN (or START TRANSACTION) and end with either a COMMIT or a ROLLBACK statement. The SQLi commands between the beginning and ending statements form the bulk of the transaction.

You can control the behavior of a transaction by setting session variable called AUTOCOMMIT. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction and committed by default when it finishes. When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT=0 command, the subsequent series of statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued.

In MySql terms:

// disable auto-commit
$mysqli->query("SET AUTOCOMMIT = 0");

// begin transaction
$mysqli->query("START TRANSACTION");

// do something
$mysqli->query("...");
$mysqli->query("...");

// some condition
if (... are we happy? ...) {

  // commit transaction
  $mysqli->query("COMMIT"); 

} else {

  // rollback transaction
  $mysqli->query("ROLLBACK"); 

}

// enable auto-commit (if needed)
$mysqli->query("SET AUTOCOMMIT = 1");