十四. MySQL 锁相关

已于 2023-04-25 11:32:40 修改
阅读量1k 收藏
点赞数
分类专栏： mysql 文章标签： mysql 数据库 java
于 2023-03-31 14:09:14 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_29799655/article/details/127906371
版权

目录

一. MySQL 锁基础

  1. MySQL 使用InnoDB存储引擎时,默认使用可重复读隔离级别,基于可重复读隔离级别使用MVCC解决了脏读,不可重复读,一定程度上解决了幻读问题, 那么真正是如何解决幻读的,或者说怎么保证数据安全的,引出了锁
  2. 或者说在快照读时基于MVCC可以读取undo版本日志中的历史版本,但是一些操作需要我们读取最新的版本,所以提出了锁
  1. 在不加锁执行读取操作时使用MVCC又称为快照度,一致性读
  2. 加锁读取时称为LBCC锁定读或当前读,LBCC(Lock-Based Concurreny Control)基于锁的并发控制
  1. MySQL不同存储引擎使用的锁不同
  1. MyISAM时采用的表锁,表锁下又细分为读锁,写锁
  2. InnoDB时根据是否命中索引添加的是行锁间隙锁,如果没命中索引则使用表锁

Mysql 锁分类

  1. 在操作角度分为: 读锁,写锁

读锁: 又叫共享锁,针对同一份数据,多个读操作可以同时进行互不影响
写锁: 又叫排它锁,在当前操作没完成之前,会阻断其它的读锁和写锁

  1. 在锁粒度角度分为:表锁,行锁,页锁(简单理解即可)

表锁(偏读锁,是不是可以理解为使用表锁是通常使用的是表锁中的读锁): 偏向MyISAM存储引擎,优点是:开销小, 加锁快, 无死锁, 锁粒度大,发生锁冲突竞争的概率高,并发度低(整个表都锁的原因)
行锁(偏写锁): 偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁现象,锁粒度小,发生死锁概率低,并发度搞

  1. 在锁模式上又分为:
  1. 共享锁 Shared Locks
  2. 排它锁 Exclusive Locks
  3. 意向锁 Intention Locks: 意向锁又分为,意向共享锁Intention Shared Locks,意向排它锁 Intention Exclusive Locks
  1. 在锁算法上又分为
  1. 记录锁 Record Locks
  2. 间隙锁 Gap Locks
  3. 临键锁 Next Key Locks

二. InnoDB 下的锁

  1. 首先在使用InnoDB存储引擎时,如果对锁进行细分,内部包含
  1. 共享锁又叫乐观锁
  2. 互斥锁又叫排它锁
  3. 意向锁
  4. 记录锁
  5. 间隙锁
  6. 记录锁+间隙锁的临键锁Next-key Locks
  7. 插入意向锁
  8. 自增锁
  9. 空间索引预测锁

增删改查操作时底层的加锁处理

  1. InnoDB对于普通select查询不加锁,基于MVCC机制解决脏读,不可重复读,一定程度上解决幻读问题
  2. 手动针对select语句加锁示例：

共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X) ：SELECT * FROM table_name WHERE … FOR UPDATE

  1. 对于UPDATE,DELETE和INSERT操作时,不考虑索引是否生效的情况下,可以概括的说为会给涉及到的数据添加X排它锁,X排它锁,因为索引失效时会锁表,并且直接执行insert操作,或者底层执行insert操作时特殊情况下会添加隐式锁,下面有分析
  2. 在delete操作时会添加X排它锁
  3. insert操作时一般是不加锁的,特殊情况下为了保证事物的隔离性会添加隐式锁(下面会讲解)
  4. update操作时要考虑更新前后数据的大小与是否更新主键等情况
  1. 如果更新前后数据大小相同,在mysql存储时可以理解为原地更新,此时添加的是X排它锁
  2. 如果更新后的数据大于更新前的,mysql底层会先删除就记录(delete_flag标记为删除状态),然后插入一条新记录,删除阶段添加的是X排它锁,插入新记录时跟insert操作相同使用隐式锁
  3. 更新主键时可以理解为组合了delete操作与insert操作,delete操作添加X排它锁,insert操作添加隐式锁

表级锁

