mysql锁表问题

MySQL锁表问题详解:从原理到实战的全链路分析 在高并发数据库场景中,MySQL的锁表问题是导致性能瓶颈和数据不一致的常见原因。本文深入解析了MySQL锁表的底层原理、常见场景及诊断修复方法。首先介绍了MySQL的锁粒度(表锁、行锁、页锁)和锁模式(共享锁、排他锁),并详细探讨了InnoDB行锁的实现原理。接着,通过具体代码示例复现了MyISAM表锁、InnoDB行锁升级及死锁问题,并提供了相应的诊断工具和解决方案。最后,总结了生产环境中的最佳实践,包括锁监控脚本、索引设计原则和连接池配置优化。通过合理使用锁机制、优化索引设计和控制事务范围,可以有效降低 阅读详情

问题描述

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

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;

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

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

<视觉>——单目相机的标定(简单原理+Opencv实现) 单目相机的标定原理大致如下: 世界坐标到像素坐标的转换。 期间的参数有S尺度因子,内参矩阵K,旋转矩阵R,平移矩阵T,一共八个未知数。 在Opencv中我们可以方便的根据相机拍摄不同位姿的标定板图片来标定相机,从而求得这些参数,从而可以将相机下的坐标和实际的世界坐标联系起来,求取单应性矩阵H 参考: 单目相机综述:写的特别好 https://www.cnblo... 阅读详情

相关推荐

单细胞数据pseudobulks差异分析实战:从聚合到DESeq2

本文详细介绍了单细胞转录组数据分析中pseudobulks(伪散装)方法的实战流程。通过将同一样本的同类型细胞表达值聚合,模拟Bulk RNA-seq数据,再利用DESeq2进行稳健的差异基因分析。该方法有效降低了单细胞数据的技术噪音,特别适用于样本量有限但追求结果稳定性的研究场景,并提供了完整的R代码实现与结果解读指南。

sea99的博客 761

Mysql频繁操作出现锁表问题

Mysql频繁操作出现锁表问题

weixin_42972832的博客 3182

VS2022 中 std::mutex 静态初始化陷阱:一次诡异崩溃的排查记录

VS2022中使用std::mutex时遇到的静态初始化陷阱问题。该问题源于VS2022 17.4版本为std::mutex添加constexpr支持后,全局静态初始化可能导致系统资源未就绪时锁失效或崩溃。文章分析了问题现象、影响范围,并提供了解决方案。最后给出了排查清单和核心建议

qq_34552942的博客 953

解决:org.springframework.dao.CannotAcquireLockException:Cause: com.mysql.cj.jdbc.exceptions.MySQLTrans

解决:org.springframework.dao.CannotAcquireLockException:Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction 发生异常!原因是: org.springframework.dao.CannotAcquireLockException: ### Error upda

sunrj_niu的博客 2877

海波龙(Oracle Hyperion)单点登陆问题记录

Oracle海波龙套件已经多年未更新,按Oracle官方文档实施单点登陆启用之后,SmartView工具通过专用链接方式无法登陆系统。 在抓包逆向分析之后 定位到HspSmartViewServlet类在执行以下代码时获得的ssotoken无效导致,在某大厂工作的时候曾通过在filter中截获请求的方式植入Attribute,保留账号密码并在单点程序中处理实现登陆。 但在完美世界的环境中...

cainiaoEzu的专栏 1973

游戏开发实战:如何在Unity中实现六边形网格地图(附完整代码)

本文详细介绍了在Unity中实现六边形网格地图的完整开发流程,从数学原理到实战代码。涵盖三种主流坐标系转换方法、距离计算算法对比、网格生成与渲染技巧,以及高级功能如网格高亮、寻路算法和性能优化策略。适合策略游戏、战棋类游戏开发者提升地图系统开发效率。

657

org.springframework.dao.CannotAcquireLockException异常分析

错误信息如下: 2017-09-27 16:27:16.153 - 【com.ldyun.base.service.impl.BaseRetailOrderServiceImpl】 - 新增零售商品订单~org.springframework.dao.CannotAcquireLockException: ### Error updating database. Cause: com.mys

敲键盘的猫 2万+

AI数字人:口播与唇形同步的福音,支持本地部署/批量生成/口齿清晰

你也可以直接输入语音,虚拟化身会根据语音的节奏和语调,同步做出相应的动作和表情,表现更加生动。不过这依然是个值得关注的工具。借助先进的 AI 算法,Heygem 高精度捕捉五官、轮廓等特征,构建逼真的虚拟模型。同时,它还能克隆语音,捕捉人声的细微特征,支持多种语音参数设置,打造高度相似的音色效果。打开设置,调整镜像存储位置,镜像文件比较大70~80G,选择合适的盘,设置完成后点击“Apply & Restart”。数字人视频画面与声音高度同步,口型匹配自然流畅,智能优化音视频效果,带来极佳的视听体验。

m0_72580657的博客 5862

mysql 锁次数,负载状态100%mysql状态锁表次数高,宝塔面板mysql锁表次数多少正常?...

如果我们建中大型的网站,最好使用服务器来建网站。很多使用服务器建网站的站长都会遇到这样的问题:自己服务器安装的宝塔面板的内存使用率很高,有时达到90%-100%。今天查看自己服务器的mysql状态发现锁表次数非常高,锁表次数:653,所以就试着搜索了下,发现别人动不动都是几十万次的锁表次数下面是网上别人的锁表次数,达到了40万次,启动时间2018/10/23 05:50:49每秒查询110总连接次...

weixin_30131977的博客 2695

matlab之作图设置游标的精度

