- 1.作用
处理操作量大,复杂度高的数据。比如删除一个人员,并且删除他的个人信息。
- 2.控制语句
- BEGIN或START TRANSACTION;显式地开启一个事务;
- COMMIT;也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的;
- ROLLBACK;有可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
- SAVEPOINT identifier;SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT;
- RELEASE SAVEPOINT identifier;删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
- ROLLBACK TO identifier;把事务回滚到标记点;
- SET TRANSACTION;用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
- 3.范例
1 mysql> use RUNOOB; 2 Database changed 3 mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表 4 Query OK, 0 rows affected (0.04 sec) 5 6 mysql> select * from runoob_transaction_test; 7 Empty set (0.01 sec) 8 9 mysql> begin; # 开始事务 10 Query OK, 0 rows affected (0.00 sec) 11 12 mysql> insert into runoob_transaction_test value(5); 13 Query OK, 1 rows affected (0.01 sec) 14 15 mysql> insert into runoob_transaction_test value(6); 16 Query OK, 1 rows affected (0.00 sec) 17 18 mysql> commit; # 提交事务 19 Query OK, 0 rows affected (0.01 sec) 20 21 mysql> select * from runoob_transaction_test; 22 +------+ 23 | id | 24 +------+ 25 | 5 | 26 | 6 | 27 +------+ 28 2 rows in set (0.01 sec) 29 30 mysql> begin; # 开始事务 31 Query OK, 0 rows affected (0.00 sec) 32 33 mysql> insert into runoob_transaction_test values(7); 34 Query OK, 1 rows affected (0.00 sec) 35 36 mysql> rollback; # 回滚 37 Query OK, 0 rows affected (0.00 sec) 38 39 mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入 40 +------+ 41 | id | 42 +------+ 43 | 5 | 44 | 6 | 45 +------+ 46 2 rows in set (0.01 sec) 47 48 mysql>