一则关于表新增与更新锁等待时间过长的优化

本文分析了一个MySQL表在更新时出现长时间锁等待的问题。通过对比不同更新策略的影响,发现使用主键而非状态索引能有效避免锁冲突。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

近期有一个表多次出现 锁等待时间长的问题,个人认为这个案例值得分享,这里记录一下。
现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操作
在会话1 中,可以看到,insert 后一直没有commit,

在2会话中执行以下SQL:
可以看到在会话1 没有commit 的情况下,进行update,一直没有成功,直到锁等待时间过长。

会话2操作

可以看到,会话2是一直等待到锁超时。因为会话1一直没有提交。说明有锁冲突了。

为了再次印证我使用ID键值更新不会有锁冲突,测试如下:

1 会话重新插入数据如下:
插入的值 create_time=1535534400 (2018-08-29 17:20:00 取自于昨天的binlog )

测试可以插入,更新 会话1

2会话:

先rollback,为了印证2个会话是否在同一时间,我在查询中查看了当前日期。可以看到在会话1 没提交时,会话2更新部分数据是不会有锁等待的。
这次更新没有锁等待,是因为我的更新,并没有走索引idx_status,我使用了id 主键做为条件更新,这样走的是主键索引,和前面的插入数据时,更新idx_status 索引没有锁冲突了。

会话2更新数据

上面的索引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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值