1. 意向锁

  1. 首先意向锁IntentionLock,又分为意向共享锁IS, 与意向排它锁IX 是表锁
  2. 我理解的意向锁,在上层使用角度看并不是真实加锁,而是在表级加了一个锁标识,是为了提高加锁效率提出的意向锁
  3. 在没有意向锁时,假设事务A想获取某个表的表锁的时,就需要对该表下的每一行记录进行遍历,查看是否有其它事务进行了锁的获取,如果存在排它锁,则需要等待这些锁释放后才能获取,遍历成本较大
  4. 提出意向锁后,mysql底层在加锁时,如果加排它锁,会在表级别上添加一个意向排它锁,如果加共享锁首先会在表级别上添加意向共享锁,当下一个事物需要获取锁时,通过添加的意向锁标识就可以判断能不能获取到锁,不需要遍历判断
  5. 并且在添加意向锁时,当发现获取的资源已经被锁,需要判断当前加的是共享锁还是排它锁,被锁定的使用的是共享锁还是排它锁,如果两个都是共享锁,可以在表上面再次添加一个意向共享锁,意向共享锁可以同时并存多个,意向排他锁同时只能有一个存在
  6. InnoDB四种锁定模式下：共享锁S, 排他锁X, 意向共享锁IS, 意向排他锁IX之间的冲突关系
    在这里插入图片描述
  7. 注意意向锁不能手动添加是InnoDB自动加的
  8. 怎么解释意向锁的表锁行锁共存: 根据2,3,意向锁相当于一个锁标识,结合锁冲突,去解释(有些博客中说意向锁就是为了解决表锁行锁共存所产生的)

2. AUTO-INC锁

  1. 给某个列加入自增长属性,例如自增主键,在并发操作时这个自增是怎么保证线程安全的,就是通过这个AUTO-INC锁加锁保证的,这个AUTO-INC也算是一个表级锁
  2. 在老版本时以自增主键为例,在插入数据时会在表级别上添加AUTO-INC锁,获取自增主键数据插入成功后再释放锁,可以认为锁定的范围是整个插入流程,进而保证自增主键的安全
  3. 在后续版本中考虑AUTO-INC在加锁与释放锁时的性能问题,进行了优化提出了轻量级锁,通过innodb_autoinc_lock_mode来设置,提供了3个模式(默认是consecutive 1)
  1. traditional: 设置为 0时,在该模式下,insert语句在开始时都会获得一个表锁autoinc_lock.该锁会一直持有到insert语句执行结束才会被释放,主从复制时,基于这种模式从库主库生成的主键id相同且有序
  2. consecutive: 设置为1时, 该模式下,对于简单的插入语句如果能够获取到插入数据条数,insert语句在开始时会获得一个表锁autoinc_lock,批量获取自增值,然后释放锁,不会等到数据插入完毕再释放,而对于复杂插入例如无法得到插入数据的条数,仍会加锁到数据插入完毕再释放,这种模式下获取的自增值是有序的,主从复制表现跟traditional下一样
  3. interleaved: 设置为2时, 该模式下,会评估的去批量获取自增值,对于能够判断出插入数据量的简单insert语句能保证ID是连续的,但是复杂的insert的ID则可能有断续出现,主从同步时,从库基于该模式可能造成id与主库不同切断续
id不连续对主从同步的影响
  1. mysql主从同步架构中,有两种同步模式:
  1. statement level基于SQL语句的复制
  2. row level基于一条记录的复制
  1. statement level与 row level 优缺点: statement模式下通过执行sql语句进行赋值,可以减少Binlog日志量,节约了IO成本,而row模式下是复制数据变更的内容,基于行的复制可能会产生大量的日志内容,IO成本比较高
  2. id不连续造成的问题: 如果我们主从同步中使用statement 基于sql语句进行复制,并且插入数据时的自增id是强制批量获取,当插入数据量不能预知,底层通过msyql自己预判生成批量获取id,是不可控的,就可能造成主库执行sql获取到id为1,3,5,当sql复制给从库,从库执行预判拿到了2,4,6的id,最终造成id不一致

3. 其它表锁

  1. 手动添加表锁
#读锁(共享锁S)
Lock table t Read;
#写锁(排它锁X)
Lock table t Write;
  1. 元数据锁: 在执行DDL语句对表结构,索引等等信息进行修改时,底层会添加一个元数据锁(Metadata Locks 简称MDL )是Server级别的锁,这也是表锁,用来保证读写的正确性

