错误描述:[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
场景描述:多线程场景下,先删除再更新操作引发的事务锁超时。
解决思路:下述是排查所需SQL
mysql排查:
>查询当前事务
select * from information_schema.innodb_trx
>查询当前锁
SELECT * FROM information_schema.INNODB_LOCKS;
>查询当前执行线程列表
show processlist;
>终止当前执行线程
kill 11111;
>查询锁的超时时间
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
>设置锁的超时时间
SET GLOBAL innodb_lock_wait_timeout=300;
>查询当前DB隔离级别
show variables like '%isolation%'
>设置DB隔离级别。全局设置将session改为GLOBAL
//设置read uncommitted级别:
set session transaction isolation level read uncommitted;
//设置read committed级别:
set session transaction isolation level read committed;
//设置repeatable read级别:
set session transaction isolation level repeatable read;
//设置serializable级别:
set session transaction isolation level serializable;
>测试环境搭建
CREATE TABLE `lock_test` (
`id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into lock_test (id,name,age) values (1,'xiaoming',23),(2,'xiaoming2',23),(3,'xiaoming3',23)
select * from lock_test limit 10;
>测试-窗口1
begin;
delete from lock_test where name = 'xiaoming';
ROLLBACK;
COMMIT;
>测试-窗口2
update lock_test set age = 30 where name = 'xiaoming';
场景重现:
1. 设置DB隔离级别是RC
2. 设置DB获取锁超时时间10(方便测试)
3. 创建测试所用表
4. 开始测试:窗口1执行Rollback;之前的SQL,但不提交也不会滚。窗口2执行统一条件的更新SQL(这里要注意:条件是否是主键要区别对待)。
5. 两种场景发生:a. 在执行窗口2后,10s内执行窗口1的事务提交。结果:可以执行。b. 在窗口2执行后10s内,窗口1仍旧没有提交事务。结果:报[Err] 1205 - Lock wait timeout exceeded; try restarting transaction。
原因解析:
RC级别,读已提交,通俗说,当一个线程(或者事务),在执行update和delete操作时,将对当前行进行行锁,其他线程(或事务)必须要等到原有线程执行完毕释放锁之后才可执行。(这里可能会涉及到间隙锁的问题)
参考:https://blog.csdn.net/weixin_43209060/article/details/103619064(RR级别Lock wait timeout)
后补: 还有一种情况,当update|delete操作时,条件列(where)不是索引列,会导致表锁。如果表锁,上述这种情况发生的概率就会大大提高。