MySQL_锁

MySQL有哪些锁？

根据加锁的范围：
全局锁，表级锁，行锁。

全局锁

使用方法：
flush tables with read lock
整个数据库就处于只读状态了。
释放全局锁：
unlock tables
会话断开，全局锁也会释放。

全局锁的应用场景

全库逻辑备份

备份数据用全局锁的缺点

整个数据库只读，业务停滞。

备份数据怎么避免影响业务

使用工具mysqldump，加上参数-single-transaction。在备份数据库前开启事务，创建Read View，整个事务执行期间都在使用这个Read View，由于MVCC的支持，备份期间业务仍然可以对数据进行更新操作。
注意：数据库的存储引擎必须支持可重复读的隔离级别。（InnoDB可以用此方法，MyISAM必须用全局锁）

表级锁

分类：
表锁，元数据锁MDL，意向锁，AUTO-INC锁。

表级锁-表锁

使用方法：
表级别的共享锁，读锁：lock tables t_student read;
表级别的独占锁，写锁：lock tables t_student write
表锁也会限制本线程的读写操作。
解锁：
unlock tables;释放当前会话中的所有表锁。（会话退出后，也会释放当前会话的所有表锁）

表级锁-元数据锁MDL

目的：
保证用户对表CRUD时，防止其他线程对表结构做变更。
分类

1. MDL读锁：对一个表进行CRUD时。
2. MDL写锁：对一个表做结构变更操作时。

MDL不需要显式调用，那它是在什么时候释放的呢
释放时机：
事务提交时释放。（事务执行期间，MDL是一直持有的）

线程A-CRUD，线程B-申请MDL写锁，线程C-CRUD，为什么BC都会阻塞
原因：
申请MDL锁的操作会形成一个队列，写锁获取的优先级高于读锁。一旦出现MDL写锁等待，会阻塞后续该表的所有CRUD操作。

怎么安全的对表结构变更
在表结构变更前，先看看数据库中的长事务，是否有事务已经对表加上了MDL读锁，可以考虑kill掉这个长事务。

表级锁-意向锁

目的：
为了快速判断表里是否有记录被加锁。
分类：

1. 意向共享锁：InnoDB在对记录加共享锁时，需要在表级别加一个意向共享锁。
2. 意向独占锁：InnoDB在对记录加独占锁时，需要在表级别加一个意向独占锁。

加锁时机：
增删改时会加意向独占锁
查SELECT不会加意向锁，因为靠的是MVCC保证一致性。
但SELECT也是可以对记录加共享锁or独占锁的。
先在表上加上意向独占锁，再对读取到的记录加独占锁
SELECT ... for update
先在表上加伤意向共享锁，再对读取到的记录加共享锁
SELECT ... lock in share mode
冲突：
意向共享锁和意向独占锁 不会和行级锁发生冲突，也不会和他们自己发生冲突。
只会和表锁发生冲突。（独占表锁lock tables...write;共享表锁lock tables read;）

表级锁-AUTO-INC 锁

作用：
给自增主键赋值。插入数据时，会加一个表级别的AUTO-INC锁，语句执行完后就释放锁，而不是等到事务提交。
优化：
(大量插入时，由于另一事务会阻塞，所以性能低。)InnoDB存储引擎提供了一种轻量级的锁实现递增，申请完递增主键就释放锁，并不需要等语句执行后才释放。

innodb_autoinc_lock_mode系统变量=0、1、2时：

1. =0 采用AUTO-INC锁。
2. =1 普通INSERT：轻量级锁。批量插入INSERT...SELECT：AUTO-INC锁。
3. =2 采用轻量级锁。

问题：
其中innodb_autoinc_lock_mode=2性能最高。但搭配binlog日志格式是statement时，在主从复制场景，会出现数据不一致问题。（=2，语句未执行完，可以被打断。但在从节点中，statement的binlog记录的操作语句是串行的，不会被打断。）

解决办法：
当innodb_autoinc_lock_mode=2时，并且binlog_format=row，既能提高并发性，又不会出现数据一致性问题。

行级锁

分类：
Record Lock，Gap Lock，Next-Key Lock

行级锁-Record Lock

记录锁，锁住一条记录，分为X锁、S锁，事务commit后锁会释放。

行级锁-Gap Lock

间隙锁，只存在于可重复读隔离级别，为了解决幻读。
间隙锁之间是兼容的，包含相同间隙的间隙锁不会互斥。

行级锁-Next-Key Lock

临键锁，左开右闭。记录锁+间隙锁
相同的X型的Next-Key Lock是会阻塞的，因为要考虑记录锁的互斥。

行级锁-插入意向锁

场景：
插入记录时，插入位置被其他事务加了间隙锁，插入操作会阻塞，生成插入意向锁，处于等待状态。直到其他事务释放间隙锁。

MySQL加锁过程：
先生成锁结构，再设置锁状态（等待状态：未成功获取到锁。正常状态：成功获取到锁）。

MySQL是怎么加行级锁的

加锁的对象是索引。
加锁的基本单位是next-key lock，前开后闭。
next-key lock在一些情况下会退化成记录锁、间隙锁，当使用记录锁、间隙锁就能避免幻读的场景下。
已提交读-只有记录锁。可重复读-有3种行级锁，目的是避免幻读。

唯一索引等值查询

