mysql 数据一致性问题方案（MVCC、LBCC）

1.MVCC

在innodb里面，在并发问题下有两种解决一致性问题的解决方案：

1. 非锁定一致性读取

解决读取数据时候的数据一致性问题。不需要对数据进行加锁

1. 锁定一致性读取

在对数据进行更改的时候，为了防止别人页进行更改，需要对数据进行加锁。

在innodb里面的MVCC，也加做多版本并发控制，也是非锁定一致性读取一种解决方案。会生成一个快照，在某个时间节点，我去查询该节点之前提交的数据，而稍后的没有提交的数据我就不能查看，当然这个规定也有个例外，就是你查看了之后又去更改了相关数据，那它一定拿到的是最新的数据。

1.1 MVCC怎么解决脏读

如果要解决脏读，那么我读到的数据应该是都已经提交了的数据。

首先，我们在查询的时候，是知道有哪些事务是没有提交的。 ----通过快照记录，也就是一个数据结构

trx_id_t m_low_limit_id; //如果大于等于这个值的事务  不可见  也称为高水位线

trx_id_t m_up_limit_id;  //所以小于这个值的事务的值都是可见 也称低水位线 其实是m_ids里面的最小值

trx_id_t m_creator_trx_id; //当前的事务ID

ids_t m_ids; //存活的事务ID 就是在创建readView 没有提交的事务的ID集合

low_limit_id：因为非自读的事务ID是递增的，所以就可以知道即将下一个分配的事务ID是多少

up_limit_id：代表当前存活的（未提交的）事务ID里面最小的

m_ids：代表所有存活的事务ID列表

不管你是什么隔离级别，在创建事务的时候可以去添加参数，

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

WITH CONSISTENT SNAPSHOT表示开启一致性的快照，如果没有开启，那么在第一次查询的时候也会开启。

还有个隐藏字段 trx_id 代表修改这个事务最后的ID

同时，我知道我查到的数据，是由哪个事务进行更改的。

事务ID 如果是非只读的，它是递增的。

根据这三个条件，我就能知道我修改数据的事务有没有提交了。

我们去查询的时候，就可以生成一个快照，根据这个快照信息就可以和我们的事务ID来比较，就能知道这个数据有没有提交。

这个肯定会有一个比较规则的。

1.2.怎么解决不可重复读和幻读呢

在第二次查询的时候，如果我时RC的隔离级别，那么我会根据当前的查询时机，再去生成一个readview，如果是RR那么我就会用之前的readview。

有些数据由于没有提交，我不能查看。

2. LBCC

LBCC解决数据一致问题，其实就是给数据加锁，就是当我操作数据的数据，先给数据加一把锁，如果我这个锁没有释放，那么其它线程就不能操作。

innodb里面最小单位是行，加锁，虽然加锁的数据是行，但是加锁的位置不在行上，而是在索引树的节点上，主键索引保存的是完整的行数据。它会根据你的操作条件，比如你根据id去修改，那么它会根据主键去索引，锁定id=1的这一个节点。如果锁的是二级索引，那么它不仅仅是锁二级索引数的节点，还回表到主键索引上锁住对应的节点。

2.1. InnoDB锁

2.1.1. 读锁与共享锁

共享锁，就是加了一把共享锁之后，虽然你不能修改，但是可以读。怎么加共享锁呢。

//正常sql
select * from user where id =1;

//加共享锁
select * from user where id =1 for share;

//这样子加，会在事务提交后，共享锁会释放。如果不释放的话，可以手动开启事务，不提交

BEGIN;
select * from user where id =1;

加了共享锁之后，还是可以加共享锁的，因为它是兼容的，但是不能加排他锁。

2.1.2. 排他锁（写锁）

除了 for update以外，添加数据、修改数据、删除数据都属于一个排他锁，排他锁跟所有的锁都是互斥的。

2.1.3. 意向锁

意向锁也可以分为排他锁、共享锁。

锁的粒度，在innodb里，锁分为表锁、行锁，表锁就是可以对整个表加锁。

//加锁
LOCK TABLES 表名  READ(添加读锁还是写锁) ;

//释放锁
UNLOCK TABLES

//如果一个表里的一个行已经加了排他锁，那么这个表是不能再添加其它的锁的

意向锁就是：
如果一个表里面 已经有数据加锁了，我就不能基于这个表去加排他锁

或者这个表里面有数据加了排他锁，这个表就不能加任何的写锁、读锁。

如果没有意向锁，应该怎么实现意向锁的理念呢，就是在加表锁的时候，去判断这里面是否有数据加锁了，如果加锁了，那么判断这个锁是否与我要加的这个锁是不是互斥的。如果有，那么就不能在表级别加互斥锁。

