文档章节

对于MySQL你必须要了解的锁知识

木木匠
 木木匠
发布于 04/15 09:19
字数 2168
阅读 344
收藏 14

一、前言

MySQL 的锁按照范围可以分为全局锁、表锁、行锁,其中行锁是由数据库引擎实现的,并不是所有的引擎都提供行锁,MyISAM 就不支持行锁,所以文章介绍行锁会以InnoDB引擎为例来介绍行锁。

二、全局锁

MySQL 提供全局锁来对整个数据库实例加锁。

语法:

FLUSH TABLES WITH READ LOCK

这条语句一般都是用来备份的,当执行这条语句后,数据库所有打开的表都会被关闭,并且使用全局读锁锁定数据库的所有表,同时,其他线程的更新语句(增删改),数据定义语句(建表,修改表结构)和更新类的事务提交都会被阻塞。

在mysql 8.0 以后,对于备份,mysql可以直接使用备份锁。

语句:

LOCK INSTANCE FOR BACKUP

UNLOCK INSTANCE

这个锁的作用范围更广,这个锁会阻止文件的创建,重命名,删除,包括 REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE操作以及账户的管理都会被阻塞。当然这些操作对于内存临时表来说是可以执行的,为什么内存表不受这些限制呢?因为内存表不需要备份,所以也就没必要满足这些条件。

三、表锁

Mysql的表级别锁分为两类,一类是元数据锁(Metadata Lock,MDL),一种是表锁。

元数据锁(MDL) 不需要显式使用,在访问一个表的时候会被自动加上。这个特性需要MySQL5.5版本以上才会支持,当对一个表做增删改查的时候,该表会被加MDL读锁;当对表做结构变更的时候,加MDL写锁。MDL锁有一些规则:

  • 读锁之间不互斥,所以可以多线程多同一张表进行增删改查。
  • 读写锁、写锁之间是互斥的,为了保证表结构变更的安全性,所以如果要多线程对同一个表加字段等表结构操作,就会变成串行化,需要进行锁等待。
  • MDL的写锁优先级比MDL读锁的优先级,但是可以设置max_write_lock_count系统变量来改变这种情况,当写锁请求超过这个变量设置的数后,MDL读锁的优先级会比MDL写锁的优先级高。(默认情况下,这个数字会很大,所以不用担心写锁的优先级下降)
  • MDL的锁释放必须要等到事务结束才会释放

所以我们在操作数据库表结构时候必须要注意不要使用长事务,这里具体是什么意思呢?我举个例子说明下:

上图表示演示了4个session执行语句,首先SessionA开启了事务没有提交,接着sessionB执行查询,因为是获取MDL读锁,所以互相不影响,可以正常执行,SessionC新增一个字段,由于MDL写和读是互斥的,所以SessionC会被阻塞,之后SessionD开始执行一个查询语句,由于SessionC的阻塞,所以SessionD也阻塞了。所以,我们模拟的SessionA的事务是长事务,然后后面执行了修改表结构,会导致后续对该表所有的读写操作都不可行了。所以在实际场景中,如果业务请求比较频繁的时候,对表结构进行修改的时候就有可能导致该库的线程被阻塞满。

表锁 的语法如下:

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES

表锁分为读锁和写锁,读锁不互斥,但是获取读锁不能写入数据,其他没有获取到读锁的session也是可以读取表的,所以读锁的目的就是限制表被写。如果表被读锁锁住后,再执行插入语句会报错,报错如下:

1099 - Table 'XXXX' was locked with a READ lock and can't be updated

写锁被获取后可以对表进行读写,写锁是互斥的,一旦某个session获取到表的写锁,另外的session无法访问这个表,直到写锁被释放。

表的解锁可以使用unlock tables解锁,也可以客户端口自动解锁。lock tables锁表会独占式的锁住表,除了限制其他线程对该表的读写,也会限制本线程接下来的操作对象。

四、行锁(InnoDB)

MySQL的行锁是在引擎层面实现的,所以这里讨论的也是InnoDB引擎下的行锁,下面会详细介绍InnoDB下常见的几种行锁

4.1 共享锁

共享锁能允许事务获取到锁后进行读操作,共享锁是不互斥的,一个事务获取到共享锁后,另外一个事务也可以获取共享锁,获取共享锁后不能进行写操作。

4.2 排它锁

排他锁允许事务获取到锁后进行更新一行或者删除某一行操作,排他锁顾名思义是互斥的,一个事务获取到排他锁后,其他事务不能获取到排他锁,直到这个锁被释放。

4.3 意向锁

InnoDB支持多种粒度的锁,允许行锁和表锁共存,这里说的意向锁其实是一种表级别的锁,但是我把它放在行锁里面是因为它不会单独存在,它的出现肯定会伴随着行锁(共享锁或者排他锁),它主要的目的就是表示将要锁定表中的行或者正在锁定表中的行。

意向锁根据和行锁的组合可以分为:

  • 意向排他锁:表明将要在表中的某些行获取排他锁

  • 意向共享锁:表明将要在表中的某些行获取共享锁

意向锁的获取必须在行锁获取之前,也就是说获取共享锁之前必须先要获取共享意向锁,对于排他锁也是一样的道理。

那么这个意向锁到底有什么作用呢?

解释这个之前,我们先看看意向锁和行锁之前的兼容关系:

---排他锁(X)意向排他锁(IX)共享锁(S)意向共享锁(IS)
排他锁(X)冲突冲突冲突冲突
意向排他锁(IX)冲突兼容冲突兼容
共享锁(S)冲突冲突兼容兼容
意向共享锁(IS)冲突兼容兼容兼容

