MySQL锁

了解全局锁吗？

使用全局锁：

flush tables with read lock

执行后，整个数据库就处于只读状态了，其它线程执行写操作都会被阻塞。

如果要释放锁：

unlock tables

全局锁的应用场景主要是数据库的全库逻辑备份。这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

表级锁？

MySQL的表级锁主要有：

• 表锁：对整个表加锁，粒度最大。
• 元数据锁：为了保证用户对表进行CURD的时候，防止其它线程对这个表结构进行变更。
• 意向锁：意向锁的存在就是为了让加表锁的时候，不需要遍历表的所有记录，查看是否有行级锁，而是直接判断是否有意向锁即可。就是为了快速判断表里面是否有记录被加锁。
• AUTO-INC锁：插入数据时候，保证主键全局自增。插入的时候会给auto_increment修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个锁释放了。

行级锁？⭐⭐

普通的select语句是不会加锁的，属于快照读。加锁的方式属于当前读或者锁定读。

select ... lock in share mode; # 读锁
select ... for update; # 写锁

• Record Lock：记录锁，可以分成S/X锁，即读/写锁。粒度最小，只锁一条记录。
• Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身。间隙锁之间是不互斥的，即两个事务可以同时包含共同的间隙锁，间隙锁是用来防止插入幻影记录提出的。
• Next-Key Lock：临键锁，记录锁+间隙锁的组合，锁定一个范围，并且锁定记录本身。InnoDB当前读解决可重复读隔离级别下的幻读问题就是基于Next-Key Lock实现的。
• 插入意向锁：插入意向锁并非意向锁，而是在插入的时候会锁住一个点，比如插入一条id为5的数据，就会锁住5这个点，可以看作一个特殊的间隙锁，区间左右相等。

MySQL是如何加行锁的？⭐⭐⭐

加锁的对象是索引，加锁的基本单位是next-key lock，next-key lock是前开后闭区间，而间隙锁是前开后开区间。

但是next-key lock在某些情况下会退化成间隙锁或记录锁。

如果在只使用间隙锁或记录锁就能避免幻读的情况下，next-key lock就会退化成间隙锁或者记录锁。

• 唯一索引等值查询：

  • 如果查询的记录不存在，那么next-key lock就会退化成间隙锁；
  • 如果查询的记录存在，那么next-key lock就会退化成记录锁；

• 非唯一索引等值查询：

  由于存在两个索引，主键索引和非唯一索引，所以加锁的时候，会同时对两个索引加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会加锁。

  • 如果查询的记录存在：由于索引不唯一，肯定存在相同的记录，在扫描到的符合条件的二级索引加next-key lock，对于第一个不符合条件的二级索引记录，该二级索引记录的next-key lock会退化成间隙锁。同时符合条件的记录会在主键索引上加上记录锁。

  • 如果查询的记录不存在：扫描到的第一个不符合条件的非唯一索引记录，该索引的next-key lock会退化成间隙锁，不会对主键加锁。

• 唯一索引范围查询：

  • 大于等于：如果等值的记录如果存在，那么该记录的next-key lock会退化成记录锁，其情况都是next-key lock。
  • 小于等于：如果等值的记录不在表中，那么不管是小于还是小于等于，扫描到终止范围查询（也就是第一个不满足条件的）的记录时，该记录的索引的next-key锁会退化成间隙锁（因为不需要对不满足条件的记录加记录锁，这条记录的修改删除操作不会影响本事务，只需要锁住区间，防止插入幻影记录即可）。如果等值得记录存在表中，那么就会加next-key lock。

• 非唯一索引等值查询：

  非唯一索引加锁的时候，都是next-key lock，不会退化成间隙锁或者记录锁。

• 没有使用索引的查询：

  如果查询的条件里面没有用到索引或者查询的语句索引失效，就会全表扫描，那么每条记录都会加上next-key lock，相当于锁住了整张表，其它事务都不能对表进行增删改了。

MySQL死锁是如何发生的？⭐⭐⭐

看个例子：

此时数据库中最大的id是6，执行下列sql语句：

# 1 事务A 不存在
select * from user where id = 10 for update;
# 2 事务B 不存在
select * from user where id = 11 for update;
# 3 事务A 阻塞
insert into user (id, name, age) values (10, 'jie', 21);
# 4 事务B 阻塞
insert into user (id, name age) values (11, 'feng', 21);

这个时候就会发生死锁。

原因是在执行1的时候会加上范围为(6, +∞)的间隙锁，执行2的时候会加上范围为(6, +∞)的间隙锁，在执行3的时候会加上一个值为10的插入意向锁，执行4的时候会加上一个值为11的插入意向锁，但是插入意向锁和间隙锁是互斥的，此时事务A等待事务B释放间隙锁，而事务B又等待事务A释放间隙锁，就造成了死锁。

如何避免死锁？⭐⭐⭐

死锁的四个必要条件：互斥、占有且等待、不可剥夺、循环等待。只要破坏一个就可以了。

在数据库层面，主要有两种方法：

• 设置事务等待锁超时的时间：如果一个事务等待的时间超过该值后，自动回滚事务，锁就释放了，另外一个事务就可以继续执行了。

  innodb_lock_wait_timeout就是设置这个值，默认50s。

• 开启死锁自动检测：主动检测发现死锁，主动回滚发生死锁的某一个事务。参数innodb_deadlock_detect设置成on，默认开启。

一个段子：

面试官：解释下什么是死锁？

求职者：你录用我，我就告诉你。

面试官：你告诉我，我就录用你。

求职者：你录用我，我就告诉你。

面试官：滚！