mysql锁表问题

问题描述

偶尔应用日志会打印锁表超时回滚

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');

读锁操作:

  1. session1:对mylock表添加共享读锁
lock table mylock read;
  1. session1:查询mylock表
select * from mylock;
  1. session2:可正常查询mylock表
select * from mylock;
  1. session1:不能查询其他没有锁定表
select * from 其他没有锁定表的表名称;
  1. session2:可正常查询、更新没有锁定的表
select * from 其他没有锁定表的表名称;
  1. session1:更新、插入锁定表会提示错误
INSERT INTO mylock (id,name,age,love) VALUES(4, 'd', 1, 'd');

UPDATE mylock SET NAME = 'e' WHERE id = 3;
  1. session2:更新、插入锁定表会一直等待获得锁。当session1 unlock tables解除锁定后会正常执行
INSERT INTO mylock (id,name,age,love) VALUES(4, 'd', 1, 'd');

UPDATE mylock SET NAME = 'e' WHERE id = 3;

写锁操作:

  1. session1:对mylock表添加独占锁
lock table mylock write;
  1. session1:对锁定表执行查询、插入、更新均可行
select * from mylock;

insert into mylock(id,name,age,love) values(5, 'e', 1, 'e');

update mylock set name = 'f' where id = 4;
  1. session2:对锁定表执行查询、插入、更新会一直等待
select * from mylock;

insert into mylock(id, name,age,love) values(5, 'e', 1, 'e');

update mylock set name = 'f' where id = 4;
  1. session1:释放锁定表
unlock tables;
  1. seesion2:第3步操作正常结束
元数据锁

从MySQL 5.5开始引入MDL,当对一张表做增删改查操作时,将加MDL读锁;当对表结构做变更操作时,加MDL写锁

  1. session1:开始事务
begin;
  1. session1:执行查询表sql将会加MDL读锁
select * from mylock;
  1. session2:执行更新表结构,将会被阻塞
alert talble mylock add f int;
  1. session1:提交事务,或者rollback回滚事务,释放读锁
commit;
  1. 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行锁的影响
  1. session1:关闭自动提交事务
SET autocommit=0;
  1. 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'
  1. 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';
  1. session1:提交事务或者回滚,将释放表级锁
commit;
  1. session2:第3步执行操作如果还没有超时,将会执行

为表添加索引字段

ALTER TABLE mylock ADD INDEX mylock_index_1 (name,love);

执行如下操作步骤:

  1. 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'
  1. 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为普通索引字段
在这里插入图片描述

  1. session1:关闭自动提交事务
SET autocommit=0;
  1. session1:根据普通索引删除数据触发临键锁,锁定范围为[2, 9)
delete from mylock where age = 5;
  1. session2:关闭自动提交事务
SET autocommit=0;
  1. 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;

解决方案三:检查长事务是否合理

长事务中锁定表数据较长,可能会导致其他事务操作同一条数据时超时。通常建议将事务的粒度做的尽量小,避免长事务,这样系统的并发度、处理效率都会高很多,而且锁超时的现象也会少很多

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/511052.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

C刊级 | Matlab实现GWO-BiTCN-BiGRU-Attention灰狼算法优化双向时间卷积双向门控循环单元融合注意力机制多变量回归预测

C刊级 | Matlab实现GWO-BiTCN-BiGRU-Attention灰狼算法优化双向时间卷积双向门控循环单元融合注意力机制多变量回归预测 目录 C刊级 | Matlab实现GWO-BiTCN-BiGRU-Attention灰狼算法优化双向时间卷积双向门控循环单元融合注意力机制多变量回归预测效果一览基本介绍程序设计参考…

ArcGIS Pro打不开Excel?Microsoft驱动程序安装不上?

刚用ArcGIS pro的朋友们可能经常在打开xls或者xlsx文件的时候都会提示&#xff0c;未安装所需的Microsoft驱动程序。 怎么办呢&#xff1f;当然&#xff0c;按照提示装一下驱动就会好吗&#xff1f;有什么状况会出现&#xff1f;有什么临时替代方案呢&#xff1f; 全文目录&a…

C++的并发世界(二)——初识多线程

0.引言 C的并发世界&#xff08;零&#xff09;和C的并发世界&#xff08;一&#xff09;的东西真的对于我这种初学者难以理解&#xff0c;我确定从第一个多线程案例进行学习归纳总结。 1.多线程的目的 ①将耗时的任务进行分解&#xff0c;进行实时响应;   ②充分利用多核CP…

ICMP,ARP协议,免费ARP

ICMP:ping检测链路通断&#xff0c;到目的地的连通性。 internet网控制消息协议&#xff0c;工作在网络层。 ttl超时&#xff0c;也是icmp报文。

LeetCode刷题记(一):1~30题

1. 两数之和 给定一个整数数组 nums 和一个整数目标值 target&#xff0c;请你在该数组中找出 和为目标值 target 的那 两个 整数&#xff0c;并返回它们的数组下标。 你可以假设每种输入只会对应一个答案。但是&#xff0c;数组中同一个元素在答案里不能重复出现。 你可以…

直流电源电路(下)

直流电源电路&#xff08;下&#xff09; 综述&#xff1a;本篇文章讲述了直流电源电路的BOOST电路以及DC-DC电路的组成原理。 四、BOOST电路 原理&#xff1a;当mos导通时&#xff0c;电源上的电流给电感充电&#xff0c;通过mos管构成回路&#xff0c;电容放电给负载供电&…

从零实现一个Http服务器

