MariaDB社区

标题: 非事务表回滚失败 [打印本页]

作者: jeff 时间: 2007-10-30 12:11
标题: 非事务表回滚失败
执行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变量的值。

作者: mysqlkumao 时间: 2008-4-10 10:42
我这有不同的情况，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: 6  Changed: 6  Warnings: 0

mysql> alter table t  add index (c3);
Query OK, 16 rows affected (0.04 sec)
Records: 16  Duplicates: 0  Warnings: 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自动提交了事务。

欢迎光临 MariaDB社区 (http://123.56.88.72/)