介绍
MyISAM
表锁是 deadlock free
的, 这是因为 MyISAM
总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。但在 InnoDB
中,除单个 SQL 组成的事务外,锁是逐步获得的,即锁的粒度比较小,这就决定了在 InnoDB 中发生死锁是可能的。
如下是一个典型的死锁场景
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kiy6YkGF-1686830636618)(MySQL死锁.assets/image-20230615195909941.png)]
死锁一般由自己应用造成的,和多线程编程死锁情况类似,大部分都是由于我们多个线程在获取多个锁资源的时候,获取的顺序不同而导致的死锁问题。
因此我们应用在对数据库的多个表做更新的时候,不同的代码段,应对这些表按相同的顺序进行更新操作,以防止锁冲突导致死锁问题。
实例演示
首先有表user
数据如下,name
已经建立了辅助索引,且设置两个会话都设置为手动提交(autocommit=0
),隔离级别为 REPEATABLE-READ
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mdbjNeHV-1686830636619)(MySQL死锁.assets/image-20230615200013241.png)]
1、会话1获取 name =‘lisi’ 这一行的X锁
2、会话2获取 name ='xiaohong’这一行的X锁,
3、会话1再获取name ='xiaohong’这一行的X锁
4、会话2获取 name =‘lisi’ 这一行的X锁,此时产生死锁,
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TAiDNjBX-1686830636619)(MySQL死锁.assets/image-20230615200102950.png)]
注意:mysql检测到死锁会自动进行回滚。
锁的优化建议
1.尽量使用较低的隔离级别
2.设计合理的索引并尽量使用索引访问数据,使加锁更加准确,减少锁冲突的机会提高并发能力
3.选择合理的事务大小,小事务发生锁冲突的概率小
4.不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
5.尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响
6.不要申请超过实际需要的锁级别
7.除非必须,查询时不要显式加锁