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:
- I have a connection.php file that establishes a PDO connection to my MySQL database.
- I’m using this PDO connection in multiple parts of my application, such as scripts and queries.
- In one of my scripts, prepare.php, I run a loop that executes several other PHP scripts, scripts_{$i}.php.
- Inside each scripts_{$i}.php script, I execute an SQL insertion operation.
- To prepare the data for insertion, these scripts may need 1-3 seconds each.
- 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;
}
}
}
2
Answers
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.
@Mruf did a good answer that covers the why:
Key points changes:
in_transaction
so that it only restarts transaction if needed;execute
orcommit
(if Galera is being used), hence the retry includes restarting the transaction;Here’s a (untested) modified code version: