3、锁

MySQL 有哪些锁？

当会话退出后，会释放所有锁

这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。如果一个写锁在队列中，可能会阻塞队列中的后续操作，导致问题

共享锁（S 锁）满足读读共享，读写互斥。独占锁（X 锁）满足写写互斥、读写互斥。

MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁

全局锁

锁住整个数据库，只读状态

上锁 flush tables with read lock
释放 unlock tables（会话断开，全局锁自动释放）
主要应用于做全库逻辑备份，但是成本很高，可以通过可重复度的MVCC进行备份
备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

表级锁

共享锁不能升级为独占锁，因为如果多个线程都获取了共享锁，都想升级，都需要其他线程释放共享锁，导致死锁
加了共享锁，所有线程都不能写，包括当前线程（上面的原因）

表锁

加锁锁：
- 共享锁：lock tables t_student read;
- 独占锁：lock tables t_student write;
释放锁：
- unlock tables：释放当前会话所有表锁

元数据锁

非显示使用
对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；
MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。
事物执行期间，MDL一直持有

意向锁

加锁前，会加上对应的意向锁

插入、更新、删除加意向独占锁
查询加对应的锁
- 独占查询加意向独占锁
- 共享查询加意向共享锁
目的时为了快速判断表里是否有记录被加锁
- 如果有行共享锁，加上意向共享锁后有想要加行独占锁的就会立刻知道，不需要遍历其他行
- 行独占锁也一样
- 如果加了意向共享锁，再进行表独占锁获取也会阻塞

AUTO-INC 锁

是在执行完插入语句后就会立即释放，而不是事务提交后释放
一个事务在持有 AUTO-INC 锁的过程中，其他的事务如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。
InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。
- 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
  - 当插入操作很大的时候，性能会比较差，其他插入操作都在等待
- 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
  - 当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题。
  - 如果几个多行插入的操作时，可能并不是交替插入，如果binlog记录原始语句，可能插入顺序不一致，可以通过binlog日志设置为row避免
- 当 innodb_autoinc_lock_mode = 1： - 普通 insert 语句，自增锁在申请之后就马上释放； - 类似 insert …… select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

行级锁

InnoDB 引擎支持， MyISAM 引擎不支持。

加锁：
- 共享锁：select ... lock in share mode;不加 lock in share mode 的select无锁
- 独占锁：
  - 查询：select ... for update;
  - 任何类型的修改、删除
释放：
- 提交事务即可

行级锁的类型主要有三类：

Record Lock，记录锁，也就是仅仅把一条记录锁上；
- 区分共享和独占锁
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
- 只存在可重复读级别
- 不区分共享和独占锁，两者没差别，可以都看成共享锁
Next-Key Lock：临键锁，Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
- 因为有记录锁，所以区分共享和独占锁
插入意向锁：如果执行插入操作，插入位置有间隙锁，会生成插入意向锁
- 是一种特殊的间隙锁，不是锁区见，而是锁一个点（但是是不存在记录的点）
- 但是和间隙锁互斥
- 两个事务先通过当前读获取到间隙锁，再进行写操作，会死锁
记录锁锁有记录的部分，间隙锁和插入意向锁锁没有记录的部分

select * from performance_schema.data_locks\G; 查看锁

从上图可以看到，共加了两个锁，分别是：

表锁：X 类型的意向锁；
行锁：X 类型的间隙锁；

这里我们重点关注行锁，图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思，通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：

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

有个业务主要逻辑就是新增订单、修改订单、查询订单等操作。然后因为订单是不能重复的，所以当时在新增订单的时候做了幂等性校验，做法就是在新增订单记录之前，先通过 select ... for update 语句查询订单是否存在，如果不存在才插入订单记录。

如果两个事务同时先查询订单是否存在，再插入记录，会都先获取间隙锁，然后等待对方释放，获取排他锁

Insert 语句是怎么加行级锁的？

Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。

什么是隐式锁？

当事务需要加锁的时，如果这个锁不可能发生冲突，InnoDB 会跳过加锁环节，这种机制称为隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁机制，其特点是只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能。

隐式锁就是在 Insert 过程中不加锁，只有在特殊情况下，才会将隐式锁转换为显式锁，这里我们列举两个场景。

如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的；
如果 Insert 的记录和已有记录存在唯一键冲突，此时也不能插入记录；

所以，在隔离级别是「可重复读」的情况下，如果在插入数据的时候，发生了主键索引冲突，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁。

如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。
当发生超时后，就出现下面这个提示：

开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。
当检测到死锁后，就会出现下面这个提示：

上面这个两种策略是「当有死锁发生时」的避免方式。

我们可以回归业务的角度来预防死锁，对订单做幂等性校验的目的是为了保证不会出现重复的订单，那我们可以直接将 order_no 字段设置为唯一索引列，利用它的唯一性来保证订单表不会出现重复的订单，不过有一点不好的地方就是在我们插入一个已经存在的订单记录时就会抛出异常。

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

MySQL 查询是怎么加锁的？

MySQL 查询是怎么加行级锁的？

基本单位是 next-key lock，由记录锁和间隙锁构成
会以最小成本进行加锁，next-key lock 在一些场景下会退化成记录锁或间隙锁
唯一索引
- 等值查询：
  - 记录存在，加记录锁
  - 记录不存在，加间隙锁（可重复读，读已提交，没有间隙锁）
- 范围查询（条件中的记录，如<5，那么5就是条件记录）
  - 对每一个扫描到的索引加 next-key 锁
  - 大于等于
    - 如果条件记录存在表中，对该记录加记录锁（因为左开右壁闭）
    - 其余都是next-key锁
  - 小于或小于等于：
    - 如果小于，最后的区间加next-key锁
    - 如果小于等于：
      - 条件记录不存在表中，该区间加间隙锁，其余加next-key锁
      - 否则条件记录也加next-key锁
