【求助】两个表tb_account & tb_user 都是 Innodb类型的，在一个事物中进行数据插入操作，tb_account数据插入成功了，tb_use

Question

【求助】两个表tb_account & tb_user 都是 Innodb类型的，在一个事物中进行数据插入操作，tb_account数据插入成功了，tb_user 确没有。
PHP代码如下：
$this->Account->begin();
        $returnQuery = $this->Account->save($account) && $this->User->save($user) && $this->Token->save($token);
        if ($returnQuery) {
            $this->Account->commit();
            ...
        } else {
            // 如果帐号建立失败，则重新跳转至注册页面
            $this->Account->rollback();
           ...
        }

Answer 1

mysql> show create table tb_account;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    744583
Current database: shouji+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                   |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_account | CREATE TABLE `tb_account` (
  `email` varchar(100) NOT NULL,
  `passwd` varchar(45) NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0: unverified\n1: active\n-1: inactive',
  PRIMARY KEY (`email`),
  UNIQUE KEY `email_UNIQUE` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Answer 2

mysql> show create table tb_user;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_user | CREATE TABLE `tb_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(45) NOT NULL,
  `mobile` varchar(100) DEFAULT NULL,
  `mobile_id` int(11) DEFAULT NULL,
  `nickname` varchar(60) NOT NULL,
  `fullname` varchar(45) DEFAULT NULL,
  `sex` char(3) NOT NULL DEFAULT '1',
  `birthday` date DEFAULT NULL,
  `province` varchar(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL,
  `qq` varchar(20) DEFAULT NULL,
  `about` varchar(900) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `IDX_USER_EMAIL` (`email`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10504 DEFAULT CHARSET=utf8 COMMENT='用户信æ¯è¡¨'      |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Answer 3

虽然INNODB支持事务，但是你也得把SQL封装到事务里面，才行啊。
不是封装在START TRANSACTION 和commit中间的，每个SQL语句都当做一个单独的事务，只有在这个中间的，才会把他们整体当做一个事务的。
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Answer 4

$this->User也应该有对应的commit和rollback吧，楼主没有添加相应调用。

Answer 5

$this->Account->begin();
$this->Account->commit();
封装了事务

Answer 6

$this->Account->begin(); 等价于 数据库级别的 begin
只要调用一次就可以

Answer 7

建议楼主可以先直接在MYSQL中试一下你的语句看看是否正常，以排除是数据库的问题还是程序代码方面的问题。另外检查一下autocommit 系统设置的情况，如果是自动提交则你的BEGIN就没有意义了。

Answer 8

看楼主的描述，我担心：$this->User->save($user)这里头的save用的是另外一个connection.
那就会出现没有提交的问题。建议楼主好好检查一下代码，应该是代码的问题。