近期有一个表多次出现 锁等待时间长的问题,个人认为这个案例值得分享,这里记录一下。
现SQL 如下:
update bank_credit
SET batch_no = '196139',
tx_date = '20180829',
tx_time = '172000',
seq_no = '704424',
`status` = 1,
update_time = 1535534400
WHERE STATUS=0;
先把细节说明一下:
1、表结构如下:
CREATE TABLE `bank_credit` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`batch_no` char(6) DEFAULT NULL COMMENT '批次号',
`tx_date` char(8) DEFAULT NULL COMMENT '本批次交易日期',
`tx_time` char(6) DEFAULT NULL COMMENT '本批次交易时间',
`seq_no` varchar(10) DEFAULT NULL COMMENT '本批次交易流水号',
......
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
`update_user` int(11) DEFAULT NULL COMMENT '更新人',
`update_time` int(10) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
我们再来看看执行计划(真表名有修改)
我们可以找到,update 语句一次性更新数量有 500之多,并且这个更新是走索引:idx_status,
也就是说,mysql 在进行更新时,先会在idx_status 中先做锁标识,如果已有其它事务占用锁,那么这个更新会一直等待。
而索引idx_status 的字段值只有4-8个左右,常用的只有4个状态值。所以说重复率是非常高的。
一个索引值对应的记录数很多(重复率高),只要有其它记录占用了此索引,update 时就有锁等待。
这就有一个问题,如果是插入一条值 status(0) ,也会在此索引上进行索引更新。这就是锁冲突。
经分析binlog 日志也可以看到,在17:40之前,没有update .对这个表的操作,全是 insert,
而我们邮件预警时间,是在 17:20. 说明这段时间内,所有的update申请锁的操作都因为超时取消。
(insert操作是在大事务中)
为了印证我的分析是否正确,我在测试1中做了以下测试:
开启2 个会话。
(以下测试为了说明是在同一个时间线内,2个会话中,增加了显示当前日期)
在1会话中执行如下:
在会话1 中,可以看到,insert 后一直没有commit,
在2会话中执行以下SQL:
可以看到在会话1 没有commit 的情况下,进行update,一直没有成功,直到锁等待时间过长。
可以看到,会话2是一直等待到锁超时。因为会话1一直没有提交。说明有锁冲突了。
为了再次印证我使用ID键值更新不会有锁冲突,测试如下:
1 会话重新插入数据如下:
插入的值 create_time=1535534400 (2018-08-29 17:20:00 取自于昨天的binlog )
2会话:
先rollback,为了印证2个会话是否在同一时间,我在查询中查看了当前日期。可以看到在会话1 没提交时,会话2更新部分数据是不会有锁等待的。
这次更新没有锁等待,是因为我的更新,并没有走索引idx_status,我使用了id 主键做为条件更新,这样走的是主键索引,和前面的插入数据时,更新idx_status 索引没有锁冲突了。
上面的索引idx_status 因为每一个值,都对应了多条数据记录。所以在insert 也需要锁定某一个值的索引(insert 时status =0)
这时就无法更新此索引值了。
这点很象是【oracle】 中的 【Bitmap 位图索引】。我们常说bitmap 只适合在数据仓库中使用,也是这个道理。
所以上面的场景中,如果此表的update 操作不是在一个大事务中。就可以把update 更新条件,更改为按id键值进行。
(先取出要更新的id 列表,再按id 做为条件更新
(伪代码)
建议修改成 bank_credit_id_list= select id from bank_credit;
#可以一次性取50个id,进行循环
for list_id in bank_credit_id_list:
update bank_credit SET batch_no = '196139',
tx_date = '20180829', tx_time = '172000', seq_no = '704424',
`status` = 1,update_time = 1535534400
WHERE id in list_id
)