php - mysql长连接断开后续获取连接操作重复
问题描述
当我得到一个pdo的长连接,执行一个事务插入操作,我手动断开或者一个mysql自动断开一段时间,我重新获取连接执行事务会继续执行最后一个操作,导致数据插入到数据库
mysql5.7
$pdoOptions = [
\PDO::ATTR_TIMEOUT => 30,
\PDO::ATTR_PERSISTENT => true,
];
$dsn = "mysql:host=mysql;port=3306;dbname=test;charset=utf8";
$connection = new \PDO($dsn, 'root', 'root', $pdoOptions);
try{
$connection->beginTransaction();
$smtm = $connection->prepare("INSERT INTO classic(class_name)VALUES(:name)");
$smtm->bindValue(':name','111');
$smtm->execute();
throw new \Exception('Manual exception throwing');
$connection->commit();
}catch (\Throwable $e){
echo ('Received exception information thrown:' . $e->getMessage());
echo "\n";
try{
$connection->rollBack();
}catch (\Throwable $e2){
echo ('Exception fired by rollback:' . $e->getMessage());
echo "\n";
}
}
$connection = null;
echo 'connection Set to null, the current pdo link is broken';
echo "\n";
$connection = new \PDO($dsn, 'root', 'root', $pdoOptions);
echo 'Set to null and get the new link again to determine whether it is in the transaction:' .($connection->inTransaction()?'是':'否');
echo "\n";
$connection->beginTransaction();
echo 'BeginTransaction to start a transaction';
echo "\n";
try{
echo('New link transaction open status:'.($connection->inTransaction()?'yes':'no'));
echo "\n";
$smtm = $connection->prepare("INSERT INTO classic(class_name)VALUES(:name)");
echo ('Current linked transaction status after connection->prepare() execution:'.($connection->inTransaction()?'yes':'no'));
echo "\n";
$smtm->bindValue(':name','222');
// echo $smtm->queryString;
$smtm->execute();
echo 'After the new link is obtained, an exception is thrown and the second execution fails to roll it back';
echo "\n";
throw new \Exception('The second transaction executes, throwing an exception manually');
$connection->commit();
}catch (\Throwable $e){
echo ('Get new link exception catch:' . $e->getMessage());
echo "\n";
try{
$connection->rollBack();
}catch (\Throwable $e2){
echo ('The exception triggered by the second rollback:' . $e2->getMessage());
echo "\n";
}
}
桌子
CREATE TABLE `classic` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`class_name` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
此代码使数据库实际插入一条数据
解决方案
关闭连接时,您应该关闭 连接对象和语句对象
$连接=空;$smtm = null;
我想这应该可以解决您的问题。花了更多时间修复!
<?php
namespace app;
ini_set('display_errors', 1);
$pdoOptions = [
\PDO::ATTR_TIMEOUT => 30,
\PDO::ATTR_PERSISTENT => true,
];
$dsn = "mysql:host=localhost;port=3306;dbname=test;charset=utf8";
$connection = new \PDO($dsn, 'root', 'infiniti', $pdoOptions);
try{
$connection->beginTransaction();
$smtm = $connection->prepare("INSERT INTO classic(class_name)VALUES(:name)");
$smtm->bindValue(':name','111');
$smtm->execute();
throw new \Exception('Manual exception throwing');
$connection->commit();
}catch (\Exception $e){
echo ('Received exception information thrown:' . $e->getMessage());
echo "\n";
try{
$connection->rollBack();
}catch (\Exception $e2){
echo ('Exception fired by rollback:' . $e->getMessage());
echo "\n";
}
}
$connection = null;
$smtm = null;
echo 'connection Set to null, the current pdo link is broken';
echo "\n";
$connection = new \PDO($dsn, 'root', 'infiniti', $pdoOptions);
echo 'Set to null and get the new link again to determine whether it is in the transaction:' .($connection->inTransaction()?'yes':'no');
echo "\n";
$connection->beginTransaction();
echo 'BeginTransaction to start a transaction';
echo "\n";
try{
echo('New link transaction open status:'.($connection->inTransaction()?'yes':'no'));
echo "\n";
$smtm = $connection->prepare("INSERT INTO classic(class_name)VALUES(:name)");
echo ('Current linked transaction status after connection->prepare() execution:'.($connection->inTransaction()?'yes':'no'));
echo "\n";
$smtm->bindValue(':name','222');
// echo $smtm->queryString;
$smtm->execute();
echo 'After the new link is obtained, an exception is thrown and the second execution fails to roll it back';
echo "\n";
throw new \Exception('The second transaction executes, throwing an exception manually');
echo 'wowowowo'."\n";
$connection->commit();
}catch (\Exception $e){
echo ('Get new link exception catch:' . $e->getMessage());
echo "\n";
try{
$connection->rollBack();
}catch (\Exception $e2){
echo ('The exception triggered by the second rollback:' . $e2->getMessage());
echo "\n";
}
}
?>
推荐阅读
- python - 使用 Docker 将 Flask 应用程序远程连接到 Selenium Grid 时遇到问题
- python - 在 Python 中输入缺失值
- puppeteer - 看起来 headerTemplate 放大了 1.33 (4/3)
- python - 在连接字符串捕获每个输入后尝试添加空格
- jquery - 是否缺少某些东西以便用 if 语句隐藏
- php - 如何为不同类别的不同用户使用会话?
- c# - 使用 C# 从 cad 库文件中获取块时出错
- elasticsearch - 如何在 elasticsearch highlight 的帮助下实现这一点?
- python - 无法在python中使用快速排序对数组进行排序
- python - 单独使用 .remove 与在 tolist() 中使用它