一、异常错误
先上一个出现异常的截图
错误的原因描述是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的话,可能更容易实现一些。