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;

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

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

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

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

【SQL Server】1. 认识+使用

【SQL Server】1. 认识+使用

1. 创建数据库的默认存储路径 C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2008 R2 当我们选择删除数据库时&#xff0c;对应路径下的文件也就删除了 2. 导入导出数据工具的路径 3. 注册数据库遇到的问题 ??? 目前的问题就是服务器新建…
阅读更多...
应急响应实战笔记04Windows实战篇（5）

应急响应实战笔记04Windows实战篇（5）

第5篇&#xff1a;挖矿病毒&#xff08;一&#xff09; 0x00 前言 ​ 随着虚拟货币的疯狂炒作&#xff0c;挖矿病毒已经成为不法分子利用最为频繁的攻击方式之一。病毒传播者可以利用个人电脑或服务器进行挖矿&#xff0c;具体现象为电脑CPU占用率高&#xff0c;C盘可使用空间…
阅读更多...
【Web】记录Polar靶场＜中等＞难度题一遍过(全)

【Web】记录Polar靶场＜中等＞难度题一遍过(全)

目录 到底给不给flag呢 写shell 注入 某函数的复仇 xxe SSTI unpickle BlackMagic 反序列化 找找shell 再来ping一波啊 wu 代码审计1 你的马呢&#xff1f; ezphp 随机值 phpurl search file PlayGame csdn 反正持续一个月&#xff0c;感觉XYCTF…
阅读更多...
2024五大招聘趋势

2024五大招聘趋势

2024年的招聘趋势梳理了五个关键趋势&#xff1a;AI驱动的招聘技术的兴起、自动化的重要性、向候选人为中心的招聘转变、招聘者外联的增加个性化&#xff0c;以及重视基于技能的招聘。尽管经济存在不确定性&#xff0c;但预计对AI用于招聘的投资将继续增长&#xff0c;大力强调…
阅读更多...
数据结构——栈(C语言版)

数据结构——栈(C语言版)

前言&#xff1a; 在学习完数据结构顺序表和链表之后&#xff0c;其实我们就可以做很多事情了&#xff0c;后面的栈和队列&#xff0c;其实就是对前面的顺序表和链表的灵活运用&#xff0c;今天我们就来学习一下栈的原理和应用。 准备工作&#xff1a;本人习惯将文件放在test.c…
阅读更多...
ubuntu22.04@Jetson Orin Nano安装配置VNC服务端

ubuntu22.04@Jetson Orin Nano安装配置VNC服务端

ubuntu22.04Jetson Orin Nano安装&配置VNC服务端 1. 源由2. 环境3. VNC安装Step 1: update and install xserver-xorg-video-dummyStep 2: Create config for dummy virtual displayStep3: Add the following contents in xorg.conf.dummyStep 4: Update /etc/X11/xorg.con…
阅读更多...
WPF-基础及进阶扩展合集（持续更新）

WPF-基础及进阶扩展合集（持续更新）

目录 一、基础 1、GridSplitter分割线 2、x:static访问资源文件 3、wpf触发器 4、添加xaml资源文件 5、Convert转换器 6、多路绑定与多路转换器 二、进阶扩展 1、HierarchicalDataTemplate 2、XmlDataProvider从外部文件获取源 3、TextBox在CellTemplate中的焦点问题…
阅读更多...
Python基础之pandas：文件读取与数据处理

Python基础之pandas：文件读取与数据处理

提示&#xff1a;文章写完后&#xff0c;目录可以自动生成&#xff0c;如何生成可参考右边的帮助文档 文章目录 一、文件读取1.以pd.read_csv()为例&#xff1a;2.数据查看 二、数据离散化、排序1.pd.cut()离散化&#xff0c;以按范围加标签为例2. pd.qcut()实现离散化3.排序4.…
阅读更多...
stream使用

stream使用

stream流式计算 在Java1.8之前还没有stream流式算法的时候&#xff0c;我们要是在一个放有多个User对象的list集合中&#xff0c;将每个User对象的主键ID取出&#xff0c;组合成一个新的集合&#xff0c;首先想到的肯定是遍历&#xff0c;如下&#xff1a; List<Long> u…
阅读更多...
通过pymysql读取数据库中表格并保存到excel(实用篇)

通过pymysql读取数据库中表格并保存到excel(实用篇)

本篇文章是通过pymysql将本地数据库中的指定表格保存到excel的操作。 这里我们假设本地已经安装了对应的数据库管理工具&#xff0c;里面有一个指定的表格&#xff0c;现在通过python程序&#xff0c;通过调用pymysql进行读取并保存到excel中。 关于数据库管理工具是Navicat P…
阅读更多...
Android Studio学习7——常用控件view

Android Studio学习7——常用控件view

