Php pdo rollBack() not working for update statement

Question

I use PHP PDO transaction. I have insert and update statements and I would like to rollback the transaction when rowCount() return 0. Please see the sample code below.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '', array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
));

$userIds = [1,2,""];
$paymentAmounts = [10.50,20.50,30.50];
$error = array();

$pdo->beginTransaction();

try
{
    foreach($userIds as $key => $userId )
    {
        $sql = "INSERT INTO payments (user_id, amount) VALUES (?, ?)";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array(
                $userId, 
                $paymentAmounts[$key]
            )
        );

        if($stmt->rowCount() <= 0)
        {
            $error[] = ["Error occurred in userId: ".$userId];
        }

        $sql = "UPDATE users SET credit = ? WHERE id = ?";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array(
                $paymentAmounts[$key],
                $userId
            )
        );
        if($stmt->rowCount() <= 0)
        {
            $error[] = ["Error occurred in userId: ".$userId];
        }
    }
    if(!empty($error))
    {
        $displayError = implode(",", $error);
        throw new Exception($displayError);
    }

    // It should not reach here, if there is an error.
    $pdo->commit();

}
catch(Exception $e){
    echo $e->getMessage(); // displayError
    $pdo->rollBack(); // <<<==== It should rollback both Insert and Update to original state
}
?>

I have $userIds array. For userId 1 and 2, rowCount() will return 1 for both Insert and Update. But for empty userId, rowCount() will return 0. If rowCount() is 0, I assign the error message into $error[]. If $error is not empty, it will catch in Exception and roll back the transaction for all the $userIds.

My expected result is when $error is not empty, will throw exception and rollback in the catch. It should suppose to remove the records in table payments for userIds (1,2) and update back the original credit value in table users for userIds (1,2).

The result that I get is when the code reaches to $pdo->rollBack(), it only removes the inserted records from the payments table and it did not update back the original credit value into users table for userIds (1,2).

Can you advise on what is the issue with the code? Thank you.

========== Query for Create Tables and Insert statement ==========

