mysql 模拟锁 mysql mdl锁

锁

表锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问；

表锁级别的两种锁：

• 表锁
  表锁的语法是 lock tables … read/write
• 原数据锁 MDL （metadata lock）
  server 层级别的锁。
  主要用于隔离DML和DDL操作之间的干扰。

执行DML，需要申请 MDL 读锁

执行DDL，需要申请 MDL 写锁

给一个小表加个字段，导致整个库挂了

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。

在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操作不慎也会出问题。

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

sessionA 申请 MDL 读锁成功，但不提交事务，MDL读锁不会释放。

sessionB 申请 MDL 读锁成功，但不提交事务，MDL读锁不会释放。

sessionC DDL 操作，申请MDL写锁失败，阻塞住。

sessionD 申请 MDL 读锁失败，也被阻塞住。

申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到MDL锁后，直到事务执行完才会将锁释放。

特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放。

例如id 44的语句改为

begin;alter table testok add z varchar(10) not Null;select * from testok;

此时一旦alter语句执行完成会马上提交事务（autocommit=1），后面的select就在本次事务之外，其执行完成后不会持有读锁。
所以 session C被阻塞后，session D也运行不了。

拓展知识：online ddl

由于ddl执行时如果锁表的话会严重影响性能，不锁表又难搞定操作期间dml语句的影响，于是mysql推出了全新的online ddl概念

1. 拿MDL写锁
   2. 降级成MDL读锁
   3. 真正做DDL
   4. 升级成MDL写锁
   5. 释放MDL锁

其实就是锁降级，拿到MDL写锁之后降级为 MDL 读锁，申请空间，填入数据，完毕之后，再升级为 MDL 写锁，再释放。

（Data Manipulation Language，数据操纵语言，如select）

（Data Definition Language，数据定义语言，如改表头新增一列）

如何安全地给小表加字段？

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。

在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。

如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

如果对数据库请求量大的话，这时候 kill 可能未必管用，因为新的请求马上就来了。

比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。

行锁

行锁是对所有行级别锁的一个统称，比如下面说的记录锁、间隙锁、临键锁都是属于行锁， 行锁是指加锁的时候锁住的是表的某一行或多行记录，多个事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问；

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

事务在执行的时候，并不是一次性把所有行锁都持有，而是执行到哪一行就拿哪一行的锁。等到最后commit的时候，一起释放。

所以把最有可能产生冲突的语句往后放，提高并发度。

记录锁(Record Lock)

记录锁属于行锁中的一种，记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录。

**触发条件：**精准条件命中，并且命中索引；

**例如：**update user_info set name=’张三’ where id=1 ,这里的 id 是索引。

**记录锁的作用：**加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

间隙锁(Gap Lock)

间隙锁属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。

比如下面的表里面的数据ID 为 1,4,5,7,10 ,那么会形成以下几个间隙区间，-n-1区间，1-4区间，7-10区间，10-n区间 （-n代表负无穷大，n代表正无穷大）

触发条件：范围查询，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重复读)的事务级别中。

例如：对应上图的表执行 select * from user_info where id>1 and id<4 (这里的id是唯一索引) ，这个SQL查询不到对应的记录，那么此时会使用间隙锁。

间隙锁作用：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下图的问题，在同一个事务里，A事务的两次查询出的结果会不一样。

A事务中间在其查询范围内因为B事务插入了一条新数据，导致幻读。

临键锁(Next-Key Lock)

临键锁也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合。

临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。

• 自己
• 范围内的
• 相邻的

select * from user_info where id>1 and id<=13 for update ;

会锁住ID为5,10的记录；【自己】

同时会锁住，1-5 、 5-10；【范围内】

10-15的区间； 【下一区间】

**触发条件：**范围查询，条件命中了索引。

**临键锁的作用：**结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之后，在范围区间内数据不允许被修改和插入。

状态锁

状态锁包括意向共享锁和意向排它锁，把他们区分为状态锁的一个核心逻辑，是因为这两个锁都是都是描述是否可以对某一个表进行加表锁的状态。

意向锁的解释：当一个事务试图对整个表进行加锁（共享锁或排它锁）之前，首先需要获得对应类型的意向锁（意向共享锁或意向共享锁）

意向共享锁

当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。

意向排他锁

当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

为什么我们需要意向锁？

意向锁光从概念上可能有点难理解，所以我们有必要从一个案例来分析其作用，这里首先我们先要有一个概念那就是innodb加锁的方式是基于索引，并且加锁粒度是行锁，然后我们来看下面的案例。

如果事务B需要对整个表进行修改操作。

update user_info set name =”李四”

因为事务B是对整个表进行修改操作，那么此SQL是需要对整个表进行加排它锁的

我们首先做的第一件事是先检查这个表有没有被别的事务锁住，只要有事务对表里的任何一行数据加了共享锁或排他锁我们就无法对整个表加锁（排他锁不能与任何属性的锁兼容）。

因为INNODB锁的机制是基于行锁，那么这个时候我们会对整个索引每个节点一个个检查，我们需要检查每个节点是否被别的事务加了共享锁或排它锁。

最后检查到索引ID为6的节点被事务A锁住了，最后导致事务B只能等待事务A锁的释放才能进行加锁操作。

需要遍历整个索引树才能知道自己是否能够进行加锁，这种方式是不是太浪费时间和损耗数据库性能了？

**所以就有了意向锁的概念：**如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是我们的意向锁。

在测试锁的实操过程中需要注意的问题。

1、关闭自动提交事务功能，自己手动begin commit rollback。

show variables like 'autocommit'
 Set autocommit = 0

2、查看当前会话隔离级别是否为REPEATABLE-READ（一般默认都是此级别）

SELECT @@tx_isolation

3、最重要的一点，查询数据的时候要使用当前读（因为Mysql 有MVCC的机制所以很多情况下都不会进行加锁，使用当前读就不会使用MVCC） 比如使用下面这个 for update 就是使用当前读。

BEGIN
 
 select * from tb_user where id>3 and id<10 for update
 
 COMMIT

死锁

死锁不是一种锁，而是出现的一种系统错误。

事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。

出现了死锁怎么办？

1. 数据库内部等待，直到超时。innodb_lock_wait_timeout 来设置。
2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

第一个方案：如果时间设置太长，默认值为50s，线上的业务是不可能等待50s的，如果设置太短如1s，那会产生很多误报。

第二个方案：正常情况下我们还是要采用第二种策略，即：主动死锁检测。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n) 的操作。消耗大量的 CPU 资源。

控制并发度：考虑在中间件实现，基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

low一点的方法：一行变成多行。比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。