matlab画出的figure游标的精度往往是固定的,这就对我们的调试和查看数据造成了不便。 那么怎么修改游标的精度呢? 首先,获取游标dcm_obj = datacursormode(gcf);然后对游标的精度进行设置:set(dcm_obj,'UpdateFcn',@NewCallback)这个NewCallback是什么东西呢?这是一个可以返回游标精度的函数,具体内容如下:function

落日之城 8959

华为OD机试:使用Ascend C语言实现Matmul与LeakyReLU融合算子的全流程设计与优化解析

通过本文的详细解析,相信你已经对华为OD机试中基于Ascend C语言的Matmul+LeakyReLU融合算子的设计与实现有了深入的理解。我们不仅从理论上探讨了融合算子的核心优势,还通过实际的代码示例演示了如何一步步完成算子的实现与优化。融合算子在AI算子开发中的应用,尤其是在处理大规模矩阵计算的场景中,能够显著提升计算效率,优化内存占用,并减少数据传输的开销。通过合理的Tiling策略设计,我们还能够充分利用多核处理器的算力,进一步提升AI模型的训练与推理性能。

qq_38334677的博客 662

工作中遇到的数据库死锁问题 - 排查方案 CannotAcquireLockException

问题·重现 正常情况下,销售人员在使用我们的销售系统为客户创建订单时候,后台java代码就会开启事务,然后往数据库中添加订单信息和订单详情信息,以及一些其他业务操作。但由于某次异常操作(可能是网络或者其他的原因),导出系统出现问题无法工作。 于是进行代码调试,发现在向数据库中插入数据的时候,一直卡在添加数据这个方法上,等待了很长一段时间,一直无法响应。在经过一段时间后,通过try…catch…捕获了 org.springframework.dao.CannotAcquireLockException异常,如

qq_38601777的博客 1万+

索尼IMX316 标定_ToF模块相机校准

很多东西一开始给出去的并不是最优的方案,而是在客户端支持中,以及在持续不断的芯片验证中,发现还可以这么做,还能够再提升Sensor的性能,所以说这是一个持续的过程。曾经听说过一个故事,在某厂里ST产品一直作为第一供应商多年,然后近段时间即将被新兴公司的ToF产品所打败,性能报告已经出来,但是ST并没有更新产品,而是在原有产品更新了固件以及标定流程,此时产品的性能又是超越其他供应商的产品。相机矩阵不考虑镜头失真,因为理想的针孔相机没有镜头。为了准确地代表一个真实的相机,相机模型包括径向和切向的镜头畸变。

dream it possible 1155

MYSQL大量锁表问题解决

MYSQL锁表问题解决 本文实例讲述了MYSQL锁表问题的解决方法。分享给大家供大家参考,具体如下: 很多时候!一不小心就锁表!这里讲解决锁表终极方法! 案例一 mysql>show processlist; 参看sql语句 mysql>kill thread_id; 就可以解决了 kill掉第一个锁表的进程, 依然没有改善. 既然不改善, 咱们就想办法将所有锁表的进程kill掉吧, ...

net@core的博客 2880

NTscan扫描工具

ntscan是一个扫描器,可以扫描没有密码和弱密码的共享,先到配置文件中填写猜测的用户名和密码,扫描过程中右侧出现的是扫描日志。

假人有限元模型

假人模型,车辆碰撞假人有限元模型,适合做乘员安全的研究者利用

MySQL锁,解决锁表的问题

有一个`truncate table` 在执行,导致锁表了。并且一直没有完成,从而导致线上服务查询基本信息的时候出现大量卡住的问题,导致服务不可用。记录下问题及解决方案,及其他知识点

假女吖的博客 1767

Camera2Demo

在Camera2 的基础上进行封装,使得Camera2一堆初始化逻辑与Activity分离,降低耦合度,完成预览拍照并保存的操作

表锁问题全解析,深度解读MySQL表锁问题及解决方案

深入解析MySQL表锁问题根源与解决方案,提升数据库并发性能。结合数据湖架构中的多语言 ETL 工具(Spark+Flink+Python),适用于高并发读写场景,提供锁机制优化策略与实践案例,显著增强系统稳定性与执行效率,值得收藏。

FastDebug的博客 748

Devexpress16.2.6破解工具

Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具

如何快速排查与解决MySQL锁表问题

如何快速排查与解决MySQL锁表问题

一起coding,一起嗨。 1241

mysql 存储过程 锁表_MYSQL锁表问题的解决方法

本文实例讲述了MYSQL锁表问题的解决方法。分享给大家供大家参考,具体如下:很多时候!一不小心就锁表!这里讲解决锁表终极方法!案例一mysql>show processlist;参看sql语句一般少的话mysql>kill thread_id;就可以解决了kill掉第一个锁表的进程, 依然没有改善. 既然不改善, 咱们就想办法将所有锁表的进程kill掉吧, 简单的脚本如下.#!/bin...

weixin_33230532的博客 1277

在 MySQL 中,处理锁表问题

执行(推荐) 或关联查询。记下 blocking_pid(阻塞者的 ID)。检查阻塞者的 SQL (INFO字段) 确认是否可以终止。执行。观察业务是否恢复。

飞鱼计划 449

Mysql行锁、表锁问题

文章目录一、Mysql锁问题1.锁概述2.锁分类3. Mysql 锁 一、Mysql锁问题 1.锁概述 锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。 在数据库中,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。 2.锁分类 从对数据操作的粒度分 : 1) 表锁:操作时,会锁定整个表。 2) 行锁:操作时,会锁定当前操作行。 从对数据

weixin_56320090的博客 505
上一篇: springboot + mybatis支持多数据库
下一篇: 通过镜像部署LLaMA Factory
炒栗子不加糖
博客等级 码龄10年 68粉丝 123原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值