专栏首页飞天小牛肉三分钟入门 InnoDB 存储引擎中的表锁和行锁

三分钟入门 InnoDB 存储引擎中的表锁和行锁

各位对 ”锁“ 这个概念应该都不是很陌生吧,Java 语言中就提供了两种锁:内置的 synchronized 锁和 Lock 接口,使用锁的目的就是管理对共享资源的并发访问,保证数据的完整性和一致性,数据库中的锁也不例外。

“锁" 是数据库系统区别于文件系统的一个关键特性,其对象是事务,用来锁定的是数据库中的对象,如表、页、行等。需要注意的是,每种数据库对于锁的实现都是不同的,并且对于 MySQL 来说,每种存储引擎都可以实现自己的锁策略和锁粒度,比如 InnoDB 引擎支持行锁和表锁,而 MyISAM 引擎只支持表锁

本文所讲的锁针对的是我们最常用的 InnoDB 存储引擎。

表锁与行锁

所谓 “表锁 (Table Lock)”,就是会锁定整张表,它是 MySQL 中最基本的锁策略,并不依赖于存储引擎,就是说不管你是 MySQL 的什么存储引擎,对于表锁的策略都是一样的,并且表锁是开销最小的策略(因为粒度比较大)。

由于表级锁一次会将整个表锁定,所以可以很好的避免死锁问题。当然,锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高,导致并发率大打折扣。

而所谓 “行锁(Row Lock)”,也称为记录锁,顾名思义,就是锁住某一行(某条记录 row)。需要的注意的是,MySQL 服务器层并没有实现行锁机制,行级锁只在存储引擎层实现 !!!

读锁和写锁

首先说明一点,对于 InnoDB 引擎来说,读锁和写锁可以加在表上,也可以加在行上

对于并发读和并发写的问题,可以通过实现一个由两种类型的锁组成的锁系统来解决。这两种类型的锁通常被称为 共享锁(Shared Lock,S Lock)排他锁(Exclusive Lock,X Lock),也叫 读锁(readlock)写锁(write lock)

  • 共享锁 / 读锁:允许事务读(select)数据
  • 排他锁 / 写锁:允许事务删除(delete)或更新(update)数据

读锁是共享的,或者说是相互不阻塞的。多个事务在同一时刻可以同时读取同一个资源,而互不干扰。写锁是排他的,也就是说一个写锁会阻塞其他的读锁和写锁,这样就能确保在给定的时间里,只有一个事务能执行写入,并防止其他用户读取正在写入的同一资源。

用行级读写锁来举个例子吧:如果一个事务 T1 已经获得了某个行 r 的读锁,那么此时另外的一个事务 T2 是可以去获得这个行 r 的读锁的,因为读取操作并没有改变行 r 的数据;但是,如果某个事务 T3 想获得行 r 的写锁,则它其必须等待事务 T1、T2 释放掉行 r 上的读锁才行。

兼容关系如下表(兼容是指对同一张表或记录的锁的兼容性情况):

X 锁

S 锁

X 锁

不兼容

不兼容

S 锁

不兼容

兼容

从上表可以看出,只有共享锁和共享锁是兼容的,而排他锁和谁都是不兼容的。

意向锁

InnoDB 存储引擎支持 多粒度(granular)锁定,就是说允许事务在行级上的锁和表级上的锁同时存在

那么为了实现行锁和表锁并存,InnoDB 存储引擎就设计出了 意向锁(Intention Lock) 这个东西:

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

很好理解:意向锁是一个表级锁,其作用就是指明接下来的事务将会用到哪种锁。

有两种意向锁:

  • 意向共享锁(IS Lock):当事务想要获得一张表中某几行的共享锁行级锁)时,InnoDB 存储引擎会自动地先获取该表的意向共享锁(表级锁)
  • 意向排他锁(IX Lock):当事务想要获得一张表中某几行的排他锁(行级锁)时,InnoDB 存储引擎会自动地先获取该表的意向排他锁(表级锁)

各位其实可以直接把 ”意向“ 翻译成 ”想要“,想要共享锁、想要排他锁,你就会发现原来就这东西啊(滑稽)。

意向锁之间是相互兼容的:

IS 锁

IX 锁

IS 锁

兼容

兼容

IX 锁

兼容

兼容

但是与表级读写锁之间大部分都是不兼容的:

X 锁

S 锁

IS 锁

不兼容

兼容

IX 锁

不兼容

不兼容

