mysql锁

锁

1 并发问题的解决方案

怎么解决 脏读 、 不可重复读 、 幻读 这些问题呢？其实有两种可选的解决方案：

**脏读：**两个事务执行，事务B更新了一个数据，还没提交，事务A读取到了这个数据，然后事务B回滚了，此时事务A就是脏读。

**不可重复读：**事务A第一次读到数据为x, 事务B将x 更新为y,此时事务A再读取该数据变成了y。这就叫不可重复读。

**幻读：**事务A第一次读到了一条数据，事务B插入了两条数据且提交了，事务A用相同条件读出来两条数据，这就叫幻读。

方案一：读操作利用多版本并发控制（ MVCC），写操作进行 加锁 。

方案二：读、写操作都采用 加锁 的方式。

2 锁的不同角度分类

1 数据操作类型

读锁 ：也称为 共享锁 、英文用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会 互相影响，相互不阻塞的。

写锁 ：也称为 排他锁 、英文用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样 就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

2 数据操作颗粒度

1. 表锁（Table Lock）

在对某个表执行一些诸如 ALTER TABLE 、 DROP TABLE 这类的 DDL 语句时会触发表锁。

1 x锁 s锁

2 意向锁

innodb支持行索，我对一条数据加了行索，现在我要修改这个表结构，DDL操作属于表锁，就会发现阻塞了，因为有一条数据加了行索。他在加表锁时就要一条条记录的去扫描，看有没有加了X锁，如果数据很多，就会很费时，此时意向锁的作用就体现了，我们为数据加锁时，会在它的上一层结构加一个意向锁，表示有人加过锁了之前加了行索，就会自动生成一个表级别意向锁（x锁就生成对应的意向x锁）,要加表锁时，就能立马发现表中有其他锁，立马进入阻塞状态。

3 自增锁

AUTO_INCREMENT就会加上自增锁。

简单插入 就是 事先知道了要插入多少条记录，它获得锁后就知道自己要自增多少，然后就可以释放锁了。

批量插入 不知道要插入多少条记录。所以会一直等到插入完成之后才会释放锁。

8.0以后就不会使用表级别的 自增锁了。

4 元数据锁

保证其他查询语句在遍历数据时，不能更改表结构，更改了就多出来一列了。当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写 锁

2. InnoDB中的行锁

1 记录锁（Record Locks）

仅对一条记录加锁。

2 间隙锁 gap locks

间隙锁的锁定区间是 开区间 （x,y）

如 对上图不存在的记录 6 上锁 锁住的区间是（3,8），在此区间插入数据都会发生阻塞。

对 21 上锁 锁住的区间是（20，+∞）

3 临键锁 next key locks

可以理解为 间隙锁 + 记录锁

begin;
select * from student where id <=8 and id > 3 for update;

锁区间 是 前开后闭 （3,8]

数据库数据 如图所示， sql语句加锁区间（10,20], 执行在 8 插入数据 发现发生了等待

实际锁住的区间是 (7,20]

4 插入意向锁 insert intention lock

效果演示

sql语句在(10,20]区间加锁，实际锁住区间，(6,20] 

在事务没提交之前插入 7这条数据 发现这个区间被锁住了 要等待

另一个事务也插入 7 也要等待 当事务提交后 插入位置发生了冲突

当事务提交后 哪个先抢到锁 ，哪个执行成功。

插入意向锁并不会锁住整个区间，只锁住当前记录。

演示 此时事务没提交

插入 10 直接成功 说明锁住的不是间隙（6，20）

插入 11 发生等待

只是锁住了 11 那个位置。

3 页锁

了解即可

3 对待锁的态度

1 悲观锁

悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 阻塞 直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞， 用完后再把资源转让给其它线程）。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当 其他线程想要访问数据时，都需要阻塞挂起。Java中 synchronized 和 ReentrantLock 等独占锁就是 悲观锁思想的实现。

2 乐观锁

乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。那么我们如何实现乐观锁呢，一般来说有以下2种方式：

1.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现 方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录 的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数 据。

2 使用时间戳 timestamp来实现，数据库添加一个时间戳字段，首先查出来，然后修改的时候看时间戳与之前查出来的一不一样。不一样就返回错误信息。

4 加锁方式

1隐式锁

2 显式锁

3 锁的内存结构

一堆比特位 记录的是哪些记录加锁了 哪些没加 加锁用1表示 占一个bit位。

type_mode 记录了索引的类型，是否处于等待状态（用一个bit位记录）。

加锁规则

next-key lock的加锁规则

建立test表 ID 是唯一索引 col1是普通索引

1：唯一索引等值查询间隙锁

根据原则1 加锁范围是 （5,10], 根据优化原则2 锁退化为 （5,10）

2：非唯一索引等值查询锁

