[MySQL高级](六) 锁机制_mysql 锁机制

2301_82241775

于 2024-04-21 18:45:48 发布

阅读量852

点赞数 16

分类专栏： 2024年程序员学习文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2301_82241775/article/details/138041456

版权

2024年程序员学习专栏收录该内容

25 篇文章 0 订阅

订阅专栏

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：xhs1999xhs （备注Java）

正文

１. 概述

1.1 定义

锁时计算机协调多个进程或线程并发访问某一资源的机制。
　　在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

1.2 应用

当然这里是举个例子表达意思，事实更加复杂。
这里写图片描述

1.3 锁的分类

从对数据库操作的类型分，分为读锁和写锁
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分，分为表锁和行锁

2. 三锁

2.1 表锁（偏读）

表锁偏向MyISAM存储引擎，开销小，加锁快，无思索，锁定粒度大，发生锁冲突的概率最高，并发度最低。

2.1.1 基本操作

建表SQL

CREATE TABLE `mylock` (
 `id` int(11) NOT NULL AUTO\_INCREMENT,
 `NAME` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO `test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO `test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO `test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');

手动增加表锁

lock table 表名称 read(write),表名称2 read(write)，其他;

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

2.1.2 案例分析(加读锁）

这里写图片描述

2.1.3 案例分析(加写锁）

这里写图片描述

2.1.4 案例结论

这里写图片描述
总结：
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

2.1.5 表锁分析

查看哪些表被加锁了

show open tables;

如何分析表锁定
可以通过检查table_lock_waited和table_locks_immediate状态变量来分析系统上的表锁定

show status like 'table%';

这里写图片描述
这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：
Table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1
Table_locks_waited 出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况。

MYISAM的读写锁调度是写优先，这也是MYISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞。

2.2 行锁（偏写）

行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MYISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

2.2.1 行锁支持事务

事务（Transaction）及其ACID属性
并发事务处理带来的问题
✈ 更新丢失（Lost Update）
　　当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。
　　
✈ 脏读（Dirty Reads）
　　一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。
　　一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
　　
✈ 不可重读（Non-Repeatable Reads）
　　一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
　　一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性
　　
✈ 幻读（Phantom Reads）
　　一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
　　一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

脏读是事务B里面修改了数据
幻读是事务B里面新增了数据

事务隔离级别

2.2.2 行锁案例分析

建表SQL

CREATE TABLE `test\_innodb\_locl` (
 `a` int(11) NOT NULL,
 `b` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test`.`test\_innodb\_locl` (`a`, `b`) VALUES ('1', 'b2');
INSERT INTO `test`.`test\_innodb\_locl` (`a`, `b`) VALUES ('2', '3');
INSERT INTO `test`.`test\_innodb\_locl` (`a`, `b`) VALUES ('4', '4000');
INSERT INTO `test`.`test\_innodb\_locl` (`a`, `b`) VALUES ('5', '5000');

create index idx\_test\_innodb\_a\_ind on test\_innodb\_locl;
create index idx\_test\_innodb\_b\_ind on test\_innodb\_locl;

行锁定基本演示

2.2.3 间隙锁危害

这里写图片描述

2.2.4 案例结论

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。
　　但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

2.2.5 行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

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（等待总时长）
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

2.2.6 优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离

2.3 行锁（偏写）

开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般。

最后

现在正是金三银四的春招高潮，前阵子小编一直在搭建自己的网站，并整理了全套的**【一线互联网大厂Java核心面试题库+解析】：包括Java基础、异常、集合、并发编程、JVM、Spring全家桶、MyBatis、Redis、数据库、中间件MQ、Dubbo、Linux、Tomcat、ZooKeeper、Netty等等**

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：xhs1999xhs （备注Java）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
转存中…(img-I8V85pbR-1713696337128)]

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：xhs1999xhs （备注Java）
[外链图片转存中…(img-421DNJsz-1713696337129)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

2301_82241775

关注

16
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
[MySQL高级](六) 锁机制_mysql 锁机制

锁时计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
复制链接

扫一扫