问题描述
偶尔应用日志会打印锁表超时回滚
org.springframework.dao.CannotAcquireLockException:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
mysql锁机制
锁的划分
按粒度划分
按照锁的粒度来划分可以将锁分为以下三种:
-
全局锁:锁的整个database。由MySQL的SQLlayer层实现
-
表级锁:锁的是某个table。由MySQL的SQLLayer层实现
-
行级锁:锁的是某行数据,也可能锁定行之间的间隙。由存储引擎实现,比如InnoDB等
按锁的功能划分
根据锁的功能可以将锁分为:
-
共享读锁
-
排他写锁
按锁的实现方式划分
根据锁的实现方式可以将锁分为:
-
悲观锁
-
乐观锁
表级锁和行及锁的区别
-
表级锁:开销小,加锁快,锁定粒度大,发生锁冲突概率高,并发度低
-
行级锁:开销大,加锁慢,会出现死锁,锁定粒度小,发生锁冲突概率低,并发度高
表级锁
mysql表级锁分为两种:
-
表锁
-
元数据锁
表锁
查看表锁的争用状态变量
show status like 'table%';
-
Table_locks_immediate:产生表级锁定的次数
-
Table_locks_waited:出现表级锁定争用而发生等待的次数
表锁的两种表现形式:
-
表共享读锁
-
表独占写锁
手动添加表锁
lock table 表名 read(共享读锁)/write(独占写锁), 表名n read(共享读锁)/write(独占写锁);
查看表锁情况
show open tables
删除表锁
unlock tables;
演示
创建演示表并插入数据
CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) DEFAULT NULL,
age int(11) DEFAULT NULL,
love varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
);
INSERT INTO mylock (id,name,age,love) VALUES (1, 'a', 1, 'a');
INSERT INTO mylock (id,name,age,love) VALUES (2, 'b', 1, 'b');
INSERT INTO mylock (id,name,age,love) VALUES (3, 'c', 1, 'c');
读锁操作:
- session1:对mylock表添加共享读锁
lock table mylock read;
- session1:查询mylock表
select * from mylock;
- session2:可正常查询mylock表
select * from mylock;
- session1:不能查询其他没有锁定表
select * from 其他没有锁定表的表名称;
- session2:可正常查询、更新没有锁定的表
select * from 其他没有锁定表的表名称;
- session1:更新、插入锁定表会提示错误
INSERT INTO mylock (id,name,age,love) VALUES(4, 'd', 1, 'd');
UPDATE mylock SET NAME = 'e' WHERE id = 3;
- session2:更新、插入锁定表会一直等待获得锁。当session1 unlock tables解除锁定后会正常执行
INSERT INTO mylock (id,name,age,love) VALUES(4, 'd', 1, 'd');
UPDATE mylock SET NAME = 'e' WHERE id = 3;
写锁操作:
- session1:对mylock表添加独占锁
lock table mylock write;
- session1:对锁定表执行查询、插入、更新均可行
select * from mylock;
insert into mylock(id,name,age,love) values(5, 'e', 1, 'e');
update mylock set name = 'f' where id = 4;
- session2:对锁定表执行查询、插入、更新会一直等待
select * from mylock;
insert into mylock(id, name,age,love) values(5, 'e', 1, 'e');
update mylock set name = 'f' where id = 4;
- session1:释放锁定表
unlock tables;
- seesion2:第3步操作正常结束
元数据锁
从MySQL 5.5开始引入MDL,当对一张表做增删改查操作时,将加MDL读锁;当对表结构做变更操作时,加MDL写锁
- session1:开始事务
begin;
- session1:执行查询表sql将会加MDL读锁
select * from mylock;
- session2:执行更新表结构,将会被阻塞
alert talble mylock add f int;
- session1:提交事务,或者rollback回滚事务,释放读锁
commit;
- session2:第3步的更新表结构将会被执行
行级锁
mysql行级锁的实现是由存储引擎实现,InnoDB存储引擎就支持行级锁。InnoDB行锁是给索引上的索引项加锁来实现,因此只有通过索引条件检索的数据,InnoDB才能使用行级锁,否则将使用表锁
按照锁定范围,将InnoDB的行级锁分为以下三种:
-
记录锁(Record Locks):锁定某行记录,执行SQL语句的条件必须是主键或唯一索引列,并且必须是精确匹配(=)
-
间隙锁(Gap Locks):锁定一段区间,此区间内的数据可以已经存在也可能还没有。例如SELECT * FROM table WHERE age > 60 FOR UPDATE; 会
锁定所有大于60的数据,之后插入一条数据库中没有的age为110的数据一样会被阻塞。间隙锁基于非唯一索引 -
临键锁(Next-Key Locks):一种特殊的间隙锁。在每个数据行的非唯一索引列上都有一把临键锁,当某个事务持有该数据行的临键锁时,会锁住一段左开右闭的区间。例如一张表有age字段,值为10、26、36、46、56的五行数据,age字段为普通索引,这五行数据存在如下范围的临键锁:范围为[负无穷,100)、范围为[10,26)
、范围为[26,36)、范围为[36,46)、范围为[46,56)、范围为[56,正无穷)。当执行UPDATE table SET name = Vladimir WHERE age = 26;,将
获取范围为[10,36)的临键锁,之后执行INSERT INTO table VALUES(100, 30, ‘Ezreal’);,将会被阻塞
实验索引对InnoDB行锁的影响
- session1:关闭自动提交事务
SET autocommit=0;
- session1:执行增、删、改操作中的一种,并且不走索引。将触发表级锁
delete from mylock where name = 'b';
insert into mylock(id,name,age,love) VALUES (13, 'm', 1, 'm');
update mylock set love = 'b' where name = 'm'
- session2:对同一张表执行增、删、改,将被阻塞,等待获取表锁,如果session1不提交事务释放锁,session2会一直被阻塞直到超时
delete from mylock where name = 'h';
insert into mylock(id,name,age,love) VALUES (14, 'n', 1, 'n');
update mylock set love = 'b' where name = 'h';
- session1:提交事务或者回滚,将释放表级锁
commit;
- session2:第3步执行操作如果还没有超时,将会执行
为表添加索引字段
ALTER TABLE mylock ADD INDEX mylock_index_1 (name,love);
执行如下操作步骤:
- session1:执行增、删、改操作中的一种,并且走索引。将触发表行级锁
delete from mylock where name = 'c';
insert into mylock(id,name,age,love) VALUES (13, 'm', 1, 'm');
update mylock set love = 'h' where name = 'm'
- session2:执行执行增、删、改操作中的一种,如果操作的行不在第一步锁定的行中,将能正常执行
delete from mylock where name = 'l';
insert into mylock(id,name,age,love) VALUES (16, 'o', 1, 'o');
update mylock set love = 'h' where name = 'c'
临键锁实验
前提数据如下age为普通索引字段
- session1:关闭自动提交事务
SET autocommit=0;
- session1:根据普通索引删除数据触发临键锁,锁定范围为[2, 9)
delete from mylock where age = 5;
- session2:关闭自动提交事务
SET autocommit=0;
- session2:插入[2, 9)之间的age数据将会被阻塞等待获取锁,之外的数据可以正常插入
insert into mylock(id,name,age,love) VALUES (34, 'm', 8, 'ddm');
commit
注意:删除表数据时,如果条件中出现不在索引中字段时,可能不会走索引,因此设置索引字段时需要注意
总结:当存在没有走索引的增、删、改将触发表级锁,如果此事务花费时间较长,可能导致其他事务对表的增、删、改被阻塞,甚至超时回滚。因此合理设置索引字段很重要
解决方案
在了解了mysql锁相关知识后,我们可以根据锁产生的条件,找到超时的原因
如何查看锁及被锁住的SQL
INNODB_TRX
此表记录了当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX;
INNODB_LOCKs
此表记录了当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs;
INNODB_LOCK_waits
此表记录了锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits;
查询锁住的SQL及事务Id及事务线程Id
select
a.trx_id 事务id ,
a.trx_mysql_thread_id 事务线程id,
a.trx_query 事务sql
from
INFORMATION_SCHEMA.INNODB_LOCKS b,
INFORMATION_SCHEMA.innodb_trx a
where
b.lock_trx_id=a.trx_id;
死锁处理
如果出现死锁临时解决方案可以在mysql会话中执行如下命令
kill 事务线程id;
解决方案一:检查索引
查看锁住的SQL,检查被锁表结构是否包含索引,由于行锁需要走索引,如果表不包含索引,将会走表锁,也就是说如果某个事务对表执行删除、更新、新增操作将锁住整张表,如果这时有另一个事务要执行删除、更新、新增操作将会被阻塞。当前一个事务比较耗时,后面事务很有可能超时
检查表索引设置是否正常,只有执行删除、更新、新增操作的SQL走索引才能触发行锁,否则将使用表锁。因此正确设置索引也很重要。尤其在删除操作时,如果条件只包含部分索引字段很有可能不会走索引,具体会不会走索引可以查看SQL执行计划
EXPLAIN 执行的SQL语句;
重点关注type字段,常见类型有:
-
system:表只有一行记录,const类型的特例
-
const:通过主键索引或唯一索引一次就找到,只匹配一行数据
-
eq_ref:主键或唯一索引扫描,对于每个索引键表中只有一条记录与之匹配
-
ref:使用非唯一索引进行查找,可以包含不在索引中的字段。可能返回多行匹配数据,但如果查询数据量占总数据的比列过高将会变为ALL
-
range:根据索引检索给定范围数据,一般条件中出现between、<、>、in等
-
index:索引扫描,与ALL区别是index只遍历索引数
-
ALL:全表扫描
key_len:表示索引中使用的字节数,查询中使用的索引的最大可能长度,并非实际使用长度,理论上长度越短越好
解决方案二:检查超时时间是否合理
运行如下命令获取当前mysql设置的锁等待超时时间(默认50秒)
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
根据自己业务确定最佳超时时间,设置过小可能会导致很多事务超时取消。过大可能会导致很多无法完成的死锁事务积压,影响到数据库的并发处理能力。设置锁等待超时方式如下
- 设置当前session锁等待超时时间
set innodb_lock_wait_timeout=1500;
- 设置全局锁等待超时时间,对于修改之后新打开的session生效
set GLOBAL innodb_lock_wait_timeout=1500;
解决方案三:检查长事务是否合理
长事务中锁定表数据较长,可能会导致其他事务操作同一条数据时超时。通常建议将事务的粒度做的尽量小,避免长事务,这样系统的并发度、处理效率都会高很多,而且锁超时的现象也会少很多