您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

mysql锁-从实战中理解

时间:2023-01-03 11:34:48  来源:今日头条  作者:打篮球的程序员

MySQL锁可能是数据库知识篇幅中普遍比较难理解的一个知识点!以前对锁理解的也是停留在八股文的的阶段,经历了这次生产问题之后重新学习了

问题表现:

早上刚到公司还没进入状态,就被拉进一个群(dba找上门了)说数据库有大量锁等待异常。

代码中的sql: delete from
order_point_line_statistics where ep_id = 376330219 and created_at <= '2022-12-15 00:00:00'

Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ### The error may exist in class path resource [MyBatis/mApper/ext/OrderPointLineStatisticsExtMapper.xml]

The error occurred while setting parameters ### SQL: delete from order_point_line_statistics where ep_id = ? and created_at <= ? ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

事故现场

场景是在并发消费kafka数据的时候,起了一个事务,事务里先插入今天的数据,然后删除数据;

CREATE TABLE `ep` (

`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

`ep_id` int(11) NOT NULL COMMENT '企业ID',

`name` varchar(255) NOT NULL COMMENT '名称',

`create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',

PRIMARY KEY (`id`),

KEY `idx_ep_id` (`ep_id`) USING BTREE,

KEY `idx_create_at` (`create_at`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

 

Session1

Session2

Locks

begin;

begin;

 

insert into ep(e_id, name, create_at) values(100, 'stt', '2022-12-04 00:00:00');

 

 

 

insert into ep(e_id, name, create_at) values(100, 'ssd', '2022-12-04 00:00:00');

 

select * from epG;
id: 1
e_id: 100
name: shijie
create_at: 2022-12-01 00:00:00
update_at: 2023-01-01 05:31:13
id: 2
e_id: 100
name: jianfeng
create_at: 2022-12-02 00:00:00
update_at: 2023-01-01 05:31:13
id: 3
e_id: 100
name: syx
create_at: 2022-12-03 00:00:00
update_at: 2023-01-01 05:31:13
id: 4
e_id: 100
name: stt
create_at: 2022-12-04 00:00:00
update_at: 2023-01-01 05:41:47

select * from epG;
id: 1
e_id: 100
name: shijie
create_at: 2022-12-01 00:00:00
update_at: 2023-01-01 05:31:13
id: 2
e_id: 100
name: jianfeng
create_at: 2022-12-02 00:00:00
update_at: 2023-01-01 05:31:13
id: 3
e_id: 100
name: syx
create_at: 2022-12-03 00:00:00
update_at: 2023-01-01 05:31:13
id: 5
e_id: 100
name: ssd
create_at: 2022-12-04 00:00:00
update_at: 2023-01-01 05:44:46

 

delete from ep where e_id=100 and create_at <= '2022-12-03 00:00:00';

 

lock waiting&dead lock

这里粘贴一张复现的图:

 

事故还原

初始化记录

 

两个session分别执行一条插入后查看的锁记录:

 

session1执行delete语句的锁记录:

 


 

此时session2的锁记录:

 

可以明显的看出:

 

 

表中最大的记录id是6,事务中插入的记录id是15,session1锁住的记录id最大到10,其实当我存量数据id是连续的时候,session1会把我刚插入的id也会锁住,这也是我一直不理解的地方。

google中提了一个问题:
https://stackoverflow.com/questions/74972932/why-does-innodb-lock-more-records-when-range-deletion;大致回答的意思就是说:其实不管大范围还是小范围都一样 在根据ep_id和create_at筛选删除时,如果大范围时,会先挑ep_id=100先筛选 而且是边筛选边变锁,把筛到的结果就要锁住 ep_id=100的筛选结果包含session2的插入的数据 就锁等待了。

思考:

一:当我把delete语句中的时间范围缩小时,还是会锁住刚插入的记录吗?

答案是不会的,只会锁住符合条件的记录。 我想这也是符合预期的锁记录。

二:思考

1)、把插入语句和删除语句调换位置

在事务中先执行删除,虽然说session2也会产生锁等待,但是session1同样可以提交不会报错

2)、删除不能使用二级索引,应尽量使用聚簇索引

