PHP mysqli begin_transaction() Method

The PHP mysqli::begin_transaction() / mysqli_begin_transaction() function is used to start a transaction. It requires the InnoDB engine (it is enabled by default).

Syntax

//Object-oriented style
public mysqli::begin_transaction(flags, name)

//Procedural style
mysqli_begin_transaction(mysql, flags, name)

Parameters

`mysql`	`Required.` For procedural style only: Specify a mysqli object returned by mysqli_connect() or mysqli_init().
`flags`	`Optional.` Specify flag. The valid flags are: MYSQLI_TRANS_START_READ_ONLY: Start the transaction as "START TRANSACTION READ ONLY". Requires MySQL 5.6 and above. MYSQLI_TRANS_START_READ_WRITE: Start the transaction as "START TRANSACTION READ WRITE". Requires MySQL 5.6 and above. MYSQLI_TRANS_START_WITH_CONSISTENT_SNAPSHOT: Start the transaction as "START TRANSACTION WITH CONSISTENT SNAPSHOT".
`name`	`Optional.` Specify savepoint name for the transaction.

Return Value

Returns true on success or false on failure.

Example: Object-oriented style

The example below shows the usage of mysqli::begin_transaction() method.

<?php
//establishing connection to the database
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//the table engine has to support transactions
$mysqli->query("CREATE TABLE IF NOT EXISTS Employee (
  Name VARCHAR(255) NOT NULL,
  Salary DECIMAL(18,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

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

try {
  //inserting some values
  $mysqli->query("INSERT INTO Employee(Name, Salary) VALUES ('John', 3250)");

  //trying to insert invalid values
  $name = 'Marry';
  $salary = 'Unknown';
  $stmt = $mysqli->prepare('INSERT INTO Employee(Name, Salary) VALUES (?,?)');
  $stmt->bind_param('ss', $name, $salary);
  $stmt->execute();

  //if code reaches this point without errors 
  //then commit the data in the database
  $mysqli->commit();
} catch (mysqli_sql_exception $exception) {
  $mysqli->rollback();

  throw $exception;
}
?>

Example: Procedural style

The example below shows the usage of mysqli_begin_transaction() function.

<?php
//establishing connection to the database
$mysqli = mysqli_connect("localhost", "user", "password", "database");
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//the table engine has to support transactions
mysqli_query($mysqli, "CREATE TABLE IF NOT EXISTS Employee (
  Name VARCHAR(255) NOT NULL,
  Salary DECIMAL(18,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

//starting transaction
mysqli_begin_transaction($mysqli);

try {
  //inserting some values
  mysqli_query($mysqli, "INSERT INTO Employee(Name, Salary) VALUES ('John', 3250)");

  //trying to insert invalid values
  $name = 'Marry';
  $salary = 'Unknown';
  $stmt = mysqli_prepare($mysqli, 'INSERT INTO Employee(Name, Salary) VALUES (?,?)');
  mysqli_stmt_bind_param($stmt, 'ss', $name, $salary);
  mysqli_stmt_execute($stmt);

  //if code reaches this point without errors 
  //then commit the data in the database
  mysqli_commit($mysqli);
} catch (mysqli_sql_exception $exception) {
  mysqli_rollback($mysqli);

  throw $exception;
}
?>

❮ PHP MySQLi Reference