1. 记录存在的情况：
   加2个锁：表锁-X类型的意向锁，行锁-X类型的记录锁。
2. 记录不存在的情况
   加2个锁：表锁-X类型的意向锁，行锁-X类型的间隙锁。

有哪些命令可以分析加了那些锁
select * from performance_schema.data_lock\G

1. 字段LOCK_TYPE：TABLE表级锁，RECORD行级锁
2. 字段LOCK_MODE：x,：next-key锁，x,REC_NOT_GAP：记录锁，GAP：间隙锁。

间隙锁的范围怎么确定
LOCK_DATA=5确定右边界5，左边界是5的上一条记录的id值1。所以是(1,5)

唯一索引范围查询

1. 大于等于，如果边界值在表中，退化成记录锁。
2. 小于或小于等于，边界值不在表中，退化成间隙锁。
3. 小于，边界值在表中，退化成间隙锁。

针对 大于 的范围查询
select * from user where id > 15 for update;
加的都是next-key lock
注意：InnoDB存储引擎，用特殊标记spremum pseudo-record标记最后一条记录。最后会加一个(20，+∞]的next-key lock。

针对 大于等于 的范围查询
select * from user where id >= 15 for update;
如果表中存在边界值15，那么这个next-key lock会退化成记录锁。

针对 小于/小于等于 的范围查询
select * from user where id < 6 for update;
如果边界值不在表中，最后一个next-key lock退化成间隙锁。
注意：特殊标记infimum record标记第一条数据。一开始会加一个(-∞，2]的next-key lock。

针对 小于等于 的范围查询
select * from user where id <= 5 for update;
如果表中存在边界值，加的都是next-key lock

针对 小于 的范围查询
select * from user where id < 5 for update;
如果表中存在边界值，最后一个next-key lock退化成间隙锁。

非唯一索引等值查询

会同时对非唯一索引（二级索引）和主键索引加锁。但加锁方式不同。

1. 记录存在时：二级索引加next-key lock，第一个不符合条件的退化成间隙锁。主键索引加记录锁。
2. 记录不存在时：二级索引第一个不符合条件的退化成间隙锁。主键索引不会加锁。

由于二级索引加的锁是next-key lock或间隙锁，所以加锁是可以插入相同二级索引的新的记录的（在主键索引上动手脚，避开锁定的区间）。

非唯一索引范围查询

非唯一索引加的都是next-key lock。
主键索引对符合条件的记录加记录锁。

没有加索引的查询

全表扫描，每一条记录的索引都会加上next-key lock，相当于锁住了全表。
同样的，delete、update如果不走索引，也会给每一条记录的索引加上next-key lock。
所以，在执行update、delete、select...for update等具有加锁性质的语句时，一定要保证其走索引。

update没加索引会锁全表吗

会。
原因：
update语句的where条件没有使用索引，就会全表扫描，会对所有记录加上next-key lock，相当于把全表锁住了。
where带上索引就能避免全表记录加锁了吗？
并不是，要看优化器最终选择的是索引扫描还是全表扫描。
加的是表锁吗？
不是，加的是行级锁 next-key lock。
如何避免：
将MySQL参数 sql_safe_updates参数设置为1。开启安全更新模式。
update语句必须满足下面条件之一才能执行成功：

1. 使用where，where条件中必须有索引列。
2. 使用limit。
3. 同时使用where和limit，此时where条件中可以没有索引列。

delete语句必须满足以下条件才能执行成功：
· 同时使用where和limit，此时where条件中可以没有索引列。

如果where带上了索引列，但优化器最终选择全表扫描，我们可以使用force index([index_name])，告诉优化器使用哪个索引，避免锁全表。

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

可以。

MySQL死锁了，怎么办

死锁发生的原因：
两个个事务都进行了当前读，对同一个区间同时加了间隙锁（不会冲突，目的是阻止区间被插入，预防幻读），然后又都想在该间隙中插入数据（生成插入意向锁），此时双方都在等待对方释放间隙锁，循环等待，发生死锁。

Insert语句是怎么加行级锁的

Insert语句正常执行时，是不会生成锁结构的，而是靠隐式锁保护记录。
隐式锁：当事务需要加锁时，如果这个锁不会发生冲突，InnoDB会跳过加锁环节。（InnoDB实现的一种延迟加锁机制，只有在可能冲突时才会加锁）
隐式锁转换为显式锁：

1. 如果记录之间有间隙锁，为了避免幻读，此时是不能插入数据的。（insert语句会被阻塞，生成插入意向锁，状态为wait）
2. 如果Insert的记录和已有记录存在唯一键冲突，此时也不能插入数据。（插入失败，新事务此时会给已存在的唯一键索引加S锁，聚簇索引：记录锁，二级索引：next-key lock）
3. 唯一键冲突，如果两个事务A、B执行了相同Insert，A未提交：事务B会阻塞。（事务A插入的记录的隐式锁会变成X型的记录锁，事务B获取S锁时会阻塞）

如何避免死锁

4个必要条件：
占有等待，互斥，不可抢占，循环等待。

数据库层面，2个策略打破循环等待，解除死锁（发生后的消除办法）：

1. 设置事务等待锁的超时时间。 innodb_lock_wait_timeout 默认50秒。
2. 开启主动死锁检测。 innodb_deadlock_detect 设置为on，默认开启。

还有：死锁预防，死锁避免，死锁检测，死锁消除。