3)、如果初始化记录中的id不连续,sessio1执行delete语句的时候是不是不会锁住插入的记录

Session1

Session2

Locks

begin;

begin;

 

insert into ep(e_id, name, create_at) values(100, 'stt', '2022-12-04 00:00:00');

 

 

 

insert into ep(e_id, name, create_at) values(100, 'ssd', '2022-12-04 00:00:00');

 

select * from epG;
id: 1
e_id: 100
name: shijie
create_at: 2022-12-01 00:00:00
update_at: 2023-01-01 05:31:13
id: 2
e_id: 100
name: jianfeng
create_at: 2022-12-02 00:00:00
update_at: 2023-01-01 05:31:13
id: 3
e_id: 100
name: syx
create_at: 2022-12-03 00:00:00
update_at: 2023-01-01 05:31:13
id: 4
e_id: 100
name: stt
create_at: 2022-12-04 00:00:00
update_at: 2023-01-01 05:41:47

select * from epG;
id: 1
e_id: 100
name: shijie
create_at: 2022-12-01 00:00:00
update_at: 2023-01-01 05:31:13
id: 2
e_id: 100
name: jianfeng
create_at: 2022-12-02 00:00:00
update_at: 2023-01-01 05:31:13
id: 3
e_id: 100
name: syx
create_at: 2022-12-03 00:00:00
update_at: 2023-01-01 05:31:13
id: 5
e_id: 100
name: ssd
create_at: 2022-12-04 00:00:00
update_at: 2023-01-01 05:44:46

 

delete from ep where e_id=100 and create_at <= '2022-12-02 00:00:00';

 

no lock

三:如何解决

先说下最终的解决方式是删除事务和删除动作;删除动作是通过另外的任务去执行。



