MySQL锁篇

说明：

此类文章是为小林coding的图解MySQL，所简写，目的在于大家更快抓到小林文章的重点
本文全部由我简化，但是其中有部分引用小林的文章内容
希望大家掌握精髓，构建知识体系和知识框架

锁篇

一、MySQL有那些锁？

全局锁

全局锁主要应用于做全库逻辑备份

缺点是：无法更新数据，会造成业务停滞，不过可重复读隔离级别和MVCC解决了这个问题
备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

# 使用全局锁
flush tables with read lock # 整个数据库就处于只读状态了
# 释放
unlock tables

表级锁

表锁
元数据锁 MDL
意向锁
AUTO-INC锁

# 表锁
# 表级别的共享锁，也就是读锁；
lock tables t_student read;
# 表级别的独占锁，也就是写锁；
lock tables t_stuent write;
# 释放所有锁，当会话退出后，也会释放所有表锁。表锁的颗粒度太大，会影响并发性能，尽量避免使用，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。
unlock tables

# 元数据锁，对数据库表进行操作时，会自动给这个表加上 MDL
# 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
# 对一张表做结构变更操作的时候，加的是 MDL 写锁；
# 有读锁时候想要加写锁，是会被堵塞的，直到读锁被释放，才能加上写锁，相反也是
# MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。
# 读读写读读...
# 可可堵堵堵...

# 意向锁
# 意向 共享/独占锁 + 共享/独占锁 # 也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。
# 而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的
# select 也是可以对记录加共享锁和独占锁的，锁定读
//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;
//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;
# 意向锁的目的是为了快速判断表里是否有记录被加锁。

#AUTO-INC 锁
# 主键自增AUTO_INCREMENT ，主要是通过AUTO-INC 锁实现的
# 执行完插入语句后就会立即释放
# 一个事务在持有 AUTO-INC 锁的过程中，其他事务想要再插入该表，就会被阻塞，为了保证连续递增
# 对大量数据进行插入的时候，会影响插入性能，InnoDB 存储引擎提供了一种轻量级的锁来实现自增
# 给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁
# InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁
# = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
# = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
# = 1
# 	普通 insert 语句，自增锁在申请之后就马上释放
# 	类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放
# 当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题。
# 当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题

表锁和行锁是满足读读共享、读写互斥、写写互斥的

行级锁
MyISAM 引擎并不支持行级锁。

• Record Lock，记录锁，也就是仅仅把一条记录锁上；
• Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
• Next-Key Lock：临键锁Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

因为记录锁是会出现不兼容的，而临键锁是Record Lock + Gap Lock 的组合，自然也是会不兼容的，与记录锁一样

一个事务在插入一条记录的时候，该位置已经被其他事务加了间隙锁（包含临键锁），就会发生阻塞，在拥有间隙锁的那个事务提交前，会生成一个插入意向锁
插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

二、MySQL 是怎么加锁的？

什么 SQL 语句会加行级锁？

普通的 select 语句是不会对记录加锁的，因为它属于快照读，是通过 MVCC（多版本并发控制）实现的。除非加锁定读

update 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)。

怎么加行级锁的？

加锁的对象是索引，加锁的基本单位是 next-key lock，前开后闭区间
间隙锁是前开后开区间。
在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成退化成记录锁或间隙锁。

select * from performance_schema.data_locks\G; 这条语句，查看事务执行 SQL 过程中加了什么锁。

• 如果 LOCK_MODE 为 X，说明是 next-key 锁；
• 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
• 如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

之后再补充五种不同的请况是如何加行级锁

三、update 没加索引会锁全表？

在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

除了 select ... from语句，其他语句都会被锁住不能执行，业务会因此停滞，接下来等着你的，就是老板的挨骂

那 update 语句的 where 带上索引就能避免全表记录加锁了吗？-并不是。
关键还得看这条语句在执行过程种，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

如何避免这种事故的发生？

我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。
update 语句必须满足如下条件之一才能执行成功：

• 使用 where，并且 where 条件中必须有索引列；
• 使用 limit；
• 同时使用 where 和 limit，此时 where 条件中可以没有索引列；

四、MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗？

当然了，就像增删改查，都可以防止，查是select … for update ，这都是当前读

举个例子哈，执行了select … for update，就是开启了一个事务，对这个区间的记录加了next-key lock锁

然后执行删除操作，这时候，因为有锁就被阻塞了，加意向锁，进入等待状态

但是记住，一定要检查，这个字段是否加了索引，否则会走全表扫面，锁全表

五、MySQL 死锁了，怎么办？

两个事务，分别执行了select … for update，后执行更新或者删除，创建，就会进入死锁，双方都在等待对方提交事务，无限循环，前提，没开死锁检查

如何避免死锁？

两种策略通过「打破循环等待条件」
数据库层面，两种策略通过「打破循环等待条件」

• 设置事务等待锁的超时时间，innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。
• 开启主动死锁检测，检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行，将参数 innodb_deadlock_detect 设置为 on，默认就开启

六、字节面试：加了什么锁，导致死锁的？

两个事务，先分别执行了update,再执行insert，出现死锁

update时候是x型意向锁，x型间隙锁

insert时候是x型插入意向锁，x型间隙锁

插入意向锁和间隙锁之间是冲突的。

双方都在等待对方的间隙锁释放，造成循环等待
满足了互斥、占有且等待、不可强占用、循环等待