MySQL事务隔离

原创
2020/01/15 17:26
阅读数 84

一.SQL标准的事务隔离级别

  • 读未提交(Read Uncommitted):一个事务还没提交时,它做的变更就能被别的事务看到
  • 读提交(Read Committed):一个事务提交之后,它做的变更才会被其他事务看到
  • 可重复读(Repeatable Read):一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据 是一致的,未提交变更对其他事务也是不可见的
  • 序列化(Serializable):是对于同一行记录,写会加写锁,读会加读锁。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行
事务A 事务B
启动事务,查询得到值1 启动事务
查询得到值1
将1改成2
查询得到值V1
提交事务B
查询得到值V2
提交事务A
查询得到值V3

对上面的操作,不同隔离级别对应的值不一样:

读未提交 读提交 可重复读 序列化
V1 2 1 1 1
V2 2 2 1 1
V3 2 2 2 2

比较难理解的是后面的两个:

  • 可重复读:事务在执行期间看到的数据前后必须是一致的,即事务A提交事务之前,看到的东西和第一次看到的东西都是一样的,不会因外面修改而变化
  • 序列化:事务A期间,事务B执行修改动作的时候,将被锁住,直到事务A提交后,事务B才可以继续执行

二.事务隔离如何实现

在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准。

  • 读未提交:直接返回记录上的最新值
  • 读提交:视图是在每个 SQL 语句开始执行的时候创建的
  • 可重复读:视图是在事务启动时创建的,整个事务存在期间都用这个视图
  • 序列化:用加锁的方式来避免并行访问

三.MySQL中的事务隔离

Oracle的默认隔离级别是读提交,但MySQL默认的是可重复读。

  • 使用 show variables like 'transaction_isolation'; 查看MySQL当前的事务隔离级别:默认为可重复读

    mysql> show variables like 'transaction_isolation';
    +-----------------------+-----------------+
    | Variable_name         | Value           |
    +-----------------------+-----------------+
    | transaction_isolation | REPEATABLE-READ |
    +-----------------------+-----------------+
    1 row in set, 1 warning (0.00 sec)
    

四.MySQL的事务启动方式

  • 显式启动事务语句

    begin / start transaction
    xxx
    commit
    rollback
    
  • 设置自动提交参数

    ## 关掉自动提交,导致的结果是,执行一个select语句,事务就启动了,而且不会自动提交,事务持续直到主动commit或者rollback或者断开连接
    set autocommit=0;
    ## 建议打开自动提交事务,通过显式语句的方式来启动事务
    set autocommit=1;
    

    注:有些客户端连接框架会默认连接成功之后先执行关闭自动提交,会导致接下来的查询都在事务中,如果是长连接,就导致了意外的长事务

  • 查询超过60s的长事务:

    select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;
    

五.MySQL中事务隔离的实现 - 可重复读

每条记录更新会同时记录一条回滚操作,记录上面的最新值,通过回滚操作,都可以得到前一个状态的值。

假设一个值从1按顺序被改成了2、3、4,在回滚日志里面就会有类似下面的记录:

回滚段(从下往上恢复)
将2改成1 read-view A
将3改成2 read-view B
将4改成3
当前值4 read-view C

虽然当前值是4,但是查询这条记录的时候,不同时刻启动的事务会有不同的read-view,如上面表格所示的视图ABC。

  • 数据库的多版本并发控制 - MVCC:同一条记录在系统中可以存在多个版本
  • MVCC好处:即使有其他事务修改,与之前视图对应的事务不会冲突

视图A要想查询到1,必须将当前值依次执行图中所有的回滚操作得到。

当没有事务再需要用到这些回滚日志时,回滚日志会被删除。这个时候是当系统里面没有比这个回滚日志更早的read-view的时候。出于这个原因,建议尽量不要使用 长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这会导致大量占用存储空间。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部