13.4.2. 不能回滚的语句
有些语句不能被回滚。通常,这些语句包括数据定义语言(DDL)语句,比如创建或取消数据库的语句,和创建、取消或更改表或存储的子程序的语句。
您在设计事务时,不应包含这类语句。如果您在事务的前部中发布了一个不能被回滚的语句,则后部的其它语句会发生错误,在这些情况下,通过发布ROLLBACK语句不能 回滚事务的全部效果。
缘起一段python脚本:
connection = MySQLdb.connect(host=settings.DATABASES['default']['HOST'],
user=settings.DATABASES['default']['USER'],
passwd=settings.DATABASES['default']['PASSWORD'],
db=settings.DATABASES['default']['NAME'],
port=int(settings.DATABASES['default']['PORT']),
charset='utf8')
connection.autocommit(False)
try:
cursor = connection.cursor()
cursor.execute("TRUNCATE `account` ;")
cursor.execute("INSERT INTO `tbl_name` (`column1`,`column2`,`column3`) VALUES ('1','2','3') ;")
connection.commit()
except:
connection.rollback()
finally:
connection.close()
如果insert语句报错,truncate照样生效
针对操作数据层面的事务是生效的:
mysql> CREATE TABLE `tbl_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(128) NOT NULL,
`password` varchar(128) NOT NULL,
`email` varchar(128) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
mysql> insert into `tbl_user`(`username`,`password`,`email`)values('user1','pwd1','email1') ;
mysql> warnings;
mysql> begin;
mysql> delete from tbl_user where username='user1' ;
mysql> select * from tbl_user;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from tbl_user;
+----+----------+----------+--------+
| id | username | password | email |
+----+----------+----------+--------+
| 1 | user1 | pwd1 | email1 |
+----+----------+----------+--------+
1 row in set (0.01 sec)