mysql隔离级别

[TOC]

并发导致的三大问题

脏读（dirty read）

• 场景：A用户修改了数据，随后B用户又读出该数据，但A用户因为某些原因取消了修改，数据恢复原值；
• 结果：B用户实际上读取了一条根本就没有存在过的、被A修改后的数据，也称脏数据；
• 避免：假设A用户在修改记录期间将数据锁住，在修改完成前，别的用户读取不到记录，就能避免；

不可重复读（nonrepeatable read）

• 场景：A用户读取数据，随后B用户读出该数据并修改；
• 结果：A用户在一次事务中读取同一记录两次，发现前后两次的值不一致；
• 避免：假设A用户在两次读取之间锁住要修改的记录，别的用户不能去修改，就能避免；

幻读（phantom read）

• 场景：A用户读取一批数据，随后B插入或删除一些数据；
• 结果：A用户在一次事务中两次读取满足条件的一批数据，发现前后两次的记录数目不一致，多了或少了；
• 引申：幻读类似于不可重复读，都是在一个事务周期内读的数据不一致，区别在于幻读是侧重于插入或删除操作带来的影响，而不可重复读是修改带来的影响；
• 避免：假设A用户在两次读取之间锁住要读取的记录，别的用户不能去修改，就能避免；

mysql的四种隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

隔离级别	描述	备注
读未提交（Read Uncommitted）	所有事务都可以读到其他事务未提交的数据	不加锁、性能最好 脏读都会发生
读已提交（Read Committed）	一个事务只能读到其他事务已经提交过的数据	解决了脏读
可重复读（Repeatable Read）	一个事务不会读到其他事务对已有数据的修改，即使其他事务已提交	若有新插入则引发幻读 其实解决了幻读问题
串行化（Serializable）	将事务的执行变为顺序执行，相当于单线程	解决了脏读、可重复读、幻读 但是效率最差

小结

• 从上往下，隔离强度越来越强，并发性也越来越低；
• 读未提交和串行化基本上是不需要考虑的隔离级别，读未提交不加锁限制，串行化相当于单线程执行，效率太差；
• 可重复读，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务新插入的数据是可以读到的，这也就引发了幻读问题；

不同隔离级别导致的并发问题

关于不同隔离级别可能导致的并发副作用，总结如下：

隔离级别	脏读	不可重复读	幻读	默认隔离级别	备注
读未提交（Read Uncommitted）	√	√	√
读已提交（Read Committed）	×	√	√	MSSQL/Oracle默认	我司强制MySQL使用RC
可重复读（Repeatable Read）	×	×	√	MySQL默认
串行化（Serializable）	×	×	×

MySQL如何实现事务隔离

• 读未提交，它的性能最好，因为它压根不加锁，可以理解为没有隔离。
• 串行化，读的时候加共享锁，也就是其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读；
• 至于读提交和可重复读，是比较复杂的，既要允许一定的并发，又要兼顾的解决问题；

通过读已提交解决脏读问题

通过读已提交解决了脏读问题。

通过MVVC实现可重复读

为了解决不可重复读，或者为了实现可重复读，MySQL 采用了 MVVC（多版本并发控制）的方式。

通过行锁解决并发更新问题

对于并发写，MySQL 会为表中所有行加行锁，没错，是所有行。但是呢，在加上行锁后，MySQL 会进行一遍过滤，发现不满足的行就释放锁，最终只留下符合条件的行。

解决幻读

并发写问题的解决方式就是行锁，而解决幻读用的也是锁，叫做间隙锁，MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做 Next-Key锁。

用下面的两个事务演示一下Next-Key锁解决幻读的过程

在事务A提交之前，事务B的插入操作只能等待，这就是间隙锁起得作用。当事务A执行update user set name='风筝2号' where age = 10; 的时候，由于条件 where age = 10 ，数据库不仅在 age =10 的行上添加了行锁，而且在这条记录的两边，也就是(负无穷,10]、(10,30]这两个区间加了间隙锁，从而导致事务B插入操作无法完成，只能等待事务A提交。不仅插入 age = 10 的记录需要等待事务A提交，age<10、10<age<30 的记录页无法完成，而大于等于30的记录则不受影响，这足以解决幻读问题了。

这是有索引的情况，如果 age 不是索引列，那么数据库会为整个表加上间隙锁。所以，如果是没有索引的话，不管 age 是否大于等于30，都要等待事务A提交才可以成功插入。

如何设置隔离级别

查看隔离级别的语句：

# 查看事务隔离级别 5.7.20 之后
show variables like 'transaction_isolation';
SELECT @@transaction_isolation

# 查看事务隔离级别 5.7.20 之后
SELECT @@tx_isolation
SELECT @@global.tx_isolation;
show variables like 'tx_isolation'

修改隔离级别的语句：

SET [作用域] TRANSACTION ISOLATION LEVEL [事务隔离级别]

• 作用域，可以是 SESSION 或者 GLOBAL。GLOBAL 是全局的，而 SESSION 只针对当前回话窗口
• 隔离级别，可以是 {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} 这四种，不区分大小写

举例1：设置全局的隔离级别为读已提交

set global transaction isolation level read committed;

举例2：设置当前会话的隔离级别为读已提交

set session transaction isolation level read committed;

其他

妙用 read uncommitted 隔离级别

假如开发同事source一个.sql文件，要插入100万行数据（开头begin，最后才commit），有什么办法可以看到这个操作已插入了多少条？

• 场景模拟：利用insert time模拟source操作如下：

CREATE TABLE test(id int);
BEGIN;
  INSERT INTO test(id) VALUES(123);
  SELECT * FROM test WHERE id=123; 
# commit;

• 解决办法：考虑到读未提交隔离级别是不加锁的，利用这点，可以新开一个查询，调整新session隔离级别从默认的 repeatable read 改为 read uncommitted，再去查行数：

SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;
SELECT * FROM test WHERE id=123;

mysql为什么不需要nolock

为了提高并发sqlserver使用with(nolock)，而mysql为什么不需要nolock？

• nolock 是 SQL Server 特有的功能。例如：对于一个表 A，更新了一行，还没有commit，这时再select from A就会死锁。用select from A（nolock）可以防止死锁，nolock可以忽略锁，直接从数据库读取数据。这意味着可以避开锁，从而提高性能和扩展性。但同时也意味着代码出错的可能性存在。你可能会读取到运行事务正在处理的无须验证的未递交数据。这种风险可以量化。
• mysql 没有这方面问题，对于一个表 A，更新了一行，还没有commit，SELECT * FROM A，将查询到更新以前的原始数据记录，而不会出现死锁问题。mysql的默认隔离级别是REPEATABLE-READ，普通的select操作都是基于快照读，所以不会发生阻塞。