Mysql的死锁与索引的关系
前言
在工作过程中,我们经常会碰到mysql的死锁问题,也是我们非常头疼的问题,今天老顾尝试着和小伙伴们一起学习一下mysql锁的相关知识点。 锁分类
我们经常说的就是 表锁 、以及 行锁 表锁:对整张表加锁
行锁:对表中的某条记录加锁
mysql数据库引擎支持的锁类型是不同的 MyISAM 只支持到表级锁 InnoDB 可以支持到行级锁 表锁
对整张表加锁,锁的颗粒度大,资源消耗小,并发请求低;表锁有2种模式:
1、表共享锁:对同一表的不阻塞读,但阻塞写。
2、表独占锁:对同一表的操作,不管是读写,都阻塞。
共享锁
独占锁
共享锁
兼容
冲突
独占锁
冲突
冲突 显式加表锁:lock tables {tb_name} read/write 显式释放锁:unlock tables行锁
我们今天的重点就是行锁,也就是死锁的根源所在。行级别的锁颗粒度小,开销比较大 锁模式:
1、共享锁(读锁)S锁: 对同一行的操作,读不阻塞,写阻塞
2、排他锁(写锁)X锁: 对同一行的操作,读写都阻塞
3、意向共享锁IS: 一个事务想要加S锁,首先先获得该表的IS锁
4、意向排他锁IX: 一个事务想要加X锁,首先先获得该表的IX锁
意向锁本质就是表锁,那为什么要有意向锁呢?
意向锁用来标识该表上有数据被加锁或将被加锁,对于表级别的请求(lock table....),就可以直接判断是否有锁冲突,不需要逐行检查锁的状态
S锁
X锁
IS锁
IX锁
S锁
兼容
冲突
兼容
冲突
X锁
冲突
冲突
冲突
冲突
IS锁
兼容
冲突
兼容
兼容
IX锁
冲突
冲突
兼容
兼容 InnoDB锁方式
InnoDb的 默认隔离级别RR(可重复读) ,在RR下读取数据的方式:
1、快照读: 事务 开启执行第一个SELECT语句后会获取一个数据快照,直到事物结束读取到的数据都是一致的
普通的 select… 查询都是快照读
2、当前读: 读取的数据的最新版本,并且在读的时候不允许其它事物修改当前记录
select… lock in share mode(S锁)select… for update(X锁)
加锁方式: 普通 select… 查询 (不加锁) 普通 insert、update、delete… (隐式加写锁) select…lock in share mode (加读锁) select…for update (加写锁)
解锁方式: 提交/回滚事物(commit/rollback) kill 阻塞进程 锁与索引
InnoDB的行锁是通过给索引上的 索引项加锁来实现 的
即使在建表的时候没有指定主键,InnoDB会默认创建一个DB_ROW_ID的自增字段为表的主键,并且其主键索引(聚簇索引)为GEN_CLUST_INDEX
主键索引也被称为聚簇索引行锁类型Record Lock: 对对应的索引 记录项加锁,称记录锁 Gap Lock:对 索引项之间的间隙加锁 ,加锁之后 间隙范围内不允许插入数据,防止发生幻读 Next-key Lock:可以理解为Record Lock+Gap Lock(InnoDB行锁默认加的是 Next-key Lock) 记录锁
这个比较好理解,就是把表中的记录锁定。 间隙锁
编程的思想源于生活,生活中的例子能帮助我们更好的理解一些编程中的思想。
生活中排队的场景,小明,小红,小花三个人依次站成一排 ,此时,如何让 新来的小刚不能站在小红旁边 ,这时候 只要将小红和她前面的小明之间的空隙封锁,将小红和她后面的小花之间的空隙封锁 ,那么小刚就不能站到小红的旁边。
这里的小红,小明,小花,小刚就是数据库的一条条记录。
他们之间的空隙也就是间隙,而封锁他们之间距离的锁,叫做间隙锁。 加锁规则
行级锁默认加 next-key lock ,查询过程中访问到的索引项都会加锁,而根据 不同的索引也有不同的加锁规则 : 唯一索引等值查询:
当索引项存在时,next-key lock 退化为 record lock;
当索引项不存在时,默认 next-key lock,访问到不满足条件的第一个值后next-key lock退化成gap lock 唯一索引范围查询:
默认 next-key lock,(特殊’<=’ 范围查询直到访问不满足条件的第一个值为止) 非唯一索引等值查询:
默认next-key lock ,索引项存在/不存在都是访问到不满足条件的第一个值后next-key lock退化成gap lock 非唯一索引范围查询:
默认 next-key lock,向右访问到不满足条件的第一个值为止
针对这几种情况分别举例说明一下,假设我有以下数据:
id
name
age
1
张三
21
4
王一
26
6
小军
18
9
小红
23
在上面的数据表我们可以得到5个next-key lock 区间:
唯一索引(id):(- ,1],(1,4],(4,6],(6,9] ,(9,+supremum]
非唯一索引(age):(- ,18],(18,21],(21,23],(23,26] ,(26,+supremum] 案例:唯一索引等值查询索引项存在eg:select * from user where id=4 for update
加锁情况:
默认加next-key lock (1,4],因索引项存在,则next-key lock退化为 record key,只对id=4的这个索引项的record key 索引项不存在eg:select * from user where id=5 for update
加锁情况:
默认加next-key lock (4,6],访问不满足条件id=6后next-key lock退化为grap lock,加锁范围(4,6) 案例:唯一索引范围查询eg: ">" select * from user where id > 4 for update
加锁情况:默认 next-key lock (4,6],(6,9],(9,+suprenum] eg: "<" select * from user where id < 4 for update
加锁情况:默认 next-key lock (- ,1],(1,4] eg: ">=" (可以拆成 > 和 =) select * from user where id >= 4 for update
加锁情况:
">4": 范围查询 默认 next-key lock (4,6],(6,9],(9,+suprenum]
"=4": 等值查询 默认next-key lock退化为 record key,id=4的索引项
‘>=4’: 合并在一起加锁范围为: [4,6],(6,9],(9,+suprenum] eg: "<="(可以拆成 < 和 =) select * from user where id <= 4 for update
加锁情况:
‘<4’: 范围查询 默认 next-key lock (- ,1],(1,4],这里有点特殊范围查询,需要访问不满足条件(<=4)的第一个值为止,因此找到了6;加锁范围 (- ,1],(1,4],(4,6]
"=4": 等值查询 默认next-key lock退化为 record key,id=4的索引项
‘<=4’: 合并在一起加锁范围为: (- ,1],(1,4],(4,6] 案例:非唯一索引等值查询索引项存在eg:select * from user where age=21 lock in share model
加锁情况:
默认加next-key lock (18,21], (21,23];
访问到不满足条件age=23后next-key lock退化为grap key
加锁范围(18,21], (21,23) 索引项不存在eg:select * from user where age=19 lock in share model
加锁情况:
默认加next-key lock (18,21],
访问不满足条件age=21后next-key lock退化为grap lock
加锁范围(18,21) 案例:非唯一索引范围查询eg:">" select * from user where age > 21 lock in share model
加锁情况:
默认加next-key lock (18,21], (21,23],(23,26], (26,+suprenum],
向右访问到不满足条件第一个值为止 (18,21], (21,23],(23,26], (26,+suprenum]
加锁范围 (18,21], (21,23],(23,26], (26,+suprenum] eg:"<" select * from user where age < 21 lock in share model
加锁情况:
默认加next-key lock (- ,18], (18,21],
向右访问到不满足条件第一个值为止 (- ,18], (18,21],(21,23]
加锁范围 (- ,18], (18,21],(21,23] eg:">=" (可以拆成 > 和 =) select * from user where age >= 21 lock in share model
加锁情况:
‘> 21 ’ 范围查询 (18,21], (21,23],(23,26], (26,+suprenum]
"= 21" 等值查询 (18,21], (21,23)
合并加锁范围 (18,21],(21,23],(23,26], (26,+suprenum] eg:"<="(可以拆成 < 和 =) select * from user where age < 21 lock in share model
加锁情况:
‘< 21 ’: 范围查询 (- ,18], (18,21], (21,23]
‘= 21 ’: 等值查询 (18,21], (21,23)
合并加锁范围 (- ,18], (18,21],(21,23]
细心一点你会发现上面例子中:
唯一索引的查询用的是 select … for update
非唯一索引的查询用的是 select … lock in share modelfor update 加的是写锁,写锁默认认为会对数据做更改,不管查询有没有涉及到回表都会对聚簇索引(主键索引)加锁 lock in share model 加的是读锁,如果没有涉及到回表(像覆盖索引),不会对聚簇索引(主键索引)加锁
如果上面例子中非唯一索引的查询用的是 select … for update,还需要分析聚簇索引(主键索引)的加锁情况(可参考文章https://www.jianshu.com/p/bf862c37c4c9)死锁
死锁指的是两个或两个以上的事物在执行过程中争抢锁资源而造成相互等待的情况
表锁不会出现死锁,主要还是针对InooDB的行锁,可以看下面的例子:
会话A与会话B 互相产生了死锁。 监控分析锁问题# 查询InnoDB锁的整体情况 # 可以重点查看Innodb_row_lock_waits和Innodb_row_lock_time_avg这两个值 # 如果数值较大,说明锁之间的竞争大 show status like "innodb_row_lock%"; #可以通过INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS这三个表 #分析可能存在的锁的问题 select * from information_schema.INNODB_TRX; # 查看所有事务 select * from information_schema.INNODB_LOCKS; # 查看锁 select * from information_schema.INNODB_LOCK_WAITS; # 查看锁等待
解决死锁:
超时等待,事物超时自动回滚(innodb_lock_wait_timeout 默认50s)
主动死锁检测,事物请求锁的时候采用 wait-for graph 等待图的方式进行死锁检测(innodb_deadlock_detect 默认on)
发现死锁也可以人为 kill 进程 总结MySQL锁分为全局锁、表级锁以及行级锁,不同的存储引擎支持锁的粒度有所不同,MyISAM 只支持到表级锁,InnoDB 则可以支持到行级锁,锁的粒度决定了业务的并发度,因此更推荐使用InnoDB InnoDB默认最小加锁粒度为行级锁,并且锁是加在索引上, 如果SQL语句未命中索引,则走聚簇索引的全表扫描,表上每条记录都会上锁,导致并发能力下降,增大死锁的概率 ,因此需要为表合理的添加索引, 线上查询尽量命中索引 行级锁默认加 next-key lock,而 根据不同的索引也有不同的加锁规则 ,我们可以根据加锁规分析加锁区间 锁粒度的减小提高了并发度的同时也增加了死锁的风险,查询应尽量考虑减少锁的范围
Windows使用技巧集锦一要不要设置windows开机密码?当然需要,不设置别人随时可以打开你的电脑,重要资料会泄露另外如果你登录了,黑客照样可以盗窃你的资料,有了这个密码他就不可轻意得手。如何设置修改和
赋能乡村振兴电商经济助推乡村产业发展本报记者李晓红乡村振兴是近两年备受广泛关注的热点话题。在数字经济的浪潮下,直播电商也逐渐成为助力乡村振兴的重要抓手。2022年中央一号文件提出,实施数商兴农工程,推进电子商务进乡村
从赌博中衍生出的数学什么是正态分布,为什么它很重要?正态分布是说,在平均值附近观察到特定数据值的概率最大,并且随着与平均值的差值的增加而迅速减小。多快取决于一个叫标准差的量。数学是模式的科学。概率的随
追着太阳跑,要多快才能一直是白天?综述夸父逐日的神话我们都听过,最后的结局是他因为劳累过度失水过多而死,最后身体变成了我们生活的这个世界。提到追赶太阳,我们都会觉得很感兴趣,如果连夸父这样的巨人都追不上太阳的话,那
Intel高管再过3近日,Intel高级副总裁中国区董事长王锐接受凤凰网封面节目采访时,就被问及中国本土芯片公司是否已对Intel造成实质威胁。对此,王锐坦诚地表示,她非常希望中国本土能出现和Inte
虎牙擅播琅琊榜被判赔偿爱奇艺23万元3月22日,北京法院审判信息网公布广州虎牙信息科技有限公司与北京爱奇艺科技有限公司不正当竞争纠纷二审民事判决书,审理法院为北京知识产权法院。判决书显示,一审原告爱奇艺公司诉称,虎牙
4年已还清6。7亿债务,罗永浩表示纯属谣传就在刚刚,罗永浩发长文就网络消息其下个月即将还完6。7亿债务后将离开交个朋友直播并进军科技领域事件。罗永浩表示,纯属造谣,他不得不站出来再次澄清一下,以免大家误会。罗永浩是怎么欠那
威马W6续航520km花费36元随着国际油价突破9元大关。很多想要购车的消费者开始犹豫,加满一次油就要花费400多,一年下来将近两万块的油费,买车容易供车难。威马新能源汽车就可以完美解决这个问题,以威马W6为例,
国产9500mAh新机来了,12256G定在1699,备货量很足不知道大家有没有发现,在现存的国产小众品牌中,有很多都在主打大电池强续航配置,力图通过最好的续航优势来增加自己产品的亮点,进而让用户有购买的欲望。现在的金立就是如此!能不能把现在的
北京通信管理局下架16款侵害用户权益App乐居买房创业邦等在列北京通信管理局下架16款侵害用户权益App乐居买房创业邦等在列财联社3月22日电,据工信部消息,按照关于开展纵深推进APP侵害用户权益专项整治行动的通知等工作部署,北京市通信管理局
徐工装载机深耕绿色智造来源交汇点新闻客户端碳达峰碳中和相继写入政府工作报告和十四五规划,强有力推动各行各业向绿色低碳转型。以徐工智能制造4。0战略为指引,徐工装载机瞄准大数据应用,不断探索适用于企业的应