MySQL InnoDB中的锁机制深入讲解

写在前面

数据库本质上是一种共享资源，因此在最大程度提供并发访问性能的同时，仍需要确保每个用户能以一致的方式读取和修改数据。锁机制（Locking）就是解决这类问题的最好武器。

首先新建表 test，其中 id 为主键，name 为辅助索引，address 为唯一索引。

CREATE TABLE `test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` int(11) NOT NULL,
 `address` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `idex_unique` (`address`),
 KEY `idx_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

INSERT 方法中的行锁

可见，如果两个事务先后对主键相同的行记录执行 INSERT 操作，因为事务 A 先拿到了行锁，事务 B 只能等待直到事务 A 提交后行锁被释放。同理，如果针对唯一索引字段 address 进行插入操作，也需要获取行锁，图同主键插入过程类似，不再重复。

但是，如果两个事务都针对辅助索引字段 name 进行插入，不需要等待获取锁，因为辅助索引字段即使值相同，在数据库中也是操作不同的记录行，不会冲突。

Update 方法与 Insert 方法结果类似。

SELECT FOR UPDATE 下的表锁与行锁

事务 A SELECT FOR UPDATE 语句会拿到表 test 的 Table Lock，此时事务 B 去执行插入操作会阻塞，直到事务 A 提交释放表锁后，事务 B 才能获取对应的行锁执行插入操作。

但是如果事务 A 的 SELECT FOR UPDATE 语句紧跟 WHERE id = 1 的话，那么这条语句只会获取行锁，不会是表锁，此时不阻塞事务 B 对于其他主键的修改操作

辅助索引下的间隙锁

先看下 test 表下的数据情况：

mysql> select * from test;
+----+------+---------+
| id | name | address |
+----+------+---------+
| 3 | 1 |  3 |
| 6 | 1 |  2 |
| 7 | 2 |  4 |
| 8 | 10 |  5 |
+----+------+---------+
4 rows in set (0.00 sec)

间隙锁可以说是行锁的一种，不同的是它锁住的是一个范围内的记录，作用是避免幻读，即区间数据条目的突然增减。解决办法主要是：

  • 防止间隙内有新数据被插入，因此叫间隙锁
  • 防止已存在的数据，在更新操作后成为间隙内的数据（例如更新 id = 7 的 name 字段为 1，那么 name = 1 的条数就从 2 变为 3）

InnoDB 自动使用间隙锁的条件为：

  • Repeatable Read 隔离级别，这是 MySQL 的默认工作级别
  • 检索条件必须有索引（没有索引的话会走全表扫描，那样会锁定整张表所有的记录）

当 InnoDB 扫描索引记录的时候，会首先对选中的索引行记录加上行锁，再对索引记录两边的间隙（向左扫描扫到第一个比给定参数小的值， 向右扫描扫描到第一个比给定参数大的值， 以此构建一个区间）加上间隙锁。如果一个间隙被事务 A 加了锁，事务 B 是不能在这个间隙插入记录的。

我们这里所说的 “间隙锁” 其实不是 GAP LOCK，而是 RECORD LOCK + GAP LOCK，InnoDB 中称之为 NEXT_KEY LOCK

下面看个例子，我们建表时指定 name 列为辅助索引，目前这列的取值有 [1,2,10]。间隙范围有 (-∞, 1]、[1,1]、[1,2]、[2,10]、[10, +∞)

Round 1:

  • 事务 A SELECT ... WHERE name = 1 FOR UPDATE;
  • 对 (-∞, 2) 增加间隙锁
  • 事务 B INSERT ... name = 1 阻塞
  • 事务 B INSERT ... name = -100 阻塞
  • 事务 B INSERT ... name = 2 成功
  • 事务 B INSERT ... name = 3 成功

Round 2:

  • 事务 A SELECT ... WHERE name = 2 FOR UPDATE;
  • 对 [1, 10) 增加间隙锁
  • 事务 B INSERT ... name = 1 阻塞
  • 事务 B INSERT ... name = 9 阻塞
  • 事务 B INSERT ... name = 10 成功
  • 事务 B INSERT ... name = 0 成功

Round 3:

  • 事务 A SELECT ... WHERE name <= 2 FOR UPDATE;
  • 对 (-∞, +∞) 增加间隙锁
  • 事务 B INSERT ... name = 3 阻塞
  • 事务 B INSERT ... name = 300 阻塞
  • 事务 B INSERT ... name = -300 阻塞

InnoDB 锁机制总结

参考资料

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对我们的支持。

赞 (0)

相关推荐

  • MySQL中InnoDB的间隙锁问题

    在为一个客户排除死锁问题时我遇到了一个有趣的包括InnoDB间隙锁的情形.对于一个WHERE子句不匹配任何行的非插入的写操作中,我预期事务应该不会有锁,但我错了.让我们看一下这张表及示例UPDATE. mysql> SHOW CREATE TABLE preferences \G *************************** 1. row *************************** Table: preferences Create Table: CREATE TABLE

  • MySQL InnoDB之事务与锁详解

    引题:为何引入事务? 1>.数据完整性 2>.数据安全性 3>.充分利用系统资源,提高系统并发处理的能力 1. 事务的特征 事务具有四个特性:原子性(Atomiocity).一致性(Consistency).隔离性(Isolation)和持久性(Durability),这四个特性简称ACID特性. 1.1原子性 事务是数据库的逻辑工作单位,事务中包括的所有操作要么都做,要么都不做. 1.2 一致性 事务执行的结果必须是使数据库从一个一致性的状态变到另外一个一致性状态. 1.3 隔离性 一

  • mysql之innodb的锁分类介绍

    一.innodb行锁分类 record lock:记录锁,也就是仅仅锁着单独的一行 gap lock:区间锁,仅仅锁住一个区间(注意这里的区间都是开区间,也就是不包括边界值. next-key lock:record lock+gap lock,所以next-key lock也就半开半闭区间,且是下界开,上界闭. www.jb51.net next-key 锁定范围:(负无穷大,最小第一记录],(记录之间],(最大记录,正无穷大) 二.语句锁定情况分析 SELECT ... FROM ... F

  • MySQL Innodb表导致死锁日志情况分析与归纳

    MySQL Innodb表导致死锁日志情况分析与归纳

    案例描述在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志.两个sql语句如下:(1)insert into backup_table select * from source_table(2)DELETE FROM source_table WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week'teamUser表的表结构如下:PRIMARY

  • MySQL中Innodb的事务隔离级别和锁的关系的讲解教程

    MySQL中Innodb的事务隔离级别和锁的关系的讲解教程

    前言: 我们都知道事务的几种性质,数据库为了维护这些性质,尤其是一致性和隔离性,一般使用加锁这种方式.同时数据库又是个高并发的应用,同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理能力.所以对于加锁的处理,可以说就是数据库对于事务处理的精髓所在.这里通过分析MySQL中InnoDB引擎的加锁机制,来抛砖引玉,让读者更好的理解,在事务处理中数据库到底做了什么. 一次封锁or两段锁? 因为有大量的并发访问,为了预防死锁,一般应用中推荐使用一次封锁法,就是在方法的开始阶段,已经预先知道会

  • MySQL中InnoDB存储引擎的锁的基本使用教程

    MySQL中InnoDB存储引擎的锁的基本使用教程

    MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-leve locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 各种锁特点 表级锁:开销小,加锁快:不会出现死锁:锁定粒度大,发生冲突的概率最高,并发度最低 行级锁:开销大,加锁慢:会出现死锁:锁定粒度最小,发生锁冲突的概率最低,并发度也最高 页面锁:开销和加锁时间介于表锁和行锁之间:会出现死锁:锁定粒度介于表锁和行锁之

  • 详解MySQL（InnoDB）是如何处理死锁的

    详解MySQL（InnoDB）是如何处理死锁的

    一.什么是死锁 官方定义如下:两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁. 这个就好比你有一个人质,对方有一个人质,你们俩去谈判说换人.你让对面放人,对面让你放人. 二.为什么会形成死锁 看到这里,也许你会有这样的疑问,事务和谈判不一样,为什么事务不能使用完锁之后立马释放呢?居然还要操作完了之后一直持有锁?这就涉及到 MySQL 的并发控制了. MySQL的并发控制有两种方式,一个是 MVCC,一个是两阶段锁协议.那么为什么要并发控制呢?是因为多个用户同时操作 M

  • MySQL InnoDB中的锁机制深入讲解

    MySQL InnoDB中的锁机制深入讲解

    写在前面 数据库本质上是一种共享资源,因此在最大程度提供并发访问性能的同时,仍需要确保每个用户能以一致的方式读取和修改数据.锁机制(Locking)就是解决这类问题的最好武器. 首先新建表 test,其中 id 为主键,name 为辅助索引,address 为唯一索引. CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) NOT NULL, `address` int(11) NOT NULL, P

  • mysql中的锁机制深入讲解

    前言 为了保证数据的一致完整性,任何一个数据库都存在锁定机制.锁定机制的优劣直接应想到一个数据库系统的并发处理能力和性能,所以锁定机制的实现也就成为了各种数据库的核心技术之一. 大概几个月之前项目中用到事务,需要保证数据的强一致性,期间也用到了mysql的锁,但当时对mysql的锁机制只是管中窥豹,所以本文打算总结一下mysql的锁机制. 本文主要论述关于mysql锁机制,mysql版本为5.7,引擎为innodb,由于实际中关于innodb锁相关的知识及加锁方式很多,所以没有那么多精力罗列所有

  • MySQL隔离级别和锁机制的深入讲解

    MySQL隔离级别和锁机制的深入讲解

    目录 简述: 1. 事务的四大特性 2.多事务并发带来的问题 3.事务的隔离级别 4.演示不同隔离级别出现的问题 读未提交 读已提交 可重复读 串行化 5.锁机制 间隙锁 临建锁 排他锁 总结 简述: 我们的MySQL一般会并发的执行多个事务,多个事务可能会并发的对同一条或者同一批数据进行crud操作;可能就会导致我们平常所说的脏读.不可重复读.幻读这些问题. 这些问题的本质都是MySQL多事务并发问题,为了解决多事务并发问题,MySQL设计了锁机制.MVCC多版本并发控制隔离机制.以及事务隔离

  • Mysql InnoDB中B+树索引使用注意事项

    Mysql InnoDB中B+树索引使用注意事项

    目录 一.根页面万年不动 二.内节点中目录项记录的唯一性 三.一个页面至少容纳 2 条记录 一.根页面万年不动 在之前的文章里,为了方便理解,都是先画存储用户记录的叶子节点,然后再画出存储目录项记录的内节点. 但实际上 B+ 树的行成过程是这样的: 每当为某个表创建一个 B+ 树索引,都会为这个索引创建一个根节点页面.最开始表里没数据,所以根节点中既没有用户记录,也没有目录项记录. 当往表里插入用户记录时,先把用户记录存储到这个根节点上. 当根节点页空间用完,继续插入记录,此时会将根节点中所有记

  • Tomcat和Spring中的事件机制深入讲解

    Tomcat和Spring中的事件机制深入讲解

    引言 最近在看tomcat源码,源码中出现了大量事件消息,可以说整个tomcat的启动流程都可以通过事件派发机制串起来,研究透了tomcat的各种事件消息,基本上对tomcat的启动流程也就有了一个整体的认识.在这一基础上,联想到之前在看spring源码过程中也存在不少事件相关知识,于是想对这两个框架中的事件派发机制做一个简单的总结,加深理解. 事件机制原理其实比较简单,抽象来看的话,设计模式中的观察者模式可以说是最经典的事件驱动机制的体现了,观察者和被观察者就体现了事件监听和事件派发的角色.还

  • Kotlin中的反射机制深入讲解

    前言 Java中的反射机制,使得我们可以在运行期获取Java类的字节码文件中的构造函数,成员变量,成员函数等信息.这一特性使得反射机制被常常用在框架中,想要比较系统的了解Kotlin中的反射,先从Java的反射说起. Java中的反射 通常我们写好的.java源码文件,经过javac的编译,最终生成了.class字节码文件.这些字节码文件是与平台无关的,使用时通过Classloader去加载这些.class字节码文件,从而让程序按照我们编写好的业务逻辑运行.Java的反射主要是从这些.class

  • linux中各种锁机制的使用与区别详解

    linux中各种锁机制的使用与区别详解

    前言: 相信需要了解这方面的知识的小伙伴,已经基本对进程间通信和线程间通信有了一定了解.例如,进程间通信的机制之一:共享内存(在这里不做详解):多个进程可同时访问同一块内存.如果不对访问这块内存的临界区进行互斥或者同步,那么进程的运行很可能出现一些不可预知的错误和结果. 接下来我们了解三种常见的Linux下的互斥操作->锁. 1.互斥锁(mutex) 特点:对于读者和写者来说.只要有一方获取了锁,另一方则不能继续获取,进而执行临界区代码. 创建锁: 有两种方法创建互斥锁,静态方式和动态方式.PO

  • PHP中的异常处理机制深入讲解

    1.异常概述 异常(Exception)是一种错误处理机制,用于在指定的错误发生时改变脚本的正常流程. 当异常被触发时,当前代码状态被保存,代码执行被切换到预定义的异常处理器函数(如果有) 根据情况,处理器也许会从保存的代码状态重新开始执行代码,终止脚本执行,或从代码中另外的位置继续执行脚本 2.异常的基本使用 当异常被抛出时,其后的代码不会继续执行,PHP 会尝试查找匹配的 "catch" 代码块. 如果异常没有被捕获,而且又没用使用 set_exception_handler()

  • spring-boot中的SPI机制实例讲解

    spring-boot中的SPI机制实例讲解

    一.从java类加载机制说起 java中的类加载器负载加载来自文件系统.网络或者其他来源的类文件.jvm的类加载器默认使用的是双亲委派模式.三种默认的类加载器Bootstrap ClassLoader.Extension ClassLoader和System ClassLoader(Application ClassLoader)每一个中类加载器都确定了从哪一些位置加载文件.于此同时我们也可以通过继承java.lang.classloader实现自己的类加载器. Bootstrap ClassL

  • 实例讲解MySQL中乐观锁和悲观锁

    数据库管理系统中并发控制的任务是确保在多个事务同时存取数据库中同一数据不破坏事务的隔离性和统一性以及数据库的统一性 乐观锁和悲观锁式并发控制主要采用的技术手段 悲观锁 在关系数据库管理系统中,悲观并发控制(悲观锁,PCC)是一种并发控制的方法.它可以阻止一个事务以影响其他用户的方式来修改数据.如果一个事务执行的操作的每行数据应用了锁,那只有当这个事务锁释放,其他事务才能够执行与该锁冲突的操作 悲观并发控制主要应用于数据争用激烈的环境,以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本环境

随机推荐