My PDO transaction doesn't commit, but also doesn't throw any exception

Question

I have some queries that have run perfectly up until now, but I wanted to wrap them in a transaction to decrease the likelihood of data corruption. After running the code, everything APPEARS to work (i.e. no exception is thrown), but the query is never committed to the database. I've looked at other questions on S.O. but I haven't found any that apply to my case.

Here's my code:

    db::$pdo->beginTransaction();  // accesses PDO object method

    try {

        $sql = "INSERT INTO expenses SET date=:date, amount=:amount, accountId=:account; ";

        $sql .= "UPDATE accounts SET balance = balance - :amount WHERE id = :account";


        $s = db::$pdo->prepare($sql);
        $s->bindValue(':date', $date);
        $s->bindValue(':amount', $amount);
        $s->bindValue(':account', $account);

        $s->execute();

        db::$pdo->commit();
        echo 'success';
    }
    catch (PDOException $e) {
        db::$pdo->rollback();
        echo '<p>Failed: ' . $e->getMessage() . '</p>';
    }

When I run the code, it outputs the success message, but like I said, nothing gets committed to the database.

Since it's relevant, I should also note that my PDO error mode is set to ERRMODE_EXCEPTION, so I don't think that's what's causing the problem. I'm running a MySQL database (InnoDB)

Any thoughts?

Answer 1

I am not completely sure how running multiple queries in a single query statement works (if it works) because I typically do transactions by running each query separately in a prepare/execute statement. The transaction will still queue up all the changes until commit/rollback as expected. According to this SO answer it appears to work, however the example is not binding values and so that might be another issue.

I would suggest just splitting up the queries into multiple prepare/bind/execute statements and it should work as expected.