非事务表回滚失败
执行ROLLBACK（回滚）时，如果收到下述消息，表示事务中使用的1个或多个表不支持事务：警告：某些更改的非事务性表不能被回滚。
这些非事务性表不受ROLLBACK语句的影响。
如果在事务中意外地混合了事务性表和非事务性表，导致该消息的最可能原因是，你认为本应是事务性的表实际上不是。如你试图使用mysqld服务器不支持的事务性存储引擎（或用启动选项禁止了它）创建表，就可能出现该情况。如果mysqld不支持存储引擎，它将以MyISAM表创建表，这是非事务性表。
可使用下述语句之一检查表的标类型：
SHOW TABLE STATUS LIKE 'tbl_name';
SHOW CREATE TABLE tbl_name;
使用下述语句，可检查mysqld服务器支持的存储引擎：
SHOW ENGINES;
也可以使用下述语句，检查与你感兴趣的存储引擎有关的变量值：
SHOW VARIABLES LIKE 'have_%';
例如，要想确定InnoDB存储引擎是否可用，可检查have_innodb变量的值。 我这有不同的情况，Look
mysql> select * from t;
+----+------+---------------------+
| c1 | c2 | c3 |
+----+------+---------------------+
|1 | abc| NULL |
|2 | zxc| 2008-04-25 00:00:00 |
|3 | gh | 0000-00-00 00:00:00 |
|4 | gh | 0000-00-00 00:00:00 |
|5 | gh | 1970-06-05 00:00:00 |
|6 | gh | 1969-06-05 00:00:00 |
|7 | gh | 1900-06-05 00:00:00 |
|8 | gh | 1800-06-05 00:00:00 |
|9 | gh | 1600-06-05 00:00:00 |
| 10 | gh | 1500-06-05 00:00:00 |
| 11 | gh | 1000-06-05 00:00:00 |
| 12 | gh | 2007-06-05 00:00:00 |
| 13 | gh | 2007-06-05 19:15:12 |
| 14 | gh | 0000-00-00 00:00:00 |
| 15 | gh | 0000-00-00 00:00:00 |
| 16 | gh | 0000-00-00 00:00:00 |
+----+------+---------------------+
16 rows in set (0.01 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set c2='yinm' where c1>10;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6Changed: 6Warnings: 0
mysql> alter table tadd index (c3);
Query OK, 16 rows affected (0.04 sec)
Records: 16Duplicates: 0Warnings: 0
mysql> select * from t;
+----+------+---------------------+
| c1 | c2 | c3 |
+----+------+---------------------+
|1 | abc| NULL |
|2 | zxc| 2008-04-25 00:00:00 |
|3 | gh | 0000-00-00 00:00:00 |
|4 | gh | 0000-00-00 00:00:00 |
|5 | gh | 1970-06-05 00:00:00 |
|6 | gh | 1969-06-05 00:00:00 |
|7 | gh | 1900-06-05 00:00:00 |
|8 | gh | 1800-06-05 00:00:00 |
|9 | gh | 1600-06-05 00:00:00 |
| 10 | gh | 1500-06-05 00:00:00 |
| 11 | yinm | 1000-06-05 00:00:00 |
| 12 | yinm | 2007-06-05 00:00:00 |
| 13 | yinm | 2007-06-05 19:15:12 |
| 14 | yinm | 0000-00-00 00:00:00 |
| 15 | yinm | 0000-00-00 00:00:00 |
| 16 | yinm | 0000-00-00 00:00:00 |
+----+------+---------------------+
16 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+------+---------------------+
| c1 | c2 | c3 |
+----+------+---------------------+
|1 | abc| NULL |
|2 | zxc| 2008-04-25 00:00:00 |
|3 | gh | 0000-00-00 00:00:00 |
|4 | gh | 0000-00-00 00:00:00 |
|5 | gh | 1970-06-05 00:00:00 |
|6 | gh | 1969-06-05 00:00:00 |
|7 | gh | 1900-06-05 00:00:00 |
|8 | gh | 1800-06-05 00:00:00 |
|9 | gh | 1600-06-05 00:00:00 |
| 10 | gh | 1500-06-05 00:00:00 |
| 11 | yinm | 1000-06-05 00:00:00 |
| 12 | yinm | 2007-06-05 00:00:00 |
| 13 | yinm | 2007-06-05 19:15:12 |
| 14 | yinm | 0000-00-00 00:00:00 |
| 15 | yinm | 0000-00-00 00:00:00 |
| 16 | yinm | 0000-00-00 00:00:00 |
+----+------+---------------------+
16 rows in set (0.00 sec)
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t | 0 | PRIMARY| 1 | c1 | A | 2 | NULL | NULL | | BTREE | |
| t | 1 | c3 | 1 | c3 | A | 2 | NULL | NULL | YES| BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
在执行alter talbe时，MySQL自动提交了事务。
页:
[1]