行锁分析

  1. 行锁在锁模式上分为共享锁与排它锁两种
  1. 共享锁又称读锁,当一个事务对某几行上读锁时,允许其他事务对这几行进行读操作,但不允许其进行写操作,也不允许其他事务给这几行上排它锁,但允许上读锁。
  2. 排它锁又称写锁,当一个事务对某几个上写锁时,不允许其他事务写,但允许读,更不允许其他事务给这几行上任何锁,包括写锁
  1. 在锁粒度上又分为: 记录锁、间隙锁、临键锁、插入意向锁
  2. 行锁的优缺点
  1. 优点: 锁定粒度小,发送锁冲突概率低,可以实现的并发度高
  2. 缺点: 锁的开销比较大,加锁会比较慢,容易出现死锁情况
  1. 使用InnoDB存储引擎创建 test_innoDB表,该表中有a,b两个字段,并分别对这两个字段添加索引,插入数据
    在这里插入图片描述
  2. 学习测试中,关闭数据库自动提交
set autocommit=0;
  1. 行锁必须有索引才能实现，否则会自动锁全表，会自动降级为表锁,两个事务不能锁同一个索引
#事务A先执行：
select math from zje where math>60 for update;
 
#事务B再执行：
select math from zje where math<60 for update；
#这样的话，事务B是会阻塞的。如果事务B把 math索引换成其他索引就不会阻塞，
#但注意，换成其他索引锁住的行不能和math索引锁住的行有重复。

1. 记录锁 Record Locks

  1. 记录锁是行锁,仅锁定一行,官方称为LOCK_REC_NOT_GAP, 记录锁内部也分为共享记录锁S, 排它记录锁X
  2. 记录锁锁定的是主键索引,如果没有主键会用唯一索引锁定,如果也没有会使用rowid

2. 间隙锁 Gap Locks

  1. MySQL在可重复读的隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用MVCC,也可以采用加锁方案解决,用来解决幻读的锁就是间隙锁,因为在执行查询时可能出现幻读的位置上还没有数据,只能针对一定的范围添加间隙锁
  2. 间隙锁的产生条件
  1. 必须在RR可重复读隔离级别下
  2. 检索条件必须有索引(主键索引,或一个唯一索引除外),因为条件更新时若没有索引,行锁会升级为表锁,锁定整张表,包括不存在的所有记录,此时其他事务不能修改删除添加等操作）
  1. 间隙锁锁定的范围: 会根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为
  2. 如下图以执行SQL :“update table set table.id=100 where number=5”为例解释,假设number上有索引, 此时间隙锁的范围向左4,向右11, 如果number=11,此时间隙锁的锁定范围向左5,向右无穷大,假设number=13,表中没有13这条数据,但是也存在间隙锁,向左11,向右无穷大,在锁定范围内的再去操作会阻塞等待
    在这里插入图片描述
  3. 通过范围更新再次举例间隙锁 假设当前表中存在id为1,4,5,6记录,假设当前执行update,where的条件是id大于1并且id小于6的记录,这个时候,虽然中间没有id为2,3的记录,但是也会给上间隙锁,如果此时第二个请求过来查询id为2,或3的记录,就会阻塞等待(实际这块应该说是临建锁)
  4. 间隙锁的危害: 当锁定一个范围时,即使范围中间的某些键值不存在也会被加锁,这样就造成无法插入锁定键值范围内的任何数据,造成阻塞
  5. 问题: 执行更新操作,where条件中命中普通索引,在更新成功前有间隙锁吗,有,于命中普通索引,数据是根据普通索引进行排序加锁,如果在更新成功前执行插入操作,插入数据中对应普通索引字段值刚好是间隙锁的范围,则会阻塞等待

3. 临键锁 Next-Key Locks

  1. 临键锁是记录锁与间隙锁的组合体,实现了锁住指定记录 ,并且阻止其它事务在该记录前边的间隙插入新记录
  2. 临键锁针对 REPEATABLE READ 隔离级别下
  3. 以执行范围update更新操作为例,在执行更新时,InnoDB首先会给符合条件的已有数据的索引项添加记录锁,同时会锁定一个范围就叫做间隙,会针对这个间隙加锁,假设当前表中存在id为1,4,5,6记录,假设当前执行update,where的条件是id大于1并且id小于6的记录,这个时候,虽然中间没有id为2,3的记录,但是也会给上间隙锁,如果此时第二个请求过来查询id为2,或3的记录,就会阻塞等待

4. 插入意向锁

  1. 这里可以问为什么rr级别下next key锁能避免幻锁的原因
  2. 在执行插入操作时,会先检查当前插入的记录对应到落库的索引上是否存在锁对象(判断是否有间隙锁)如果锁住了,如果有,当前插入操作进行等待,并生成一个"插入意向锁"结构,插入意向锁也就是配合间隙锁或者临键锁一起防止幻读
  3. 我是这样理解插入意向锁的,一个锁结构,锁标识,表示一种意向,可以同时存在多个插入意向锁,多个插入操作打到同一个间隙锁上进行等待时都会生成插入意向锁,多个插入意向锁按顺序等待执行

