PDO在提交/回滚之前丢失事务

Question

我正在尝试使用与使用PDO的mysql进行事务处理。我遇到的问题是在我提交之前交易已经中断。我知道这是因为我在连接上回显inTransaction（）函数。

在我看来，原因是我实例化一个PDODatabase类，然后在实际执行任何查询之前做一些其他的编码工作，在这段时间我失去了事务。

实例化我班

$pdo = new PdoDatabase; 
$pdo->beginTransaction(); 
echo "first ".$pdo->transactionStarted()."<br />"; 

的PdoDatabase类

public function __construct(){ 
    $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME; 
    $options = array(PDO::ATTR_PERSISTENT => TRUE, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION); 
    try{ 
     $this->_connection = new PDO($dsn, DB_USER, BD_PASS, $options); 
    } catch (PDOException $e){ 
     $this->_error = $e->getMessage(); 
    } 
} 

public function query($q){ 
    if($this->_error != ''){ 
     echo $this->_error; 
    } else { 
     $this->_stmt = $this->_connection->prepare($q); 
    } 
} 

public function bind($param, $value, $type = null){ 
    //echo "<br>".$value."<br>"; 
    if (is_null($type)) { 
     switch (true) { 
     case is_int($value): 
      $type = PDO::PARAM_INT; 
      break; 
     case is_bool($value): 
      $type = PDO::PARAM_BOOL; 
      break; 
     case is_null($value): 
      $type = PDO::PARAM_NULL; 
      break; 
     default: 
      $type = PDO::PARAM_STR; 
     } 
    } 
    $this->_stmt->bindValue($param, $value, $type); 
} 

public function execute($class = null){ 
    $object_array = array(); 

    if($class !== null){ 
     if($this->_stmt->execute()){ 
      $this->_stmt->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, $class, null); 
      while($row = $this->returnRow()){ 
       $object_array[] = $class::instantiate($row); 
      } 
     } 
     return $object_array; 
    } else { 

     return $this->_stmt->execute(); 
    } 
} 

public function transactionStarted(){ 
    return $this->_connection->inTransaction(); 
} 

我如何使用它（部分）