Android控件 双击shift键——>搜索想要找的文件 Ctrlshift回车——>补全“&#xff1b;”号 CtrlX——>删除一行&#xff0c;只需把鼠标放在那一行 windows自带字体
阅读更多...
LC 111.二叉树的最小深度

LC 111.二叉树的最小深度

111. 二叉树的最小深度 给定一个二叉树&#xff0c;找出其最小深度。 最小深度是从根节点到最近叶子节点的最短路径上的节点数量。 说明&#xff1a; 叶子节点是指没有子节点的节点。 示例 1&#xff1a; 输入&#xff1a; root [3,9,20,null,null,15,7] 输出&#xff1a;…
阅读更多...
mongodb的简单操作

mongodb的简单操作

文章目录 前言数据库的创建和删除集合的创建和删除文档的插入和查询异常处理更新数据局部修改符合条件的批量更新加操作 删除文档删除全部数据删除符合条件的数据 统计count统计有多少条数据统计特定条件有多少条数据 分页查询排序查询正则查询比较查询包含查询条件连接查询索引…
阅读更多...
Docker 哲学 - push 本机镜像 到 dockerhub

Docker 哲学 - push 本机镜像 到 dockerhub

注意事项&#xff1a; 1、 登录 docker 账号 docker login 2、docker images 查看本地镜像 3、注意的是 push镜像时 镜像的tag 需要与 dockerhub的用户名保持一致 eg&#xff1a;本地镜像 express:1 直接 docker push express:1 无法成功 原因docker不能识别 push到哪里 …
阅读更多...
轻松上手 Tanssi：应用链开发与部署终极指南

轻松上手 Tanssi：应用链开发与部署终极指南

随着 Polkadot 2.0 的推进&#xff0c;一个既强大又用户友好的技术支撑成为推动生态进步的关键&#xff0c;目的是为了降低应用链启动的成本和复杂度。在这个转折点上&#xff0c;Tanssi 逐渐成为应用链开发的首选解决方案。Tanssi 是一个旨在简化应用链部署流程的模块化基础设…
阅读更多...
kubernetes-Pod基于污点、容忍度、亲和性的多种调度策略(二)

kubernetes-Pod基于污点、容忍度、亲和性的多种调度策略(二)

Pod调度策略 一.污点-Taint二.容忍度-Tolerations三.Pod常见状态和重启策略1.Pod常见状态2.Pod的重启策略2.1测试Always重启策略2.2测试Never重启策略2.3测试OnFailure重启策略&#xff08;生产环境中常用&#xff09; 一.污点-Taint 在 Kubernetes 中&#xff0c;污点&#x…
阅读更多...
采用大语言模型进行查询重写——Query Rewriting via Large Language Models

采用大语言模型进行查询重写——Query Rewriting via Large Language Models

文章&#xff1a;Query Rewriting via Large Language Models&#xff0c;https://arxiv.org/abs/2403.09060 摘要 查询重写是在将查询传递给查询优化器之前处理编写不良的查询的最有效技术之一。 手动重写不可扩展&#xff0c;因为它容易出错并且需要深厚的专业知识。 类似地…
阅读更多...
codeforces Edu 142 D. Fixed Prefix Permutations 【思维、字典树求LCP】

codeforces Edu 142 D. Fixed Prefix Permutations 【思维、字典树求LCP】

D. Fixed Prefix Permutations 题意 给定 n n n 个长度为 m m m 的排列 a 1 , a 2 , . . . a n a_1,a_2,...a_n a1​,a2​,...an​ 定义一个排列 p p p 的 价值 为 最大顺序长度 k k k&#xff1a; p 1 1 , p 2 2 , p 3 3 , . . . p k k p_1 1,p_2 2, p_3 3, ...…
阅读更多...
CLIP网络结构解析 openai/CLIP (Contrastive Language-Image Pre-Training)

CLIP网络结构解析 openai/CLIP (Contrastive Language-Image Pre-Training)

1、简单介绍 CLIP是openai公司提出的网络&#xff0c;可以处理文本和图像&#xff0c;是一个多模态网络&#xff0c;对多模态的研究具有一定的推动作用。作为学习&#xff0c;记录一下对CLIP的理解。 clip的官方网站&#xff1a; https://openai.com/research/clip clip的GitH…
阅读更多...
优于五大先进模型，浙江大学杜震洪团队提出 GNNWLR 模型：提升成矿预测准确性

优于五大先进模型，浙江大学杜震洪团队提出 GNNWLR 模型：提升成矿预测准确性

卡塔尔世界杯自 2010 年荣膺举办权&#xff0c;直至 2022 年辉煌成功举办&#xff0c;累计投入资金高达约 2,290 亿美元。相较之下&#xff0c;此前七届世界杯的总花费仅约 400 多亿美元。这场体育盛事展现出奢华无度的风采&#xff0c;归根结底源于卡塔尔这个国度的深厚底蕴。…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023