MySQL基础(十二):锁机制

文章目录

    • 一、锁的概述
        • 1、什么是锁?
        • 2、锁的分类
    • 二、MySQL中的三种锁
        • 1、表锁(偏读)
            • (1)表锁的特点
            • (2)表加读锁、写锁的语法
            • (3)读锁特性实验
            • (4)写锁特性实验
            • (3)结论
        • 2、行锁(偏写)
            • (1) 由于行锁支持事务,复习老知识
            • (2)建表sql
            • (3)行锁加读锁、写锁语法
            • (4)读锁特性实验
            • (5)写锁特性实验
            • (6)无论哪种锁的,一个共性
            • (7)无索引,行锁升级为表锁
            • (8)间隙锁的危害
            • (9)总结
            • (10)优化建议
        • 3、页锁(了解)

一、锁的概述

1、什么是锁?

锁是计算机协调多个进程或线程并发访问某一资源的机制。 

2、锁的分类

1. 从对数据操作的类型(读\写)分:
  读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响。但是谁都不能写!
  写锁(排它锁):持有写锁的线程,读写权限都有,而且它会阻断其他进程对该资源的读和写。
'总结:读锁(谁都能读,谁都不能写)  写锁(对该资源独占)'

2. 从对数据操作的粒度分:
  表锁
  行锁

二、MySQL中的三种锁

1、表锁(偏读)

Myisam一般采用表锁,它不支持行锁!

(1)表锁的特点

偏向MyISAM存储引擎,开销小,加锁快;无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低

(2)表加读锁、写锁的语法
给表加读锁:
lock table mylock read;
给表加写锁:
lock table mylock write;
释放锁:
unlock table; # 释放所有表的所有锁!mysql好像不支持释放指定的锁

注:晓得怎么释放指定表的读锁、写锁的小伙伴,请在评论区写下语法
mysql若不支持,如果知道为什么不支持的,也希望大家说下为什么!谢谢各位小伙伴的分享

接下来分析读锁的特点!
我们先看,我开了两个session会话:
在这里插入图片描述

(3)读锁特性实验

读锁肯定都能读的,主要验证写!

  • 对自己的影响:

在这里插入图片描述

  • 对其他人的影响:

在这里插入图片描述对于其他人是阻塞状态!当我们释放读锁,这边将会成功update!

(4)写锁特性实验
  • 对自己的影响(读):

在这里插入图片描述

  • 对自己的影响(写):

在这里插入图片描述

  • 对他人的影响(读):

在这里插入图片描述阻塞的!

  • 对他人的影响(写):

在这里插入图片描述阻塞的!

(3)结论

MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。
MySQL的表级锁有两种模式:

表共享读锁(Table Read Lock) 
表独占写锁(Table Write Lock
锁类型 自己可读 自己可写 他人可读 他人可写
读锁
写锁

结论, 结合上表,所以对MyISAM表进行操作,会有以下情况:

  1. 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。  
  2. 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。 

简而言之,就是读锁会阻塞所有写,但是不会堵塞读。而写锁则会把读和写都堵塞,自己独占资源

2、行锁(偏写)

innodb支持表锁,与更细粒度的行锁!innodb一般使用行锁。

(1) 由于行锁支持事务,复习老知识
1. 事务(Transaction)及其ACID属性
	(1) 事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。  
	(2) 原子性(Atomicity) :事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。  
	(3) 一致性(Consistent) :在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。  
	(4) 隔离性(Isolation) :数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。  
	(5) 持久性(Durable) :事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。  
2. 并发事务处理带来的问题
	(1) 更新丢失(Lost Update)
	(2) 脏读(Dirty Reads)
	(3) 不可重复读(Non-Repeatable Reads)
	(4) 幻读(Phantom Reads)
3. 事务隔离级别
	(1) 脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。
	(2) '事务隔离级别:读未提交、读已提交、可重复读、可序列化'
	(3) 常看当前数据库的事务隔离级别:show variables like 'tx_isolation'; 
(2)建表sql
# 创建表
create table test_innodb_lock (a int(11),b varchar(16)) engine=innodb;

# 插入数据
insert into test_innodb_lock values(1,'b2'); 
insert into test_innodb_lock values(3,'3'); 
insert into test_innodb_lock values(4,'4000'); 
insert into test_innodb_lock values(5,'5000'); 
insert into test_innodb_lock values(6,'6000'); 
insert into test_innodb_lock values(7,'7000'); 
insert into test_innodb_lock values(8,'8000'); 
insert into test_innodb_lock values(9,'9000'); 
insert into test_innodb_lock values(1,'b1'); 

# 创建索引
create index test_innodb_a_ind on test_innodb_lock(a); 
create index test_innodb_lock_b_ind on test_innodb_lock(b);

# 查看索引、查看test_innodb_lock表
show index from test_innodb_lock;
select * from test_innodb_lock;

在这里插入图片描述在这里插入图片描述

(3)行锁加读锁、写锁语法

对一行或者说一个记录进行update、delete或者说insert时,会自动为行加写锁!select不会加锁,有需求需要自己加锁!

# select加读锁
select * from test_innodb_lock lock in share mode

# select加写锁
select * from test_innodb_lock for update # 相当于就是给查询语句披了一个update语句的外衣
(4)读锁特性实验

和表锁的读锁一样,这个记录只能读,谁都不能对这个记录进行操作!

(5)写锁特性实验

要想看到效果需要关闭autocommit,自动提交会自动释放写锁!
在这里插入图片描述两个会话都需要做!现在我们就需要手动commit才能释放对行写锁!


现在我们执行update,实现行锁加写锁
在这里插入图片描述加锁会话,数据已变更,另外一个会话没变更,因为加锁会话的数据修改没有提交嘛!现在只是在内存中修改了!现在a为4的记录应该处于加了写锁的状态,我们接下来看看其特性:

# 由上图可知,行加写锁,都可以读
由于加锁会话,读的是内存中已修改的数据,另一会话内存中没有,读的是硬盘中未修改数据,因此读的数据不一致

'注意:这里这个不是脏读,读取的是磁盘未修改的数据,这本来就是干净的数据!'

我们接下来提交修改:
在这里插入图片描述为什么还不变?因为innodb默认隔离级别为可重复读,因此它会重复读取上次的结果!我们上次读了次磁盘中未修改的数据嘛,这么说,我新开一个会话执行该命令,我是不是就能立马看到变化后的值,!当然不新开会话,这个会话,你commit提交一下,也能马上看到效果

首先行的写锁,加锁会话肯定写和读都可以,上面我们知道了其他会话也可以读!,其他会话的写是什么情况呢?

在这里插入图片描述阻塞!

(6)无论哪种锁的,一个共性

无论你是行锁(读或写锁)还是表锁(读或写锁),加锁的那个线程必须释放当前的锁,才能去找其他资源,不能吃到碗里,看着锅里的!

(7)无索引,行锁升级为表锁

一个查询语句如果没有采用索引,那么它的type一定是all,全表扫描,因此这时行锁会升级成表锁!因此索引一定要好好优化,慎重创建!

  • 先看下,不同行是否可以同时操作:
    在这里插入图片描述可以!

  • 删除索引:
    在这里插入图片描述

  • 再次查看不同行是否可以操作:
    在这里插入图片描述不行了!因为升级成了表锁!

注意:即是你有索引,如果某种原因造成索引失效了,依然会锁定整张表!
比如索引字段id为varchar类型,你却where a = 1,那么也会造成索引失效!因此sql语句的优化、使用也要慎重!


注意:这里我在网上搜了好久,都没找到如何查看当前库下的行锁或表锁数量,也就是我无法直观得知到底这里是行锁还是表锁!如果有知道的小伙伴请评论区分享!感谢分享!

# 这里是搜的时候的一些命令
show status like 'innodb_row_lock%'; # 查看行锁信息
show status like 'Table_locks%'; # 查看表锁信息
(8)间隙锁的危害
  • 间隙锁带来的插入问题
    在这里插入图片描述

  • 什么叫间隙锁?
    当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,
    InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(GAP Lock)。

  • 间隙锁带来的危害?
    因为Query执行过程中通过过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值并不存在。
    间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

(9)总结

Innodb存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些,但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候,Innodb的整体性能和MyISAM相比就会有比较明显的优势了。

但是,Innodb的行级锁定同样也有其脆弱的一面,当我们使用不当的时候,可能会让Innodb的整体性能表现不仅不能比MyISAM高,甚至可能会更差

(10)优化建议
1. 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁。
2. 尽可能检索范围减小,避免或减少间隙锁
3. 尽量控制事务大小,减少锁定资源量和时间长度
4. 锁住某行后,尽量不要去调别的行或表,赶紧处理被锁住的行然后释放掉锁。
5. 涉及相同表的事务,对于调用表的顺序尽量保持一致。
6. 在业务环境允许的情况下,尽可能低级别事务隔离

3、页锁(了解)

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

热门文章

暂无图片
编程学习 ·

MySQL不完全干货教程(持续更新中)

已经有很多教程面面俱到、事无巨细,但实际上能用到的、消化的内容很少。本文聚焦于常见的使用场景,给出MySQL用法和基本原理说明。为便于实践和消化,同时提供了很多案例和脚本。 为了读者进一步深入学习、掌握自我升级的方法,提供了一些权威文档的参考。希望能帮助MySQL初中…
暂无图片
编程学习 ·

《剑指 Offer》——调整数组顺序使奇数位于偶数前面

1. 本题知识点 数组 2. 题目描述 输入一个整数数组,实现一个函数来调整该数组中数字的顺序,使得所有的奇数位于数组的前半部分,所有的偶数位于数组的后半部分,并保证奇数和奇数,偶数和偶数之间的相对位置不变。 例如: Input: [1,2,3,4,5]Output: [1,3,5,2,4]3. 解题思路 …
暂无图片
编程学习 ·

记一次spark-submi 提交python脚本 遇到的问题

一、通过spark-submit 提交报错如下 yarn运行模式spark用的版本是2.4.0是支持pandas_udf的,而且通过pyspark的shell命令行一条条执行 都是没有问题的 但是将代码作为文件用spark submit提交就报这个错误 二、解决办法: @pandas_udf(returnType=“string”, PandasUDFType.…
暂无图片
编程学习 ·

unordered_map/unorderd_set使用与哈希介绍

在C++98中,STL提供了底层为红黑树结构的一系列关联式容器,在查询时效率可达到 O(logN),即最差情况下 需要比较红黑树的高度次,当树中的节点非常多时,查询效率也不理想。最好的查询是,进行很少的比较次 数就能够将元素找到,因此在C++11中,STL又提供了4个unordered系列的…
暂无图片
编程学习 ·

Unity学习(C#)——正则表达式

正则表达式:专门用于字符串处理的语言。 可以 解决: 1.检索:获取我们想要的部分 2.匹配:判断给定字符串是否符合正则表达式的过滤逻辑。即表述了字符串书写的规则。 定位元字符 $、^ (要用using System.Text.RegularExpressions;) $在结尾处插入 ^在开头处插入string s =…
暂无图片
编程学习 ·

在海外如何寻找蓝海市场

2010年左右,我国跨境进口零售电商企业开始逐渐出现,到2015年电商数量实现了爆发式的增长。当然,随后就开始进入红海时代,网易考拉海购、天猫国际、京东全球购、唯品国际、小红书、洋码头占领了大部分市场。 国内红海,那就出海。 红海、蓝海概念的提出,让更多的创业者积极…
暂无图片
编程学习 ·

防火墙中的DMZ区域,Trust区域,Untrust区域

** 区域的作用: ** 1.安全策略都基于区域实施 2.在同一区域内部发生的数据流动是不存在风险的,不需要实施任何安全策略。 只有当不同安全区域之间发生数据流动时,才会触发设备的安全检查,并实施相应的安全策略。 3.一个接口只能属于一个区域,而一个区域可以有多个接口。 *…
暂无图片
编程学习 ·

STM32CubeMX5.6.1生成的代码无启动文件

丢失启动文件 使用这个版本的CubeMX,生成的代码里面没有对应的启动文件。编译报错 展开图片,在Drivers/CMSIS文件夹下面,没有启动文件。编译不通过,报错No section matches selector - no section to be FIRST/LAST。//------------------------------------------ 解决方法…
暂无图片
编程学习 ·

Python超轻量数据库之SQLite

欢迎关注【无量测试之道】公众号,回复【领取资源】, Python编程学习资源干货、 Python+Appium框架APP的UI自动化、 Python+Selenium框架Web的UI自动化、 Python+Unittest框架API自动化、资源和代码 免费送啦~ 文章下方有公众号二维码,可直接微信扫一扫关注即可。1、什么是 SQ…
暂无图片
编程学习 ·

RPC框架正确的使用姿势

RPC框架-Thrift介绍RPC(Remote Procedure Call)远程过程调用,简单的理解是一个节点请求另一个节点提供的服务;本地过程调用:如果需要将本地student对象的age+1,可以实现一个addAge()方法,将student对象传入,对年龄进行更新之后返回即可,本地方法调用的函数体通过函数指…
暂无图片
编程学习 ·

Kotlin - 变量 val 和 var

什么是变量 变量是一个值的存储空间,这个值可以是一个字符串、一个数字或者其他东西。 每个变量都有一个名称(或标识符)来区别于其他变量。 可以通过变量的名称访问值。变量是程序中最常用的元素之一,因此理解如何使用它们非常重要。 声明变量 在开始使用变量之前,必须先声明…
暂无图片
编程学习 ·

https://www.cnblogs.com/hdk1993/p/5853233.html

c++文件打开方式详解 csdn编译器使用方法 欢迎使用Markdown编辑器 你好! 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章,了解一下Markdown的基本语法知识。 新的改变 我们对Markdown编辑器进行了一些功能拓展与…
暂无图片
编程学习 ·

Codeforces 1342 E Placing Rooks —— 第二类斯特林数

This way 题意: 现在有一个n*n的棋盘,n个棋子,你要放置这些棋子使得他们满足以下条件: 每个格子都能被某个棋子打到 共有k对棋子能够打到对方 如果一个格子所处的这一行或这一列有一个棋子,那么这个格子就能被打到。两个棋子处在同一行或同一列并且它们之间没有别的棋子,…
暂无图片
编程学习 ·

windows系统远程提权提升、MySQL UDF提权

本文目录权限提升提权本质提权分类windows系统提权基础命令windows提权辅助工具辅助工具介绍windows远程提权上手操作一下Mysql UDF提权udf介绍udf.dll获取上传udf执行提权命令 前言 小白一枚,之前听过的比较厉害的操作就是提权,维权。今天学习一下。 权限提升 提权本质 提权…
暂无图片
编程学习 ·

Java工厂模式解耦合的例子

目录第一个例子第二个例子第三个例子第四个例子Reference 最终项目路径如下:第一个例子 假设有如下接口, package service;public interface Notice {public void work(String message); }实现类如下, package service;public class NoticeImpl implements Notice {@Overrid…
暂无图片
编程学习 ·

PyPA Installing Packages

https://packaging.python.org/tutorials/installing-packages/#installing-packages 确认pip,seuptools,wheel都是最新 python -m pip install --upgrade pip setuptools wheel 可以创建虚拟环境 Installing from PyPI Installing from VCS Installing from other Indexes Ins…