根据 原则1 加锁范围为 （0,5]， 由于是普通索引 还要向后扫描 加锁范围为（5,10],根据优化2 退化为（5,10),如果此时另一个事务更新 where col1 =5 会发生阻塞（原因我猜的，他在修改时，col1上有个事务id与当前事务id不一致，所以发生阻塞），

sessionB能更新成功因为没有发生回表操作，用到了索引覆盖，sessionA 的col1索引记录了主键值，所以不会给主键加索引。

sessionA改一下 多查询一个值 col2 ,根据原则2 要给主键也加索引。 示例如下

发生了阻塞

3：主键索引范围查询锁

语句1 根据优化1 主键等值查询 锁住的是 id =10 一条记录。

语句2 它是范围查询， 范围查找就往后继续找，找到 id=15 这一行停下来，不满足条件，因此需要加 next-key lock(10,15] 。

4：非唯一索引范围查询锁

在第一次用 col1=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 col1 是非唯 一索引，没有优化规则，也就是 说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-keylock 。

这里需要扫描到 col1=15 才停止扫描，是合理的，因为 InnoDB 要扫到 col1=15 ，才知道不需要继续往后找了。

5：唯一索引范围查询锁 bug （个人验证时 8.0.24 bug修复了）

session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15] 这个 next-key lock ，并且因 为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。

但是实现上， InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20 。而且由于这是个范围扫 描，因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。照理说，这里锁住 id=20 这一行的行为，其 实是没有必要的。因为扫描到 id=15 ，就可以确定不用往后再找了。

6：非唯一索引上存在 " " 等值 " " 的例子

7： limit 语句加锁

session A 的 delete 语句加了 limit 2 。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2 ，删 除的效果都是一样的。但是加锁效果却不一样 。

这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (col1=10, id=30) 这一行之后， 满足条件的语句已经有两条，循环就结束了。因此，索引 col1 上的加锁范围就变成了从（ col1=5,id=5) 到（ col1=10,id=30) 这个前开后闭区间，如下图所示：

8：一个死锁的例子

9：order by索引排序的间隙锁1

如下面一条语句

begin;
select * from test where id>9 and id<12 order by id desc for update;

下图为这个表的索引id的示意图。

1. 首先这个查询语句的语义是 order by id desc ，要拿到满足条件的所有行，优化器必须先找到 “ 第 一个 id<12 的值 ” 。
2. 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=12 的这个值，只是最终 没找到，但找到了 (10,15) 这个间隙。（ id=15 不满足条件，所以 next-key lock 退化为了间隙锁 (10, 15) 。）
3. 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到 id=5 这一行，又因为区间是左开右 闭的，所以会加一个next-key lock (0,5] 。 也就是说，在执行过程中，通过树搜索的方式定位记录 的时候，用的是 “ 等值查询 ” 的方法。

10：order by索引排序的间隙锁2

1. 由于是 order by col1 desc ，第一个要定位的是索引 col1 上 “ 最右边的 ”col1=20 的行。这是一个非唯一索引的等值查询：

   左开右闭区间，首先加上 next-key lock (15,20] 。 向右遍历，col1=25不满足条件，退化为间隙锁 所以会 加上间隙锁(20,25) 和 next-key lock (15,20] 。

2. 在索引 col1 上向左遍历，要扫描到 col1=10 才停下来。同时又因为左开右闭区间，所以 next-key lock 会加到 (5,10] ，这正是阻塞session B 的 insert 语句的原因。

3. 在扫描过程中， col1=20 、 col1=15 、 col1=10 这三行都存在值，由于是 select * ，所以会在主键 id 上加三个行锁。 因此， session A 的 select 语句锁的范围就是：

   1. 索引 col1 上 (5, 25) ；
   2. 主键索引上 id=15 、 20 两个行锁

11：update修改数据的例子-先插入后删除

注意：根据 col1>5 查到的第一个记录是 col1=10 ，因此不会加 (0,5] 这个 next-key lock 。

session A 的加锁范围是索引 col1 上的 (5,10] 、 (10,15] 、 (15,20] 、 (20,25] 和(25,supremum] 。

之后 session B 的第一个 update 语句，要把 col1=5 改成 col1=1 ，你可以理解为两步：

1. 插入 (col1=1, id=5) 这个记录；
2. 删除 (col1=5, id=5) 这个记录。

通过这个操作， session A 的加锁范围变成了图 7 所示的样子:

好，接下来 session B 要执行 update t set col1 = 5 where col1 = 1 这个语句了，一样地可以拆成两步：

1. 插入 (col1=5, id=5) 这个记录；
2. 删除 (col1=1, id=5) 这个记录。 第一步试图在已经加了间隙锁的 (1,10) 中插入数据，所以就被堵住了。