我有一个PDO事务需要运行，第一条查询创建了一个开关，第二次将关于开关的信息添加到另一个表中。我的问题是，由于某种原因第一条查询没有正确执行，但是事务已经提交。(我正在使用以下PDO类 http://culttt.com/2012/10/01/roll-your-own-pdo-php-class/)
try{
//Insert into required tables
$db->beginTransaction();
$db->Query("INSERT INTO firewall (Name)VALUES(:Name)");
$db->bind(':Name',$Name);
$db->execute();
$db->Query("INSERT INTO firewall_switch (Switch_ID, firewall_id,customer_ID)VALUES(:Switch,LAST_INSERT_ID(),:Customer)");
$db->bind(':Switch',$switch);
$db->bind(':Customer',$customer);
$db->execute();
$db->endTransaction();
}catch(PDOException $e){
$db->cancelTransaction();
}
6 Query START TRANSACTION
6 Prepare [6] INSERT INTO firewall (Name)VALUES(?)
6 Prepare [7] INSERT INTO firewall_switch (Switch_ID, firewall_id,customer_ID)VALUES(?,LAST_INSERT_ID(),?)
6 Execute [7] INSERT INTO firewall_switch (Switch_ID, firewall_id,customer_ID)VALUES('2',LAST_INSERT_ID(),'164')
6 Query COMMIT
正如您所看到的，第一个查询从未执行，但第二个查询执行了。由于存在不允许的重复ID，因此这个特定的事务应该已经回滚了。
如果没有重复项，则交易似乎按预期完成，但我不确定为什么回滚不起作用...
编辑：
DB类：class Db{
private static $Connection = array();
public $connection;
private $dbh;
private $error;
private $stmt;
public static function GetConnection($connection)
{
if(!array_key_exists($connection,self::$Connection))
{
$className = __CLASS__;
self::$Connection[$connection] = new $className($connection);
}
return self::$Connection[$connection];
}
public function __construct($connection){
global $config;
//Load Settings
$this->id = uniqid();
$this->connection = $connection;
if(array_key_exists($connection,$config['connections']['database'])){
$dbConfig = $config['connections']['database'][$connection];
// Set DSN
$dsn = 'mysql:host=' . $dbConfig['host'] . ';port='.$dbConfig['port'].';dbname=' . $dbConfig['database'];
}
// Set options
$options = array(
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
// Create a new PDO instantiate
try{
$this->dbh = new PDO($dsn, $dbConfig['user'], $dbConfig['password'], $options);
}
// Catch any errors
catch(PDOException $e){
$this->error = $e->getMessage();
error_log($e->getMessage());
}
}
//Create the SQL Query
public function query($query){
$this->stmt = $this->dbh->prepare($query);
}
//Bind SQL Params
public function bind($param, $value, $type = null){
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);
}
//Execute the SQL
public function execute($array = NULL){
if($array == NULL){
return $this->stmt->execute();
}else{
return $this->stmt->execute($array);
}
}
public function resultset(){
$this->execute();
return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
}
//Return Single Record
public function single(){
$this->execute();
return $this->stmt->fetch(PDO::FETCH_ASSOC);
}
//Count rows in table
public function rowCount(){
return $this->stmt->rowCount();
}
//Show last ID Inserted into table
public function lastInsertId(){
return $this->dbh->lastInsertId();
}
//Transactions allows the tracking of multiple record inserts, should one fail all will rollback
public function beginTransaction(){
return $this->dbh->beginTransaction();
}
public function endTransaction(){
return $this->dbh->commit();
}
public function cancelTransaction(){
return $this->dbh->rollBack();
}
//Debug dumps the info that was contained in a perpared statement
public function debugDumpParams(){
return $this->stmt->debugDumpParams();
}
}
?>
数据库结构：
CREATE TABLE `firewall` (
`id` int(10) unsigned NOT NULL auto_increment,
`Name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `Name_UNIQUE` (`Name`),
UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1
CREATE TABLE `firewall_switch` (
`id` int(11) NOT NULL auto_increment,
`Switch_ID` int(10) unsigned NOT NULL,
`firewall_id` int(10) unsigned NOT NULL,
`Customer_ID` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
KEY `fk_firewall_switch_Switch1_idx` (`Switch_ID`),
KEY `fk_firewall_switch_firewall1_idx` (`firewall_id`),
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1
$db类代码以及构造它的位置..链接只展示了如何编写，没有提供完整的类。重复检查的性质是什么？你也能展示一下在这两个表中每个表的SHOW CREATE TABLE结果吗？ - Arth