Question 241397 in PHP Versions Question posted in
You can find a list of all versions here.

Php versions – "Handling Active Transaction Error After Adding Retry Logic to Avoid Deadlocks in PHP PDO

Question:
I had encountered deadlock errors in my application’s database transactions. To address this issue, I added retry logic, which successfully resolved the deadlock problem. However, now I’m facing a new issue, and I’m hoping to get some guidance on how to handle it.

Before adding the retry logic, I was frequently encountering deadlock errors. After implementing the retry logic, the deadlock errors no longer occur. However, I’m now encountering a different error:.

There is already an active transaction;

Here’s how my application is set up:

  1. I have a connection.php file that establishes a PDO connection to my MySQL database.
  2. I’m using this PDO connection in multiple parts of my application, such as scripts and queries.
  3. In one of my scripts, prepare.php, I run a loop that executes several other PHP scripts, scripts_{$i}.php.
  4. Inside each scripts_{$i}.php script, I execute an SQL insertion operation.
  5. To prepare the data for insertion, these scripts may need 1-3 seconds each.
  6. I added retry logic to the insertion process to handle deadlock errors.

Here’s a simplified version of the code:

// This is connection.php file
$pdo_conn = new PDO('mysql:host='localhost';dbname=my_db', "username", "password",array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8" , PDO::ATTR_PERSISTENT => true));
$pdo_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


// This is connection.php file
$pdo_conn = new PDO('mysql:host='.$this->host.';dbname=bde_zeiten', $this->user_name,$this->password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::ATTR_PERSISTENT => true));
$pdo_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// This is prepare.php script; which is responsible to run insertion.php
for ($i=0; $i < 40; $i++) { 
   shell_exec("/usr/bin/php scripts_{$i}.php");
}

// This is my data insertion.php script; To prepare the data, this script needs 1-3 seconds
$insert_query = substr($insert_query, 0, -1);

$retryAttempts = 3;
$retryDelay = 100000; // Microseconds (0.1 seconds)

for ($attempt = 1; $attempt <= $retryAttempts; $attempt++) {
try {
    $pdo_conn->beginTransaction();
    $stmt = $pdo_conn->prepare($insert_query);
    $stmt->execute();
    $pdo_conn->commit();
    break;
} catch (PDOException $e) {
    if (($e->getCode() == "1213" || $e->getCode() == "40001") && $attempt <= $retryAttempts) {
        echo "Inside the retry logic" . PHP_EOL;
        usleep($retryDelay); 
        continue;
    } else {
        echo $e->getMessage();
        $pdo_conn->rollback();
        break;
    }
}

}

Tags:

2

Answers


  1. 0

    tl;dr: You need to add a rollback within both cases of your IF statement inside the exception handling

    Why this happens?

    With $pdo_conn->beginTransaction(); an transaction is opened.

    In code, the $pdo_conn->commit(); and $pdo_conn->execute(); send some information to the database for processing and as you already saw, there are many different reasons, on why the process is interrupted, resulting in different Exceptions.

    Depending on the exception, someone might want to not close the transaction, and add something else! (exp. some kind of DB constrain is not fulfilled, and there is a default behavior)

    So, your problem is, that you are not able to open another transaction, which drills down to: Executing $pdo_conn->beginTransaction(); twice without doing a rollback or successful commit.

    You might want to change your understanding of transactions. Transactions are more like a "bag of interactions". As soon, as you add something, it will not directly influence the database, but they are "agreed" on with the database. Here the database will already tell the script, if the statement is valid. What ever is returned as exception: Your error is, that within your handling of the deadlock, you never close the the old transaction.

    The solution to your problem is to rollback within the other error case. What also might work, is to just retry the execute and commit instead the transaction and execution.

    Login or Signup to reply.
  2. 0

    @Mruf did a good answer that covers the why:

    Key points changes:

    • prepare outside the loop, it doesn’t get destroyed and can be used during reattempt;
    • if in_transaction so that it only restarts transaction if needed;
    • deadlock could be in execute or commit (if Galera is being used), hence the retry includes restarting the transaction;
    • testing attempts in the exception was not needed – covered by main loop.
    • (unrelated) prepared statements only work as SQL prevention if data and statement are separated;
    • Technically a single transaction in MariaDB’s autocommit doesn’t need transaction wrapping, but its there if multiple SQL in the transaction are required.

    Here’s a (untested) modified code version:

    $stmt = $pdo_conn->prepare($insert_query);
for ($attempt = 1; $attempt <= $retryAttempts; $attempt++) {
    try {
        if (!$pdo_conn->in_transaction()) {
            $pdo_conn->beginTransaction();
        }
        $stmt->execute($data);
        $pdo_conn->commit();
        break;
    } catch (PDOException $e) {
        if ($e->getCode() == "1213" || $e->getCode() == "40001") {
            echo "Inside the retry logic" . PHP_EOL;
            usleep($retryDelay); 
            continue;
        } else {
            echo $e->getMessage();
            $pdo_conn->rollback();
            break;
        }
    }
 }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search