vi系统设计是什么,放心网站推广优化咨询,网站建设与开发学习,百度新闻源网站有哪些目录
一、MySQL中的锁
1.1、全局锁
1.2、表级锁
1.2.1、表锁
1.2.2、元数据锁#xff08;MDL#xff09;
1.2.3、意向锁
1.2.4、AUTO-INC 锁
1.3、行级锁
1.3.2、Gap Lock
1.3.3、Next-Key Lock
1.3.4、插入意向锁
二、MySQL 是怎么加锁的#xff1f;
2.1、为什…目录
一、MySQL中的锁
1.1、全局锁
1.2、表级锁
1.2.1、表锁
1.2.2、元数据锁MDL
1.2.3、意向锁
1.2.4、AUTO-INC 锁
1.3、行级锁
1.3.2、Gap Lock
1.3.3、Next-Key Lock
1.3.4、插入意向锁
二、MySQL 是怎么加锁的
2.1、为什么 SQL 语句会加行级锁
2.2、MySQL是怎么加行级锁的
2.2.1、唯一索引等值查询
2.2.2、唯一索引范围查询
2.2.3、非唯一索引等值查询
2.2.4、非唯一索引范围查询
2.2.5、没有加索引的查询
三、 update 没加索引会锁全表
四、MySQL 死锁了怎么办
4.1、死锁
4.2、Insert 语句是怎么加行级锁的
4.2.1、遇到唯一键冲突
4.3、如何避免死锁呢 一、MySQL中的锁
1.1、全局锁
要使用全局锁就需要执行这条命令
flush tables with read lock
执行之后整个数据库就处于只读的状态了其他线程执行的增删改查等操作都会被阻塞。
使用 unlock table 释放全局锁会话断开也会自动释放全局锁。
全局锁主要应用于做【全库逻辑备份】这样在备份数据库之间不会因为数据或者表结构的更新而出现备份文件的数据与预期的不一样。
如果备份的时候不加锁会出现什么情况呢下面我直接举个
在全库逻辑备份期间我购买了一台劳斯莱斯魅影而购买车的业务逻辑会涉及到多张数据表的更新比如得更新我的账户余额、商品表的库存数......等等.
假设出现这样的顺序先备份了用户表的数据然后有用户发起了购买商品的操作接着再备份商品表的数据
也就是说在备份用户表和商品表之间我买到了劳斯莱斯魅影。
这个情况下备份的结果就是我的账户余额没有扣除反而商品表的库存减少了如果后续继续使用这个备份文件恢复数据库数据的话我的钱没有少反而库存少了等于我白嫖了一台劳斯莱斯魅影现在是幻想时刻。所以在做全库逻辑备份的时候加上全局锁是不是很有必要不然就被我狠狠白嫖了
缺点加上全局锁会导致整个数据库都是只读的状态如果数据很多备份就会花费很多时间会造成业务停滞。
如何避免呢如果数据库支持可重复读的隔离级别那么在备份数据库之前先开启事务创建 Read View 然后整个事务都使用这个 Read View 而且由于 MVCC 的支持备份期间业务仍然可以对数据进行更新操作。备份MySQL数据库的工具是 mysqldump 在使用 mysqldump 时加上 -single-transaction 参数的时候就会在备份数据库之前开启事务MyISAM 这种不支持事务的引擎在备份数据库的时候就要使用全局锁的方法哟~
1.2、表级锁
1.2.1、表锁
表锁除了会限制别的线程的读写之外也会限制本线程接下来的读写操作。
解释一下上句话也就是说如果本线程对表加了【共享表锁】那么本线程接下来要对学生表执行写操作的语句是会被阻塞的其他线程进行写操作也会被阻塞直到锁被释放。
1.2.2、元数据锁MDL
我们不需要显示的使用 MDL因为当我们对数据库进行操作时会自动给这个表加上 MDL
对一张表进行 CURD增删改查操作时加的是 MDL 读锁对一张表做结构变更操作时加的是 MDL 写锁
是为了保证当用户对表执行 CURD 操作时防止其他线程对这个表结构做了变更。
当有线程在执行 select 语句加 MDL 读锁的期间如果有其他线程要更改该表的结构申请 MDL 写锁那么将会被阻塞直到执行完 select 语句释放 MDL 读锁。
相反如果有线程对表结构进行变更加 MDL 写锁的期间如果有其他线程执行了 CURD 操作申请 MDL 读锁那么就会被阻塞直到表结构变更完成释放 MDL 写锁。
突然有一个问题偷袭我MDL 不显示调用那它啥时候释放呢
答MDL 是在事务提交之后才会释放这就是说事务执行期间MDL 是一直持有的。下面举个
首先事务 A 先启动了事务但是一直不提交然后执行一条 select 语句此时就先对该表加上 MDL 读锁。然后线程 B 也执行了同样的 select 语句此时并不会阻塞因为【读读】并不冲突紧接着线程 C 修改了表字段此时由于线程 A 的事务并没有提交也就是 MDL 读锁还在占用着这时 C 线程就无法申请到 MDL 写锁就会被阻塞
在线程 C 阻塞后后续有对该表的 select 语句就都会被阻塞如果此时有大量该表的 select 语句的请求到来就会有大量的线程被阻塞住这是数据库的线程很快就会爆满啦~
为什么 C 线程申请不到 MDL 的写锁会导致后续申请的读锁的查询操作也会被阻塞
答这是因为申请 MDL 锁的操作会形成一个队列队列中写锁获取优先级高于读锁一旦出现 MDL 写锁等待会阻塞后续该表的所有 CURD 操作。所以为了更安全的对表结构进行变更在变更之前先要看看数据库中的长事务是否有事务已经对表加上了 MDL 读锁如果考虑 kill 这个长事务然后再做表结构变更。
1.2.3、意向锁
在使用 InnoDB 引擎里面的表对某些记录加上【共享锁】之前需要先在表级别加上一个【意向共享锁】在使用 InnoDB 引擎的表里对某些记录加上【独占锁】之前需要现在表级别加上一个【意向独占锁】
也就是说在进行【增删改】操作时需要先对表加上【意向独占锁】然后对该记录加独占锁。而普通的 select 是不会加行级锁的普通的 select 语句是利用 MVCC 实现一致性读是无锁的。不过 select 也是可以对记录加共享锁和独占锁的
//先在表上加意向共享锁然后对读取的记录加共享锁
select ... lock in share mode;//先在表上加意向独占锁然后对读取的记录加独占锁
select ... for update;
注意意向共享锁和意向独占锁是表级锁不会和行级的共享锁和独占锁发生冲突而且意向锁之间也不会发生冲突只会和共享表锁和独占表锁发生冲突。
表锁和行锁是 读读共享、读写互斥、写写互斥的
如果没有【意向锁】那么在加【独占行锁】时就需要遍历表里所有记录查看是否有记录存在独占锁效率会很慢。有了【意向锁】由于在对记录加独占锁前会加上表级别的意向独占锁那么在加【独占行锁】时直接查该表是否有意向独占锁如果有就意味着表里已经有记录被加了独占锁这样就不用去遍历表里的记录。
得出意向锁得目的是为了快速判断表里是否有记录被加锁。
1.2.4、AUTO-INC 锁
表中的主键通常都会设置为自增的这是通过对主键字段声明【AUTO-INCREMENT】 属性实现的之后可以再插入数据时可以不指定主键的值数据库会自动给主键赋值递增的值这主要是通过【AUTO-INC】锁实现的。
【AUTO-INC】锁是特殊的表锁机制锁不是在一个事务提交之后才释放的而是在执行完插入语句之后立即释放。在插入数据时会加上一个表级别的【AUTO-INC】锁然后为被【AUTO-INCREMENT】修饰的字段的值是连续递增的值等插入语句执行完成后才会把【AUTO-INC】锁释放掉。
当一个事务在持有【AUTO-INC】锁的过程中其他事物如果要向该表插入语句都会被阻塞从而保证插入数据时被【AUTO-INCREMENT】修饰的字段的值是连续递增的。
问题来啦 ~ 【AUTO-INC】锁在对大量数据进行插入的时候会影响插入性能因为另一个事务中的插入会被阻塞如何解决呢
答在MySQL 5.1.22 版本开始InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样的是在插入数据的时候会被【AUTO-INCREMENT】修饰的字段加上轻量级锁然后给该字段赋值一个自增的值就把这个轻量级锁释放了而不需要等待整个插入语句执行完后才释放锁。
InnoDB 引擎中提供了一个【innnodb_autoinc_lock_mode】的系统变量用来控制选择用 【AUTO-INC】锁还是轻量级的锁。
当【innnodb_autoinc_lock_mode】 0就采用【AUTO-INC】锁语句执结束后才释放锁。当【innnodb_autoinc_lock_mode】 2就采用【轻量级】锁申请自增主键后就释放锁并不需要等语句执行后才释放当【innnodb_autoinc_lock_mode】 1普通 select 语句自增锁在申请之后就马上释放。类似 insert ... select 这样批量插入数据的语句自增锁还是要等语句结束后才被释放
当 innodb_autoinc_lock_mode 2 是性能最高的方式但是当搭配 binlog 日志格式是 statement 一起使用的时候在【主从复制的场景】中会发生数据不一致的问题。
举个
session A 往表 t 中插入了 4 行数据然后创建了一个相同结构的表 t2 然后两个 session 同时执行向表 t2 中插入数据。如果 innodb_autoinc_lock_mode 2意味着【申请自增主键后就释放锁不必等插入语句执行完】。就可能出现如下情况
session B 先插入两个记录111、222然后session A 来申请自增 id 得到 id 3插入了355之后session B 继续执行插入两条记录433、544
可见session B 的 insert 语句生成的 id 不连续。当【主库】发生了这种情况binlog 面对 t2 表的更新只会记录这两个 session 的 insert 语句如果 binlog_format statement记录的语句就是原始语句。记录的顺序要么先记 session A 的 insert 语句要么先记 session B 的 insert 语句。
无论是哪种这个 binlog 拿去【从库】执行这时从库是按【顺序】执行语句的只有当执行完一条 SQL 语句后才会执行下一条 SQL。因此在从库上【不会】发生像主库那样两个 session 【同时】向表 t2 中插入数据的场景。所以在备库上执行了 session B 的 insert 语句生成的结果里面id 都是连续的。此时主从库就发生了数据不一致。
想解决这个问题binlog 日志格式要设置为 row这样在 binlog 里面记录的是主库分配的自增值到备库执行的时候主库的自增值是什么从库的子增值就是什么。所以当 innodb_autoinc_lock_mode 2 时并且 binlog_format row既能提升并发性能又不会出现数据不一致性问题。
1.3、行级锁
InnoDB 引擎是支持行级锁的而 MyISAM 引擎并不支持行级锁。
普通的 select 语句是不会对记录加锁的因为它属于快照读。如果要在查询时对记录加行锁可以使用下面这两个方式这两种查询会加锁的语句称为锁定读。
//对读取的记录加共享锁
select ... lock in share mode;//对读取的记录加独占锁
select ... for update;
上面这两条语句必须在一个事务中因为当事务提交了锁就会被释放所以在使用这两条语句的时候要加上 begin、start transaction 或者 set autocommit 0。
共享锁S锁是 读读共享、读写互斥
独占锁X锁是 读写互斥、写写互斥
行级锁主要有三类
Record Lock记录锁也就是仅仅把一条记录锁上
Gap Lock间隙锁锁定一个范围但是不包含记录本身
Next-Key LockRecord Lock 和 Gap Lock 的组合锁定一个范围并且锁定记录本身
1.3.1、Record Lock
Record Lock 称为记录锁锁住的是一条记录。而且记录锁是有 S 锁共享锁和 X 锁独占锁之分的
当一个事务对一条记录加了 S 型记录锁后其他事物也可以继续对该记录加 S 型记录锁S 与 S 锁兼容但是不可以对该记录加 X 型记录锁S 型与 X 锁不兼容当一个事务对一条记录加了 X 型记录锁后其他事物既不可以对该记录加 S 型记录锁S 型与 X 锁不兼容也不可以对该记录加 X 型记录锁X 型与 X 锁不兼容
1.3.2、Gap Lock
Gap Lock 称为记录锁只存在于可重复读隔离级别目的是为了解决可重复读隔离级别下幻读的现象。 间隙锁虽然也存在 X 型和 S 型但是并没有什么区别间隙锁之间是兼容的即两个事务可以同时持有包含共同间隙范围的间隙锁并不存在互斥关系因为间隙锁并不存在互斥关系因为间隙锁的目的是为了防止插入幻影记录而提出的。
1.3.3、Next-Key Lock
Next-Key-Lock 称为临建锁是 Record Lock Gap Lock 的组合锁定一个范围并且锁定记录本身举个 此时把【汤面】也包含进来了啦~
所以next-key lock 既能保护该记录又能阻止其他事物将新记录插入到被保护记录前面的间隙中。
next-key lock 是包含间隙锁 记录锁的如果一个事务获取了 X 型的 next-key lock 那么另一个事务在获取相同范围的 X 型的 next-key lock 时是会被阻塞的
1.3.4、插入意向锁
一个事务在插入一条记录的时候需要判断插入位置是否已被其他事物加了间隙锁next-key lock 也包含间隙锁。如果有的话插入操作就会发生阻塞直到拥有间隙锁的那个事务提交为止释放间隙锁的时刻在此期间会生成一个插入意向锁表明有事务想在某个区间插入新记录但是现在处于等待状态。
举个 假设事务 A 已经对表加了一个 范围 id 为35间隙锁。
当事务 A 还没有提交的时候事务 B 向该表插入一条 id 4 的新记录这时会判断到插入的位置已经被事务 A 加了间隙锁于是事务 B 会生成一个插入意向锁然后将锁的状态设置为等待状态MySQL 加锁时是先生成锁结构然后设置锁的状态如果锁状态是等待状态并不是意味着事务成功获取到了锁只有当锁状态为正常状态时才代表事务成功获取到了锁此时事务 B 就会发生阻塞直到事务 A 提交了事务。
注意插入意向锁名字虽然有意向锁但是它并不是意向锁它是一种特殊的间隙锁属于行级别锁。
如果说间隙锁锁住的是一个区间那么【插入意向锁】锁住的就是一个点。因而从这个角度来说插入意向锁确实是一种特殊的间隙锁。插入意向锁与间隙锁的另一个非常重要的差别是尽管【插入意向锁】也属于间隙锁但两个事务却不能在同一时间内一个拥有间隙锁另一个拥有该间隙区间内的插入意向锁当然插入意向锁如果不在间隙锁区间内是可以的。
二、MySQL 是怎么加锁的
2.1、为什么 SQL 语句会加行级锁
InnoDB 引擎是支持行级锁的而 MyISAM 引擎并不支持行级锁所以后面的内容都是基于 InnoDB 引擎的。
所以在说 MySQL 是怎么加行级锁的时候其实就是再说 InnoDB 引擎是怎么加行级锁的。
普通的 select 语句是不会对记录加锁的除了串行化隔离级别因为它属于快照读是通过 MVCC多版本并发控制实现的。
如果要在查询时对记录加行级锁可以使用下面两个方式这两种查询会加锁的语句称为锁定读。
//对读取的记录加共享锁S型锁
select ... lock in share mode;//对读取的记录加独占锁X型锁
select ... for update;
上面这两条记录必须在一个事务中因为当事务提交了锁就会被释放所以在使用这两条语句的时候要加上 begin 或者 start transaction 开启事务的语句。
除了上面的两条锁定读语句会加行级锁之外update 和 delete 操作都会加行级锁且锁的类型都是独占锁X型锁。
//对操作的记录加独占锁X型锁
update table ... where id 1;//对操作的记录加独占锁X型锁
delete from table where id 1;
共享锁S锁 读读共享读写互斥独占锁X锁 写写互斥读写互斥
2.2、MySQL是怎么加行级锁的
2.2.1、唯一索引等值查询
当我们用唯一索引进行等值查询的时候查询的记录存不存在加锁的规则也会不同
当查询的记录是【存在】的在索引数上定位到这一条记录后将该记录的索引中的 next-key lock 会退化成【记录锁】。当查询的记录是【不存在】的在索引树找到第一条大于该查询记录的记录后将该记录的索引中的 next-key lock 会退化成【间隙锁】。
PS本篇文章中的【唯一索引】是用【主键索引】作为案例说明的加锁只加在主键索引项上。许多人误认为如果是二级索引的【唯一索引】加锁也是只加在二级索引项上。这个是不对的这里特别说明一下如果是用二级索引不管是不是非唯一索引还是唯一索引进行锁定读查询的时候除了会对二级索引项加行级锁如果是唯一索引的二级索引加锁规则和主键索引的案例相同而且还会对查询到的记录的主键索引项加【记录锁】。
①、记录存在的情况
假设事务 A 执行了一条等值查询语句查询的记录是【存在】于表中的。那么事务 A 会为 id 1 的这条记录就会加上 X 型的记录锁。 接下来如果有其他事物对 id 1 的记录进行更新或者删除操作的话这些操作都会被阻塞因为更新或者删除操作也会对记录加 X 型的记录锁而 X 锁和 X 锁之间是互斥关系。
加锁的对象是针对索引因为这里查询语句扫描的 B 树是聚簇索引树即主键索引树所以是对主键索引加锁。将对应记录的主键索引加记录锁后就意味着其他事物无法对该记录进行更新和删除操作了。
问题来啦 ~ 为什么唯一索引等值查询并且查询记录存在的场景下该记录的索引中的 next-key lock 会退化成记录锁
答在唯一索引等值查询记录存在的场景下仅靠记录锁也能避免幻读的问题。 由于主键具有唯一性所以其他事务插入 id 1 的时候会因为主键冲突导致无法插入 id 1 的新记录。这样事务 A 在多次查询 id 1 的记录时候不会出现前后两次查询的结果集不同也就避免了幻读的问题。 由于对 id 1 加了记录锁其他事物无法删除该记录这样事务 A 在多次查询 id 1 的记录时候不会出现前后两次查询的结果集不同也就避免了幻读的可能。
②、记录不存在的情况
假设事务 A 执行了这条等值查询语句查询的记录是【不存在】于表中的。
select * from user where id 2 for update; 此时事务 A 在 id 5 记录的主键索引上加的是间隙锁锁住的范围是15
接下来如果有事务插入 id 值为 234 这一些记录的话这些插入语句都会发生阻塞。如果其他事务插入的 id 1 或者 id 5 的记录话并不会报主键冲突的错误因为已经存在 id 1 和 id 5 的记录了。
问题来了啦 ~ 为什么唯一索引等值查询并且查询记录【不存在】的场景下在索引树找到第一条大于该记录后要将该记录的索引中的 next-key lock 退化成【间隙锁】
答原因就是在唯一索引等值查询并且查询记录不存在的场景下仅靠间隙锁就能避免幻读的问题
那为啥 id 5 记录上的主键索引的锁不可以是 next-key lock如果是 next-key lock就意味着其他事物无法删除 id 5 这条记录但是这次的案例是查询 id 2 的记录只要保证前后两次查询 id 2 的结果集相同就能避免幻读的问题了所以即使 id 5 被删除也不会有什么影响那就没必要加 next-key lock 因此只需要在 id 5 加间隙锁避免其他事务插入 id 2 的新记录就行
为什么不可以针对不存在的记录加记录锁锁是加在索引上的而这个场景下查询的记录是不存在的自然就没办法锁住这条不存在的记录
2.2.2、唯一索引范围查询
范围查询和等值查询的加锁规则是不同的。当唯一索引进行范围查询时会对每一个扫描到的索引加 next-key lock然后如果遇到以下情况会退化为记录锁或者间隙锁PS由于可能会对一行加多种不同的锁这里的 “退化” 是指加的多种的锁中等级最低的锁等级顺序如下next-key lock gap lock record lock
情况1针对【大于等于】的范围查询因为存在等值查询的条件那么如果等值查询的记录是存在于表中那么该纪录的索引中的 next-key 锁会退化成记录锁。
情况2针对【小于或者小于等于】的范围查询要看条件值得记录是否存在于表中 当条件值的记录不在表中那么不管是【小于】还是【小于等于】条件的范围查询扫描 到终止范围查询的记录时该记录的索引的 next-key lock 会退化成间隙锁其他扫描到的 记录都是在这些记录的索引上加 next-key lock。 当条件值的记录在表中如果是【小于】条件的范围查询扫描到终止范围查询的记录 时该记录的索引的 next-key lock 会退化成间隙锁其他扫描到的记录都是在这些记录 的索引上加next-key lock 如果【小于等于】条件的范围查询扫描到终止范围查询的记 录时该记录的索引 next-key lock 不会退化成间隙锁。其他扫描到的记录都是在这些记 录的索引上加 next-key lock。
①、情况1中针对【大于】的范围查询情况
假设事务 A 执行了以下语句
select * from user where id 15 for update;
事务 A 加锁变化如下
最开始要找的第一行是 id 20由于查询该记录不是一个等值查询不是大于等于条件查询所以对该主键索引加的是范围为 (1520] 的 next-key 锁由于是范围查找就会继续往后找存在的记录虽然我们看见表中最后一条记录是 id 20 的记录但是实际在 InnoDB 引擎中会用一个特殊的记录来标识最后一条记录该特殊的记录的名字叫【supremum pseudo-record】所以扫描到第二行的时候也就扫描到了这个特殊的记录的时候会对该主键索引加的是范围为 (20无穷] 的 next-key lock。停止扫描 由上图可得
在 id 20 这条记录的主键索引上加了范围为1520] 的 next-key lock 意味着其他事物即无法更新或者删除 id 20 的记录同时无法插入 id 值为 16171819 的这一些新记录。
在特殊记录supremum pseudo-record的主键索引上加了范围为20无穷] 的 next-key lock 意味着其他事物无法插入 id 值大于 20 的这一些新记录。
②、情况1中针对【大于等于】的范围查询情况
假设事务 A 执行了以下语句
select * from user where id 15 for update;
事务 A 加锁变化过程如下
最开始要找的第一行是 id 15 由于查询该记录是一个等值查询等于 15所以该主键索引的next-key lock 会退化为记录锁也就是仅锁住 id 15 这一行记录。由于是范围查找就会继续往后找存在的记录扫描到的第二行是 id 20 于是对该主键索引加的是范围为 (1520] 的 next-key lock。接着扫描到第三行的时候扫描到了特殊记录supremum pseudo-record于是对该主键索引加的范围为 (20无穷] 的 next-key lock 。停止扫描。 由上图可知
在 id 15 这条记录的主键索引上加了记录锁范围是 id 15 这一行记录意味着其他事物无法更新或者删除 id 15 的这一条记录在 id 20 这条记录的主键索引上加了 next-key lock 范围是1520] 。意味着其他事物既无法删除 id 20 的这条记录同时无法插入 id 值为 16、17、18、19 的这一些新记录。在特殊记录supremum pseudo-record的主键索引上加了 next-key lock范围是 (20无穷]。意味着其他事物无法插入 id 值大于 20 的这一些新记录。
情况2中针对【小于或者小于等于】的范围查询情况
①、针对【小于】的范围查询时查询条件值得记录【不存在】表中的情况。
假设事务 A 执行了以下语句
select * from user where id 6 for update;
事务 A 加锁变化过程如下
最开始要找的第一行是 id 1于是对该主键索引加的是范围为-无穷1] 的 next-key lock 由于是范围查找就会继续往后找存在的记录扫描到的第二行是 id 5所以对该主键索引加的是范围为15] 的 next-key lock由于扫描到的第二行记录id 5满足 id 6 条件而且也没有达到终止扫描的条件接着会继续扫描扫描到的第三行是 id 10该记录不满足 id 6 条件的记录所以 id 10 这一行记录的锁会退化成间隙锁于是对该主键索引加的是范围为510的间隙锁由于扫描到的第三行记录id 10不满足 id 6 条件达到了终止扫描的条件于是停止扫描 由上图可知
在 id 1 这条记录的主键索引上加了范围为-无穷1] 的 next-key lock 意味着其他事物即无法更新或者删除 id 1 的这一条记录同时也无法插入 id 值小于 1 的这一些新记录在 id 5 这一条记录的主键索引上加了范围为15] 的 next-key lock 意味着其他事物既无法更新或者删除 id 5 的这一条记录同时也无法插入 id 值为 2、3、4 的这一些新记录在 id 10 这条记录的主键索引上加了范围为510的间隙锁意味着其他事物无法插入 id 值为 6789 的这一些新记录
因此针对【小于或者小于等于】的唯一索引范围查询如果条件值得记录不在表中那么不管是【小于】还是【小于等于】的范围查询扫描到终止范围查询的记录时该记录中索引的 next-key lock 会退化成间隙锁其他扫描的记录则是在这些记录上加 next-key lock
②、针对【小于等于】的范围查询时查询条件值得记录【存在】表中得情况
假设事务 A 执行了这条范围查询语句
select * from user where id 5 for update;
事务 A 加锁变化过程如下
最开始要找的第一行是 id 1于是对该记录加的是范围为-无穷1] 的 next-key lock 由于是范围查找就会继续往后找存在的记录扫描到的第二行是 id 5 于是对该记录加的是范围为15] 的 next-key lock由于主键索引具有唯一性不会存在两个 id 5 的记录所以不会再继续扫描于是停止扫描 由上图可知
在 id 1 这条记录的主键索引上加了范围为-无穷1] 的 next-key lock 意味着其他事物既无法更新或者删除 id 1 的这一条记录同时也无法插入 id 小于 1 的这些新记录在 id 5 这条记录的主键索引上加了范围为15] 的 next-key lock 意味着其他事物既无法更新或者删除 id 5 的这一条记录同时也无法插入 id 值为 234 的这一些新记录
③、针对【小于】的范围查询且查询条件值得记录id 5存在于表中的情况
假设事务 A 执行了以下语句
select * from user where id 5 for update;
事务 A 加锁变化过程如下
最开始要找的第一行是 id 1于是对该记录加的是范围为-无穷1] 的 next-key lock
由于是范围查找就会继续往后找存在的记录扫描到的第二行是 id 5 该记录是第一条不满足 id 5 条件的记录于是该记录的锁会退化成间隙锁锁的范围是15
由于找到了第一条不满足 id 5 条件的记录于是停止扫描 由上图可知
在 id 1 这条记录的主键索引上加了范围为-无穷1] 的 next-key lock 意味着其他事物无法更新或者删除 id 1 的这一条记录同时也无法插入 id 小于 1 的这一些新记录在 id 5 这条记录的主键索引上加了范围为15的间隙锁意味着其他事务无法插入 id 值为 2、3、4 的这一些新记录
2.2.3、非唯一索引等值查询
当我们用非唯一索引进行等值查询的时候因为存在两个索引一个是主键索引一个是非唯一索引二级索引所以在加锁时同时会对这两个索引都会加锁但是对主键索引加锁的时候只有满足查询条件的记录才会对它们的主键索引加锁。
针对非唯一索引等值查询时查询的记录存不存在加锁的规则也会不同
当查询的记录【存在】时由于不是唯一索引所以肯定存在索引值相同的记录于是非唯一索引等值查询的过程是一个扫描的过程直到扫描到第一个不符合条件的二级索引记录就停止扫描然后在扫描的过程中对扫描到的二级索引记录加的是 next-key lock 而对于第一个不符合条件的二级索引记录该二级索引的 next-key lock 会退化成间隙锁。同时在符合查询条件的记录的主键索引上加记录锁。
当查询到的记录【不存在】时扫描到的第一条不符合条件的二级索引记录该二级索引的 next-key lock 会退化成间隙锁因为不存在满足查询条件的记录所以不会对主键索引加锁。
①、针对非唯一索引等值查询时查询的值不存在的情况
假设事务 A 对非唯一索引age进行了等值查询且表中不存在 age 25 的记录
select * from user where age 25 for update;
事务 A 加锁变化过程如下
定位到第一条不符合查询条件的二级索引记录即扫描到 age 39 于是该二级索引的 next-key lock 就会退化成间隙锁范围是2239停止查询 由上图可知
事务 A 在 age 39 记录的二级索引上加了范围为2239的 X 型间隙锁。此时如果有其他事物插入了 age 值为 23242526.... 38 这些新记录那么这些插入语句都会发生阻塞。不过对于插入 age 39 记录的语句在一些情况是可以成功插入的而一些情况就无法成功插入。
汤圆有个疑惑什么情况下插入语句会发生阻塞呢
插入语句在插入一条记录之前 需要先定位到该记录在 B Tree 的位置如果插入的位置的下一条记录的索引上有间隙锁才会发生阻塞。
PS二级索引是如何存放记录的呢 答二级索引树是按照二级索引值age 列按顺序存放的在相同的二级索引值情况下再按主键 id 的顺序存放。
问题又来啦~ 当有一个事务持有二级索引的间隙锁2239时什么情况下可以让其他事物的插入 age 22 或者 age 39 这条记录的语句成功什么情况下插入 age 22 或者 age 39 记录时的语句会被阻塞
插入 age 22 记录的成功和失败的情况如下
成功当其他事务插入一条 age 22id 3 的记录的时候在二级索引树上定位到插入的位置而该位置的下一条是 id 10age 22 的记录该记录的二级索引上没有间隙锁所以这条插入语句可以被执行。失败当其他事务插入一条 age 22id 12 的记录的时候在二级索引树上定位到插入的位置而该位置的下一条是 id 20age 39 的记录正好是该记录的二级索引上有间隙锁所以这条插入语句就会被阻塞。
插入 age 39 记录的成功和失败的情况如下
成功当事务插入一条 age 39id 21 的记录的时候在二级索引树上定位到插入的位置而该位置的下一条记录不存在也就没有间隙锁了所以这条插入语句可以插入成功。失败当其他事务插入一条 age 39id 3 的记录的时候在二级索引树上定位到插入的位置而该位置的下一条是 age 39id 20 的记录正好该记录的二级索引上有间隙锁所以这条插入语句会被阻塞无法插入成功。
所以当有一个事务持有二级索引的间隙锁2239时插入 age 22 或者 age 39 记录的语句是否可以执行成功关键还要考虑插入记录的主键值因为【二级索引值age列主键值id列】才可以确定插入的位置确定了插入的位置后就要看插入的位置的下一条记录是否有间隙锁如果有间隙锁就会发生阻塞如果没有间隙锁则可以成功插入。
②、针对非唯一索引等值查询时查询的值存在的情况
假设事务 A 对非唯一索引age进行了等值查询且表中存在 age 22 的记录。执行以下语句
select * from user where age 22 for update;
事务 A 加锁变化过程如下
由于不是唯一索引所以肯定存在值相同的记录于是非唯一索引等值查询的过程是一个扫描的过程最开始要找的第一行是 age 22 于是对该二级索引加上范围为2122] 的 next-key lock 同时因为 age 22 符合查询条件于是对 age 22 的记录的主键索引加上记录锁即对 id 10 这一行加记录锁。接着继续扫描扫描到的第二行是 age 39 该记录是第一个不符合条件的二级索引记录所以该二级索引的 next-key lock 会退化成间隙锁范围为2239停止查询 主键索引在 id 这条记录的主键索引上加了记录锁意味着其他事务无法更新或者删除 id 10 的这一行记录。
二级索引
在 age 22 这条记录的二级索引上加了范围为2122] 的 next-key lock 意味着其他事物无法更新或者删除 age 22 的这一些新记录不过对于插入 age 20 和 age 21 新记录的语句在一些情况下是可以成功插入的而一些情况则无法插入成功。在 age 39 这条记录的二级索引上加了范围2239的间隙锁。意味着其他事物无法插入 age 值为 2324...38 的这一些新记录。不过对于插入 age 22 和 age 39 记录的语句在一些情况可以插入成功而一些情况无法插入成功。
在 age 22 这条记录的二级索引上加了范围为2122] 的 next-key lock 意味着其他事务无法删除 age 22 的这一些新记录针对是否可以插入 age 21 和 age 22 的新记录分析
是否可以插入 age 21 的新记录还要看插入的新记录的 id 值如果插入 age 21 新记录的 id 值小于 5 那么就可以插入成功因为此时插入的位置的下一条记录是 id 5age 21 的记录该记录的二级索引上没有间隙锁。如果插入 age 21 新记录的 id 值大于 5那么就无法插入成功因为此时插入的下一条记录是 id 10age 22 的记录该记录的二级索引上有间隙锁。是否可以插入 age 22 的新记录还要看插入的新记录的 id 值其他事物插入 age 值为 22 的新记录时如果插入的新记录的 id 值小于 10 那么插入语句会发生阻塞如果插入的新记录的 id 值大于 10还要看该新记录插入的位置的下一条记录是否有间隙锁如果没有间隙锁则可以插入成功如果有间隙锁则无法插入成功。
一个问题呼之欲出为什么在这个案例中需要给二级索引加范围2239的间隙锁
宝子们先想一想加锁最初是为了什么呢是为了避免幻读现象的发生呀~在非唯一索引上加了范围为2122] 的 next-key lock 是无法完全锁住 age 22 新记录的插入因为对于是否可以插入 age 22 的新记录值还需要看插入的新记录的 id 值其他事物插入 age 22 的新记录时如果插入的新纪录的 id 值小于 10那么插入语句会发生阻塞如果插入的新记录的 id 值大于 10则可以插入成功
也就是说只有在二级索引上加发范围为2122] 的 next-key lock 其他事物是有可能插入 age 值为 22 的新记录的比如插入一个 age 22id 12 的新记录那么如果事务 A 再一次查询 age 22 的记录的时候前后两次查询 age 22 的结果集就不一样了此时就会发生幻读现象。
那么当在 age 39 这条记录的二级索引上加了范围为2239的间隙锁后其他事务是无法插入一个 age 22id 12 的新记录因为当其他事务插入一条 age 22id 12 的新记录时在二级索引定位到插入的位置而该位置的下一条是 id 20age 39 的记录正好该记录的二级索引上有间隙锁所以这条插入语句会被阻塞无法插入成功这样就可以避免幻读现象的发生。
2.2.4、非唯一索引范围查询
非唯一索引和主键索引的范围查询的加锁也有所不同不同之处在于非唯一索引范围查询索引的 next-key lock 不会有退化为间隙锁和记录锁的情况也就是非唯一索引进行范围查询的时对二级索引记录加锁都是加 next-key lock。
假设事务 A 执行下面这条查询语句
select * from user where age 22 for update;
事务 A 的加锁变化
最开始要找的第一行是 age 22虽然范围查询语句包含等值查询但是这里不是唯一索引范围查询所以是不会发生退化锁的现象因此对该二级索引记录加 next-key lock 范围是2122] 。同时对 age 22 这条记录的主键索引加记录锁即对 id 10 这一行记录的主键索引加记录锁。由于是范围查询接着继续扫描已经存在的二级索引记录扫描的第二行是 age 39 的二级索引记录于是对该二级索引记录加 next-key lock 范围是2239] 同时对 age 39 这条记录的主键索引加记录锁即对 id 20 这一行记录的主键索引加记录锁。虽然我们看到表中最后一条二级索引记录是 age 39 的记录但是实际在 InnoDB 存储引擎中会用一个特殊的记录来标识最后一条记录该特殊记录【supremum pseudo-record】所以扫描第二行的时候也就扫描到了这个特殊记录的时候会对该二级索引记录加的是范围为39无穷] 的 next-key lock。停止查询 主键索引id 列
在 id 10 这条记录的主键索引上加了记录锁意味着其他事务无法更新或者删除 id 10 的这一行记录在 id 20 这条记录的主键索引上加了记录锁意味着其他事务无法更新或者删除 id 20 的这一行记录
二级索引age 列
在 age 22 这条记录的二级索引上加了范围为2122] 的 next-key lock意味着其他事务无法更新或者删除 age 22 的这一些新记录不过对于是否可以插入 age 21 和 age 22 的新记录还需要看新记录的 id 值有些情况是可以成功插入的而一些情况则无法插入如上文所述在 age 39 这条记录的二级索引上加了范围为2239] 的 next-key lock 意味着其他事务无法更新或者删除 age 39 的这一些记录也无法插入 age 值为 232425...38 的这一些新记录。不过对于是否可以插入 age 22 和 age 39 的新记录还需要看新记录的 id 值有些情况是可以成功插入的而一些情况无法插入。在特殊记录【supremum pseudo-record】的二级索引上加了范围为39无穷] 的 next-key lock意味着其他事务无法插入 age 值大于 39 的这些新记录
这里又有一个问题在 age 22 的范围中 明明查询 age 22 的记录存在并且属于等值查询为什么不会像唯一索引那样将 age 22 记录的二级索引上的 next-key lock 退化成记录锁
因为 age 字段是非唯一索引不具有唯一性所以如果只加记录锁记录锁无法防止插入只能防止删除或者修改就会导致其他事务插入一条 age 22 的记录这样前后两次的查询的结果集就不同了出线了幻读现象。
2.2.5、没有加索引的查询
前面的案例我们的查询语句都有使用索引查询也就是查询记录的时候是通过索引扫描的方式查询的然后对扫描出来的记录进行加锁。
如果锁定读查询语句没有使用索引列作为查询条件或者查询语句没有走索引查询导致扫描是全表扫描。那么每一条记录的索引上都会加 next-key 锁这样就相当于锁住的全表这时如果其他事务对该表进行增、删、改操作的时候都会被阻塞。
不只是锁定读查询语句不加索引才会导致这种情况update 和 delete 语句如果查询条件不加索引那么由于扫描的方式是全表扫描于是就会对每一条记录的索引上都会加 next-key 锁这样就相当于锁住的全表。
因此在线上在执行 update、delete、select ... for update 等具有加锁性质的语句一定要检查语句是否走了索引如果是全表扫描的话会对每一个索引加 next-key 锁相当于把整个表锁住了这是挺严重的问题。
三、 update 没加索引会锁全表
当我们执行 update 语句时实际是会对记录加独占锁的如果其他事务对持有独占锁的记录进行修改时会被阻塞的。另外这个锁并不是执行完 update 语句就会释放的而是会等事务结束时才会释放。
在 update 语句的 where 条件 没有使用索引就会全表扫描于是就会对所有记录加上 next-key lock 相当于把整个表都锁住了而不是使用了表锁。那 update 语句的 where 带上索引就能避免全表记录扫描了吗并不是还是得看这条语句在执行过程中优化器最终选择的是索引扫描还是全表扫描如果走了全表扫描就会对全表的记录加锁了。
如何解决呢
可以将 MySQL 里的 sql_safe_updates 参数设置为 1 开启安全更新模式。当设置为 1 时 update 语句必须满足以下条件才能执行成功
使用 where 并且 where 条件中必须有索引列使用 limit 同时使用 where 和 limit 此时 where 条件中可以没有索引列
delete 语句必须满足以下条件才能执行成功
同时使用 where 和 limit 此时 where 条件中可以没有索引列
如果 where 条件带上了索引列但是优化器最终扫描选择的是全表而不是索引的话我们可以用 force index( [ index_name ] ) 可以告诉优化器使用哪个索引以此避免有几率锁全表带来的隐患。
四、MySQL 记录锁 间隙锁可以防止删除操作而导致的幻读吗
T1 时间执行的结果有 5 条记录而 T2 时间执行结果有 6 条记录那就产生了幻读的问题T1 时间执行的结果有 5 条记录而 T2 时间执行结果有 4 条记录也是产生了幻读的问题
当我们使用 updatedeleteselect ... for update 等具有加锁性质的语句时如果没走索引就会导致全表扫描会将整个表都锁住。此时的更新删除等操作都不能进行所以可防止删除操作导致的幻读。
四、MySQL 死锁了怎么办
4.1、死锁
先举一个死锁的
此时表中已经有 6 条数据
主键索引为 id 二级索引为 order_no
id order_no create_date
1 1001 2021
2 1002 2021
3 1003 2021
4 1004 2021
5 1005 2021
6 1006 2021 事务 A 在执行第一条语句的时候在二级索引上加的是 X 型的 next-key lock锁范围是1006无穷] 此时如果此时事务 B 直接执行第二条语句就会被阻塞。因为插入意向锁和间隙锁是冲突的insert 语句会先加一个插入意向锁判断插入的位置有没有间隙锁所以当其他事务持有该间隙的间隙锁时需要等待其他事物释放之后才能获取意向锁。但是间隙锁与间隙锁之间是兼容的所以两个事务中的 select ... for update 语句不会影响而是各自加了一个间隙锁。在事务 A 和 B 都执行完 select ... for update 语句之后接下来的插入操作为了获取到插入意向锁都在等待对方事务的间隙锁释放于是造成了循环等待导致死锁。
为什么间隙锁之间是兼容的呢
间隙锁的意义只在于阻止区间被插入因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁共享和排它的间隙锁是没有区别的之间不互相冲突且功能相同两个事务可以同时包含共同间隙的间隙锁。
共同间隙
两个间隙锁的间隙区间完全一样一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子区
注意next-key lock 是包含间隙锁 记录锁的如果一个事务获取了 X 型的 next-key lock那么另外一个事务在获取相同范围的 X 型的 next-key lock 时是会被阻塞的。这就奇怪了上文不是说锁住的区域不都是1006无穷] 的 next-key lock 吗为什么都能获取呢听我来说虽然相同范围的间隙锁是多个事务相互兼容的但对于记录锁我们还是要考虑 X 型和 S 型的关系。X 型和 X 型的记录锁是冲突的但是对于范围为1006无穷] 的 next-key lock 两个事务是可以同时持有的不会冲突。因为 无穷 不是一个真实的记录不需要考虑 X 型与 S 型的关系。
4.2、Insert 语句是怎么加行级锁的
insert 语句在正常执行时是不会生成锁结构的它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。
什么是隐式锁呢
当事务需要加锁的时候如果这个锁不可能发生冲突InnoDB 会跳过加锁环节这种机制称为隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁机制其特点是只有在可能发生冲突时才加锁从而减少了锁的数量提高了系统整体性能。
隐式锁就是在 insert 过程中不加锁只有在特殊情况下才会将隐式锁转换为显示锁下面用两个场景分别举
如果记录之间加有间隙锁为了避免幻读此时是不能插入记录的这个就不举了前面讲过了很明显会上锁然后阻塞如果 insert 语句和已有的记录存在唯一键冲突此时也不能插入记录
4.2.1、遇到唯一键冲突
如果在插入新记录时插入了一个与【已有记录的主键或者唯一二级索引列值相同】的记录不过可以有多条记录的唯一二级索引列的值同时为 NULL 这里不考虑此种情况此时插入就会失败然后对于这条记录加上 S 型锁。
如果主键索引重复插入新数据的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁如果唯一二级索引重复插入新数据的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key lock
接下来分析两个事务执行过程中执行了相同的 insert 语句的场景。
表中有如下数据order_no 为唯一二级索引
id order_no create_date
1 1001 2001
2 1002 2001
3 1003 2001
4 1004 2001
5 1005 2001 两个事务加锁的过程
事务 A 先插入 order_no 为 1006 的记录可以插入成功此时对应的唯一二级索引记录被【隐式锁】保护此时还没有实际的锁结构
接着事务 B 也插入 order_no 为 1006 的记录由于事务 A 已经插入 order_no 值为 1006 的记录所以事务 B 在插入二级索引记录时会遇到重复的唯一二级索引列值此时事务 B 想获取一个 S 型 next-key lock 但是事务 A 并未提交事务 A 插入的 order_no 值为 1006 的记录上的【隐式锁】会变【显示锁】且锁的类型为 X 型的记录锁所以事务 B 向获取 S 型 next-key lock 时会遇到锁冲突事务 B 进入阻塞状态。
4.3、如何避免死锁呢
死锁产生的条件循环等待占有并等待不可剥夺互斥四大条件缺一不可
在数据库层面中有两种策略通过【打破循环等待条件】来解除死锁状态
设置事务等待锁的超时时间当一个事务等待时间超过该值后就对这个事务进行回滚于是锁就释放了另一个事务就可以执行了。其中在 InnoDB 中参数【innodb_lock_wait_timeout】是用来设置超时时间的默认值为 50 s
主动开启死锁检测主动死锁检测在发现死锁后主动回滚死锁链条中的某一个事务让其他事物得以继续执行。将参数【innodb_deadlock_detect】设置为 on代表开启这个逻辑默认是开启的。