注意,这里强调一点:上表中的读写锁指的是表级锁,意向锁不会与行级的读写锁互斥!!!

来理解一下为什么说意向锁不会与行级的读写锁互斥。举个例子,事务 T1、事务 T2、事务 T3 分别想对某张表中的记录行 r1、r2、r3 进行修改,很普通的并发场景对吧,这三个事务之间并不会发生干扰,所以是可以正常执行的。

这三个事务都会先对这张表加意向写锁,因为意向锁之间是兼容的嘛,所以这一步没有任何问题。那如果意向锁和行级读写锁互斥的话,岂不是这三个事务都没法再执行下去了,对吧。

OK,看到这里,我们来思考两个问题:

1)为什么没有意向锁的话,表锁和行锁不能共存?

2)意向锁是如何让表锁和行锁共存的?

首先来看第一个问题,假设行锁和表锁能共存,举个例子:事务 T1 锁住表中的某一行(行级写锁),事务 T2 锁住整个表(表级写锁)。

问题很明显,既然事务 T1 锁住了某一行,那么其他事务就不可能修改这一行。这与 ”事务 T2 锁住整个表就能修改表中的任意一行“ 形成了冲突。所以,没有意向锁的时候,行锁与表锁是无法共存的。

再来看第二个问题,有了意向锁之后,事务 T1 在申请行级写锁之前,MySQL 会先自动给事务 T1 申请这张表的意向排他锁,当表上有意向排他锁时其他事务申请表级写锁会被阻塞,也即事务 T2 申请这张表的写锁就会失败。

如何加锁

在说加锁之前,我们有必要了解下解锁机制。对于 InnoDB 来说,随时都可以加锁,但是并非随时都可以解锁。具体来说,InnoDB 采用的是两阶段锁定协议(two-phase locking protocol):即在事务执行过程中,随时都可以执行加锁操作,但是只有在事务执行 COMMIT 或者 ROLLBACK 的时候才会释放锁,并且所有的锁是在同一时刻被释放。

说完了解锁机制,再来讲讲加锁机制。

先来看如何加意向锁,它比较特殊,是由 InnoDB 存储引擎自己维护的,用户无法手动操作意向锁,在为数据行加读写锁之前,InooDB 会先获取该数据行所在在数据表的对应意向锁。

再来看如何加表级锁:

1)隐式锁定:对于常见的 DDL 语句(如 ALTERCREATE 等),InnoDB 会自动给相应的表加表级锁

2)显示锁定:在执行 SQL 语句时,也可以明确显示指定对某个表进行加锁(lock table user read(write)

lock table user read; # 加表级读锁
unlock tables; # 释放表级锁

如何加行级锁:

1)对于常见的 DML 语句(如 UPDATEDELETEINSERT ),InnoDB 会自动给相应的记录行加写锁

2)默认情况下对于普通 SELECT 语句,InnoDB 不会加任何锁,但是在 Serializable 隔离级别下会加行级读锁

上面两种是隐式锁定,InnoDB 也支持通过特定的语句进行显式锁定,不过这些语句并不属于 SQL 规范:

3)SELECT * FROM table_name WHERE ... FOR UPDATE,加行级写锁

4)SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE,加行级读锁

另外,需要注意的是,InnoDB 存储引擎的行级锁是基于索引的(这个下篇文章会详细解释),也就是说当索引失效或者说根本没有用索引的时候,行锁就会升级成表锁

