mysql8隔离级别分析示例

mysql8隔离级别

数据库隔离级别有4种，分别是

• 未提交读（read-uncommitted）
• 提交读（read-committed）
• 可重复读 （repeatable-read）
• 串行化 （serializable）

1：未提交读

在该隔离级别下，A事务下可以查询到B事务下修改而未提交的数据，造成脏读

2：提交读

在提交读的隔离级别下，不会出现脏读情况。但是会出现A事务下多次查询某条数据，该数据在B事务下多次修改并提交，造成A事务下的查询的数据前后不一致，造成不可重复读

大多数数据库的隔离级别

3：可重复读

在该隔离级别下，当A事务开始查询某条数据后，B事务在对该数据进行修改并提交时，A事务查询的结果始终一致，从而避免了不可重复读。 但是不能阻止B事务插入数据，比如A事务查询小明的金额总和，B事务给小明加了100块钱，此时查询的总金额就会和第一次查询不同，造成幻读（这里描述可能有问题，具体请参照下面脏读的示例）

这也是mysql默认的隔离级别

4：串行化

最高隔离级别，确保每个事务串行执行，不会对数据造成读取错误，但是会影响数据库性能，可能会导致大量的响应超时。

举例看下这几个不同隔离级别

​ 首先登陆两个mysql终端，设置会话的隔离级别，然后看结果，首先预制了一张账户表

• 未提交读示例

  A终端set session transaction isolation level read uncommitted;设置为未提交读

  start transaction;开启事务，并查询账户表

  

  B终端同样设置未提交读，并开启事务，更新小明的money但不提交

  

  看下A终端的查询结果

  

  能看到出现了脏读，如果此时B终端事务回滚，会造成数据的错误

• 提交读示例

  我们设置A，B终端隔离级别为提交读read-committed，在重复下刚才的操作

  

  

  此时B修改了小明的钱为90，但事务未提交，我们这时候查看下A终端的结果

  

  可以看到A还是查询到的是100，并没有脏读的情况，然后我们提交B的事务，再看下A的结果

  

  

  可以看到B提交事务之后A查询结果才发生了变化。同时这也验证了A两次查询结果不一致，出现了不可重复读的问题。

• 可重复读

  同样设置AB终端的隔离级别为repeatable read，然后开启事务，A查询小明的账户

  

  然后B终端更新小明的钱后提交

  

  然后我们在A终端查一下小明的账户信息

  

  可以看到小明还是100元，这样就避免了不可重复读的问题。但是当我们在B终端往账户表里插一条数据的时候，看下A的查询结果

  

  

  A终端也没有出现能查询到两条记录的存在。（这里看到有人说在RR隔离级别下MYSQL不会出现幻读，然后查阅了官方说明有了如下示例）

  关于幻读的话，参考Phantom Rows

  1：给account表加主键索引 ALTER TABLE account ADD PRIMARY KEY (id )

  2：A终端查询accout表上id>1的数据，可以查询到一条小红的数据

  3：B终端insert一条id为3的小兰数据，并提交事务。

  4：A终端执行update操作，更新id>1的人员的money -10; 这时候会提示更新了两条几录

  5：A终端再次查询accout表上id>1的数据，查到了两条数据。出现了脏读。

  

  

  我们可以手动解决这个问题，就是加互斥锁

  用select * from account where id>1 FOR UPDATE;

  1:将数据还原，在重试上边操作

  

  2：然后我们在B终端执行update操作

  

  可以发现已经被锁定了。我们在A上更新的时候就只能更新到一条数据了。这样就避免了幻读。

  原理是：为了防止产生幻读，InnoDB使用称为“下一键锁定”的算法，该算法将索引行锁定与间隙锁定结合在一起。 InnoDB执行行级锁定的方式是，当它搜索或扫描表索引时，会在遇到的索引记录上设置共享或互斥锁。因此，行级锁实际上是索引记录锁。另外，索引记录上的下一键锁定也会影响该索引记录之前的 “间隙”。即，下一键锁定是索引记录锁定加上索引记录之前的间隙上的间隙锁定。如果一个会话记录了共享或独占锁R在索引中，另一个会话不能在索引顺序之前的间隙中插入新的索引记录

• 串行化

  这个就不举例了，Serializable完全串行化的读，其实最终也是通过加锁来实现的，但会造成数据库响应慢，因为会竞争锁，所以都不建议开发是开启串行化的隔离级别（待深入研究 = =!）