MySQL是如何保证数据一致性的？

原创

王二蛋

发布于 2024-01-28 10:19:51

8K7

文章被收录于专栏：MySQLMySQL

前言

通过上文《MySQL是如何保证数据不丢失的？》可以了解DML的操作流程以及数据的持久化机制。对于一个数据库而言，除了数据的持久性、不丢失之外，一致性也是非常重要的，不然这个数据是没有任何意义的。在使用MySQL时，数据不一致的情况也可能出现，所以，本文就来看看MySQL是如何保证数据一致的。

MySQL保证的一致性

在这之前先划清一下界限，看一下MySQL保证的是哪里的一致性。

拿一个最简单的转账例子，用户A向用户B转1000元，正常的sql是这样的

update account set balance=balance-4000 where user='A' and balance >= 4000;
update account set balance=balance+4000 where user='B';

示例表数据如下

如果最终用户A账户没有扣4000，而用户B账户多了4000，总金额也无缘无故的多了4000。这个时候就造成了数据不一致了。出现这个问题可能存在几个原因：

在MySQL客户端执行sql时没有做校验。如果用户A余额并不足以4000，由于没有校验，两条sql都会成功执行，用户B就会凭空多出4000。
两条sql并不在同一事务中。可能sql1执行失败了，sql2执行成功，由于不再一个事务中导致用户B多了4000。
在MySQL内部执行时因为某些故障而出现了不一致情况。

很显然，第三点是需要MySQL解决处理的。而第一点是属于MySQL客户端的逻辑BUG，第二点会存在客户端在使用事务时不遵循规则的情况，都属于外部因素，MySQL不可控。

所以，MySQL保证的一致性是：在一个事务中的DML（增删改）操作。尽管DML本身可能存在问题。

MySQL发生不一致环节

划清界限后再分析一下在DML执行过程中，哪个环节会发生数据不一致。以上面的sql为例，假设已经进行过校验且在同一事务。

并发冲突

在执行第一条sql时，「执行器」会通过条件user='A' and balance >= 4000在「存储引擎」获取到符合条件的记录，然后进行balance扣减操作。（不知道这个流程的可以看下前面的文章）

如果这个时候存在并发现象，扣减操作可能会执行多次，这个balance肯定就不是预想中的结果了，也就发生数据不一致了。如下图

当3个update请求同一时间调用存储引擎对同一数据页更新后，正常情况下，balance值应该为0。但是因为并发操作，balance的值可能会被修改为-1000或者-2000等其他值，这样的bug显然是不可被接受的。

有并发经验的应该都知道需要通过锁资源可以避免这个情况，InnoDB也是通过加锁来处理的。

redolog不完整

通过上文可以知道，InnoDB是通过「双写缓冲」、「Redo Log」等机制保证数据不丢失的。

这种情况下，假设第一条sql执行成功并且对应的redo log已经被刷新到磁盘中，但是第二条sql执行失败或者MySQL服务宕机导致其redolog未刷新到磁盘，那么在下次启动恢复时，就会发生数据不一致了。如下图

sql示例的第一条执行结果通过redolog恢复了，但是第二条的redolog随着宕机丢失了，于是乎造成了数据的不一致。（redo log的刷盘机制和构建脏页可以通过上文进行了解。）

对于这种情况，InnoDB是通过上文提到的「Undo Log」来解决的。

binlog&redolog不一致

我们知道，binlog中记录了所有对数据更新的原始sql，以便数据备份恢复、主从复制。与redolog不一样的是binlog属于MySQL server层，而redolog是InnoDB的机制，用于故障恢复，两者并不冲突，这里不过多赘述。

虽然不冲突，但是要保证两者在事务提交后都可以持久化到磁盘，不然就会在主从复制的时候出现数据不一致现象，如下图

只要binlog和redolog有一方没有同步持久到磁盘都会发生类似现象。针对这种情况MySQL是通过两阶段提交解决的。

以上就是DML在执行过程中可能出现不一致的环节（没有想到的欢迎评论交流）。接下来具体看一下InnoDB针对以上几种情况是如何处理解决，从而保证数据一致性的。

MySQL解决不一致方案

加锁解决并发冲突

锁没有什么好说的，innoDB根据隔离级别决定是否用锁(当然，还有server层的表锁什么的这里不展开)。这里就演示下在隔离级别REPEATABLE-READ下，锁在SQL执行中的具体作用和效果。

当在第一个事务中执行 update account set balance=balance-4000 where user='A' and balance >= 4000; 时，其他事务不能对user为'A'的记录进行更新。如下图，当第二个事务窗口执行 update account set balance=balance-1000 where user='A' and balance >= 1000; 时会被阻塞住，直到第一个事务提交或者超时。

这个时候可以通过 select * from sys.innodb_lock_waits ; 查看一下锁的相关信息

这里的locked_type是RECORD，也就是行记录锁，还有一个是间隙锁。

间隙锁的作用是保证某个范围内的数据在锁定情况下不会发生任何变化。比如，当第一个事务执行update account set balance=balance-100 where id between 7 and 9;后，第二个事务在执行INSERT INTO account (id, user,balance) VALUES (8, 'ABD',5000);时会阻塞，但是执行 INSERT INTO account (id, user,balance) VALUES (16, 'ABDD',5000);会成功执行，因为插入id为16的行数据不会影响到7～9之间的数据。这个时候去查看select * from sys.innodb_lock_waits;时会发现waiting_lock_mode值为 X,GAP（间隙）。