假如数据量很大，比如100w，我就要去遍历100W的数据，性能肯定很慢。

所以意向锁就是为了解决这个问题的，数据量越大，遍历越多，性能越慢的问题。

意向锁，就是你加表锁的时候，不需要你遍历里面是否加了锁，如何达到这个效果呢。就是如果有数据加锁的话，在表上面做一个标记，这个标记就是意向锁。所以意向锁可以提升我们的性能，特别是表锁。然后数据在加锁的时候，都会先加一个意向锁，告诉这个表已经有行数据加锁了。

在大部分场景下，不会加表锁，会根据条件去更改，锁某些行。那到底锁哪些行呢，跟以下几个锁的概念有关：

2.1.3.1. 记录锁

它锁的是一条记录，这个记录就是索引数的节点。如果这个节点加锁了，那么我就不能再给这个节点加互斥的锁

在data_locks表中可以看到锁的信息。

//假设id =1 加了一个互斥锁
BEGIN;
UPDATE user set age =age+1 where id =1 ;

在data_locks表中，可以看到两条信息，一条是意向锁，一条是id =1 的锁。以下是data_locks表中关键的字段

字段	含义	案例
INDEX_NAME	索引名称	在本例子中，根据id加锁，所以也是一个主键索引
LOCK_TYPE	锁粒度，是表锁，还是行锁	意向锁是表锁
LOCK_MODE	锁的类型
LOCK_STATUS	锁状态，是持有状态，
LOCK_DATA	持有锁的数据	例如 1 （id=1）

记录锁就是锁索引树的节点 并且这个节点不能再去加其它的互斥锁

2.1.3.2. 间隙锁

锁肯定也是锁的节点

代表这个节点到上一个节点之间的数据不能添加。

间隙锁加锁的前提是加锁给某个范围，或者是一个不存在的值，否则就是记录锁。

例子：

假设id =5 的数据不存在，并且id=5 的前一个数据 和后一个数据分别 为id =1  ,id =10

那么对 id =5 加间隙锁 ，那么加锁加在10上面，那么id 在1 到 10 范围内的数据 都不能在添加数据，但是能修改

BEGIN;
UPDATE `user` set age=age+1 where id =4

//查询data_locks表时发现：

锁类型：X,GAP  其中X是排他锁  GAP是间隙锁

//对于添加会失败
INSERT INTO `user`(id,name,age,job) VALUES (3,'李四',11,'时机')
> 1205 - Lock wait timeout exceeded; try restarting transaction
> Query Time: 50.467s
//但可以修改
UPDATE `user` set age=age+1 where id =10

间隙锁解决了幻读问题，加了间隙锁，那么在这个区间内不能添加了。

在RC的情况下是禁用间隙锁的。

2.1.3.3. 临键锁

如果在改记录的时候，它不是一个区间，也不是一个记录，那我会加什么锁。例如

BEGIN;
UPDATE `user` set age=age+1 where id >2 AND ID <13;

结果：

临键锁 = 间隙锁+记录锁。代表这个记录不能修改 并且记录到上个记录区间不能添加数据。

2.2. 总结

从大的方面来讲，又分为共享锁、排他锁。共享锁和共享锁是不互斥的，但是共享锁和排他锁是互斥的。在我们修改数据的时候默认加一个排他锁，或者手动添加也可以。具体锁哪些数据，又分为三个记录锁、间隙锁、临键锁。

记录锁：
锁索引树的某条数据，在索引树的节点节点添加记录锁，代表改记录加锁

间隙锁：

锁索引树节点区间，在索引树的节点添加记录锁，代表该节点到上一个节点的区间的数据不能添加，但是可以修改。间隙锁在RC级别下禁用，所以RR解决幻读而RC没有

临建锁

锁记录也锁节点区间

如果索引树的节点是临建锁，代表该节点以及该节点到上一个节点的区间都会加锁，其实就是记录锁+区间锁。

2.3. 死锁

死锁的四个条件：

互斥：比如修改都是相关的数据

请求或保存：只要你的锁的状态没有过期，就是一直等待

不可剥夺：你不能手动去释放锁

循环等待：我在一直等着你

在mysql里面有默认的死锁检测，默认是开启的，当然你也可以关闭。

你可以用以下的措施减低死锁的可能：

//查看死锁的原因
SHOW ENGINE INNODDB STATUS

保证事务小、执行时间短，尽可能避免大事务。因为大事务执行时间比较久，它的锁会一直占用的。锁是在事务提交以后才会释放。

可以在更低的隔离级别，在更低的隔离级别场景下，它不会加锁。

确保它的执行顺序。

加锁，尽量加锁在索引上面

尽可能少用锁