MySQL系列(9): MySQL InnoDB存储引擎之事务模型

  事务(Transaction )是InnoDB存储引擎一个很重要并且区别于其它存储引擎的特性。 使用了 Innodb 存储引擎的数据库或表才支持事务。

  事务:指作为单个逻辑工作单元执行的一组操作,这组操作的各个单元要么全部成功,要么全部失败。
  事务处理:通过将一组相关操作组合为一个要么全部成功,要么全部失败的单元,保证数据一致性。

事务的概念

  1. MySQL事务
    MySQL Innodb 存储引擎的默认关闭了事务,即开启了自动提交,执行完 SQL 语句后就会马上自动执行 COMMIT 操作。 若要显式地开启一个事务,在 SQL 语句中使用命令 BEGINSTART TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来关闭当前会话的自动提交。
  2. 事务 ACID 特性。
    • 原子性(Atomicity):事务用中最小的执行单位,多条语句要么都成功,要么都失败。
    • 一致性(Consistency):事务执行结果必须使数据库从一个致性状态到另一个一致性状态;事务执行的前后,数据状态要保持一致。
    • 隔离性(Isolation):并发事务之间不能看到对方的中间状态,并发执行的事务之间不能相互影响。
    • 持续性(Durablility):事务一旦提交,所有改变都要记录到底层数据库存储器中(存盘)。
  3. 数据库的事务语句组成
    • 一组 DML(数据操纵语言) 语句:以INSERT、UPDATE、DELETE三种指令为核心,加上SELECT构成了四大指令CRUD
    • 一条 DDL(数据定义语言) 语句:负责数据结构定义与数据库对象定义,由CREATE、ALTERDROP三个语法所组成。
    • 一条 DCL(数据控制语言) 语句:用来设置或更改数据库用户或角色权限的语句,包括GRANT、DENY、REVOKE等
      DDL 和 DCL 语句最多只能有一条,因为 DDL 和 DCL 语句都会导致事务立即提交。

事务操作

  1. 查看事务提交

    SELECT @@autocommit; //1-自动提交开启,0-自动提交关闭
    SHOW VARIABLES LIKE 'autocommit'; //ON-自动提交开启, OFF-自动提交关闭

  2. 开启事务:

    start transaction;
    或者:begin;
    MySQL InnoDB存储引擎,默认开启了事务自动提交。

  3. 提交事务:commit; 自动提交:使用 DDL 或 DCL 语句,或程序正常退出。
    不管是显式提交,还是自动提交(隐式提交),都会结束当前事务。
  4. 回滚事务:rollback; 当事务所包含的数据库执行操作失败后,应该回滚事务,使该事务中所做的所有修改失效。
    显式回滚:使用 rollback。
    自动回滚:系统错误或强行退出。
    不管是显式回滚还是自动回滚,都会结束当前事务。 MySQl 提供了 savepoint 来设置事务的中间点,可以让事务回滚到指定中间点,而不是回滚全务事务。
    备注:普通的提交、回滚都回结束当前事务,但回滚指定中间点因为依然处于事务中,所以不会结束当前事务;判断是否在同一个事务里,多条 sql 语句使用的是同一条 Connections
  5. 开启和关闭事务
    • MySQL InnoDB 存储引擎默认关闭事务(即打开自动提交),开始事务支持命令如下:set autocommit = {0 | 1},0关闭自动提交,即开启事务。 自动提交与开启事务互相取反。
    • 一个MySQL命令行窗口代表一次连接session,在该窗口中设置了set autocommit = 0相当于关闭了该连接 session 的自动提交。对其他连接的命令窗口没有影响。
    • 临时性地开始事务:若不想关闭整个命令行窗口的自动提交,可使用MySQL提供的 start transactionbegin 两个命令,两个命令都表示临时性地开始一次事务,处于这两个命令之后的 DML 语句不会立即生效,除非用 commit 显式提交事务,或执行DDL、DCL语句来隐式提交事务。
    • 在MySQL配置文件my.cnf设置事务开关
      1
      2
      [mysqld]
      autocommit=0
  6. 事务操作示例
    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条记录。

    查看未提交的事务

  7. 查询正在执行的事务
    1
    SELECT * FROM information_schema.INNODB_TRX;
  8. 查看正获取锁的事务
    1
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
  9. 查看等待锁的事务
    1
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

