TDSQL(MySQL)死锁问题分析
TDSQL(MySQL)死锁问题分析
一.问题阐述:
发生死锁的环境是腾讯的分布式数据库(一主二从,分两片),是在批量执行sql时发生了死锁,事务进行了回滚;
涉及到的mysql知识点:
1.数据库的锁类型:共享锁(s锁),排它锁(x锁)
2.pt-table-checksum(数据库主从一致性校验工具),知识点链接
二.问题分析:
1.查看数据库死锁日志
查看最后一次死锁的日志信息
SHOW ENGINE INNODB STATUS;
得到以下关于事务的日志
第5行和第18行分别是两个不同的事务:
- 其中事务一所展示的信息为在系统中执行的sql语句(在第14行可以看到使用了X排它锁),事务一中语句如下
2.事务二执行的语句不是业务sql,于是复制了sql在百度搜索了一番,发现replace语句是pt-table-checksum在工作时执行的语句.
可以看到这里面有一条select语句,结合第27行发现该select语句使用了S共享锁,语句意思为对khbh范围内的数据进行CRC32(类似hash),对从库和主库的数据进行比对(详见上文链接);同时该范围内的数据加上了s锁.
三.问题总结:
两个事务互相争夺对方手中的锁(数据资源):
1.其中红框一处表示事务一想要获取space id 1748,page no 99 n bits 168(这一串数字可以理解为一个RECORD LOCKS行锁)索引类型为primary数据(数据A)的锁(该位置的锁被事务二持有–见26,27行),
2.第29,30行表示事务二要获取位置1743,110,160(数据B)的锁(猜测此处的锁正在被事务一持有)
3.如果猜测正确,那么就可以这样解释(事务一用的X锁,事务二用的S锁。不可共存且互相争夺对方手中持有的锁，形成循环关系,导致死锁):
死锁超时时间应该指的是锁等待时间，参数为
innodb_lock_wait_timeout
这两个处理方法对应的是不同的情况:
1️⃣:发现死锁的情况–>死锁检测: 默认开启，InnoDB 在加锁的时候会检测加锁后是否会造成死锁，如果会就回滚代价最小的那一个事务(undolog最小的那个)
2️⃣:未发现死锁的情况–>锁等待超时时间: 一是为了处理检测不出来的那种死锁，二是避免等待正常锁（非死锁，可能是大事务）时间过长。获取锁发生超时后报异常，进行回滚。
附:
查阅pt-table-checksum的相关资料发现该工具工作时不建议在业务高峰时使用,恰好批量sql(大概800条)执行时出现了该问题.
到此该问题已分析完成,生产上对每次800条批量插入修改为500.以降低同时争夺锁的概率