举个例子(这里就以比较典型的索引失效情况 “使用 or" 来举例),有数据库如下,id 是主键索引:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

新建两个事务,先执行事务 T1 的前两行,也就是不要执行 rollback 也不要 commit:

这个时候事务 T1 没有释放锁,并且由于索引失效事务 T1 其实是锁住了整张表,此时再来执行事务 2,你会发现事务 T2 会卡住,最后超时关闭事务:

本文分享自微信公众号 - 飞天小牛肉(CS-Wiki),作者:飞天小牛肉

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-07-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 图文实例解析,InnoDB 存储引擎中行锁的三种算法

    前文提到,对于 InnoDB 来说,随时都可以加锁(关于加锁的 SQL 语句这里就不说了,忘记的小伙伴可以翻一下上篇文章),但是并非随时都可以解锁。具体来说,I...

    飞天小牛肉
  • 全面分析 MySQL并发控制

    无论何时,只要有多个查询需要在同一时刻查询数据,都会产生并发问题。 我也不多废话,如果是进来找代码实现的,请移步:不是你记忆中的单例模式,但适用的程度,更胜一...

    看、未来
  • 温故而知新:MySQL存储引擎入门介绍

    写在前面:2020年面试必备的Java后端进阶面试题总结了一份复习指南在Github上,内容详细,图文并茂,有需要学习的朋友可以Star一下! GitHub地...

    用户5546570
  • 深入浅出MySQL - MyISAM有趣的那些“锁”事儿

      今天我们一起来聊聊MyISAM存储引擎中的锁,MySQL中的表锁主要使用对象就是MyISAM存储引擎,大家可能会疑问,Innodb不用表锁吗?

    陈哈哈
  • Mysql锁初步

    存储引擎 要了解mysql的锁,就要先从存储引擎说起。 常用存储引擎列表如下图所示: ? 最常使用的两种存储引擎: Myisam是Mysql的默认存储引擎。当c...

    SecondWorld
  • MySQL性能优化(一):MySQL架构与核心问题

    作为程序员的你,数据库作为一门必修课,而MySQL数据库毫无疑问已经是最常用的数据库了。系统的稳定、高效、高并发等指标,很大程度上取决于数据库性能是否够优,可见...

    田维常
  • MySQL性能优化(一):MySQL架构与核心问题

    作为程序员的你,数据库作为一门必修课,而MySQL数据库毫无疑问已经是最常用的数据库了。系统的稳定、高效、高并发等指标,很大程度上取决于数据库性能是否够优,可见...

    xcbeyond
  • MySQL锁机制及优化

    总的来说,MySQL各存储引擎使用了三种类型(级别)的锁定机制:行级锁定,页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。

    lyb-geek
  • MySQL锁详解

    转载自http://www.cnblogs.com/luyucheng/p/6297752.html

    allsmallpig
  • Java开发5年,四面美团(多线程+redis+JVM+数据库),终拿offer!

    Java开发五年多.投递阿里、腾讯、头条、美团、京东等各互联网公司的高级Java岗位,最终得到了美团的面试机会,并成功拿下美团高级Java岗的offer。美团J...

    程序员追风
  • 后端程序员必备:Mysql数据库相关流程图与原理图

    mysql主从复制原理是大厂后端的高频面试题,了解mysql主从复制原理非常有必要。

    Bug开发工程师
  • 后端程序员必备:Mysql数据库相关流程图/原理图

    mysql主从复制原理是大厂后端的高频面试题,了解mysql主从复制原理非常有必要。

    macrozheng
  • MySQL - 常用存储引擎区别总结(2020最新版)

    为什么先说InnoDB?InnoDB 从 MySQL5.5(2010年) 版本代替 MyISAM 成为默认引擎,可以说只要玩儿过 MySQL 的,都用过Inno...

    陈哈哈
  • 面试必备常见存储引擎与锁的分类,请查收

    我们在上篇文章中提到了记录锁(行锁)、间隙锁和临键锁,后台有小伙伴催我更新一下其他的锁。拖延症又犯了,趁周末,今天我们来总结一下MyISAM和InnoDB引擎下...

    阿Q说代码
  • MySQL面试题/知识点总结!(2021 最新版)

    这篇文章之前发过,不过,我最近对其进行了重构完善并且修复了很多小问题。所以,在公号再同步一下!

    Guide哥
  • MySQL探秘(三):InnoDB的内存结构和特性

     常言说得好,每个成功男人背后都有一个为他默默付出的女人,而对于MySQL来说,这个“人”就是InnoDB存储引擎。  MySQL区别于其他数据库的最为重要的特...

    aoho求索
  • MySQL探秘(三):InnoDB的内存结构和特性

    常言说得好,每个成功男人背后都有一个为他默默付出的女人,而对于MySQL来说,这个“人”就是InnoDB存储引擎。  MySQL区别于其他数据库的最为重要的特点...

    用户3128938
  • MySQL探秘(三):InnoDB的内存结构和特性

     常言说得好,每个成功男人背后都有一个为他默默付出的女人,而对于MySQL来说,这个“人”就是InnoDB存储引擎。  MySQL区别于其他数据库的最为重要的特...

    程序员历小冰
  • innodb存储引擎原理

    存储引擎位于文件系统(各种数据,二进制形式)之上,各种管理工具(连接池、语义分析器、优化器、缓存区、SQL接口)之下。

    mariolu

扫码关注云+社区

领取腾讯云代金券