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

最新推荐文章于 2023-10-08 15:28:02 发布

边城cn

最新推荐文章于 2023-10-08 15:28:02 发布

阅读量2.5k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MYSQL 开发语言

本文链接：https://blog.youkuaiyun.com/miyatang/article/details/82454999

MYSQL 同时被 2 个专栏收录

30 篇文章

订阅专栏

开发语言

7 篇文章

订阅专栏

本文分析了一个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

）