【数据库】MySQL中的锁

2301_82242204

于 2024-04-18 14:07:59 发布

阅读量386

点赞数 4

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

本文链接：https://blog.csdn.net/2301_82242204/article/details/137919450

版权

程序员专栏收录该内容

448 篇文章 0 订阅

订阅专栏

MySQL事务常见的问题

更新丢失（Lost Update）

原因：当多个事务选择同一行操作，并且都是基于最初选定的值，由于每个事务都不知道其他事务的存在，就会发生更新覆盖的问题。类比github提交冲突。

脏读（Dirty Reads）

原因：事务A读取了事务B已经修改但尚未提交的数据。若事务B回滚数据，事务A的数据存在不一致性的问题。

不可重复读（Non-Repeatable Reads）

原因：事务A第一次读取最初数据，第二次读取事务B已经提交的修改或删除数据。导致两次读取数据不一致。不符合事务的隔离性。

幻读（Phantom Reads）

原因：事务A根据相同条件第二次查询到事务B提交的新增数据，两次数据结果集不一致。不符合事务的隔离性。

幻读和脏读有点类似，脏读是事务B里面修改了数据，幻读是事务B里面新增了数据

事务隔离级别

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大。这是因为事务隔离实质上是将事务在一定程度上"串行"进行，这显然与"并发"是矛盾的。根据自己的业务逻辑，权衡能接受的最大副作用。从而平衡了"隔离" 和 "并发"的问题。MySQL默认隔离级别是可重复读。脏读，不可重复读，幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决

在这里插入图片描述

锁的分类：主要的是表、行、页锁

按锁粒度从大到小分类：表锁，页锁和行锁；以及特殊场景下使用的全局锁
如果按锁级别分类则有：共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁；
以及Innodb引擎为解决幻读等并发场景下事务存在的数据问题，引入的Record Lock（行记录锁）、Gap Lock（间隙锁）、Next-key Lock（Record Lock + Gap Lock结合）等；
还有就是我们面向编程的两种锁思想：悲观锁、乐观锁。

在这里插入图片描述

全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句
全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本

但是让整个库都只读，可能出现以下问题

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

既然要全库只读，为什么不使用set global readonly=true(全库只读)的方式？

在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此修改global变量的方式影响面更大

在异常处理机制上有差异。

如果执行Flush tables with read lock命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。
而将整个库设置为readonly之后如果客户端发生异常，则数据库会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高

表锁

主要是MyISAM下的表锁

表锁的优势：开销小；加锁快；无死锁
表锁的劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预。
但是为了测试，我们手动加锁

加读锁 : lock table 表名 read;

加写锁 : lock table 表名 write；

同时加读、写锁: lock table 表名 read , write;

查看表加锁情况: SHOW OPEN TABLES;

释放锁: UNLOCK TABLES;

表锁的语法是lock tables … read/write。可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象
如果在某个线程A中执行lock tables t1 read,t2 wirte;这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许

表锁测试(读写锁)

读锁测试

准备两张表 student、teacher 引擎是MySAM

在这里插入图片描述

T1线程去对student加读锁，发现是不能对其他表查询的

#取消自动提交

SET autocommit=0;

#给student加读锁

LOCK TABLE student READ;

#再对student查询是ok的

SELECT * FROM student;

#再对同库其他表查询，失败

#原因是因为我们已经对student加锁了

#这就相当于在12306中抢票一样，抢到了一张火车票，但是没有完成支付，就无法再购买另一张火车票。把student这张表锁定了，还没有释放锁，就不能再操作其他的表了

SELECT * FROM teacher;

但是用T2线程去查询student、teacher都是ok的

在这里插入图片描述

然后在用T1线程去对student写(更新，修改…)试试，发现不能操作

#写其他表也不行

UPDATE teacher SET name= ‘tony’ WHERE id =1;

在这里插入图片描述

再用T2线程去写student，发现线程阻塞了

在这里插入图片描述

我们试试把T1的锁释放UNLOCK TABLES，发现t2继续运行并且修改成功了

在这里插入图片描述

总结
MyISAM中T1线程对某一张表加了读锁，它不会阻塞其它线程的读操作，但是会阻塞其它线程的写操作。
T1线程也不能对其他表进行读，写操作

写锁测试

T1线程对student加上写锁，发现对student读写都ok，但是不能对其他表进行读写操作

#对student加写锁

LOCK TABLE student WRITE ;

#读写student都ok

SELECT * FROM student;

UPDATE student SET name= ‘tony’ WHERE id =1;

#释放锁

UNLOCK TABLES;

再让T2对student进行读写，发现线程阻塞，当T1去释放写锁时，T2立马执行成功，并且T2可以对其他表进行读写操作

在这里插入图片描述

总结读写锁

在这里插入图片描述

对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写

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

行锁

主要时InnoDB下的行锁

行锁的劣势：开销大；加锁慢；会出现死锁
行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强
加锁的方式：自动加锁，默认支持的是行级锁
InnoDB 实现了以下两种类型的行锁。

共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于写(UPDATE、DELETE和INSERT)语句，InnoDB会自动给涉及数据集加排他锁
对于普通SELECT语句，InnoDB不会加任何锁
当然事务可以通过显示的加锁：

共享锁(S) :SELECT * FROM 表名 WHERE … LOCK IN SHARE MODE

排他锁(X) :SELECT * FROM 表名 WHERE … FOR UPDATE

行锁测试(共享锁、排他锁)

准备student、teacher 引擎是InnoDB

#先关掉事务自动提交

set autocommit = 0;

T1线程对student中id=1进行写将名字改为Tom1(更新…)，但是不提交；（已关闭自动提交）

UPDATE student SET name= ‘tony’ WHERE id =1;

然后让T2线程对id=1进行写将名字改Tom2，发现会阻塞

在这里插入图片描述

当T2线程手动commit的时候，发现T2恢复并正常执行，然后将T2事务提交commit，但是当我们查询的时候出现了问题
再T1线程中查询显示 name = Tom1
在T2线程中查询显示 name = Tom2
只能我们再次将T1进行commit才能发现 name = Tom2

当我们指定了更新语句，它会对我们这一行数据加上排它锁，当我们提交了之后，才会释放排他锁，另外一个客户端才可以解除阻塞状态。

【注意】

InnoDB行锁是通过给索引上的索引项加锁来实现的，InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁，实际效果跟表锁一样
查看当前表的索引 ： show index from 表名;

测试没有索引的锁

查看student的索引，发现一个也没有

show index from student

在这里插入图片描述

T1线程对student写

UPDATE student SET name= ‘tony1’ WHERE id =1

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

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

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

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后我们该如何学习？

1、看视频进行系统学习

这几年的Crud经历，让我明白自己真的算是菜鸡中的战斗机，也正因为Crud，导致自己技术比较零散，也不够深入不够系统，所以重新进行学习是很有必要的。我差的是系统知识，差的结构框架和思路，所以通过视频来学习，效果更好，也更全面。关于视频学习，个人可以推荐去B站进行学习，B站上有很多学习视频，唯一的缺点就是免费的容易过时。

另外，我自己也珍藏了好几套视频资料躺在网盘里，有需要的我也可以分享给你：