一、事务的自动提交
- 默认情况下,没有使用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;
select * from t;
- 查询表数据为空,因此TRUNCATE TABLE命令回滚也无效
三、链事务(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;
select * from t;
- 查询表格,发现数据还有一行(这就是因为completion_type之后,commit work后面又自动开启了一个事务,我们使用rollback将事务撤销了)
completion_type为2的演示案例
- 接着上面的表格,将参数设置为2
set @@completion_type=2;
show variable like 'completion_type'\G
- 开启一个事务,然后插入一条数据,并结束本次事务
begin;
insert into t select 3;
commit work;
select version();
- 然后随便执行一条SQL语句,发现抛出异常,错误的原因是当前会话已与服务器断开连接,并且重新开启了一个会话
- 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变量
(com_commit+com_rollback)/time
- 计算TPS的方法是:
- 使用上面公式的前提是:所有的事务都是显式提交的,如果存在隐式地提交和回滚(默认autocommit=1),不会计算到com_commit和com_rollback变量中
演示案例
handler_commit、handler_rollback
- 这两个参数也用于事务的统计操作
- 但是这两个参数在MySQL 5.1中可以很好地统计InnoDB存储引擎显式和隐式的事务提交操作,但是在InnoDB Plugin中这两个参数的表现有些“怪异”,并不能很好地统计事务的次数
- 所以,如果用户的程序都是显式控制事务的提交和回滚,可以通过com_commit和com_rollback进行统计。如果不是,那么情况就显得有些复杂