How to Use PDO::beginTransaction with PDO::rollBack to Implement Transaction Rollback?

gitbox 2025-08-11

Transaction management is a crucial concept in database operations, ensuring atomicity, consistency, isolation, and durability (the ACID properties) of data operations. In PHP, we can interact with databases using PDO (PHP Data Objects), and transaction management can be achieved via PDO’s beginTransaction(), commit(), and rollBack() methods. This article provides a detailed explanation of how to use PDO’s beginTransaction combined with rollBack to implement transaction rollback operations.

1. What is a Transaction?

A transaction refers to a set of operations that either all succeed or all fail together. Transactions ensure the consistency and integrity of database operations. The fundamental properties of transactions in database systems can be described by the ACID principles:

Atomicity: All operations within a transaction are either completed entirely or not done at all.
Consistency: The execution of a transaction transforms the database from one consistent state to another.
Isolation: When multiple transactions execute concurrently, the execution of one transaction should not be affected by others.
Durability: Once a transaction is committed, the changes to the database are permanently saved.

2. Basic Transaction Operations

In PDO, the basic transaction operations include:

beginTransaction(): Starts a transaction.
commit(): Commits a transaction.
rollBack(): Rolls back a transaction.

Typically, during database operations, if any step encounters an error, the rollBack() method can be used to roll back the transaction, undoing all previous operations.

3. Example of Using beginTransaction() and rollBack()

Suppose we need to execute multiple database operations such as inserting records and updating records. If any of these operations fail, we want to undo all previous operations to ensure database consistency. In this case, we can use beginTransaction() and rollBack().

<span><span><span class="hljs-meta">&lt;?php</span></span><span>
</span><span><span class="hljs-keyword">try</span></span><span> {
    </span><span><span class="hljs-comment">// Create PDO connection</span></span><span>
    </span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-string">&#039;mysql:host=localhost;dbname=testdb&#039;</span></span><span>, </span><span><span class="hljs-string">&#039;username&#039;</span></span><span>, </span><span><span class="hljs-string">&#039;password&#039;</span></span><span>);
    </span><span><span class="hljs-comment">// Set error mode to exception</span></span><span>
    </span><span><span class="hljs-variable">$pdo</span></span><span>-></span><span><span class="hljs-title function_ invoke__">setAttribute</span></span><span>(PDO::</span><span><span class="hljs-variable constant_">ATTR_ERRMODE</span></span><span>, PDO::</span><span><span class="hljs-variable constant_">ERRMODE_EXCEPTION</span></span><span>);
</span><span><span class="hljs-variable">$pdo</span></span><span>-></span><span><span class="hljs-title function_ invoke__">beginTransaction</span></span><span>();

</span><span><span class="hljs-comment">// Execute first SQL operation</span></span><span>
</span><span><span class="hljs-variable">$sql1</span></span><span> = </span><span><span class="hljs-string">"INSERT INTO users (username, email) VALUES (&#039;john_doe&#039;, &#039;[email protected]&#039;)"</span></span><span>;
</span><span><span class="hljs-variable">$pdo</span></span><span>-></span><span><span class="hljs-title function_ invoke__">exec</span></span><span>(</span><span><span class="hljs-variable">$sql1</span></span><span>);

</span><span><span class="hljs-comment">// Execute second SQL operation</span></span><span>
</span><span><span class="hljs-variable">$sql2</span></span><span> = </span><span><span class="hljs-string">"UPDATE accounts SET balance = balance - 100 WHERE username = &#039;john_doe&#039;"</span></span><span>;
</span><span><span class="hljs-variable">$pdo</span></span><span>-></span><span><span class="hljs-title function_ invoke__">exec</span></span><span>(</span><span><span class="hljs-variable">$sql2</span></span><span>);

</span><span><span class="hljs-comment">// Commit transaction if all is well</span></span><span>
</span><span><span class="hljs-variable">$pdo</span></span><span>-></span><span><span class="hljs-title function_ invoke__">commit</span></span><span>();
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"Transaction committed successfully!"</span></span><span>;

} catch (Exception $e) {
// Rollback transaction if an error occurs
$pdo->rollBack();
echo "Transaction rolled back: ", $e->getMessage();
}
?>

4. Explanation of the Code Flow

Establish PDO connection: First, we connect to the MySQL database using PDO and set the error mode to PDO::ERRMODE_EXCEPTION so that exceptions are thrown when errors occur.
Start the transaction: By calling $pdo->beginTransaction(), we instruct PDO to begin a transaction. All subsequent database operations are managed within this transaction, meaning if any operation fails, none will be committed.
Execute SQL operations:
- The first SQL operation inserts a user record.
- The second SQL operation updates the balance of a user account.
Commit the transaction: If all operations succeed, we call $pdo->commit() to commit the transaction, permanently saving all changes to the database.
Rollback the transaction: If an exception occurs during execution (for example, if the second SQL operation fails), the catch block is triggered, calling $pdo->rollBack() to roll back the transaction and undo all uncommitted changes.

5. Points to Note

Atomicity of transactions: After calling beginTransaction(), all database operations are treated as a single unit. If an error occurs, rollBack() can undo the entire transaction, ensuring data consistency.
Exception handling: Always use a try-catch block to catch exceptions, ensuring that the transaction is rolled back promptly if an operation fails.
Multiple operations within a transaction: When executing multiple operations inside a transaction, all will take effect as long as no errors occur before the transaction is committed.

6. Summary

Using PDO’s beginTransaction() and rollBack() methods, we can efficiently manage transactions in PHP and roll back transactions when errors arise, ensuring the atomicity and consistency of database operations. This approach is especially useful when performing multiple database operations, such as handling financial transactions or order processing, where transaction rollback prevents data inconsistency issues.

Related Tags:
PDO

PDO::beginTransaction