5. 隐式锁

  1. 此处也可以问:insert操作会加锁吗?, 默认情况下是不加锁的,也可以说成会加一个隐式锁
  2. 在执行插入时,默认会给添加一个隐式锁,此时时无锁状态,当其它事物执行通过trx_id判断当前记录的事物还在活跃中,并且要获取该记录的X排它锁或者S共享锁时,这个隐式锁会升级为显示的X排它锁,只有当可能会产生冲突的时候才会加锁,减少锁的数量,提高系统的性能
  3. 隐式锁应该与插入意向锁一块来说:
  1. 隐式锁: INSERT 语句在执行插入之前,如果判断被插入数据落库的索引上有间隙锁,会先在这个间隙上插入意向锁,并且如果是唯一索引会进行重复 Key 判断,如果唯一索引值重复,会加排它锁与共享锁,(因为重复唯一索引后续可能会回滚删除,防止发生死锁)然后等待,获取到锁后进行插入
  2. 数据插入时会插入隐式锁, 在该插入事物未提交完毕时,其它事物要针对这条记录申请加锁时,这个隐式锁会升级为显示的X排它锁,有点像synchronized偏向锁到轻量级锁的过程

6. 加锁算法

  1. MySQL是针对索引进行加锁的,遵守以下加锁算法
  1. 主键索引: 如果我们加锁的行上存在主键索引, 会在这个主键索引上加 Record Lock 记录锁
  2. 辅助索引: 如果我们加锁的行上存在辅助索引, 会在这行的辅助索引上添加 Next-Key Lock,并在这行之后的辅助索引上添加一个 Gap Lock, 辅助索引上的 Next-Key Lock 和 Gap Lock 都是针对可重复读隔离模式存在的,这两种锁都是为了防止幻读现象的发生。
  3. 唯一的辅助索引: 也就是辅助索引是唯一索引时,MySQL 会将 Next-Key Lock 降级为 Record Lock，只会锁定当前记录。
  4. 如果唯一索引由多个列组成的，而我们只锁定其中一个列的话，那么此时并不会进行锁降级，还会添加 Next-KeyLock 和 GapLock。
  5. Insert 语句: 在 InnoDB 存储引擎中,对于 Insert 的操作,其会检查插入记录的下一条记录是否被锁定,若已经被锁定,则不允许查询。
  6. 未添加主键问题: 如果 InnoDB 表在创建的时候没有设置任何主键索引,那么 Record Lock 会锁住隐式的主键。
  1. 在加锁时,一条记录一个锁吗,不是, 有锁共享的条件,同一个事物,同一个数据页,同一个锁类型共享一把锁,否则会生成新锁

InnoDB 行锁变降级为表锁

  1. InnoDB行锁是通过给索引上的索引项加锁来实现的,只有通过索引条件检索数据才使用行级锁,否则会降级为表锁,所以在执行增删改操作时要注意是否能命中索引,有没有索引失效的问题
  2. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,不论是使用主键索引,唯一索引还是普通索引,InnoDB都会通过行锁来对数据加锁
  3. 进而引出一个问题: 由于MySQL的行锁不是针对记录加的锁,而是针对索引加的锁,所以即使是访问不同行的记录,如果使用了相同的索引键,也会出现锁冲突问题
  4. 还有一个问题索引是否生效是由MySQL通过执行优化器来觉得的,如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁,而不是行锁。因此在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引

锁冲突

  1. 什么是锁冲突: 假如对某一行数据加锁未释放,此时第二个请求需要操作这个被锁定的数据,第二个请求会阻塞等待前面的提交释放锁后才可以执行,这个阻塞等待,我们称为锁冲突
  2. 考虑锁冲突要了解InnoDB锁实现原理,是通过索引实现的,假如当前执行sql没有索引,则行锁变为表锁,表锁下如果第一个请求未释放,第二个请求无法更新该表数据会一直阻塞等待
  3. 如果一条执行更新的sql用到了索引,会根据该索引值锁定,如果此时第二个操作数据请求过来,有两种情况如果刚好用到被锁定的数据,会阻塞等待,如果用不到会正常执行
  4. 如果一条执行更新的sql用到了索引,并且是范围查询,如果此时第二个操作数据请求过来刚好命中这个范围内的数据(假设实际数据是1,4,5,范围是大于1小于等于5,就是第二个请求操作的是2,3也会阻塞,因为有间隙锁)

