mysql5 中的事务回滚ROLLBACK说明

1. 不能回滚的语句
有些语句不能被回滚。通常，这些语句包括数据定义语言（DDL）语句，比如创建或取消数据库的语句，和创建、取消或更改表或存储的子程序的语句。
2. 会造成隐式提交的语句
以下语句（以及同义词）均隐含地结束一个事务，完成SQL后即进行了一个COMMIT。
ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD MASTER DATA, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE TABLE, UNLOCK TABLES.
如在InnoDB中用TRUNCATE TABLE语句删除了一个表之后就不能用ROLLBACK进行恢复：Truncate table is an Oracle SQL extension adopted in MySQL and cannot roll back a truncate statement in Oracle.
3. 如果在事务中使用非事务安全型表(例如：MyISAM)，则对这些表的任何变更被立刻存储，不论autocommit模式的状态如何。
4. 在默认autocommit=1情况下，使用START TRANSACTION，autocommit仍然被禁用，直到使用COMMIT或ROLLBACK结束事务为止。然后autocommit模式恢复到原来的状态。如下SQL:

mysql> show create table users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `hashed_password` varchar(255) default '',
  `email` varchar(255) default NULL,
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  `last_login` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `users_name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select name from users;
+-------+
| name  |
+-------+
| admin | 
| yu    | 
+-------+
2 rows in set (0.00 sec)

mysql> set SESSION autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 | 
+--------------+
1 row in set (0.00 sec)

mysql> select name from users;
+-------+
| name  |
+-------+
| admin | 
| yu    | 
+-------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update users set name = 'davidyu' where name = 'yu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select name from users;
+---------+
| name    |
+---------+
| admin   | 
| davidyu | 
+---------+
2 rows in set (0.01 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 | 
+--------------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> select name from users;
+-------+
| name  |
+-------+
| admin | 
| yu    | 
+-------+
2 rows in set (0.00 sec)