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,而 根据不同的索引也有不同的加锁规则 ,我们可以根据加锁规分析加锁区间 锁粒度的减小提高了并发度的同时也增加了死锁的风险,查询应尽量考虑减少锁的范围
任天堂Wii创新游戏玩法的开拓者任天堂Wii是一款在2006年发售的游戏机,其主要特点是采用了运动控制器和互动性游戏玩法,从而为游戏市场带来了颠覆性的创新。任天堂wiiWii的控制器由主机运动控制器和Nunchu
在激流时代,看见一线创业者的奋斗底色遍地开花的直播背后是怎样的生态链?精品咖啡的发展路上有多少人在坚持?长沙为什么有那么多网红店?爆红的上海安福路到底藏着多少新品牌业态?近期,腾讯视频推出的全新商业观察节目激流时代,
走!到印尼市场去!本报记者孟妮中国商务新闻网去印尼能拿到订单吗?带着疑问和期许,做电子音响出口的某深圳企业负责人搭乘飞往印尼的国际航班,他此行是为了参加2023印尼电子商务大会。这场大会汇聚了整个印
约基奇221710无缘今日最佳,里弗斯化身詹姆斯把湖人拉回附加赛20222023赛季NBA常规赛正在如火如荼的进行中,3月20日比赛结束后,来看下今日赛况以及各队球员数据之最掘金108102击败篮网掘金队波特砍下全队最高的28分,约基奇送出全队
抗糖之主食篇很多高血糖的友友们以为抗糖是不能吃主食,这个想法是错误的。完全不摄入碳水化合物会危害身体健康,当身体长期缺乏可以能量的碳水化合物后,可能会诱发糖尿病酮症酸中毒,并且不吃主食会经常感
炒木耳,下锅噼里啪啦太吓人?大厨教你3招,不炸锅,香又脆家里的黑木耳银耳黄花菜等干制的蔬菜,老妈每年都会在春节前后购买,特别是黑木耳,每次购买的都吃不完,总会剩下不少,放着放着就又到了第二年的腊月,老妈担心木耳变质,影响我们的身体健康,
瑞幸咖啡月底开业,欢迎来到新加坡这个咖啡修罗场此前,在新加坡积极招聘的瑞幸咖啡终于要开业了。有网友分别在乌节路义安城和滨海广场附近,看到瑞幸咖啡的门店目前正在装修,牌上标注着即将在3月开业。瑞幸咖啡作为中国目前最大的咖啡连锁品
吃雪莲果的好处不知道大家有没有喜欢吃雪莲果的,在超市里看促销员在大声吆喝着雪莲果便宜了,便宜又好吃,禁不住诱惑我也买了几个,看着像红薯,拿回家按照他们说的方法削皮后就可以吃,吃起来感觉口感清脆和
烘焙教学高比克80S卡通抱抱饼干隐露厨房日式卡通小动物抱抱饼干两种口味多种造型丰富的表情,形状各异的小动物,让饼干更加软萌可爱,万物皆可抱抱!食材软化的黄油400g白砂糖200g常温鸡蛋4个低筋面粉780g(40
它是西餐里的蜜雪冰城,人均30,年入100亿,它何以逆市增长它是一家诞生于1973年的意式餐厅。它在北京上海广州的餐厅,20元吃饱30元吃好50元吃扶墙跑。它多年不涨价,即便涨价也是十年涨一次价,涨价的幅度你也根本感觉不出来。它从不做外卖不
最会做生意的女明星詹妮弗洛佩兹JenniferLopez2023年度奥斯典礼卡才结束,美国超级巨星詹妮弗洛佩兹(JenniferLopez)就与奢侈品REVOLVE合作推出了红毯系列的鞋子,这位创业巨星与Revolve合作推出了一系列全