Mysql并发DML锁等待的优化

1、概述

​ 在Mysql数据库中，慢SQL、长事务、没执行commit的DML操作，都有可能导致并发会话的阻塞，还可能发生锁等待报错。那么这一类的报错应该如何优化呢？

通常有三种方案可以考虑：

1）优化SQL性能，SQL执行快了，阻塞时间自然就短了；

2）使用合适的索引，缩小数据上锁范围；

3）调高锁等待超时时间参数。

本文主要介绍第2种方案，下面我们来看看实验效果

2、实验

场景1：不使用索引

在没有索引和主键的表上做update和delete操作，锁定的是表中所有数据，无论sql中是否含有过滤条件。

先准备测试数据

create table tb_test_lock1(id int ,c1 varchar(10)); insert into tb_test_lock1 values(1,'aaa'); insert into tb_test_lock1 values(2,'bbb'); insert into tb_test_lock1 values(3,'ccc'); insert into tb_test_lock1 values(4,'ddd'); insert into tb_test_lock1 values(5,'eee'); commit;

在会话1中修改id=1的数据，不提交，如下图。

在这个会话中，修改Id=1的记录，由于没有索引，使用的是全部扫描的方式检索数据，事务结束前会锁定整表数据，而不是只锁定Id=1的1条数据。

#会话1 start transaction; update tb_test_lock1 set c1='a1' where id=1;

与此同时，在会话2中修改id=5的数据，可以看到，因为会话1锁定了整表数据，修改id=5的sql被阻塞，在超时后触发报错。

#会话2 update tb_test_lock1 set c1='e5' where id=5;

场景2：创建索引，缩小锁范围

合适的索引，能够缩小锁的范围，减少并发锁冲突

create index idx_id on tb_test_lock1(id);

在会话1中修改id=1的数据，不提交，如下图。

#会话1 start transaction; update tb_test_lock1 set c1='a1' where id=1;

与此同时，在会话2中修改id=5的数据，如下图。我们看到会话2执行成功了，这表明，会话1并没有阻塞会话2。这是因为我们创建了索引idx_id，会话1按索引idx_id检索数据，数据上锁的范围变小了，只锁定Id=1的记录。而会话2请求的是Id=5的记录锁，2者不冲突。

#会话2 update tb_test_lock1 set c1='e5' where id=5;

实验表明，创建合适的索引，并使用索引字段做检索条件，可以有效解决并发会话阻塞问题，避免锁等待报错。

​

DLM 2026.7.21