如何分析行锁定

  1. 执行"show status like ‘innodb_row-lock%’ " 查看是否有行锁,重点关注(Innodb_row_lock_time_avg等待的平均时长,Innodb_row_lock_waits等待的总次数,Innodb_row_lock_time等待总时长,如果等等次数很高,并且等待时长也不小的时候,就需要使用show proFile进行分析优化)

Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花费平均时间
Innodb_row_lock_time_max: 从系统启动到现在等待最长一次所花费时间
Innodb_row_lock_waits: 系统启动后到现在总共等待次数
在这里插入图片描述

  1. 还有默认情况下,看不到事物对哪些数据加了锁,需要通过"innodb_status_output_locks" 设置为"ON"(很少使用)
    在这里插入图片描述
  2. 然后执行"show engine innodb status\G " 查看状态时,就可以获取到加锁的详细信息(可以获取到锁的哪个数据页, 锁的哪个索引列,锁类型等等信息)(很少使用)
    在这里插入图片描述

三. MyISAM与表锁

  1. 使用MyISAM 存储引擎创建mylock表并添加数据
    在这里插入图片描述
  2. 使用InnoDB存储引擎,创建book表(此处不加示例了)
  3. 加锁命令,read表示读锁, write表示写锁
#对一张表加锁
LOCK TABLE 表名 read/write;

#一次对多张表加锁
LOCK TABLE 表名 read/write, 表2名 read/write;

在这里插入图片描述
4. 查看库中表是否被加锁命令

show open tables;

在这里插入图片描述
5. 释放锁命令

#执行完该命令后原则上当前库中的所有锁都会被释放
unlock tables;

在这里插入图片描述

1. 读锁

  1. 解释: 启动两个session会话对使用MyISAM存储引擎的mylock表添加读锁,查看当前session下,锁释放前能否再次读,能否修改数据,能否读取其它表数据,查看另外一个session会话能否读当前被加锁的数据,能否读取其它表数据,能发修改当前被加锁的数据,
  2. 当前加锁session会话执行示例:加锁后,发现,可以再次读取当前表数据,但是不能读取其它表数据,并且不能对当前加锁表进行增删改操作
    在这里插入图片描述
  3. 其它session会话可以读取加读锁表的数据,可以读取其它不加锁的表数据,但是如果其它session会话对加锁表进行增删改操作会阻塞等待

2. 写锁

  1. 解释: 启动两个session会话对使用MyISAM存储引擎的mylock表添加写锁,
  2. 加写锁后,当前session会话下,可以再次读取当前表数据,可以更新当前表数据,但是无法更新,读取其它表数据
    在这里插入图片描述
  3. 加锁后其它session会话,可以读取其操作其它表数据,但是读取与更新被加锁的表数据,会一直阻塞

3. 如何分析表锁定

  1. 执行" show status like ‘table%’ " 命令,通过table_locaks_waited 和table_locks_immediate状态变量来分析系统上的表锁定

Table_locks_immediate: 产生表级锁定的次数,表示可以立即获取锁的查询此处,每理解获取锁值加1
Table_locks_waited: 出现表级锁定,争用而发生的等待次数(不能立即获取锁的次数,每等待一次锁值加1),次值越高说明存在较严重的表级锁竞争情况
在这里插入图片描述

4. 总结

  1. MyISAM在执行查询语句(SELECT)前,会自动给所涉及表添加读锁,在执行增删改操作前会自动给涉及表添加写锁
  2. MySql表级锁有两种模式: 表共享读锁, 表独占写锁
  3. 根据上面的示例总结出
  1. MyISAM 执行查询操作时,会自动添加读锁,不会阻塞其它进程对当前表的读操作,会阻塞其它进程对当前表的更新操作,并且当前加锁进程无法再次读取当前表与其它表操作,也无法对当前表与其它表进行更新操作
  2. MySAM 执行增删改操作时会自动对该表添加写锁,写锁下,会阻塞其它进程对当前表的任何操作,但是当前进程是可以再次读取当前表的数据(无法读取其它表数据)
  1. 简单来说: 读写会阻塞写不会阻塞读,写锁时读与写都会阻塞
  2. MyISAM的读写锁调度是"写优先",不适合做以写为主的表的引擎,因为写锁后,其它线程不可以对该表进行任何操作,大量的更新操作可能会造成查询请求无法获取到锁,一直阻塞等待