if(isset($_POST['id']) && $_POST['id'] != ''){ 
       echo "id exists ".$pdo->transactionStarted()."<br />"; 
       $bidder->getBidderById($_POST['id']); 
       echo "step 1 ".$pdo->transactionStarted()."<br />"; 
       $bidder = $bidder->getList(0); 
       echo "step 2 ".$pdo->transactionStarted()."<br />"; 
       $old_bidder = clone $bidder; 
       echo "step 3 ".$pdo->transactionStarted()."<br />"; 

       $bidder_phones->getPhones($_POST['id']); 
       echo "step 4 ".$pdo->transactionStarted()."<br />"; 
       $bidder_phones = $bidder_phones->getList(); 
       echo "step 5 ".$pdo->transactionStarted()."<br />"; 
       if($_POST['phone'] == ''){ 
        // check to see if there are any phone numbers in the database already and delete if there is 
        foreach($bidder_phones as $bp){ 
         $q = "delete from bidder_phones where id = :id"; 
         $pdo->query($q); 
         $pdo->bind(":id", $bp->getId()); 
         $pdo->execute(); 
         //$bp->remove(); 
        } 
       } else { 
        echo "phone to check ".$pdo->transactionStarted()."<br />"; 
        $old_phone_numbers = array(); 
        $new_phone_numbers = explode(',', $_POST['phone']); 
        foreach($bidder_phones as $bp){ 
         // remove any unused phone numbers 
         if(!in_array($bp, $new_phone_numbers)){ 
          $q = "delete from bidder_phones where id = :id"; 
          $pdo->query($q); 
          $pdo->bind(":id", $bp->getId()); 
          $pdo->execute(); 
          //$bp->remove(); 
         } 
         // push to an array to test the new numbers 
         array_push($old_phone_numbers, $bp->getPhone()); 
        } 
        foreach($new_phone_numbers as $phone){ 
         // adding new phone numbers 
         if(!in_array($phone, $old_phone_numbers)){ 
          $new_phone = new BidderPhone; 
          $new_phone->setPhone($phone); 
          $new_phone->setBidderId($_POST['id']); 
          $pdo->save('BidderPhones', $new_phone); 
          //$new_phone->save(); 
         } 
        } 
       } 

正如你看到的我是呼应$ pdo- > transactionStarted（）几次。这是尝试查看我何时失去交易。我期望看到的是我的消息，然后是1，表明交易仍然活跃。这就是我得到：

first 1 
id exists 1 
step 1 
step 2 
step 3 
step 4 
step 5 
phone to check 
in save 
before create 

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'clickbid.bidder_phoneses' doesn't exist' in /var/www/classes/PdoDatabase.php:59 Stack trace: #0 /var/www/classes/PdoDatabase.php(59): PDOStatement->execute() #1 /var/www/classes/PdoDatabase.php(158): PdoDatabase->execute() #2 /var/www/classes/PdoDatabase.php(187): PdoDatabase->getFields('BidderPhones') #3 /var/www/classes/PdoDatabase.php(176): PdoDatabase->create('BidderPhones', Object(BidderPhone), false) #4 /var/www/admin/data/post_data.php(284): PdoDatabase->save('BidderPhones', Object(BidderPhone)) #5 {main} thrown in /var/www/classes/PdoDatabase.php on line 59 

，所以我失去了存在的ID之后，这是因为我在做其他的节目，而不是准备查询和执行它们的交易？还有什么我需要知道的，我失踪了？在过去的两天中，我一直在努力解决这个问题。问题是我真的需要能够开始我的交易，并在实际执行一些查询之前做一些工作。有没有办法做到这一点？

在此先感谢您的帮助。

编辑 因此，我对代码进行了一些重大更改，并且能够更密切地确定何时丢失活动事务。

我保存方法是这样的

public function save($table, $object, $old_object = null){ 
    echo "in save ".$this->transactionStarted()."<br />"; 
    if($object->_id != ''){ 
     echo "before update ".$this->transactionStarted()."<br />"; 
     //return $this->update($table, $object, $old_object, $transaction); 
     if($this->update($table, $object, $old_object)){ 
      echo "update true ".$this->transactionStarted()."<br />"; 
      return true; 
     } else { 
      echo "update false ".$this->transactionStarted()."<br />"; 
      return false; 
     } 
    } else { 
     echo "before create ".$this->transactionStarted()."<br />"; 
     //return $this->create($table, $object, $transaction); 
     if($this->create($table, $object)){ 
      echo "create true ".$this->transactionStarted()."<br />"; 
      return true; 
     } else { 
      echo "create false ".$this->transactionStarted()."<br />"; 
      return false; 
     } 
    } 
} 

在这种特定情况下有从物体发出的_id所以这个 - $>更新是我们在这里调试是更新方法

private function update($table, $object, $old){ 
    echo "in update ".$this->transactionStarted()."<br />"; 
    $audit = new PdoDatabase; 
    echo "after new ".$this->transactionStarted()."<br />"; 
    $aq = "insert into audit_trails 
      (table_name, table_id, user_id, field_name, original_value, new_value) values 
      (:table_name, :table_id, :user_id, :field_name, :original_value, :new_value)"; 
    echo "before query ".$this->transactionStarted()."<br />"; 
    $audit->query($aq); 
    echo "after query ".$this->transactionStarted()."<br />"; 
    //$update = new PdoDatabase;  

    $binding = array(); 
    echo "before field_names ".$this->transactionStarted()."<br />"; 
    $field_names = self::getFields($table); 
    echo "after field_names ".$this->transactionStarted()."<br />"; 

    $uc = new UserConfig; 
    $uc->getConfig(); 
    $user = $uc->getList(0); 
    echo "before foreach ".$this->transactionStarted()."<br />"; 
    foreach($field_names as $field){ 
     $thisField = "_".$field['Field']."<br>"; 
     $getField = 'get'.self::to_camel_case($field['Field']); 
     $method = $getField; 
     $class = self::to_camel_case($table); 
     $field_list = ''; 

     if($field['Field'] == 'id'){ 
      $where = 'where id = :id'; 
      $binding[':id'] = ($object->$getField()); 
     } else { 

      if(method_exists($class, $method)){ 
       if($object->$getField() != $old->$getField()){ 
        $field_list .= $field['Field']."= :".$field['Field'].", "; 
        $binding[':'.$field['Field']] = $object->$getField(); 

        $audit->bind(':table_name', $table); 
        $audit->bind(':table_id', $object->getId()); 
        $audit->bind(':user_id', $user->getUserId()); 
        $audit->bind(':field_name', $thisField); 
        $audit->bind(':original_value', $object->$getField()); 
        $audit->bind(':new_value', $old->$getField()); 

        echo "before audit execute ".$this->transactionStarted()."<br />"; 
        $audit->execute(); 
        echo "after audit execute ".$this->transactionStarted()."<br />"; 

       } 
      } 
     } 
    } 
    echo "before binding ".$this->transactionStarted()."<br />"; 
    if(count($binding) > 1){ 
     $q = "update ".self::singularToPlural($table)." set "; 
     foreach($binding as $key => $value){ 
      if($key != ':id'){ 
       $q .= str_replace(':', '', $key)." = ".$key.", "; 
      } 
     } 
     $q = rtrim($q, ", "); 
     $q .= ' '.$where; 

     //$update->query($q); 
     echo "before this query ".$this->transactionStarted()."<br />"; 
     $this->query($q); 
     echo "after this query ".$this->transactionStarted()."<br />"; 
     /*if($transaction && !$this->_stmt->inTransaction()){ 
      $this->_stmt->beginTransaction(); 
     }*/ 

     foreach($binding as $key => $value){ 
      //$update->bind($key, $value); 
      $this->bind($key, $value); 
     } 
     //$update->bind($id); 
     //return $update->execute(); 
     echo "before this execute ".$this->transactionStarted()."<br />"; 
     $stupid = $this->execute(); 
     echo "after this execute ".$this->transactionStarted()."<br />"; 
     return $stupid; 
    } else { 
     echo "before return true ".$this->transactionStarted()."<br />"; 
     return true; 
    } 

} 

，输出为

first 1 
in save 1 
before update 1 
in update 1 
after new 1 
before query 1 
after query 1 
before field_names 1 
before execute 1 
after execute 1 
after field_names 1 
before foreach 1 
before audit execute 1 
before execute 1 
after execute 1 
after audit execute 1 
before binding 1 
before this query 1 
after this query 1 
before this execute 1 
before execute 1 
after execute 1 
after this execute 1 
update true 
the save 1 
second 
after save 
before commit 

Fatal error: Uncaught exception 'PDOException' with message 'There is no active transaction' in /var/www/classes/PdoDatabase.php:86 Stack trace: #0 /var/www/classes/PdoDatabase.php(86): PDO->commit() #1 /var/www/admin/data/post_data.php(403): PdoDatabase->commit() #2 {main} thrown in /var/www/classes/PdoDatabase.php on line 86 

从这个我能看到积极的办理当我们从更新返回到保存方法时离子会丢失，我只是不确定这是为什么。

再次感谢。

编辑添加getBidderById功能

public function getBidderById($bidder_id){ 
    $pdo = new PdoDatabase; 

    $q = "select * 
      from bidders Bidder 
      where Bidder.id = :bidder_id 
      limit 1"; 

    $pdo->query($q); 
    $pdo->bind(":bidder_id", $bidder_id); 
    $this->_bidders = $pdo->execute('Bidder'); 

    if(!empty($this->_bidders)){ 
     return true; 
    } else { 
     return false; 
    } 
} 

编辑添加创建方法

private function create($table, $object){ 
    //$insert = new PdoDatabase; 

    $field_names = self::getFields($table); 

    foreach($field_names as $field){ 
     $getField = 'get'.self::to_camel_case($field['Field']); 
     $method = $getField; 
     $class = self::to_camel_case($table); 

     if(method_exists($class, $method)){ 
      if($field['Field'] != 'id'){ 
       $fields = $field['Field'].", "; 
       $binding_fields = ":".$field['Field'].", "; 
       $binding[':'.$field['Field']] = $object->$getField(); 
      } 
     } 
    } 
    $fields = rtrim($fields, ", "); 
    $binding_fields = rtrim($binding_fields, ", "); 

    $iq = "insert into ".self::singularToPlural($table)." 
      (".$fields.") values 
      (".$binding_fields.")"; 
    $this->query($iq); 
    /*if($transaction && !$this->_stmt->inTransaction()){ 
     $this->_stmt->beginTransaction(); 
    }*/ 

    foreach($binding as $key => $value){ 
     $this->bind($key, $value); 
    } 

    if($this->execute()){ 
     $object->setId($this->getConnection()->lastInsertId()); 
     return true; 
    } else { 
     return false; 
    } 

} 

Answer 1

HMZ，我注意到，你在一个函数中实例化PdoDatabase并使用$这 - > transactionStarted（ ）。

通常情况下不会成为问题，但由于您使用的是持久连接，因此您可能会在某个其他类中启动新的事务，这会导致正在运行的事务的隐式提交。

if($this->create($table, $object)){ 
     echo "create true ".$this->transactionStarted()."<br />"; 
     return true; 
    } 

如果使用此功能的DDL语句（CREATE，ALTER，DROP，TRUNCATE等）犯也将被应用于

Answer 2

在MySQL中有些语句引起的隐式提交。您可能正在进行的任何交易均已落实。举例来说，所有的DDL语句都是这样做的。有关更多信息，请参阅https://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html。

令人惊讶的是很难知道您的会话中是否有活动事务。在导致隐式提交的语句之间，以及查询可以是“COMMIT”（不是通过任何commit（）函数，而是通过query（）函数）的客户端可能无法确定它们是否仍然存在交易正在进行。

此问题是尝试创建可重用DBAL的开发人员的一种祸根。

另见我的回答How do detect that transaction has already been started?

---

回复评论：

对于MySQL，PDO::inTransaction()是不可靠，因为PDO MySQL驱动程序没有实现的方法mysql_handle_in_transaction()。 MySQL C API不支持询问当前会话的事务状态。

因此PDO类试图使用，当你调用$dbh->commit()或$dbh->rollback()被设置为1，当你调用$dbh->beginTransaction()，并设置为0，内部变量做出最好的猜测。

但是，如果DDL语句导致隐含的提交，驱动程序没有任何通知，该事务结束。所以PDO中的内部变量可能会与现实不同步。如果您拨打$dbh->query('COMMIT')，也可能会发生这种情况，绕过事务控制的PDO功能。

其他PDO驱动程序，例如PostgreSQL驱动程序，确实实现了获取当前事务状态的方法。

---

我不是说这是你的问题的原因在这种情况下。但是试图检测客户端的事务状态并不是一直能够正常工作。

我不能说出你的情况发生了什么。您仍然有几行调试输出，但您尚未共享该代码。