PHP Pdo Transactions

The connection object $dbo is taken from config.php file.
Sample Student table SQL dump

1. What are Transactions?

Transactions ensure that a series of SQL operations are treated as a single unit of work, ensuring either all operations succeed or none. This guarantees data integrity.

2. Starting a Transaction in PDO

$dbo->beginTransaction(); // Start transaction

3. Committing and Rolling Back Transactions

$dbo->commit(); // Commit the transaction
$dbo->rollBack(); // Rollback in case of error

4. Handling Exceptions in Transactions

try {
    $dbo->beginTransaction();
    $stmt = $dbo->prepare("UPDATE student SET mark = mark + 10 WHERE id = ?");
    $stmt->execute([1]);
    $dbo->commit();
    echo " Number of record updated ".$stmt->rowCount();
} catch (PDOException $e) {
    $dbo->rollBack();
    echo "Failed: " . $e->getMessage();
}

5. Error Modes in PDO

$dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

6. Nested Transactions and Savepoints

Savepoints allow partial rollback within a nested transaction.

$dbo->exec("SAVEPOINT savepoint1");
$dbo->rollBack("savepoint1"); // Roll back to savepoint

Adding two records to student table, only one will be stored.

try {
    $dbo->beginTransaction(); // Start outer transaction

    // Insert first record into the student table
    $dbo->exec("INSERT INTO student (name, class, mark, gender) VALUES ('John Doe', 'Six', 85, 'Male')");
    $id1 = $dbo->lastInsertId();
    echo "Inserted John Doe with ID: $id1 <br>";

    // Create a savepoint
    $dbo->exec("SAVEPOINT savepoint1");

    // Insert second record into the student table
    $dbo->exec("INSERT INTO student (name, class, mark, gender) VALUES ('Ronnee', 'Seven', 90, 'Female')");
    $id2 = $dbo->lastInsertId();
    echo "Inserted Ronee with ID: $id2 <br>";

    // Rollback to savepoint1 (undoes Jane Doe's insert)
    $dbo->exec("ROLLBACK TO savepoint1");

    $dbo->commit(); // Commit the transaction (only 'John Doe' will be saved)
} catch (Exception $e) {
    $dbo->rollBack(); // Roll back the entire transaction
    echo "Transaction failed: " . $e->getMessage();
}

The output ( id 43 and 44 ) cames for two records but in the database table only 43 will be available.

Inserted John Doe with ID: 43
Inserted Ronee with ID: 44

7. Common Transaction Pitfalls

Long-running transactions can cause deadlocks and should be handled carefully. Avoid holding a transaction open while waiting for user input.

8. Combining Transactions with Prepared Statements

$dbo->beginTransaction();
$stmt = $dbo->prepare("INSERT INTO student (name, class, mark, gender) VALUES (?, ?, ?, ?)");
$stmt->execute(['John', '10th', 85, 'Male']);
$dbo->commit();

Here's an example of using transactions with try-catch for updating a student's data:

try {
    // Begin the transaction
    $dbo->beginTransaction();

    // Update student mark
    $stmt = $dbo->prepare("UPDATE student SET mark = :mark WHERE id = :id");
    $stmt->bindParam(':mark', $newMark);
    $stmt->bindParam(':id', $studentId);

    $newMark = 90;
    $studentId = 1;
    $stmt->execute();

    // Commit the transaction
    $dbo->commit();
    echo "Transaction successful!";

} catch (PDOException $e) {
    // Rollback if there is an error
    $dbo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

Transaction Begin: beginTransaction() starts the transaction.
Prepared Statement: The query updates the student's mark based on their id.
Commit: commit() commits the changes if no error occurs.
Catch Block: If any error happens, rollBack() undoes the changes, ensuring data consistency.

Example 2: Insert Multiple Students with Transaction

try {
    $dbo->beginTransaction();
    
    $stmt = $dbo->prepare("INSERT INTO student (name, class, mark, gender) VALUES (?, ?, ?, ?)");
    
    $stmt->execute(['Alice', '10th', 88, 'Female']);
    $stmt->execute(['Bob', '12th', 91, 'Male']);
    
    $dbo->commit();
    echo "Records inserted successfully!";
} catch (PDOException $e) {
    $dbo->rollBack();
    echo "Error: " . $e->getMessage();
}

Example 3: Delete Student Record

Example 4: Updating Multiple Records Based on Condition

try {
    $dbo->beginTransaction();

    $stmt = $dbo->prepare("UPDATE student SET mark = mark + 5 WHERE class = ?");
    $stmt->execute(['12th']);

    $dbo->commit();
    echo "Marks updated!, No or rows updated: ".$stmt->rowCount();
} catch (PDOException $e) {
    $dbo->rollBack();
    echo "Failed to update marks: " . $e->getMessage();
}

DDL (Data Definition Language)

In transaction management, DDL queries such as DROP, ALTER, and CREATE execute immediately and trigger an implicit commit. These structural changes cannot be rolled back, even when part of a transaction, because most databases perform an automatic commit when a DDL query is issued. For example, once a DROP TABLE command is executed, the table is permanently removed.

DML (Data Manipulation Language)

DML queries such as INSERT, UPDATE, and DELETE modify the data inside tables. These queries can be rolled back within a transaction until a commit is issued, allowing for reversibility if necessary.

DDL (Data Definition Language)	DML (Data Manipulation Language)
Modifies database structure (e.g., CREATE, DROP). Implicitly commits transactions. Cannot be rolled back.	Modifies data (e.g., INSERT, UPDATE, DELETE). Can be used inside transactions. Can be rolled back before commit.

TCL (Transaction Control Language)

Is responsible for managing transactions within a database to ensure consistency and integrity. The key commands in TCL include:

COMMIT: Saves all changes made during the transaction permanently to the database.
ROLLBACK: Reverts the changes made during the transaction, undoing any modifications since the transaction began.
SAVEPOINT: Creates a point within a transaction that we can later roll back to, without affecting the entire transaction.
SET TRANSACTION: Defines properties for a transaction, such as the isolation level, ensuring proper transaction control.

Real-Life Transaction Scenarios

In a booking system, multiple related tables might need to be updated within a transaction to ensure consistency across seats, payments, and booking history.

Best Practices for Transaction Management

Keep transactions short to avoid locking issues.
Always handle exceptions with try-catch to ensure rollback on failure.

Podcast on MySQL database management using PHP PDO

PDO References

Subscribe to our YouTube Channel here

PDO Transactions