非唯一索引：
- 因为还存在主键索引，所以两个索引加锁（主键索引只有满足条件的记录才会加记录锁）
- 等值查询：
  - 记录存在
    - 会扫描所有的等值记录（非唯一），扫描过程中加next-key锁
    - 对第一个不符合条件的二级索引记录加间隙锁，不符合条件的记录可以修改删除、但是因为不符合条件的记录有间隙锁，不能再插入等值的数据（只要保证下次查询结果不变即可）
    - 对符合条件的记录的主键索引加记录锁
  - 记录不存在
    - 只有第一条不符合记录的二级索引加间隙锁，
    - 没有主键索引加锁（没有符合的）
没有走索引(加了索引，如果优化器决定不走索引，等价于没有加)
- 如果锁定读查询，没有走索引，会全表扫描，全部加上next-key锁，等价于==锁全表==
- update、delete如果查询条件不加索引，也会==锁全表==
- 建议在update这种前，先通过explain进行查看是否会走索引
  - 如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用 force index([index_name]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。

可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式 update 语句必须满足如下条件之一才能执行成功：
使用 where，并且 where 条件中必须有索引列；
使用 limit；
同时使用 where 和 limit，此时 where 条件中可以没有索引列；
delete 语句必须满足以下条件能执行成功：
同时使用 where 和 limit，此时 where 条件中可以没有索引列；

非唯一索引范围查询

非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。

就带大家简单分析一下，事务 A 的这条范围查询语句：

sql

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where age >= 22  for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 10 | 山治      |  22 |
| 20 | 香克斯    |  39 |
+----+-----------+-----+
2 rows in set (0.01 sec)

事务 A 的加锁变化：

最开始要找的第一行是 age = 22，虽然范围查询语句包含等值查询，但是这里不是唯一索引范围查询，所以是不会发生退化锁的现象，因此对该二级索引记录加 next-key 锁，范围是 (21, 22]。同时，对 age = 22 这条记录的主键索引加记录锁，即对 id = 10 这一行记录的主键索引加记录锁。
由于是范围查询，接着继续扫描已经存在的二级索引记录。扫面的第二行是 age = 39 的二级索引记录，于是对该二级索引记录加 next-key 锁，范围是 (22, 39]，同时，对 age = 39 这条记录的主键索引加记录锁，即对 id = 20 这一行记录的主键索引加记录锁。
虽然我们看见表中最后一条二级索引记录是 age = 39 的记录，但是实际在 Innodb 存储引擎中，会用一个特殊的记录来标识最后一条记录，该特殊的记录的名字叫 supremum pseudo-record，所以扫描第二行的时候，也就扫描到了这个特殊记录的时候，会对该二级索引记录加的是范围为 (39, +∞] 的 next-key 锁。
停止查询

可以看到，事务 A 对主键索引和二级索引都加了 X 型的锁：

主键索引（id 列）：
- 在 id = 10 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 10 的这一行记录。
- 在 id = 20 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 20 的这一行记录。
二级索引（age 列）：
- 在 age = 22 这条记录的二级索引上，加了范围为 (21, 22] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 22 的这一些新记录，不过对于是否可以插入 age = 21 和 age = 22 的新记录，还需要看新记录的 id 值，有些情况是可以成功插入的，而一些情况则无法插入，具体哪些情况，我们前面也讲了。
- 在 age = 39 这条记录的二级索引上，加了范围为 (22, 39] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 39 的这一些记录，也无法插入 age 值为 23、24、25、...、38 的这一些新记录。不过对于是否可以插入 age = 22 和 age = 39 的新记录，还需要看新记录的 id 值，有些情况是可以成功插入的，而一些情况则无法插入，具体哪些情况，我们前面也讲了。
- 在特殊的记录（supremum pseudo-record）的二级索引上，加了范围为 (39, +∞] 的 next-key 锁，意味着其他事务无法插入 age 值大于 39 的这些新记录。

在 age >= 22 的范围查询中，明明查询 age = 22 的记录存在并且属于等值查询，为什么不会像唯一索引那样，将 age = 22 记录的二级索引上的 next-key 锁退化为记录锁？

因为 age 字段是非唯一索引，不具有唯一性，所以如果只加记录锁（记录锁无法防止插入，只能防止删除或者修改），就会导致其他事务插入一条 age = 22 的记录，这样前后两次查询的结果集就不相同了，出现了幻读现象。

如果是age>22，只会锁age=39的临键锁和∞
- 因为不用担心插入age=22的数据，虽然age =22 且id >10 的也无法插入，右侧就是临键锁
如果是 age<19，只会锁 age = 19 的临键锁
- 因为不用担心age =19的插入，虽然age =19且id<1 无法插入，右侧就是临键锁
如果是 age <=19 , 会锁 age = 19 和 age = 20的临键锁
- 因为要防止 age = 19 的记录插入， age = 19 的临键锁只能锁 age <=19 且 id <1的部分， age = 19 且 id > 1的部分需要通过 age = 20 的临键锁进行限制

3、锁 ​

MySQL 有哪些锁？ ​

全局锁 ​

表级锁 ​

表锁 ​

元数据锁 ​

意向锁 ​

AUTO-INC 锁 ​

行级锁 ​

Insert 语句是怎么加行级锁的？ ​

如何避免死锁？ ​

MySQL 查询是怎么加锁的？ ​

MySQL 查询是怎么加行级锁的？ ​

非唯一索引范围查询 ​

3、锁