-- CREATE TABLE "payments" -------------------------------
CREATE TABLE `payments` ( 
    `id` BigInt( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
    `user_id` BigInt( 11 ) UNSIGNED NOT NULL,
    `amount` VarChar( 20 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
    PRIMARY KEY ( `id` ) )
CHARACTER SET = latin1
COLLATE = latin1_swedish_ci
ENGINE = InnoDB
AUTO_INCREMENT = 1;
-- -------------------------------------------------------------

-- CREATE TABLE "users" -------------------------------
CREATE TABLE `users` ( 
    `id` BigInt( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
    `credit` VarChar( 20 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
    PRIMARY KEY ( `id` ) )
CHARACTER SET = latin1
COLLATE = latin1_swedish_ci
ENGINE = InnoDB
AUTO_INCREMENT = 1;
-- -------------------------------------------------------------

INSERT INTO `users` ( `credit`, `id`) 
VALUES ( '5', 1 ),( '10', 2 ),( '15', 3 );

Answer 1

$dbport =   3306;
$dbhost =   'localhost';
$dbuser =   'root'; 
$dbpwd  =   'xxx'; 
$dbname =   'xxx';

$options=array( 
    PDO::ATTR_CURSOR                    =>  PDO::CURSOR_SCROLL,
    PDO::ATTR_PERSISTENT                =>  FALSE,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY  =>  TRUE,
    PDO::MYSQL_ATTR_INIT_COMMAND        =>  'SET NAMES \'utf8mb4\' COLLATE \'utf8mb4_unicode_ci\', @@sql_mode = STRICT_ALL_TABLES, @@foreign_key_checks = 1'
);

$dsn='mysql:host='.$dbhost.';port='.$dbport.';dbname='.$dbname;
$dbo=new PDO( $dsn, $dbuser, $dbpwd, $options );


        $userIds = array(1,2,3,4,5,6);
        $paymentAmounts = array( 25,30,55,30,45,44 );

        try {

            $dbo->beginTransaction();

            if( count( $userIds )!=count( $paymentAmounts ) ) throw new Exception( sprintf('Unbalanced source arrays: $userids:%d, $paymentAmounts:%d',count( $userIds ),count( $paymentAmounts )));

            foreach( $userIds as $key => $userId ){

                $sql = "insert into payments ( user_id, amount ) values ( ?, ? )";
                $stmt = $dbo->prepare( $sql );
                if( $stmt ){
                    $stmt->execute( array( $userId, $paymentAmounts[ $key ] ) );
                    if( $stmt->rowCount() <= 0 ) throw new Exception( sprintf('Query #1 -> Error occurred in userId: %d count: %d', $userId, $stmt->rowCount() ) );
                } else {
                    throw new Exception( sprintf('Query #1 Prepare method failed @ %d for %d', $paymentAmounts[ $key ], $userId ) );
                }


                $sql = "update users set credit = credit + ? where id = ?";
                $stmt = $dbo->prepare( $sql );
                if( $stmt ) {
                    $stmt->execute( array( $paymentAmounts[ $key ], $userId ) );
                    if( $stmt->rowCount() <= 0 ) throw new Exception( sprintf('Query #2 -> Error occurred in userId: %d count: %d', $userId, $stmt->rowCount() ) );
                } else {
                    throw new Exception( sprintf('Query #2 Prepare method failed @ %d for %d', $paymentAmounts[ $key ], $userId ) );
                }
            }
            $dbo->commit();
        }catch( Exception $e ){
            echo 'Exception Raised: '.$e->getMessage();
            $dbo->rollBack();
        }

---

Before Test 1

mysql> select * from payments;
Empty set (0.00 sec)

mysql> select * from users;
+------+--------+
| id   | credit |
+------+--------+
|    1 | 0      |
|    2 | 0      |
|    3 | 0      |
|    4 | 0      |
|    5 | 0      |
|    6 | 0      |
+------+--------+
6 rows in set (0.00 sec)

After Test #1

mysql> select * from payments;
+----+---------+--------+
| id | user_id | amount |
+----+---------+--------+
|  1 |       1 | 25     |
|  2 |       2 | 30     |
|  3 |       3 | 55     |
|  4 |       4 | 30     |
|  5 |       5 | 45     |
|  6 |       6 | 44     |
+----+---------+--------+
6 rows in set (0.00 sec)

mysql> select * from users;
+------+--------+
| id   | credit |
+------+--------+
|    1 | 25     |
|    2 | 30     |
|    3 | 55     |
|    4 | 30     |
|    5 | 45     |
|    6 | 44     |
+------+--------+
7 rows in set (0.00 sec)

Test #2 ~ deliberately cause an exception - mismatched array lengths using this data:

        $userIds = array(1,2,3,4,5,6);
        $paymentAmounts = array( 25,30,55,30,45,44,21 );/* <----- extra item to cause exception */

This causes the exception:

Exception Raised: Unbalanced source arrays: $userids:6, $paymentAmounts:7

mysql> select * from users;
+----+--------+
| id | credit |
+----+--------+
|  1 | 25     |
|  2 | 30     |
|  3 | 55     |
|  4 | 30     |
|  5 | 45     |
|  6 | 44     |
+----+--------+
6 rows in set (0.00 sec)

The same data as before - the query did not get committed to db after an exception.

Test #3 - fudged query with fake column name

$sql = "update users set credit = boomerang + ? where id = ?";/* <----- ficticious column cause exception */

This causes exception

Exception Raised: Query #2 -> Error occurred in userId: 1 count: 0

mysql> select * from users;
+----+--------+
| id | credit |
+----+--------+
|  1 | 25     |
|  2 | 30     |
|  3 | 55     |
|  4 | 30     |
|  5 | 45     |
|  6 | 44     |
+----+--------+
6 rows in set (0.00 sec)

Again, a quick check reveals no change to the data so it looks like the whole process is working as it should