MySQL 事务的隔离级别

2020-04-17 10:44:32 浏览数 (1)
  1. MySQL 事务隔离级别查看及修改 参考：mysql修改事务隔离级别
代码语言：javascript复制
SELECT @@global.tx_isolation; 						
SELECT @@session.tx_isolation; 							
SELECT @@tx_isolation;
代码语言：javascript复制
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
代码语言：txt复制
1.  注意：默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。
  1. MySQL 事务的隔离级别及每种隔离级别存在的问题 参考：深入理解MySQL的四种隔离级别及加锁实现原理
代码语言：txt复制
1.  事务的四大特性
代码语言：txt复制
    1.  原子性 （ Atomicity ） 事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
代码语言：txt复制
    2.  一致性 （ Consistency ） 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。也就是说数据库中只包含成功事务提交的结果
代码语言：txt复制
    3.  隔离性  （ Isolation ） 并发执行的各个事务之间不会互相干扰
代码语言：txt复制
    4.  持久性   （ Durability ） 指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的
代码语言：txt复制
2.  事务的隔离级别
代码语言：txt复制
    1.  **Read Uncommitted（读未提交）** 所有事务都可以看到其他未提交事务的执行结果，会产生脏读（读取未提交的数据）
代码语言：txt复制
    2.  **Read Committed（读提交）** 一个事务只能看见已经提交事务所做的改变，会产生不可重复读问题
代码语言：txt复制
    3.  **Repeatable Read（可重读）** 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）
代码语言：txt复制
    4.  **Serializable（串行化）** 这是最高的隔离级别，读加共享锁，写加排他锁，读写互斥，从而解决幻读问题。在这个级别，可能导致大量的超时现象和锁竞争，如果业务并发的特别少，同时又要求数据及时可靠的话，可以使用。
代码语言：txt复制
3.  隔离级别产生的问题
代码语言：txt复制
    1. 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据不正确了
    2. 不可重复读(Non-repeatable read)：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
    3. 幻读(Phantom Read)：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

读未提交 可能会导致 脏读、不可重复读、幻读

读提交 可能会导致 不可重复读、幻读

可重读 可能会导致 幻读

代码语言：txt复制
1.  事务的加锁机制
代码语言：txt复制
    1.  一次封锁 和 两段锁协议
代码语言：txt复制
        1.  一次封锁，在方法的开始阶段，已经预先知道会用到哪些数据，然后全部锁住，在方法运行之后，再全部解锁。这种方式不会产生循环死锁的问题，但数据库中在事务开始阶段，数据库并不知道会用到哪些数据，所以在数据库中不适用。
代码语言：txt复制
        2.  两段锁协议 将事务分为 加锁阶段 和 解锁阶段
代码语言：txt复制
            1.  加锁阶段，在这个阶段只能进行加锁，读操作加共享锁，写操作加排它锁，有时候删除和插入操作会加区间锁
代码语言：txt复制
            2.  解锁阶段，当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。
代码语言：javascript复制
事务                加锁/解锁处理

  begin；

  insert into test .....    # 加insert对应的锁

  update test set...      # 加update对应的锁

  delete from test ....  # 加delete对应的锁

				commit;                # 事务提交时，同时释放																insert、update、delete对应的锁

这种方式虽然无法避免死锁(当事务A先需要资源1然后需要资源2， 事务B 先需要资源2然后需要资源1)，但是两段锁协议可以保证事务的并发调度是串行化

代码语言：txt复制
    1.  悲观锁和乐观锁
代码语言：txt复制
        1.  悲观锁：往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。 在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。
 在MySQL InnoDB 中 RR 级别下，悲观锁加的是 next-Key锁
代码语言：txt复制
        2.  乐观锁：基于数据版本（ Version ）记录机制实现。为数据增加一个版本标识，读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。
代码语言：txt复制
    2.  MVCC 的实现 在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。 在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。 在可重读Repeatable reads事务隔离级别下： SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。 INSERT时，保存当前事务版本号为行的创建版本号。 DELETE时，保存当前事务版本号为行的删除版本号。 UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行。 通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多数读操作都不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行。
 在MVCC中 为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读（读取历史数据）的概念，使得select不用加锁。
 写（当前读）：为了幻读问题，MySQL事务使用了Next-Key锁。Next-Key锁是行锁和GAP（间隙锁）的合并，行锁防止别的事务修改或删除，GAP锁防止别的事务新增。

注：如需转载，请注明出处：https://cloud.tencent.com/developer/article/1615842

云数据库SQLServer 数据库 sql mvc mvcc

0 人点赞