四. 死锁排查

  1. 产生死锁的四个必要条件:(跟jdk中的死锁产生原因一样)
  1. 互斥: 首先时互斥锁
  2. 请求和保持: A先获取到锁一致持有,并请求获取B的锁
  3. 不剥夺: 进程已经持有的资源在未使用完毕时不会强制释放
  4. 环路等待: 线程1持有a锁等待获取线程2持有的b锁, 线程2持有b锁等待获取线程1持有a锁,形成环
  1. InnoDB发现死锁之后,会计算出两个发生死锁的事务各自操作的数据量(增删改)来判定两个事务的大小,数据较小的事务回滚，较大的事务成功完成.也就是说改变的记录条数越多在死锁中就越不会被回滚掉
  2. 但是需要注意,当产生死锁的场景中涉及到不止InnoDB存储引擎,InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
  3. 通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法：
  4. 执行"SHOW ENGINE INNODB STATUS;"来查看死锁日志,显示
------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
##1.TRANSACTION: 表示当前执行中的事物
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
##2.关注点: root updating后是实际执行的sql
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2

##3.WAITING FOR THIS LOCK TO BE GRANTED: 等待这个锁被授予(也就是当前执行的sql正在等待的锁)
##index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting Record loc : 表示在等待oauthdemo库下的test表中的索引列为a,的x锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;

##4.表示当前第二个执行中的事物
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
##5.当前实际执行的sql
insert into test (id,a) values (10,2)

##6. HOLDS THE LOCK(S):记录了事物2中持有的锁(index `a` of table `oauthdemo`.`test` 表示oauthdemo库中的tst表的a索引的锁) Record lock 记录锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;

##7.(2) WAITING FOR THIS LOCK TO BE GRANTED: 事物2中在申请的锁(index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting Record lock 表示事物2持有a列的锁,正在申请s锁"lock mode S waiting")
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
  1. 死锁日志解释: 在日志中,每个动作都会进行分块记录,每个块中包含"TRANSACTION", “WAITING FOR THIS LOCK TO BE GRANTED”, " HOLDS THE LOCK(S)"大概这三个部分,通过前缀的序号来区分,序号相同的代表是同一个完整操作记录
#1. " *** (1) TRANSACTION: ": 可以看为第一次事物执行,里面包含了当前事物执行的sql语句,操作的库表信息
#2. " *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ": 等待这个锁被授予(当前事物正在等待的锁),记录了等待的哪个库表中的锁,锁类型,
#3. " *** (2) HOLDS THE LOCK(S): ": 当前事物持有的锁,锁在哪个库表,锁的类型等前面的"(2)"对应第二个事物操作
  1. 根据上方的死锁日志分析为什么出现死锁:
  1. 通过 “(1) WAITING FOR THIS LOCK TO BE GRANTED” 分析到事物1中正在等待oauthdemo库下的test表中的索引列为a,的x锁
  2. 通过 “(2) HOLDS THE LOCK(S):” 分析到事物2正在持有oauthdemo库下的test表中的索引列为a锁,也就是事物1正在等待的锁
  3. 通过 “(2) WAITING FOR THIS LOCK TO BE GRANTED” 分析到事务2正在申请S锁,也就是共享锁,
  4. 重点: 为什么事物2要申请s锁,而不是x锁, s锁该锁是insert into test (id,a) values (10,2)语句申请的。insert语句在普通情况下是会申请排他锁，也就是X锁，但是这里出现了S锁。这是因为a字段是一个唯一索引，所以insert语句会在插入前进行一次duplicate key的检查，为了使这次检查成功，需要申请S锁防止其他事务对a字段进行修改。
  5. 重点: 为什么出现死锁,是因为申请s锁失败,为什么失败: 对同一个字段的锁的申请是需要排队的。S锁前面还有一个未申请成功的X锁，所以S锁必须等待
  6. 所以事物1在等待a的x锁,事物2持有a的x锁,在等待S锁,事物2的s锁获取前需要先拿到一个x锁,但由于x锁需要排队,形成了循环等待，死锁出现了

推荐优化

  1. 在检索查询过滤数据时尽可能使用索引完成,避免无索引行锁降级为表锁
  2. 合理设计索引,InnoDB通过索引加锁,尽量缩小锁的范围
  3. 尽可能少的过滤条件,避免间隙锁
  4. 尽量控制事物大小,减少锁定的资源量,时间长度
  5. 保证业务功能不受影响的前提下尽可能的降低事物隔离级别
