MySQL(十)—线上MySQL锁超时了怎么办？update操作怎么上了个表锁啊？

文章目录

- 一、异常错误
- 二、尽量还原这个错误
- - 1.准备数据
  - 2.阐述业务
  - 3.分析原因
- 三、线上如何解决这个异常呢？
- - 1.设置锁超时时间
  - 2.使用online ddl方式建立唯一索引
  - 3.动态增加服务节点

一、异常错误

先上一个出现异常的截图

错误的原因描述是Lock wait timeout exceeded；try restarting transaction。大致的意思就是锁超时等待；尝试重新执行该事务。当时还没有深入了解过MySQL的锁机制，现在再次整理时会想到，执行sql的时候上锁的范围变大了，可能上了一个表锁。

二、尽量还原这个错误

因为涉及到项目中的sql还需要讲业务，所以选择重新建一个简单的表，然后还原下这个错误

1.准备数据

软件环境：MySQL5.7
建表语句，只有一个主键索引

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `score` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据如下

2.阐述业务

以姓名name和年龄age修改分数score，很简单的一个sql语句

update t_user set  score=2 where name='小1' and age=1;

但是别看这么简单的sql语句，在线上就足以引发超时了。

测试锁超时

//session1,注意没有提交事务
BEGIN;
update t_user set  score=2 where name='小1' and age=1;

//session2
BEGIN;
update t_user set  score=4 where name='小3' and age=1;

在navicat上测试，session2会出现锁超时现象

3.分析原因

当执行update操作时，会给当前的数据加锁。InnoDB提供了行锁的机制，相对于myisam的表锁，锁的粒度降低，提供了并发的能力。但是如果使用不当也会出现表锁的现象

为什么呢？

再把上面session1的sql语句拿出来

update t_user set  score=2 where name='小1' and age=1;

mysql执行where name=‘小1’ and age=1时，因为name和age字段都没有加索引，所以会回表查找记录，循环遍历表数据，注意，update在遍历时会将遍历到的数据都会加上锁。又因为name和age都不是唯一索引，所以mysql不知道当前表到底有多少行是name=‘小1’ and age=1，只能全表扫描，这时就出现了表锁了。

又因为上面的例子session1是一个长事务，长时间占用锁不释放，所以session2根据条件where name=‘小3’ and age=1更新时只能去等待锁，直到session1事务提交事务锁，session2才会执行。

然而事务等待是有超时时间的，所以就出现了锁超时现象。

三、线上如何解决这个异常呢？ 1.设置锁超时时间

将锁等待的时间稍微再延长一点，但是治标不治本

//设置锁超时时间
SET innodb_lock_wait_timeout=60
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

2.使用online ddl方式建立唯一索引

我们知道在线上直接执行Alter table add key 操作会发生锁表操作，具体原理如下。参考文章
官方文档

例如，对表 A 进行 DDL 的具体过程如下：

1.按照表 A 的定义新建一个表 B
2.对表 A 加写锁
3.在表 B 上执行 DDL 指定的操作
4.将 A 中的数据拷贝到 B
5.释放 A 的写锁
6.删除表 A
7.将表 B 重命名为 A
8.如果表 A 数据量比较大，拷贝到表 B 的过程会消耗大量时间，并占用额外的存储空间。此外，由于 DDL 操作占用了表 A 的写锁，所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务。

因此，MySQL 5.6 增加了 online DDL，允许在不中断数据库服务的情况下进行 DDL 操作。

执行如下语句

ALTER TABLE t_user add UNIQUE KEY(`name`,age),ALGORITHM=INPLACE, LOCK=NONE;

需要注意的是，执行该语句只是不会影响后面事务的DML操作，但是如果前面的DML操作事务锁没有释放该语句也会锁等待

所以这种办法在事务并发量小的情况下可以直接执行

3.动态增加服务节点

写到这里可能有读者问了，你的update语句不能根据id进行更新吗，它就是一个唯一索引。是的！你想的完全没有问题，出现上面的原因是因为特殊业务场景没有返回前端id，所以直接用的其他字段标识了一行记录。

如果上面的online ddl还解决不了问题的话，只能去增加一个节点，修改nginx配置，使用nginx热更新机制新的节点，根据id更改的代码的节点动态添加到集群中。

可以参考这篇博客：https://blog.csdn.net/weixin_39805802/article/details/105982482https://blog.csdn.net/weixin_39805802/article/details/105982482 主要是用的nginx reload命令

如果使用k8s加docker的话，可能更容易实现一些。

MySQL(十)—线上MySQL锁超时了怎么办？update操作怎么上了个表锁啊？

Linux相关栏目本月热门文章