天天看点

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;

MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 设置autocommit为0,向表格中插入一条语句并查询

set autocommit=0;

insert into t select 1;

select * from t;

MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 此时进行回滚,再次查询表格,可以看到语句没有插入成功(因为autocommit为0,每次执行都属于一个事务)

rollback;

select * from t;

MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 回滚之后又开启了一个新的事务(因此类推)

二、事务控制语句

  • 显式开启一个事务
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;

MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 开启一个事务,然后清空表,并回滚事务

begin;

truncate table t;

rollback;

MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 查询表数据为空,因此TRUNCATE TABLE命令回滚也无效
select * from t;
MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)

三、链事务(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,在事务提交后会自动断开与服务器的连接并重新开启一个会话
MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)

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;

MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 此时开启一个事务,然后向表中插入一行数据,使用COMMIT WORK提交事务(此时后面默认开启一个事务)

begin;

insert into t select 1;

commit work;

MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 此时再插入一行数据,然后进行回滚操作

insert into t select 2;

rollback;

MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 查询表格,发现数据还有一行(这就是因为completion_type之后,commit work后面又自动开启了一个事务,我们使用rollback将事务撤销了)
select * from t;
MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)

completion_type为2的演示案例

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

set @@completion_type=2;

show variable like 'completion_type'\G

MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 开启一个事务,然后插入一条数据,并结束本次事务

begin;

insert into t select 3;

commit work;

MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 然后随便执行一条SQL语句,发现抛出异常,错误的原因是当前会话已与服务器断开连接,并且重新开启了一个会话
select version();
MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • roll和roll work的使用与commit和commit work相同

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

  • 创建一张表格t
drop table if exists t;

create table t(
    a int,
    primary key(a)
)engine=innodb;      
MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 开启一个事务,然后插入一条语句,设置第一个保存点t1
begin;

insert into t select 1;

savepoint t1;      
MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 再插入一条语句,设置第二个保存点t2
insert into t select 2;

savepoint t2;      
MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 再插入一条语句,回滚至保存点t2
insert into t select 3;

rollback to savepoint t2;      
MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)
  • 然后结束事务,查看表格中的数据
commit;

select * from t;      
MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)

五、隐式提交的SQL语句

  • 文章开头说过,默认情况下执行的SQL语句会自动提交,下面列出了含有隐式COMMIT操作的语句
MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)

六、对于事务操作的统计

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

com_commit、com_rollback变量

  • 计算TPS的方法是:
(com_commit+com_rollback)/time
  • 使用上面公式的前提是:所有的事务都是显式提交的,如果存在隐式地提交和回滚(默认autocommit=1),不会计算到com_commit和com_rollback变量中
MySQL(InnoDB剖析):42---事务之(事务控制语句:begin、commit、rollback、savepoint、transaction)

演示案例

handler_commit、handler_rollback

  • 这两个参数也用于事务的统计操作
  • 但是这两个参数在MySQL 5.1中可以很好地统计InnoDB存储引擎显式和隐式的事务提交操作,但是在InnoDB Plugin中这两个参数的表现有些“怪异”,并不能很好地统计事务的次数
  • 所以,如果用户的程序都是显式控制事务的提交和回滚,可以通过com_commit和com_rollback进行统计。如果不是,那么情况就显得有些复杂

继续阅读