I need to be sure that the following works fine with PDO Transactions:

// Database Transaction
try
{
    // Start the transaction.
    $db->beginTransaction();

        // Some method in this class which runs a database query 
        $this->do_something($data);

        // Another method doing multiple database queries
        $this->do_another_thing($other_data);

        // Directly doing some query
        $stmt = $db->prepare("DELETE FROM `table` WHERE `time` < DATE_SUB(NOW(), INTERVAL :days DAY)");
        $stmt->execute(['days' => 2]);

    // Commit
    $db->commit();

}
catch(Exception $e)
{
    $error = $e->getMessage();
    // log

    // Rollback the transaction.
    $db->rollBack();
}

My question is:

Will it roll back everything (including queries done in other methods) this way? or only the direct query?

1 Answers

1
Nigel Ren On Best Solutions

A transaction covers the database connection and not the unit of PHP code. So any query which uses that connection will be subject to the transaction.

You have to be careful that sometimes programmers will create their own database connections within (for example) a class constructor, which means the class methods may be using their own connection and therefore not subject to the transaction you are trying to control. This is also a reason for passing in database connections and not creating your own or using global.

Although you also have to be aware that certain SQL statements will cause commits etc. So refer to https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-transactions.html which will give more details as to when/what/where these may happen.