How to handle rollback operations in transactions in combination with PDOStatement::rowCount

gitbox 2025-05-28

When using PDO for database operations, transaction processing is a very important mechanism, which ensures that a series of operations are either successful or all failed. Transactions usually include the core methods of beginTransaction() , commit() and rollBack() . When we perform multiple SQL operations, we sometimes need to determine whether to roll back the transaction based on certain conditions. At this time, the PDOStatement::rowCount function can come in handy.

PDOStatement::rowCount can return the number of rows affected by the previous SQL statement. Using it, we can effectively judge whether the database operation is successful and thus decide whether to roll back the transaction.

Use PDO for transaction processing

Here is an example that demonstrates how to use rowCount to determine the results of database operations and roll back transactions if necessary.

 <?php
// Database connection
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'root';
$password = '';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

// createPDOObject
try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

// Start a transaction
$pdo->beginTransaction();

try {
    // Execute the first oneSQLoperate
    $stmt1 = $pdo->prepare('UPDATE users SET balance = balance - 100 WHERE id = :id');
    $stmt1->execute(['id' => 1]);

    // Check the number of rows affected
    if ($stmt1->rowCount() == 0) {
        // If no rows are updated，Roll back the transaction
        throw new Exception('Update failed，Insufficient user balance');
    }

    // Execute the secondSQLoperate
    $stmt2 = $pdo->prepare('INSERT INTO transactions (user_id, amount) VALUES (:user_id, :amount)');
    $stmt2->execute(['user_id' => 1, 'amount' => -100]);

    // 检查第二个operate的影响行数
    if ($stmt2->rowCount() == 0) {
        // If the insertion fails，Roll back transactions
        throw new Exception('Transaction record insertion failed');
    }

    // If everything is OK，Submit transactions
    $pdo->commit();
    echo "Transaction submission successful！";
} catch (Exception $e) {
    // 出现异常时Roll back transactions
    $pdo->rollBack();
    echo "Transaction rollback，error message: " . $e->getMessage();
}
?>

Code Analysis

Opening and committing transactions <br> Use $pdo->beginTransaction() to start the transaction, and $pdo->commit() is used to commit the transaction. If any problems occur, we will roll back the transaction using $pdo->rollBack() .
Use rowCount to make judgments <br> After each SQL operation, we use $stmt->rowCount() to check if the operation is successful. For example, if the UPDATE statement does not update any rows (i.e. insufficient balance or mismatch of conditions), it can be judged by rowCount() returning 0 and triggering a rollback operation.
Exception handling <br> Through the try-catch statement, we catch any exception and roll back the transaction. If rowCount() returns 0 or other errors occur, throw new Exception() will be triggered to throw an exception, and then rollback will be performed.
Reliability of database operations <br> Use transaction processing to ensure the atomicity of database operations. Even if an exception occurs, the rollback of the transaction ensures the consistency of the database and avoids the situation where some operations take effect and data inconsistency.

Use rowCount to determine whether SQL statements are successfully executed

The rowCount() function is very useful, especially if you need to ensure that data modifications are actually in effect. If the SQL statement does not modify any rows, rowCount() will return 0, which usually means that the operation does not achieve the expected effect. In transaction processing, we can decide whether to roll back the transaction based on the value of rowCount() .

For example, in a bank transfer operation, we check whether updating the account balance and inserting the transaction history successfully. If any step fails, the transaction will roll back, maintaining the consistency and integrity of the data.

Summarize

By combining PDOStatement::rowCount and transaction management, we can perform database operations efficiently and reliably in PHP applications. rowCount() provides us with an important means to judge whether SQL operations are successful, while transactions ensure the atomicity of operations. Once problems arise, they can be rolled back in time to avoid data errors.

This method can be widely used in scenarios where data consistency is required, such as financial systems, inventory management systems, etc.

PDOStatement::rowCount