事务隔离级别

事务隔离是数据库事务处理的基础之一,隔离(Isolation) 是 ACID 的缩写中的 I

隔离级别:在多个事务同时进行更改和执行查询时,对结果的性能、可靠性、一致性和可再现性进行微调使之平衡的设置。

  1. 并发事务处理带来的安全问题
    • 更新丢失:当两个或多个事务选择同一行,最后的更新会将其它事务的更新覆盖,就会发生更新丢失的问题。
    • 脏读:一个事务读到另一个事务还没有提交的数据(脏数据)。
    • 不可重复读:一个事务在先后读取同一条数据时,数据出现不一致的问题。一个事务读到了另一个事务已经提交更新的数据,导致在当前的事务中多次查询结果不一致。
    • 虚读/幻读:一个事务读到另一个事务已经提交的的数据,导致在当前的事务中多次的查询结果不一致。
  2. 事务隔离级别:InnoDB 提供了 SQL:1992 标准所定义的四个事务隔离级别
    设置事务的隔离级别:级别越高、越安全、效率越低。JDBC 默认的 4 个隔离级别,分别是数字:1、2、4、8表示,与 SQL:1992标准定义的四个隔离级别对应。
    • 1 级(READ UNCOMMITTED):未提交读,也称为脏读(读到旧数据)。 不可重复读,虚读都可能发生.
    • 2 级(READ COMMITTED):已提交读,可以避免脏读。 但是不可重复读和虚读有可能发生。(oracle默认)
    • 4 级(REPEATABLE READ):可重复读,避免脏读,不可重复读。 但是虚读有可能发生。(mysql默认)
    • 8 级(SERIALIZABLE):可序列化,可以避免脏读,不可重复读,虚读的发生。
      MySql Isolation
  3. 使用JDBC控制事务隔离级别
    • Connection() 进行控制。
    • void setTransactionIsoLation(int level),level 是常量,有四个。

更改MySQL事务隔离级别

MYSQL中的事务隔离级别操作:

  1. 查看当前的事务隔离级别:SELECT @@TX_ISOLATION;
  2. 更改当前的事务隔离级别:SET SESSION TRANSACTION ISOLATION LEVEL 事务隔离级别名称/;,四个级别之一,如下:
    1
    2
    3
    4
    SELECT @@TX_ISOLATION;
    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    -- 查看全局事务级别,会话事务级别
    SELECT @@global.tx_isolation, @@session.tx_isolation;
  3. 修改事务权限的语句
    set [ global | session ] transaction isolation level Read uncommitted | Read committed | Repeatable | Serializable;
    • global,意思是此语句将应用于之后的所有 session,而当前已经存在的 session 不受影响。
    • session,意思是此语句将应用于当前 session 内之后的所有事务。
    • 什么都不写,意思是此语句将应用于当前 session 内的下一个还未开始的事务。

JDBC中的事务管理

  1. JDBC 的事务的管理的 API:Connection 对象
    • void setAutoCommit(boolean autoCommit)
    • void commit()
    • void rollback()
  2. QueryRunner 对象的 API
    • QueryRunner():Constructor for QueryRunner
      此空参构造没有传递连接,使用 sql 方法时需要给方法添加 conn 对象。手动提交事务时使用。
    • QueryRunner(DataSource ds):Constructor for QueryRunner which takes a DataSource
      此构造有传递连接,使用sql方法时无需添加conn对象。自动提交事务

DBUtils实现事务管理

  1. 无事务管理
    • QueryRunner(DataSource ds)
    • query(不需要传入数据库连接的方法)
    • update(不需要传入数据库连接的方法)
  2. 有事务管理
    • QueryRunner()
    • query(需要传入数据库连接的方法)
    • update(需要传入数据库连接的方法)

相关参考

  1. MySQL 5.7 官方文档:InnoDB Transaction Model
  2. MySQL 5.7 官方文档:Transaction Isolation Levels

MySQL系列(9): MySQL InnoDB存储引擎之事务模型

http://blog.gxitsky.com/2019/01/28/MySQL-09-transaction/

作者

光星

发布于

2019-01-28

更新于

2023-07-04

许可协议

评论