MySQL系列(9): MySQL InnoDB存储引擎之事务模型
事务(Transaction )是InnoDB
存储引擎一个很重要并且区别于其它存储引擎的特性。 使用了 Innodb 存储引擎的数据库或表才支持事务。
事务:指作为单个逻辑工作单元执行的一组操作,这组操作的各个单元要么全部成功,要么全部失败。
事务处理:通过将一组相关操作组合为一个要么全部成功,要么全部失败的单元,保证数据一致性。
事务的概念
- MySQL事务
MySQL Innodb 存储引擎的默认关闭了事务,即开启了自动提交,执行完SQL
语句后就会马上自动执行COMMIT
操作。 若要显式地开启一个事务,在 SQL 语句中使用命令BEGIN
或START TRANSACTION
,或者执行命令SET AUTOCOMMIT=0
,用来关闭当前会话的自动提交。 - 事务 ACID 特性。
- 原子性(Atomicity):事务用中最小的执行单位,多条语句要么都成功,要么都失败。
- 一致性(Consistency):事务执行结果必须使数据库从一个致性状态到另一个一致性状态;事务执行的前后,数据状态要保持一致。
- 隔离性(Isolation):并发事务之间不能看到对方的中间状态,并发执行的事务之间不能相互影响。
- 持续性(Durablility):事务一旦提交,所有改变都要记录到底层数据库存储器中(存盘)。
- 数据库的事务语句组成
- 一组 DML(数据操纵语言) 语句:以
INSERT、UPDATE、DELETE
三种指令为核心,加上SELECT
构成了四大指令CRUD
。 - 一条 DDL(数据定义语言) 语句:负责数据结构定义与数据库对象定义,由
CREATE、ALTER
与DROP
三个语法所组成。 - 一条 DCL(数据控制语言) 语句:用来设置或更改数据库用户或角色权限的语句,包括
GRANT、DENY、REVOKE等
。
DDL 和 DCL 语句最多只能有一条,因为 DDL 和 DCL 语句都会导致事务立即提交。
- 一组 DML(数据操纵语言) 语句:以
事务操作
- 查看事务提交
SELECT @@autocommit; //1-自动提交开启,0-自动提交关闭
SHOW VARIABLES LIKE 'autocommit'; //ON-自动提交开启, OFF-自动提交关闭 - 开启事务:
start transaction;
或者:begin;
MySQL InnoDB存储引擎,默认开启了事务自动提交。 - 提交事务:
commit;
自动提交:使用 DDL 或 DCL 语句,或程序正常退出。
不管是显式提交,还是自动提交(隐式提交),都会结束当前事务。 - 回滚事务:
rollback;
当事务所包含的数据库执行操作失败后,应该回滚事务,使该事务中所做的所有修改失效。
显式回滚:使用 rollback。
自动回滚:系统错误或强行退出。
不管是显式回滚还是自动回滚,都会结束当前事务。 MySQl 提供了 savepoint 来设置事务的中间点,可以让事务回滚到指定中间点,而不是回滚全务事务。
备注:普通的提交、回滚都回结束当前事务,但回滚指定中间点因为依然处于事务中,所以不会结束当前事务;判断是否在同一个事务里,多条 sql 语句使用的是同一条Connections
。 - 开启和关闭事务
- MySQL InnoDB 存储引擎默认关闭事务(即打开自动提交),开始事务支持命令如下:
set autocommit = {0 | 1}
,0
为关闭自动提交,即开启事务。 自动提交与开启事务互相取反。 - 一个MySQL命令行窗口代表一次连接
session
,在该窗口中设置了set autocommit = 0
相当于关闭了该连接session
的自动提交。对其他连接的命令窗口没有影响。 - 临时性地开始事务:若不想关闭整个命令行窗口的自动提交,可使用MySQL提供的
start transaction
或begin
两个命令,两个命令都表示临时性地开始一次事务,处于这两个命令之后的DML
语句不会立即生效,除非用commit
显式提交事务,或执行DDL、DCL
语句来隐式提交事务。 - 在MySQL配置文件
my.cnf
设置事务开关1
2[mysqld]
autocommit=0
- MySQL InnoDB 存储引擎默认关闭事务(即打开自动提交),开始事务支持命令如下:
- 事务操作示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18--查看事务提交规则:1和ON为开启事务自动提交
SELECT @@autocommit;
SHOW VARIABLES LIKE 'autocommit';
-- 开关事务,0=关闭自动提交
set autocommit = 0;
--临时开启事务
begin;
-- 向books表插入2条记录
insert into books values(null, ‘xxx1’, 1);
insert into books values(null, ‘xxx2, 1);
--查询记录1
select * fro books;
--回滚事务
rollback;
--再次查询
select * fro books;
--查询记录1可以看到刚插入的2条记录,回滚后的查看则看不到这2条记录。查看未提交的事务
- 查询正在执行的事务
1
SELECT * FROM information_schema.INNODB_TRX;
- 查看正获取锁的事务
1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
- 查看等待锁的事务
1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
事务隔离级别
事务隔离是数据库事务处理的基础之一,隔离(Isolation) 是 ACID 的缩写中的 I。
隔离级别:在多个事务同时进行更改和执行查询时,对结果的性能、可靠性、一致性和可再现性进行微调使之平衡的设置。
- 并发事务处理带来的安全问题
- 更新丢失:当两个或多个事务选择同一行,最后的更新会将其它事务的更新覆盖,就会发生更新丢失的问题。
- 脏读:一个事务读到另一个事务还没有提交的数据(脏数据)。
- 不可重复读:一个事务在先后读取同一条数据时,数据出现不一致的问题。一个事务读到了另一个事务已经提交更新的数据,导致在当前的事务中多次查询结果不一致。
- 虚读/幻读:一个事务读到另一个事务已经提交的的数据,导致在当前的事务中多次的查询结果不一致。
- 事务隔离级别:InnoDB 提供了 SQL:1992 标准所定义的四个事务隔离级别
设置事务的隔离级别:级别越高、越安全、效率越低。JDBC 默认的 4 个隔离级别,分别是数字:1、2、4、8表示,与 SQL:1992标准定义的四个隔离级别对应。- 1 级(READ UNCOMMITTED):未提交读,也称为脏读(读到旧数据)。 不可重复读,虚读都可能发生.
- 2 级(READ COMMITTED):已提交读,可以避免脏读。 但是不可重复读和虚读有可能发生。(oracle默认)
- 4 级(REPEATABLE READ):可重复读,避免脏读,不可重复读。 但是虚读有可能发生。(mysql默认)
- 8 级(SERIALIZABLE):可序列化,可以避免脏读,不可重复读,虚读的发生。
- 使用JDBC控制事务隔离级别
- Connection() 进行控制。
- void setTransactionIsoLation(int level),level 是常量,有四个。
更改MySQL事务隔离级别
MYSQL中的事务隔离级别操作:
- 查看当前的事务隔离级别:
SELECT @@TX_ISOLATION;
。 - 更改当前的事务隔离级别:
SET SESSION TRANSACTION ISOLATION LEVEL 事务隔离级别名称/;
,四个级别之一,如下:1
2
3
4SELECT @@TX_ISOLATION;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 查看全局事务级别,会话事务级别
SELECT @@global.tx_isolation, @@session.tx_isolation; - 修改事务权限的语句
set [ global | session ] transaction isolation level Read uncommitted | Read committed | Repeatable | Serializable;- global,意思是此语句将应用于之后的所有
session
,而当前已经存在的 session 不受影响。 - session,意思是此语句将应用于当前 session 内之后的所有事务。
- 什么都不写,意思是此语句将应用于当前 session 内的下一个还未开始的事务。
- global,意思是此语句将应用于之后的所有
JDBC中的事务管理
- JDBC 的事务的管理的 API:Connection 对象
- void setAutoCommit(boolean autoCommit)
- void commit()
- void rollback()
- QueryRunner 对象的 API
- QueryRunner():Constructor for QueryRunner
此空参构造没有传递连接,使用 sql 方法时需要给方法添加 conn 对象。手动提交事务时使用。 - QueryRunner(DataSource ds):Constructor for QueryRunner which takes a DataSource
此构造有传递连接,使用sql方法时无需添加conn
对象。自动提交事务
- QueryRunner():Constructor for QueryRunner
DBUtils实现事务管理
- 无事务管理
- QueryRunner(DataSource ds)
- query(不需要传入数据库连接的方法)
- update(不需要传入数据库连接的方法)
- 有事务管理
- QueryRunner()
- query(需要传入数据库连接的方法)
- update(需要传入数据库连接的方法)
相关参考
MySQL系列(9): MySQL InnoDB存储引擎之事务模型