PHP PDO-没有活动的交易

问题描述

我在php脚本中进行交易时遇到问题.如果其中至少一个失败，我想进行多重查询并能够全部调用它们.在下面，您可以找到我正在使用的脚本的简单示例:

I am having problem with transactions in php script. I would like to make multiply queries and be able to recall them all, if at least one of them fails. Below you can find a simple example of the script I am using:

$tags_input = array(6,4,5);
$conn = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset=utf8',
DB_USER, DB_PASSW, array(
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

    $conn->beginTransaction();

    $sql = "INSERT INTO projects (id, pr_id, enabled) VALUES ( :val0, :val1, :val2)";
    $stmt = $conn->prepare($sql);
    if(count($tags_input)>0){
            for($i = 0;$i<count($tags_input);$i++){
                    $stmt->bindValue(':val0', 57);
                    $stmt->bindValue(':val1', $tags_input[$i]);
                    $stmt->bindValue(':val2', 'Y');
                    $result = $stmt->execute();

            }

    }

    $res1 = $conn->commit();
    $conn->rollBack();

现在，此示例生成一个错误:

Now, this example generates an error:

如果我删除行$conn->rollBack();，该错误消失.因此，我无法理解为什么pdo对象看不到打开的事务(begintransaction和commit不会产生任何错误).我还尝试将rollBack()放入事务中，但没有区别.我仍然收到错误消息没有有效的交易".

If I erase the line $conn->rollBack();, the error disappears. Therefore I cannot understand, why pdo object can't see open transaction (begintransaction and commit do not generate any errors). I also tried putting rollBack() inside the transaction, but made no difference. I was still getting an error 'There is no active transaction'.

我正在InnoDB上运行PHP 5.6和Mysql表.

I am running PHP 5.6 and Mysql tables on InnoDB.

推荐答案

将您的事务代码包装在try-catch语句中.

Wrap your transaction code inside a try-catch statement.

//try {
$tags_input = array(6,4,5);
$conn = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset=utf8',
DB_USER, DB_PASSW, array(
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
} catch (Exception $e) {
  die("Unable to connect: " . $e->getMessage());
}
try {
    $conn->beginTransaction();
    $sql = "INSERT INTO projects (id, pr_id, enabled) VALUES ( :val0, :val1, :val2)";
    $stmt = $conn->prepare($sql);
    if(count($tags_input)>0){
            for($i = 0;$i<count($tags_input);$i++){
                    $stmt->bindValue(':val0', 57);
                    $stmt->bindValue(':val1', $tags_input[$i]);
                    $stmt->bindValue(':val2', 'Y');
                    $result = $stmt->execute();
            }
    }
$res1 = $conn->commit();
} catch (Exception $e) {
  $conn->rollBack();
  echo "Failed: " . $e->getMessage();
}

编辑

Richard 提供了一个非常基础且直接的答案说明.

A really well-based and straight-forward explanation of the answer was provided by Richard as a comment.

这篇关于PHP PDO-没有活动的交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！