HttpServer HTTPServer项目是一个基于C编写的简单的HTTP服务器实现&#xff0c;用于处理客户端的HTTP请求并提供相应的服务。该项目使用了Socket编程来实现服务器与客户端之间的通信&#xff0c;通过监听指定的端口并接受客户端连接&#xff0c;然后解析HTTP请求并生成对应的H…

Octavia Venture 成立,打造数十亿美元规模的 AI 价值体系

​随着 OpenAI 相继发布 ChatGPT、Sora 等 AIGC 大模型后&#xff0c;AI 赛道的发展迎来了一轮又一轮的热潮&#xff0c;这也让极具想象力的 AI 赛道涌入大量资金&#xff0c;比如英伟达股票市值短时间内从 1 万亿美元暴涨至 2 万亿美元&#xff0c;就是最好的佐证。当然&#…

Maven依赖管理项目构建工具

一、Maven简介 1、为什么学习Maven 1.1、Maven是一个依赖管理工具 ①jar 包的规模 随着我们使用越来越多的框架&#xff0c;或者框架封装程度越来越高&#xff0c;项目中使用的jar包也越来越多。项目中&#xff0c;一个模块里面用到上百个jar包是非常正常的。 比如下面的例…

表白墙项目(JAVA实现)

1、在html里 class使用. id使用# 2、记得引入响应依赖&#xff08;举例lombok&#xff09; 3、messageController package com.example.demo.demos.web; import org.springframework.util.StringUtils; import org.springframework.web.bind.annotation.RequestMapping; i…

惊喜!这一国产数据库认证考试限免了!

今年第一个季度过去了&#xff0c;又到春暖花开时&#xff0c;群里的小伙伴开始躁动不安&#xff0c;焦虑加倍。 有考虑被 cloud 淘汰的&#xff0c;有考虑被共享 emp 的&#xff0c;还有问粗粮 car 能不能当专车开的。 但技术人&#xff0c;更多时间还是在讨论正能量&#xff…

使用纯注解的方式管理bean对象

前置知识&#xff1a; Component , Repository , Controller , Service 这些注解只局限于自己编写的类&#xff0c;而Bean注解能把第三方库中的类实例加入IOC容器中并交给spring管理。 其中&#xff1a; Component一般用于公共类 Repository 用于dao数据访问层 Service 用…

基本电路理论入门讲解系列-电路和电路模型

&#x1f308;个人主页&#xff1a;会编程的果子君 &#x1f4ab;个人格言:“成为自己未来的主人~” 电路 电路概念&#xff1a;把若干个电气设备和电气元件按照一定的方式组合起来&#xff0c;构成电流的通路&#xff0c;此路径的总体称为电路。在电子通信&#xff0c;自…

Spring IoCDI(2)

IoC详解 通过上面的案例, 我们已经知道了IoC和DI的基本操作, 接下来我们来系统地学习Spring IoC和DI的操作. 前面我们提到的IoC控制反转, 就是将对象的控制权交给Spring的IoC容器, 由IoC容器创建及管理对象. (也就是Bean的存储). Bean的存储 我们之前只讲到了Component注解…

SpringBoot项目使用SpringSecurity和JWT实现登录功能

使用SpringSecurity,Redis实现登录功能 首先&#xff0c;思路如下&#xff1a; 登录 ①自定义登录接口 调用ProviderManager的方法进行认证 如果认证通过生成jwt 把用户信息存入redis中 ②自定义UserDetailsService 在这个实现类中去查询数据库 注意配置passwordEncoder为BCry…

数据结构进阶篇 之 【插入排序】详细讲解(直接插入排序,希尔排序)

千万不要因为一件事不会做而失去信心&#xff0c;你又不是只有这一件事不会&#xff0c;你还有很多呢 一、插入排序 1.直接插入排序 InsertSort 1.1 基本思想 1.2 实现原理 1.3 代码实现 1.4 直接插入排序的特性总结 2.希尔排序 ShellSort 2.1 基本思想 2.2 实现原理 …

视觉Transformer和Swin Transformer

视觉Transformer概述 ViT的基本结构&#xff1a; ①输入图片首先被切分为固定尺寸的切片&#xff1b; ②对展平的切片进行线性映射&#xff08;通过矩阵乘法对维度进行变换&#xff09;&#xff1b; ③为了保留切片的位置信息&#xff0c;在切片送入Transformer编码器之前&…

基于vue实现动态table

1、代码 <div style"height: 600px; overflow: scroll;"> <!-- height: 600px; overflow: scroll;作用是超出页面可以滑动 --><div ng-repeat"row in entity.procedureList"><cb-title title"工序{{row.procedireLocation}}&quo…

【保姆级讲解下MySQL中的drop、truncate和delete的区别】

&#x1f308;个人主页:程序员不想敲代码啊 &#x1f3c6;CSDN优质创作者&#xff0c;CSDN实力新星&#xff0c;CSDN博客专家 &#x1f44d;点赞⭐评论⭐收藏 &#x1f91d;希望本文对您有所裨益&#xff0c;如有不足之处&#xff0c;欢迎在评论区提出指正&#xff0c;让我们共…

【面试八股总结】传输控制协议TCP(三)

参考资料 &#xff1a;小林Coding、阿秀、代码随想录 一、TCP拥塞控制⭐ 1. 慢启动 – Slow Start 慢启动是指TCP连接刚建立&#xff0c;一点一点地提速&#xff0c;试探一下网络的承受能力&#xff0c;以免直接扰乱了网络通道的秩序。 慢启动算法&#xff1a; 初始拥塞窗口…