苹果香蕉西红柿
关注
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
mysql readpast_SQL Server-聚焦NOLOCK、UPDLOCK、HOLDLOCK、READPAST你弄懂多少？（三十四）...
weixin_33514163的博客
01-19 325
前言时间流逝比较快，博主也在快马加鞭学习SQL Server，下班回来再晚也不忘记更新下博客，时间挤挤总会有的，现在的努力求的是未来所谓的安稳，每学一门为的是深度而不是广度，求的是知识自成体系而不是零散，废话不多说本节我们来讲讲SQL Server基础系列最后几节内容，这话博主说了n次，呵呵。NOLOCK和READPASTNOLOCK随便翻翻博客园对于各种锁的介绍真的是一个字【多】，仅仅介绍其概念...
mysql 行锁升级为表锁
小洪帽i的博客
07-20 4263
行锁升级为表锁 总所周知，MySQL 的 InnoDB 存储引擎支持事务，支持行级锁（InnoDB 的行锁是通过给索引项加锁实现的）。得益于这些特性，数据库支持高并发。如果 InnoDB 更新数据使用的不是行锁，而是表锁呢？是的，InnoDB 其实很容易就升级为表锁，届时并发性将大打折扣了。 常用的索引有三类：主键、唯一索引、普通索引。主键不由 ...
mysql锁升级？
tiny的专栏
06-08 371
锁升级是指将当前锁的力度降低。举例来说，数据库可以把一个表的1000个行锁升级一个页锁，或者将页锁升级为表锁。如果在数据库中的设计中认为锁是一种稀有资源，而且想避免锁的开销，那shujuku ...
MySQL innoDB 中的锁升级
Data & Analysis
03-28 873
什么是锁升级？ 锁升级是指将当前锁的粒度降低，如一把行锁升级唯一把页锁，或者将页锁升级为表锁，如果在数据库设计中认为锁是一中稀有资源，哪么就会频繁有锁升级的现象 发生锁升级的现象 当一条SQL语句对一个对象上持有的锁数量超锁了阈值，默认这个阈值为5000，但是对于不同对象不会发生锁升级 锁资源占用的内存超过激活内存的百分之40 就会发生锁升级 但是！！！！！ innoDB 引擎不存在锁升级的问题，因为其不是根据每个记录来产生啊行锁的，是根据每个事务访问的每个页对锁进行管理的。 其实吧，这个根据页.
mysql 与sqlserver的锁升级
布道
06-18 2717
锁可谓RDBMS中最复杂、最神秘的技术。锁升级（Lock Escalation）是指将当前锁的粒度降低（有点Java锁粗化的味道），是一种优化技术，数据库设计者们认为锁是一种稀有资源，为了避免锁的开销（占用内存），数据库中会频繁的出现锁升级的现象。比如sqlserver数据库可以1000个行锁升级为一个页锁，或者将页锁升级为表锁。而InnoDB中不存在锁升级。 锁升级的缺陷 虽然锁升级会带来一...
mysql innodb 行级锁升级
06-01 214
创建数据表test，表定义如下所示： CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB AU...
新版 MySQL DBA 高级视频 基于MySQL 5.7 MySQL 8.0版本.rar
07-17
│ 3_MySQL锁相关参数设置.mp4 │ 4_InnoDB事务隔离级别详解.mp4 │ 5_InnoDB死锁发生原理和规避.mp4 │ 6_MySQL字符集和排序规则.mp4 │ 作业.docx │ 锁等待分析.txt │ ├─新版MySQL DBA综合实战班 第08天 │ 1...
java mysql 行锁_MySQL高级知识（十四）——行锁
weixin_42505477的博客
01-19 365
前言：前面学习了表锁的相关知识，本篇主要介绍行锁的相关知识。行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，但并发度高。0.准备#1.创建相关测试表tb_innodb_lock，注意数据库引擎为InnoDB。drop table if exists test_innodb_lock;CREATE TABLE test_innodb_lock (a INT ...
MySQL数据库面试宝典1.pdf
06-16
**1.4 MySQL有关权限的表都有哪几个？** MySQL中有多个表用于管理用户权限： - **user**：存储全局级别的用户权限信息。 - **db**：存储每个数据库级别的权限信息。 - **tables_priv**：存储每个表级别的权限信息。 ...
mysql基础知识----锁升级
u013915286的博客
03-11 699
InnoDB中根据页的组织形式进行锁管理，并使用位图记录锁信息。 n_bits变量表示位图占用的字节数，它后面紧跟着一个bitmap，bitmap占用的字节为：1 + (nbits-1)/8，bitmap中的每一位标识对应的行记录是否加锁。 因此，lock_rec_struct占用的实际存储空间为：sizeof(lock_rec_struct) + 1 + (nbits-1)/8。 比较好的文章 ...
mysql行级锁升级_mysql innodb 行级锁升级
weixin_33744799的博客
01-26 552
创建数据表test，表定义如下所示：CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,PRIMARY KEY (`id`),KEY `idx_name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin...
了解MySQL锁升级吗
越努力越幸运。
01-03 862
所以在实际应用时，合理的索引设计、适当的事务隔离级别设置可以帮助减少锁升级的发生，从而提高并发性能和系统吞吐量。然而，在某些情况下，事务无法获取到所需的锁级别，就会触发锁升级。通常情况下，MySQL 会根据事务的需求和操作的对象。
mysql行锁升级表锁
weixin_64027360的博客
09-16 2086
需要注意，表锁通常会降低并发性，因为它会限制多个事务同时访问同一表的能力。因此，在设计数据库和应用程序时，应根据实际需求仔细选择锁的级别，并尽量避免不必要的锁升级。但在某些情况下，表级锁可能是必要的，例如执行表级操作或确保操作的原子性。如果锁等待时间过长，会导致性能下降。如果事务需要锁定多个行，MySQL可能会选择升级为表锁，以减少锁管理开销。：某些MySQL存储引擎，例如MyISAM，只支持表级锁，因此无论如何都会使用表锁。：有时，开发人员可能会显式请求表锁，以确保一组操作的原子性，而不是逐行锁定数据。
【MySQL技术内幕】44-锁升级
通往神秘的道路的专栏
10-28 1149
锁升级(Lock Escalation)是指将当前锁的粒度降低。举例来说,数据库可以把一个表的1000个行锁升级为一个页锁,或者将页锁升级为表锁。如果在数据库的设计中认为锁是一种稀有资源,而且想避免锁的开销,那数据库中会频繁出现锁升级现象。 Microsoft SQL Server数据库的设计认为锁是一种稀有的资源,在适合的时候会自动地将行、键或分页锁升级为更粗粒度的表级锁。这种升级保护了系统资源...
MySQL高级 - 锁 - InnoDB行锁 - 行锁升级为表锁
Leon_Jinhai_Sun的博客
12-08 576
无索引行锁升级为表锁 如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。 查看当前表的索引 ： show index from test_innodb_lock ; Session-1 Session-2 关闭事务的自动提交 关闭事务的自动提交 执行更新语句 ： 执行更新语句， 但处于阻塞状态： 提交事务： 解除阻塞，执行更新成功 ： 执行提交操作 ： 由于 执行更新时 ， name字段本来为va..
MySQL-锁：共享锁（读）、排他锁（写）、表锁、行锁、意向锁、间隙锁，锁升级
小孔靠得住的博客
03-09 1912
在MySQL中，锁升级是指事务在执行过程中，将当前持有的锁从低级别升级到更高级别的过程。MySQL数据库中的锁是控制并发访问的重要机制，它们确保数据的一致性和完整性。因此，在这种场景下，合理使用共享锁和排他锁可以确保数据的完整性和一致性，避免潜在的并发问题。为1的产品行的共享锁，但在后续执行中需要对该行进行修改，因此需要将共享锁升级为排他锁。为100的订单行的意向排他锁，后续执行了删除操作，需要将意向锁升级为表级锁。
mysql类型强转导致锁升级_MySql类型转换导致行锁升级为表锁
weixin_30767257的博客
02-02 140
在MySql的写语句中，给表列赋值与表类型不符合时，MySql底层的优化器发挥作用，会做一个强制类型转化，此时能正常操作，但会导致行锁升级为表锁。示例如下以student表为例，表字段类型：表内容如下： 打开两个session会话窗口，并把两个会话窗口中的MySql的自动提交模式改为手动提交>set autocommit=false;在会话窗口1中执行更新语句，但不提交事务。age列在建表时...
面试——数据库中的锁升级（Lock Escalation）机制
最新发布
Wanankl的博客
04-24 540
以MySQL为例，按照两阶段锁协议，会先给tb_user加上表意向锁，然后对tb_user的所有行加上行锁，但是当tb_user数据量非常多的时候，频繁的获取行锁会影响性能，所以会将表意向锁升级为表锁，后续访问的时候不需要再加锁，这样导致的结果是并发性能降低，因为其他线程访问该表会被阻塞。条件，分批去操作数据，尽量防止全表扫描。因此得出建议：操作表的时候尽量带仨很。假设执行下面的sql语句。
MySQL工作笔记面试题解析与经验分享
知识点四：事务和锁机制 事务保证了数据库操作的原子性、一致性、隔离性和持久性。MySQL中的锁机制和事务隔离级别也是面试中的常见问题。掌握事务的ACID特性、MyISAM与InnoDB存储引擎的锁机制区别，以及不同事务隔离...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值