一、事务控制语句
对于一个事务存储引擎来说如果没有明确启动一个事务，默认是能实现自动提交每一个操作都能直接提交。而MySQL默认采用自动提交模式。也就是说，在 InnoDB 中，所有的用户操作均是以事务方式处理的如果不是显式地开始一个事务，每个 SQL 语句将以一个单独的事务来处理。MySQL 通常是以自动提交方式建立一个服务连接的。查看变量autocommit就是控制事务是否自动提交的变量。
|
1 2 3 4 5 6 7 |
mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.01 sec) |
1或ON表示启用，0或OFF表示禁用。当AUTOCOMMIT=0时，所有的操作都是在一个事务中，直到显式地执行COMMIT提交或者ROLLBACK回滚，该事务就结束，但同时又开始了另外一个事务。修改AUTOCOMMIT对非事务型的表，不会有任何影响。对这类表来说没有提交和回滚的概念。也可以说是相当于一只处于AUTOCOMMIT启用的模式。
MySQL可以通过执行“set transaction isolation level”命令来设置隔离级别，新的隔离级别会在下一个事务开始的时候生效。也可以在配置文件中设置这是数据库需要重启MySQL全局生效此隔离级别，InnoDB的隔离级别有（READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE）。
1. 配置文件中修改
|
1 2 3 |
[mysqld] transaction-isolation = READ-COMMITTED binlog-format = ROW |
2. 只改变当前会话的隔离级别
|
1 2 3 |
mysql> set session transaction isolation level read uncommitted; 或 mysql> set tx_isolation='READ-COMMITTED';<strong> </strong> |
既然默认关闭了自动提交事务，那么在默认提交关闭的情况下，开始一个事务必须使用BEGIN或START TRANSACTION语句，但是在存储过程中开启一个事务不能使用BEGIN，因为MySQL分析会自动降BEGIN识别为BEGIN….END，因此在存储过程中只能使用START TRANSACTION语句来开启一个事务。具体使用方式如下：
1） START TRANSACTION/BEGIN
BEGIN或START TRANSACTION显示地开启一个事务。
2）COMMIT
COMMIT提交事务，并使得已对数据库做的所有修改成为永久性。
3）ROLLBACK
回滚事务并结束事务，并撤销正在进行的所有未提交的事务的修改。
4）SAVEPOINT TO indentifier
SAVEPOINT允许你在事务中创建一个保存点作为一个可恢复对象，一个事务中可以有多个SAVEPOINT。
5）RELEASE SAVEPOINT identifier
删除一个事务的保存点，当没有保存点执行此语句或抛出异常。
6）ROLLBACK indentifier
回滚到指定的保存点。
二、实例操作
提交事务
|
1 2 3 4 5 6 7 8 |
mysql> select * from blog.tn\G *************************** 1. row *************************** id: 1 *************************** 2. row *************************** id: 2 *************************** 3. row *************************** id: 4 3 rows in set (0.00 sec) |
|
1 2 3 4 5 6 7 8 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from blog.tn where id=4; Query OK, 1 row affected (0.04 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) |
|
1 2 3 4 5 6 |
mysql> select * from blog.tn\G *************************** 1. row *************************** id: 1 *************************** 2. row *************************** id: 2 2 rows in set (0.00 sec) |
回滚事务
|
1 2 3 4 5 6 7 8 9 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into blog.tn select 4; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> rollback; Query OK, 0 rows affected (0.01 sec) |
|
1 2 3 4 5 6 |
mysql> select * from blog.tn\G *************************** 1. row *************************** id: 1 *************************** 2. row *************************** id: 2 2 rows in set (0.00 sec) |
SAVEPOINT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into blog.tn select 4; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> savepoint id4; Query OK, 0 rows affected (0.00 sec) mysql> insert into blog.tn select 5; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> rollback to id4; Query OK, 0 rows affected (0.01 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec) |
|
1 2 3 4 5 6 7 8 |
mysql> select * from blog.tn\G *************************** 1. row *************************** id: 1 *************************** 2. row *************************** id: 2 *************************** 3. row *************************** id: 4 3 rows in set (0.00 sec) |
隐式提交的SQL语句
一下这些SQL语句会产生一个隐士的提交操作，即执行完这些语句后，会有一个隐式的COMMIT提交操作，也就是说这些语句无法回滚的。大概包括“DDL语句”、“管理语句”以及对用户和权限操作的相关语句。
三、事务操作的统计变量
因为InnoDB存储引擎是支持事务的，因此对于InnoDB存储引擎的应用，在考虑每秒请求数（Question Per Second，QPS）的同时，也许更应该关注每秒事务处理的能力（Transaction Per Second看，TPS）。
计算TPS的方法是（com_commit+com_rollback）/ time。但是用这种方法计算的前提是：所有的事务必须都是显式提交的，如果存在隐式的提交和回滚，不会计算到com_commit和com_rollback变量中。如：
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show global status like 'com_commit'\G *************************** 1. row *************************** Variable_name: Com_commit Value: 2 1 row in set (0.03 sec) mysql> show global status like 'com_rollback'\G *************************** 1. row *************************** Variable_name: Com_rollback Value: 2 1 row in set (0.00 sec) |
所以这个计算方式在实际应用中并不靠谱，更多的可能是统计insert、update、delete这些dml操作。
