MySQL⾏级锁
来源:SegmentFault
时间:2023-02-17 13:24:22 243浏览 收藏
本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的,希望这篇《MySQL⾏级锁》对你有很大帮助!欢迎收藏,分享给更多的需要的朋友学习~
⾏级锁介绍
MySQL的⾏级锁,是由存储引擎来实现的,利⽤存储引擎锁住索引项来实现的。这⾥我们主要讲解InnoDB的⾏级锁。
InnoDB的⾏级锁,按照锁定范围来说,分为三种:
- 记录锁(Record Locks):锁定索引中⼀条记录。 id=1
- 间隙锁(Gap Locks):要么锁住索引记录中间的值,要么锁住第⼀个索引记录前⾯的值或者最后⼀个索引记录后⾯的值。
- Next-Key Locks:是索引记录上的记录锁和在索引记录之前的间隙锁的组合。
InnoDB的⾏级锁,按照功能来说,分为两种: RR
- 共享锁(S):允许⼀个事务去读⼀⾏,阻⽌其他事务获得相同数据集的排他锁。
- 排他锁(X):允许获得排他锁的事务更新数据,阻⽌其他事务取得相同数据集的共享读锁(不是读)和排他写锁。
对于UPDATE、DELETE和INSERT语句,InnoDB会⾃动给涉及数据集加排他锁(X);
对于普通SELECT语句,InnoDB不会加任何锁,事务可以通过以下语句显示给记录集加共享锁或排他锁。
⼿动添加共享锁(S):
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
⼿动添加排他锁(x):
SELECT * FROM table_name WHERE ... FOR UPDATE
InnoDB也实现了表级锁,也就是意向锁,意向锁是mysql内部使⽤的,不需要⽤户⼲预。
- 意向共享锁(IS):事务打算给数据⾏加⾏共享锁,事务在给⼀个数据⾏加共享锁前必须先取得该表的IS锁。
- 意向排他锁(IX):事务打算给数据⾏加⾏排他锁,事务在给⼀个数据⾏加排他锁前必须先取得该表的IX锁。
意向锁和⾏锁可以共存,意向锁的主要作⽤是为了【全表更新数据】时的性能提升。否则在全表更新数据时,需要先检索该表是否某些记录上⾯有⾏锁。
InnoDB⾏锁是通过给索引上的索引项加锁来实现的,因此InnoDB这种⾏锁实现特点意味着:只有通过索引条件检索的数据,InnoDB才使⽤⾏级锁,否则,InnoDB将使⽤表锁!
Innodb所使⽤的⾏级锁定争⽤状态查看:
mysql> show status like 'innodb_row_lock%';
- Innodb_row_lock_current_waits:当前正在等待锁定的数量;
- Innodb_row_lock_time:从系统启动到现在锁定总时间⻓度;
- Innodb_row_lock_time_avg:每次等待所花平均时间;
- Innodb_row_lock_time_max:从系统启动到现在等待最常的⼀次所花的时间;
- Innodb_row_lock_waits:系统启动后到现在总共等待的次数;
对于这5个状态变量,⽐较重要的主要是:
- Innodb_row_lock_time_avg(等待平均时⻓)
- Innodb_row_lock_waits(等待总次数)
- Innodb_row_lock_time(等待总时⻓)这三项。
尤其是当等待次数很⾼,⽽且每次等待时⻓也不⼩的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着⼿指定优化计划。
两阶段锁
传统RDBMS加锁的⼀个原则,就是2PL (Two-Phase Locking,⼆阶段锁)。相对⽽⾔,2PL⽐较容易理解,说的是锁操作分为两个阶段:加锁阶段与解锁阶段,并且保证加锁阶段与解锁阶段不相交。下⾯,仍旧以MySQL为例,来简单看看2PL在MySQL中的实现。
从上图可以看出,2PL就是将加锁/解锁分为两个完全不相交的阶段。
加锁阶段:只加锁,不放锁。
解锁阶段:只放锁,不加锁。
InnoDB⾏锁演示
⾏读锁
session1(Navicat)、session2(mysql)
查看⾏锁状态 show STATUS like 'innodb_row_lock%';
1、session1: begin;--开启事务未提交
select * from mylock where ID=1 lock in share mode; --⼿动加id=1的⾏读锁,使⽤索引
2、session2:update mylock set name='y' where id=2; -- 未锁定该⾏可以修改
3、session2:update mylock set name='y' where id=1; -- 锁定该⾏修改阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 锁定超时
4、session1: commit; --提交事务 或者 rollback 释放读锁
5、session2:update mylock set name='y' where id=1; --修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
注:使⽤索引加⾏锁 ,未锁定的⾏可以访问
⾏读锁升级为表锁
session1(Navicat)、session2(mysql)
1、session1: begin;--开启事务未提交
--⼿动加name='c'的⾏读锁,未使⽤索引
select * from mylock where name='c' lock in share mode;
2、session2:update mylock set name='y' where id=2; -- 修改阻塞 未⽤索引⾏锁升级为表锁
3、session1: commit; --提交事务 或者 rollback 释放读锁
4、session2:update mylock set name='y' where id=2; --修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
注:未使⽤索引⾏锁升级为表锁
⾏写锁
session1(Navicat)、session2(mysql)
1、session1: begin;--开启事务未提交
--⼿动加id=1的⾏写锁,
select * from mylock where id=1 for update;
2、session2:select * from mylock where id=2 ; -- 可以访问
3、session2: select * from mylock where id=1 ; -- 可以读 不加锁
4、session2: select * from mylock where id=1 lock in share mode ; -- 加读锁被阻塞
5、session1:commit; -- 提交事务 或者 rollback 释放写锁
5、session2:执⾏成功
主键索引产⽣记录锁
间隙锁
间隙锁有两种情况
1、防⽌插⼊间隙内的数据
2、防⽌已有数据更新为间隙内的数据
session1(Navicat)、session2(mysql)
案例演示:
mysql> create table news (id int, number int,primary key (id));
mysql> insert into news values(1,2);
......
--加⾮唯⼀索引
mysql> alter table news add index idx_num(number);
session 1:
start transaction ;
select * from news where number=4 for update ;
session 2:
start transaction ;
insert into news value(2,4);#(阻塞)
insert into news value(2,2);#(阻塞)
insert into news value(4,4);#(阻塞)
insert into news value(4,5);#(阻塞)
insert into news value(7,5);#(执⾏成功)
insert into news value(9,5);#(执⾏成功)
insert into news value(11,5);#(执⾏成功)
注:id和number都在间隙内则阻塞。 session 1: start transaction ; select * from news where number=13 for update ; ( select * from news where id>1 and id
文中关于mysql的知识介绍,希望对你的学习有所帮助!若是受益匪浅,那就动动鼠标收藏这篇《MySQL⾏级锁》文章吧,也可关注golang学习网公众号了解相关技术文章。
-
499 收藏
-
244 收藏
-
235 收藏
-
157 收藏
-
101 收藏
-
368 收藏
-
475 收藏
-
266 收藏
-
273 收藏
-
283 收藏
-
210 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 立即学习 507次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 立即学习 484次学习