PHP PDO Transactions with Commit and Rollback

A PDO transaction groups related database operations so they either complete together with commit() or are undone together with rollBack().

<?php
require 'config.php';

try{
    $dbo->beginTransaction();

    $stmt=$dbo->prepare(
        "INSERT INTO student
         (name,class,mark,gender)
         VALUES
         (:name,:class,:mark,:gender)"
    );

    $stmt->execute([
        ':name' => 'John',
        ':class' => 'Six',
        ':mark' => 85,
        ':gender' => 'Male'
    ]);

    $stmt->execute([
        ':name' => 'Alice',
        ':class' => 'Seven',
        ':mark' => 90,
        ':gender' => 'Female'
    ]);

    $dbo->commit();

    echo 'Both records were saved.';
}catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());
    echo 'The transaction could not be completed.';
}

The two INSERT operations are treated as one unit. If an exception occurs before the commit, the transaction can be rolled back.

Why Use a Database Transaction? Top ↑

A transaction is useful when two or more related database changes must succeed together.

For example, an application may need to:

• insert several related records,
• update more than one table,
• delete a parent record and related records,
• change inventory and create an order,
• record a booking while reserving its availability.

If one required operation fails, the application can undo the earlier operations instead of leaving only part of the work completed.

beginTransaction(), commit() and rollBack() Top ↑

PDO provides three main methods for controlling a transaction:

$dbo->beginTransaction();

// Run database operations here.

$dbo->commit();

beginTransaction() starts the transaction. commit() makes its changes permanent.

To cancel the uncommitted changes:

$dbo->rollBack();

Rollback when an Operation Fails Top ↑

With PDO exception mode enabled, use try and catch around the complete unit of work.

try{
    $dbo->beginTransaction();

    $stmt=$dbo->prepare(
        "INSERT INTO student
         (name,class,mark,gender)
         VALUES
         (:name,:class,:mark,:gender)"
    );

    $stmt->execute([
        ':name' => 'Alex',
        ':class' => 'Six',
        ':mark' => 82,
        ':gender' => 'Male'
    ]);

    // Run the next required operation.

    $dbo->commit();
}catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());

    echo 'No transaction changes were saved.';
}

Checking inTransaction() before rollback is safer because an exception can occur before a transaction starts or after it has already ended.

PDO Transactions with Prepared Statements Top ↑

Prepared statements and transactions solve different problems. Prepared statements keep supplied values separate from SQL structure, while transactions control whether a group of database changes is committed or rolled back.

try{
    $dbo->beginTransaction();

    $sql="INSERT INTO student
          (name,class,mark,gender)
          VALUES
          (:name,:class,:mark,:gender)";

    $stmt=$dbo->prepare($sql);

    $students=[
        [
            'name' => 'John',
            'class' => 'Six',
            'mark' => 85,
            'gender' => 'Male'
        ],
        [
            'name' => 'Alice',
            'class' => 'Seven',
            'mark' => 88,
            'gender' => 'Female'
        ]
    ];

    foreach($students as $student){
        $stmt->execute([
            ':name' => $student['name'],
            ':class' => $student['class'],
            ':mark' => $student['mark'],
            ':gender' => $student['gender']
        ]);
    }

    $dbo->commit();
}catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());

    echo 'The batch insert failed.';
}

The same prepared statement can be executed several times inside one transaction.

UPDATE inside a Transaction Top ↑

An UPDATE statement can participate in a transaction just like an INSERT.

$student_id=1;
$new_mark=90;

try{
    $dbo->beginTransaction();

    $stmt=$dbo->prepare(
        "UPDATE student
         SET mark=:mark
         WHERE id=:id"
    );

    $stmt->bindValue(
        ':mark',
        $new_mark,
        PDO::PARAM_INT
    );

    $stmt->bindValue(
        ':id',
        $student_id,
        PDO::PARAM_INT
    );

    $stmt->execute();

    // Run other dependent database work here.

    $dbo->commit();
}catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());
    echo 'Update transaction failed.';
}

Remember that rowCount() after a MySQL UPDATE normally reports changed rows. A value of zero can mean no matching record or that the requested value was already stored.

DELETE inside a Transaction Top ↑

A DELETE statement can be rolled back while the transaction remains active and the database table supports transactions.

$id=3;

try{
    $dbo->beginTransaction();

    $stmt=$dbo->prepare(
        "DELETE FROM student
         WHERE id=:id"
    );

    $stmt->bindValue(
        ':id',
        $id,
        PDO::PARAM_INT
    );

    $stmt->execute();

    // Other dependent operations can run here.

    $dbo->commit();
}catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());
    echo 'Delete transaction failed.';
}

Savepoints and Partial Rollback Top ↑

A savepoint creates a named position inside an active transaction. A database that supports savepoints can roll back changes made after that point without cancelling the complete transaction.

