发表更新MySQL系列16 分钟读完 (大约2340个字)

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

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

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

事务的概念

  1. MySQL事务
    MySQL Innodb 存储引擎的默认关闭了事务，即开启了自动提交，执行完 SQL 语句后就会马上自动执行 COMMIT 操作。 若要显式地开启一个事务，在 SQL 语句中使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来关闭当前会话的自动提交。
  2. 事务 ACID 特性。
    • 原子性(Atomicity)：事务用中最小的执行单位，多条语句要么都成功，要么都失败。
    • 一致性(Consistency)：事务执行结果必须使数据库从一个致性状态到另一个一致性状态；事务执行的前后，数据状态要保持一致。
    • 隔离性(Isolation)：并发事务之间不能看到对方的中间状态，并发执行的事务之间不能相互影响。
    • 持续性(Durablility)：事务一旦提交，所有改变都要记录到底层数据库存储器中(存盘)。
  3. 数据库的事务语句组成
    • 一组 DML(数据操纵语言) 语句：以INSERT、UPDATE、DELETE三种指令为核心，加上SELECT构成了四大指令CRUD。
    • 一条 DDL(数据定义语言) 语句：负责数据结构定义与数据库对象定义，由CREATE、ALTER与DROP三个语法所组成。
    • 一条 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 transaction 或 begin 两个命令，两个命令都表示临时性地开始一次事务，处于这两个命令之后的 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

许可协议

#

喜欢这篇文章？打赏一下作者吧

评论

关注我
×