Php pdo rollBack() 不适用于更新语句

Question

我使用 PHP PDO 事务。我有插入和更新语句，我想在 rowCount() 返回 0 时回滚事务。请参阅下面的示例代码。

<?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
}
?>

我有 $userIds 数组。对于 userId 1 和 2，rowCount() 将为 Insert 和 Update 返回 1。但是对于空的 userId，rowCount() 将返回 0。如果 rowCount() 为 0，我将错误消息分配给 $error[]。如果 $error 不为空，它将捕获异常并回滚所有 $userIds 的事务。

我的预期结果是当 $error 不为空时，将抛出异常并在 catch 中回滚。它应该假设删除 userIds (1,2) 表 payments 中的记录，并更新回 userIds (1,2) 表 users 中的原始信用值。

我得到的结果是当代码到达 $pdo->rollBack() 时，它只从 payments 表中删除插入的记录并且它没有将原始信用值更新回用户表中的 userIds (1, 2).

你能告诉我代码有什么问题吗？谢谢。

========== 建表查询和Insert语句==========

-- 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();
        }

---

测试 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)

测试 #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)

测试 #2 ~ 故意导致异常 - 使用此数据的数组长度不匹配:

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

与以前相同的数据 - 异常后查询未提交给数据库。

测试 #3 - 伪造列名的伪造查询

$sql = "update users set credit = boomerang + ? where id = ?";/* <----- ficticious column cause 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)

同样，快速检查显示数据没有变化，因此看起来整个过程都在正常工作