我们假设有2个事务A和事务B,事务获取到了共享锁,锁住了表中的某一行,这一行只能读,不能写,现在事务B要申请整个表的写锁。如果事务B申请成功,那么肯定是可以对表中所有的行进行写操作的,那么肯定与A获取的行锁冲突。数据库为了避免这种冲突,就会进行冲突检测,那么如何去检测呢?有两种方式:

  • 判断表是否已经被其他事务用表级锁锁住。
  • 判断表中的每一行是否被行锁锁住。

判断表中的每一行需要遍历所有记录,效率太差,所以数据库就用第一种方式去做冲突检测,也就是用到了意向锁。

总结

本文主要从MySQL的加锁范围来分析了MySQL的锁,MySQL根据加锁范围可以分为全局锁、表锁、行锁。全局锁和表锁是MySQL自己实现,行锁都是由引擎层面去实现。InnoDB下的行锁主要分为共享锁和排他锁。共享锁请求后,行只能读,共享锁之间不互斥。排他锁获取后能更新和删除行,排他锁与其他锁都互斥。最后我在行锁的基础上提到了意向锁,意向锁主要表示正在锁住行或者即将锁住行,为了在锁冲突检测中提高效率。当然InnoDB下还有其他锁,比如间隙锁,记录锁,Next-Key锁等,这些都不在本文的探讨范围之内,如有兴趣的同学可以自行研究。

参考

© 著作权归作者所有

上一篇: 位运算
下一篇: MySql索引那些事
木木匠
粉丝 62
博文 27
码字总数 54247
作品 0
广州
高级程序员
私信 提问
加载中

评论(4)

木木匠
木木匠

引用来自“yj7758423”的评论

看来也是看过丁奇老师mysql课程的同学😄

@yj7758423 嘿嘿,多多指教
yj7758423
yj7758423
看来也是看过丁奇老师mysql课程的同学😄
木木匠
木木匠

引用来自“小熊猫大暴走”的评论

"读锁之间不互斥,所以可以多线程多同一张表进行增删改查。" ?, 增删改不算是写锁吗? 本人小白
这里说的读锁是在MDL元数据锁下面说的,如果对一张表进行更新操作,那么首先会获取MDL读锁,然后再获取到某一行的排它锁。这里所说的读写并不是数据的读写,而是表结构的读写,对于MDL锁来说,只有修改表结构才算写。
小熊猫大暴走
小熊猫大暴走
"读锁之间不互斥,所以可以多线程多同一张表进行增删改查。" ?, 增删改不算是写锁吗? 本人小白
MySQL 笔记 - 索引优化

巧妙的使用 Explain 看一条 SQL 语句的性能,可以使用explain关键字查看语句性能,这里说一下其中的type字段的部分含义, all,即全表扫描,说明这个 SQL 语句没有使用到索引,有可能是表本身...

Java架构解析
2018/09/16
0
0
mysql锁知识小了解

一、概述 mysql的锁分为表锁和行锁两种,其中myisam引擎用的是表锁, innoDB默认的使用是行锁, 其他情况是表锁。 两种锁的优缺点: 表级锁:加锁速度快,开销小。不会出现死锁的情况,粒度大...

GuoYJ
2015/09/13
201
0
为什么开发人员必须要了解数据库锁?

1.锁? 1.1何为锁 锁在现实中的意义为:封闭的器物,以钥匙或暗码开启。在计算机中的锁一般用来管理对共享资源的并发访问,比如我们java同学熟悉的Lock,synchronized等都是我们常见的锁。当然...

Java架构解析
2018/08/10
0
0
谈谈MySQL支持的事务隔离级别,以及悲观锁和乐观锁的原理和应用场景?

在日常开发中,尤其是业务开发,少不了利用 Java 对数据库进行基本的增删改查等数据操作,这也是 Java 工程师的必备技能之一。做好数据操作,不仅仅需要对 Java 语言相关框架的掌握,更需要对...

架构师springboot
01/02
0
0
青铜到王者,看看你的MySQL数据库是什么段位,如何提升?

新的一周,老张(superZS)与大家见面,我们又要面临快速的生活节奏而令人厌恶的工作!现在大多数人选择放松自己的方式就是玩游戏,最为突出的可能就要属手游"王者荣耀"。 据说这款游戏上到7...

技术小能手
2018/06/07
0
0

没有更多内容

加载失败,请刷新页面

加载更多

OSChina 周二乱弹 —— 吾不好梦中插人

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @鱼豆腐233 :#今日歌曲分享# 分享My Chemical Romance的单曲《I Don't Love You》: 《I Don't Love You》- My Chemical Romance 手机党少年们...

小小编辑
今天
204
10
ss5 vpn 安装(linux版本)

1. 创建一个文件夹 /ss5 你也可以自定义,不过后续的地方需要注意自己的地址 2. 下载ss5文件(如果你的服务器没有安装wget请使用 yum -y install wget 命令安装 如果连yum都没安装自己查去)(下...

太黑_thj
今天
2
0
八、RabbitMQ的集群原理

集群架构 写在前面 RabbitMQ集群是按照低延迟环境设计的,千万不要跨越WAN或者互联网来搭建RabbitMQ集群。如果一定要在高延迟环境下使用RabbitMQ集群,可以参考使用Shovel和Federation工具。...

XuePeng77
今天
6
0
mac系统下,brew 安装mysql,用终端可以连接,navicat却连接不上?

问题: 1.报错? 2059 - Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(../Frameworks/caching_sha2_password.so, 2): image not found 2.自己通过设置,已经把密......

写bug的攻城狮
昨天
3
0
老生常谈,HashMap的死循环

问题 最近的几次面试中,我都问了是否了解HashMap在并发使用时可能发生死循环,导致cpu100%,结果让我很意外,都表示不知道有这样的问题,让我意外的是面试者的工作年限都不短。 由于HashMap...

群星纪元
昨天
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部