mysql 死锁案例

Mysql 死锁问题

记录一下mysql使用中遇到的一些问题和心得

1. 遇到一个mysql的死锁问题， 情况是这样的， 两个客户端分别在不同的事务中执行如下SELECT...FOR UPDATE和INSERT...., 结果发生死锁；

对这个问题进行了一些调研， 原因是：

代码中交易和收款单对象由于涉及外部订单号的问题. 需要在创建之前先确定数据库中是否有相同的数据, 因此首先会

SELECT xxx FROM t_table WHERE F_trans_id_ext = x FOR UPDATE;

按照 InnoDB 手册, InnoDB 在 REPEATABLE-READ 隔离级别的时候, SELECT FOR UPDATE 会在所有选出的数据上加排他锁防止其他 session select for update , 另外同时会阻塞插入操作, 但是阻塞插入（个人理解：for update会对整个表加意向排他锁，导致插入操作对应的行数失败）操作的时候, 并不会阻塞别的 session SELECT FOR UPDATE 空数据, 于是两个 session 如果同时 SELECT FOR UPDATE 出来空结果, 这两个session都可以成功, 然后各自去从订单服务拿到了不同的订单号, 开始插入数据库, 第一个session插入库的时候, 因为第 2 个 session 会阻塞它(实际验证 只会阻塞相同索引的行插入), 因此第 1 个 session 需要等待 2 commit 或者 rollback 才可以进行, 这时候 2 开始 insert, 就产生死锁, innodb 能检测到这种类型死锁, 而马上返回.就造成了这个错误. 这里其实这两条数据的唯一索引列和主键列都是不一样的,只是因为开始 SELECT FOR UPDATE 的时候会一概阻塞所有插入操作造成的.

上面说的是默认的 REPEATABLE-READ 隔离级别, 如果隔离级别为 READ COMMITTED, 那么此时 SELECT FOR UPDATE 不会阻塞插入, 因此只要数据本身没有主键或者唯一索引冲突, 就可以正常插入.

之所以很久都没有发生这个问题, 包括压力, 是因为代码中大多数情况不需要 SELECT FOR UPDATE 后, 发现没有再继续进行 INSERT, 大部分情形是假设库中应该有此记录, 如果 SELECT FOR UPDATE 失败就直接报错回滚了. 另外有些情形例如提现申请, 是完全不管原来库中有没有, 直接生成订单号并直接插入, 唯一有这种问题逻辑的两个地方, 一个是创建/修改交易, 另一个是创建收款单, 因为需要先根据银行订单号进行除重. 这里面, 交易因为根据订单号, 会被散列至 1000 个表中, 而订单号的增长是顺序的, 所以连续出现的交易创建请求要相隔 1000 个才会被散列至同一表中, 基本不可能出现冲突, 只有收款单,因为是单表, 所以出现这种问题的概率较高.

解决方案

1: 最简单的方案是使用 READ COMMITTED 隔离级别, 由于我们所有关键操作之前, 都使用了 SELECT FOR UPDATE 来锁定记录本身, 因此数据仍然是安全的, 而且不会造成上面提到的这种问题, 但 READ COMMITTED 对性能的影响巨大.因此还需要考虑是不是值得

2: 还有一个办法是修改逻辑, 因为创建收款单的逻辑是判断如果已经有数据了, 那么什么逻辑都不用做, 直接返回订单已经存在, 也就是说 SELECT FOR UPDATE 其实不是 FOR UPDATE, 而是 FOR INSERT, 因此这里可以不用 SELECT FOR UPDATE, 而是直接 SELECT, 同时捕捉插入主键重复这个错误, 对主键重复也报告一个订单已经存在即可, 创建/修改交易的逻辑比较复杂, 如果发现有数据后,还需要 UPDATE , 这里不能使用这个办法, 但好在因为被散列到 1000 张表中, 冲突的机会微乎其微. 可以忍了, 我比较倾向使用这种方案, 但可能还需要再考虑下可能有没有其他问题.

从这里看， 有几个地方比较的有意思： 1. SELECT ... FOR UPDATE在不同的隔离级别下有不通的效果

在调查这个问题的时候发现：

1. 在autocommit=1的情况下， SELECT ...FOR UPDATE是不会锁记录的； 这个比较好理解， 如果autocommit=1, 则会自动的提交事务。

2. 事务在取得S锁之前必须先得到IS锁， 在取得X锁之前必须先得到IX锁

3. 用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。

 例如

session1:  select where id=1 lock in share mode;

session2:select where id=1 lock share mode;

session1: update where id=1 （等待session2)

session2: update where id=1 (等待session1)

发送死锁

2. 查看锁争用情况

  1.   show status like 'innodb_row_lock%';可以看出行锁争用情况

  2. 执行语句   CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB; 后可以收集所有发生的争用情况， 从而可以较好的查看相关争用信息

 

3. 区分度和数据量对innodb索引使用的影响

从开始使用mysql起， 就觉得：
1. 索引的使用对innodb的性能的影响是很大的， 在做explain的时候， 当extra中出现了filesort的时候一般就意味者低效率和应当优化sql语句 
2. 在select语句中， where中用到的条件字段order by中的字段最好是同一个(些)字段(假定只有都只有一个(些)字段)， 且这个字段最好是有索引的， 这样在explain的时候， type就会是index, 且extra中不会有filesort; 如果where中的字段和order by的字段不一样的话， 则可能出现filesort, 从而会导致低效

在实际中遇到了一个例子， 发现上面的理解其实是比较片面的， 这个例子的大体情况是：

表t_mytable中有字段F_time_1和F_time_2,分别是datatime， 且分别都有索引；字段F_pid是主键 
1. 运行sql语句 SELECT ... FROM t_mytable WHERE F_pid='val' AND F_time_1>= 'time1' AND F_TIME_1 <= 'time2' ORDER BY F_time_1， 记录条目是12条， 但发现效率很低；觉得很奇怪：这个语句的效率应该高才对的 
2. 尝试着改了一下sql语句为SELECT ... FROM t_mytable WHERE F_pid='val' AND F_time_2>= 'time1' AND F_TIME_2 <= 'time2' ORDER BY F_time_1，记录条目是8条，效率很高；

 

效率上的差别让人觉得有些奇怪， 觉得是违反一直以来的直觉的， 于是再explain了两条语句， 对比了一下结果，发现一个很大的不一样是第一条语句type是index， rows的结果是100w级别； 而第二条语句的type为range, extra中有filesort， 但是rows那一栏大概10w级。想了一下， 问题应该在这个记录的数量上了：
1. 第一条语句中， where中字段和order by的字段一致，被优化成index类型， 但是由于F_time_1的区分度很低， 符合F_time_1条件的记录很多， 结果导致扫描索引很慢， 虽然省掉了filesort，但效率依旧很低 
2. 第二条语句中， where中的字段和order by字段不一致， mysql采用了range扫描加filesort的方式， 这样以来，扫描的记录大大减少，虽然后面有个filesort, 但是由于符合条件的记录少， 内排序就得了， 结果效率反而高很多

总结如下：
1. explain的结果有时候会骗人的， 采用具有最优explain结果的语句效率不一定最好， 因为语句的执行效果受到数据量的影响 
2. 在通过explain优化语句时， 需要把数据的区分度与考虑进来， 尽量使用区分度好的字段； 不要为了追求漂亮的explain的结果而建立区分度不大的索引来使用