Tags:mysql锁   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
mysql锁可能是数据库知识篇幅中普遍比较难理解的一个知识点!以前对锁理解的也是停留在八股文的的阶段,经历了这次生产问题之后重新学习了问题表现:早上刚到公司还没进入状态,就...【详细内容】
2023-01-03  Tags: mysql锁  点击:(0)  评论:(0)  加入收藏
查看mysql执行的线程 show full processlist; 查看mysql 事务处理列表 select * from information_schema.INNODB_TRX 杀死事务进程 kill 16490...【详细内容】
2021-04-30  Tags: mysql锁  点击:(369)  评论:(0)  加入收藏
最近遇到一个由于唯一性索引,导致并发插入产生死锁的场景,在分析死锁产生的原因时,发现这一块还挺有意思的,涉及到MySql中不少的知识点,特此总结记录一下。 一、MySql常见的锁谈...【详细内容】
2021-02-19  Tags: mysql锁  点击:(246)  评论:(0)  加入收藏
前言今天就为大家介绍一下MySQL中锁相关的知识。本文在没有特别声明的情况下,均是默认InnoDB引擎,如涉及到其他引擎或者数据库则会特别指出。什么是锁锁是一种用于保证在并发...【详细内容】
2020-10-19  Tags: mysql锁  点击:(157)  评论:(0)  加入收藏
【51CTO.com原创稿件】随着 IT 技术的飞速发展,各种技术层出不穷,让人眼花缭乱。尽管技术在不断更新换代,但是有些技术依旧被一代代 IT 人使用至今。 图片来自 PexelsMySQL...【详细内容】
2020-02-18  Tags: mysql锁  点击:(201)  评论:(0)  加入收藏
本文章向大家介绍MySQL锁详细讲解,包括数据库锁基本知识、表锁、表读锁、表写锁、行锁、MVCC、事务的隔离级别、悲观锁、乐观锁、间隙锁GAP、死锁等等,需要的朋友可以参考一下...【详细内容】
2019-12-24  Tags: mysql锁  点击:(156)  评论:(0)  加入收藏
▌简易百科推荐
mysql锁可能是数据库知识篇幅中普遍比较难理解的一个知识点!以前对锁理解的也是停留在八股文的的阶段,经历了这次生产问题之后重新学习了问题表现:早上刚到公司还没进入状态,就...【详细内容】
2023-01-03  打篮球的程序员  今日头条  Tags:mysql锁   点击:(0)  评论:(0)  加入收藏
今天在工作中写项目的时候,遇到了一个让我感到几乎无解的问题,在转换了思路后,想出了一个折中的解决方案,记录如下。其实,问题的场景,非常简单: 就是需要查询出上图的数据,红框是从...【详细内容】
2022-12-24  Java架构师修炼之道  今日头条  Tags:MySQL   点击:(14)  评论:(0)  加入收藏
最近,某同事在生产上遇到一个 mysql 死锁的问题,于是在帮忙解决问题后,特意花了一周的时间,把 MySQL 所有的锁都整理了一遍,今天就来一起聊聊 MySQL锁。 申明:本文基于 MySQL 8.0....【详细内容】
2022-12-18  猿Java  知乎  Tags:MySQL   点击:(11)  评论:(0)  加入收藏
Insert Intention Lock,中文我们也称之为插入意向锁。这个可以算是对我们之前所讲的 Gap Lock 的一个补充,关于 Gap Lock,如果还有小伙伴不懂,可以参考:记录锁、间隙锁与 Next-Ke...【详细内容】
2022-12-18  江南一点雨  今日头条  Tags:MySQL   点击:(18)  评论:(0)  加入收藏
Adminer (以前叫phpMinAdmin),是一个用 PHP 编写的前端数据库管理工具。与 phpMyAdmin 不同的是,它只包含一个 PHP 文件,可以在安装 Adminer 的目标服务器上下载。与 phpMyAdmi...【详细内容】
2022-11-23  程序猿阿嘴  今日头条  Tags:MySQL   点击:(39)  评论:(0)  加入收藏
pt-heartbeat的工作原理通过使用时间戳方式在主库上更新特定表,然后在从库上读取被更新特定表里的时间戳,再与本地系统时间对比来得出其延迟。具体流程:1)在主库上创建一张heart...【详细内容】
2022-11-11  mj谈云技术  今日头条  Tags:MySQL   点击:(42)  评论:(0)  加入收藏
今天我们来详细了解一下主从同步延迟时读写分离发生写后读不到的问题,依次讲解问题出现的原因,解决策略以及 Sharding-jdbc、MyCat 和 MaxScale 等开源数据库中间件具体的实现...【详细内容】
2022-11-09   ITPUB2022   网易号  Tags:MySQL   点击:(49)  评论:(0)  加入收藏
谈到索引失效,大家可能都能列举出几个场景,比如:后模糊查询、条件中带函数、索引中断等等。今天我想和你分享另一个场景:索引成本分析。我先用一个具体的例子来描述一下这个场景...【详细内容】
2022-11-07  杨同学technotes  今日头条  Tags:MySQL   点击:(46)  评论:(0)  加入收藏
最近发现之前使用的MyBatis插件很久都没更新了,就想换个其他插件来用用。偶然发现MyBatis-Plus团队也开发了一款插件MyBatisX,体验了一把确实非常好用,提示很全,而且还能通过GUI...【详细内容】
2022-11-01  老诚不bug  今日头条  Tags:MyBatis-Plus   点击:(50)  评论:(0)  加入收藏
满怀忧思不如先干再说!前言数据库拆分属于中高级开发要做的事情,不过具体的看企业吧。各种情况都会出现,这篇文章主要是基于Mycat去实现一下数据库拆分,至于拆分的思想,后边补一...【详细内容】
2022-11-01  添甄  今日头条  Tags:MySQL   点击:(53)  评论:(0)  加入收藏
站内最新
站内热门
站内头条