Executing MySQLi Transactions

Turn a group of MySQLi writes into a commit-or-rollback unit.

The examples focus on the operation itself, including the important distinction: mysqli::begin_transaction is object-oriented; mysqli_begin_transaction is procedural.

What It Does

Use the examples below to observe the behavior on a small dataset before extending it to application code.

Before You Start

The examples use PHP 8.2+ features where shown. Database snippets require the corresponding PDO driver, demo tables, and credentials configured outside source code.

Example 1: Basic Use

InnoDB supports transactions, whereas DDL can implicitly commit regardless of the open transaction.

<?php
$mysqli = new mysqli(getenv('DB_HOST') ?: '127.0.0.1', getenv('DB_USER') ?: 'app', getenv('DB_PASSWORD') ?: '', getenv('DB_NAME') ?: 'demo');
$mysqli->set_charset('utf8mb4');
$userId = 3;
$mysqli->begin_transaction();
try {
$stmt = $mysqli->prepare("UPDATE users SET active=1 WHERE id=?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$mysqli->commit();
} catch (Throwable $e) { $mysqli->rollback(); throw $e; }

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 2: Checking Behavior

InnoDB supports transactions, whereas DDL can implicitly commit regardless of the open transaction.

<?php
$mysqli = new mysqli(getenv('DB_HOST') ?: '127.0.0.1', getenv('DB_USER') ?: 'app', getenv('DB_PASSWORD') ?: '', getenv('DB_NAME') ?: 'demo');
$mysqli->set_charset('utf8mb4');
$userId = 8;
$mysqli->begin_transaction();
try {
$stmt = $mysqli->prepare("UPDATE users SET active=1 WHERE id=?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$mysqli->commit();
} catch (Throwable $e) { $mysqli->rollback(); throw $e; }

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 3: Practical Example

InnoDB supports transactions, whereas DDL can implicitly commit regardless of the open transaction.

<?php
$mysqli = new mysqli(getenv('DB_HOST') ?: '127.0.0.1', getenv('DB_USER') ?: 'app', getenv('DB_PASSWORD') ?: '', getenv('DB_NAME') ?: 'demo');
$mysqli->set_charset('utf8mb4');
$userId = 21;
$mysqli->begin_transaction();
try {
$stmt = $mysqli->prepare("UPDATE users SET active=1 WHERE id=?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$mysqli->commit();
} catch (Throwable $e) { $mysqli->rollback(); throw $e; }

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Important Behavior and Edge Cases

Use exceptions and rollback on Throwable; transactional storage engines such as InnoDB are required.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• Turn a group of MySQLi writes into a commit-or-rollback unit.
• Use exceptions and rollback on Throwable; transactional storage engines such as InnoDB are required.
• mysqli::begin_transaction is object-oriented; mysqli_begin_transaction is procedural.

Use Executing MySQLi Transactions when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.