try{
    $dbo->beginTransaction();

    $stmt=$dbo->prepare(
        "INSERT INTO student
         (name,class,mark,gender)
         VALUES
         (:name,:class,:mark,:gender)"
    );

    $stmt->execute([
        ':name' => 'John Doe',
        ':class' => 'Six',
        ':mark' => 85,
        ':gender' => 'Male'
    ]);

    $id1=$dbo->lastInsertId();

    $dbo->exec(
        "SAVEPOINT before_second_insert"
    );

    $stmt->execute([
        ':name' => 'Ronee',
        ':class' => 'Seven',
        ':mark' => 90,
        ':gender' => 'Female'
    ]);

    $id2=$dbo->lastInsertId();

    $dbo->exec(
        "ROLLBACK TO SAVEPOINT before_second_insert"
    );

    $dbo->exec(
        "RELEASE SAVEPOINT before_second_insert"
    );

    $dbo->commit();

    echo 'First inserted ID: '.$id1.'<br>';
    echo 'Second generated ID before rollback: '.$id2;
}catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());
    echo 'Savepoint example failed.';
}

The first insert remains when the outer transaction is committed. The second insert is undone by the rollback to the savepoint.

PDO Does Not Provide Native Nested Transactions Top ↑

Calling beginTransaction() again while a PDO transaction is already active should not be treated as a portable way to create a nested transaction.

When partial rollback is required, use database-supported savepoints rather than attempting this:

// Do not use repeated beginTransaction()
// as a portable nested transaction mechanism.

$dbo->beginTransaction();
$dbo->beginTransaction();

Savepoints are SQL features and their exact capabilities can vary by database system.

Checking PDO inTransaction() Top ↑

inTransaction() reports whether PDO currently considers a transaction active.

if($dbo->inTransaction()){
    $dbo->rollBack();
}

This is particularly useful inside a catch block.

catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());
}

DDL and Implicit Commits in MySQL Top ↑

Transaction behavior for database-definition commands is database-specific. In MySQL, many DDL statements cause an implicit commit and should not be treated like ordinary transactional INSERT, UPDATE or DELETE statements.

Examples include operations involving:

• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• TRUNCATE TABLE

For example, do not assume that wrapping a MySQL DROP TABLE statement between beginTransaction() and rollBack() will restore the dropped table.

DML inside Transactions Top ↑

Data manipulation statements are the normal transaction use case:

• INSERT
• UPDATE
• DELETE

These changes can normally be committed or rolled back when executed against transactional tables.

MySQL Transaction Support and Storage Engines Top ↑

PDO can request a transaction, but the underlying database and table storage engine must support transactions.

For MySQL, InnoDB tables support transactions. A non-transactional table does not gain rollback capability simply because the PHP code calls beginTransaction().

If rollback behavior is important, confirm the storage engine used by the tables participating in the transaction.

PDO Transactions and Autocommit Top ↑

Without an explicit transaction, database changes are normally committed according to the connection's autocommit behavior.

After:

$dbo->beginTransaction();

the relevant transactional changes remain pending until:

$dbo->commit();

or:

$dbo->rollBack();

After the transaction ends, normal connection behavior resumes.

Long Transactions, Locks and Deadlocks Top ↑

Keep transactions as short as practical. An open transaction can retain locks and increase contention with other database sessions.

Avoid patterns such as:

$dbo->beginTransaction();

// Do not now wait for a visitor to fill in another form,
// confirm something minutes later or perform slow unrelated work.

Gather and validate the required application data first, then start the transaction immediately before the related database work.

Deadlocks Top ↑

Concurrent transactions can sometimes lock resources in conflicting orders. The database may resolve a deadlock by cancelling one transaction.

Applications performing important high-concurrency operations may need controlled retry logic, but a retry should be designed for the specific operation rather than blindly repeating every database failure.

When Should You Use a PDO Transaction? Top ↑

Common PDO Transaction Problems Top ↑

Starting a Transaction for Every Query Top ↑

A transaction is not automatically better for every database call. Use one when multiple operations need an all-or-nothing boundary or when the database consistency requirement specifically calls for it.

Calling rollBack() when No Transaction Is Active Top ↑

Inside catch blocks, check inTransaction() before attempting rollback.

Displaying Database Exceptions Top ↑

Log detailed errors on the server rather than displaying raw exception messages to visitors.

Keeping the Transaction Open during User Input Top ↑

Do not start a transaction and then wait for another browser request. Validate the required input first and keep the database transaction short.

Assuming PDO Supports Nested Transactions Top ↑

PDO does not provide portable nested transactions by repeatedly calling beginTransaction(). Use savepoints where supported.

Using Invalid Savepoint Rollback Syntax Top ↑

rollBack() rolls back the PDO transaction. It does not accept a savepoint name.

For MySQL partial rollback, issue the SQL command through PDO:

$dbo->exec(
    "ROLLBACK TO SAVEPOINT savepoint1"
);

Assuming Every MySQL Table Supports Rollback Top ↑

Transaction behavior depends on the table's storage engine. Use transactional tables such as InnoDB when rollback is required.

Assuming DDL Can Always Be Rolled Back Top ↑

MySQL performs implicit commits for many DDL operations. Transaction behavior for structural SQL commands varies between database systems.

Related PHP PDO Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: