MySQL(InnoDB剖析):42---事务之（事务控制语句：begin、commit、rollback、savepoint、transaction）

一、事务的自动提交

• 默认情况下，没有使用begin显式开启事务，事务都是自动提交的

autocommit变量

• 该变量用于控制SQL语句是否自动提交（auto commit）
• 默认值为1，表示自动提交
• 设置为0后，需要自己手动使用commit或rollback来结束事务

-- SQL语句自动提交（默认值）
set autocommit=1;

-- SQL语句不自动提交
set autocommit=0;

演示案例

• 创建一个表格t

drop table if exists t;

create table t(
    a int,
    primary key(a)
)engine=innodb;

• 设置autocommit为0，向表格中插入一条语句并查询

set autocommit=0;

insert into t select 1;

select * from t;

• 此时进行回滚，再次查询表格，可以看到语句没有插入成功（因为autocommit为0，每次执行都属于一个事务）

rollback;

select * from t;

• 回滚之后又开启了一个新的事务（因此类推）

二、事务控制语句

• 显式开启一个事务

start transaction;

begin;

• 提交事务，对事务中所做的修改进行永久性的保存

-- 两者的区别见下
commit;

commit work;

• 回滚事务，撤销所有的事务并结束本次事务

-- 两者的区别见下
rollback;

rollback work;

• 设置保存点

savepoint point_name;

• 删除保存点，如果删除一个不存在的保存点会抛出异常

release savepoint point_name;

• 回滚至某个保存点，从当前到该保存点之间的事务会撤销，保存点之前的事务不撤销

rollback to savepoint point_name;

• 设置事务隔离级别，可以设置的隔离级别有：
  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

set transaction xxx;

存储过程的事务开启

• 存储过程中只能使用start transaction开起来一个事务，因为begin在存储过程中会被认为一对“begin...end”来使用

TRUNACTE TABLE命令不可以回滚

• TRUNCATE TABLE清空表的语句不能进行回滚

演示案例

• 创建一个表格t，并插入两行数据

drop table if exists t;

create table t(
    a int,
    primary key(a)
)engine=innodb;

insert into t select 1;

insert into t select 2;

• 开启一个事务，然后清空表，并回滚事务

begin;

truncate table t;

rollback;

• 查询表数据为空，因此TRUNCATE TABLE命令回滚也无效

select * from t;

三、链事务（commit与commit work、rollback与rollback work）

• commit与commit work都是用来提交一个事务
• 但是commit work可以用来控制事务结束后的行为是CHAIN还是RELEASE的：
  • 如果是CHAIN，那么事务就变成了链事务（见下面的completion_type参数）

completion_type参数

• 该参数为0（NO_CHAIN）：此时COMMIT和COMMIT WORK都是相同的
• 设置为1（CHAIN）后：COMMIT WORK等同于COMMIT AND CHAIN，后面会立马开启一个相同隔离级别的事务（链事务）
• 设置为2（RELEASE）后：COMMIT WORK等同于COMMIT AND RELEASE，在事务提交后会自动断开与服务器的连接并重新开启一个会话

completion_type为1的演示案例

• 创建一个表格，将completion_type设置为1（表示开启链事务）

drop table if exists t;

create table t(
    a int,
    primary key(a)
)engine=innodb;

set @@completion_type=1;

• 此时开启一个事务，然后向表中插入一行数据，使用COMMIT WORK提交事务（此时后面默认开启一个事务）

begin;

insert into t select 1;

commit work;

• 此时再插入一行数据，然后进行回滚操作

insert into t select 2;

rollback;

• 查询表格，发现数据还有一行（这就是因为completion_type之后，commit work后面又自动开启了一个事务，我们使用rollback将事务撤销了）

select * from t;

completion_type为2的演示案例

• 接着上面的表格，将参数设置为2

set @@completion_type=2;

show variable like 'completion_type'\G

• 开启一个事务，然后插入一条数据，并结束本次事务

begin;

insert into t select 3;

commit work;

• 然后随便执行一条SQL语句，发现抛出异常，错误的原因是当前会话已与服务器断开连接，并且重新开启了一个会话

select version();

 

• roll和roll work的使用与commit和commit work相同

四、保存点的使用演示案例

• 创建一张表格t

drop table if exists t;

create table t(
    a int,
    primary key(a)
)engine=innodb;

• 开启一个事务，然后插入一条语句，设置第一个保存点t1

begin;

insert into t select 1;

savepoint t1;

• 再插入一条语句，设置第二个保存点t2

insert into t select 2;

savepoint t2;

• 再插入一条语句，回滚至保存点t2

insert into t select 3;

rollback to savepoint t2;

• 然后结束事务，查看表格中的数据

commit;

select * from t;

五、隐式提交的SQL语句

• 文章开头说过，默认情况下执行的SQL语句会自动提交，下面列出了含有隐式COMMIT操作的语句

六、对于事务操作的统计

• 由于InnoDB存储引擎是支持事务的，因此InnoDB存储引擎的应用需要在考虑每秒请求书（QPS）的同时，应该关注每秒事务处理的能力（TPS）

com_commit、com_rollback变量

• 计算TPS的方法是：

(com_commit+com_rollback)/time

• 使用上面公式的前提是：所有的事务都是显式提交的，如果存在隐式地提交和回滚（默认autocommit=1），不会计算到com_commit和com_rollback变量中

演示案例

show global status like 'com_commit'\G

insert into t select 1;
insert into t select 2;

select * from t;

show global status like 'com_commit'\G

handler_commit、handler_rollback

• 这两个参数也用于事务的统计操作
• 但是这两个参数在MySQL 5.1中可以很好地统计InnoDB存储引擎显式和隐式的事务提交操作，但是在InnoDB Plugin中这两个参数的表现有些“怪异”，并不能很好地统计事务的次数

• 所以，如果用户的程序都是显式控制事务的提交和回滚，可以通过com_commit和com_rollback进行统计。如果不是，那么情况就显得有些复杂