MySQL探秘(四)MySQL事务与锁详解

By prince 2 comments

1.什么是事务

 

1.1 事务的定义

 

事务(Transaction)是数据库管理系统(DBMS)执行过程中的一个逻辑单位,由 一个有限的数据库操作序列构成;事务中可能包含一个或多个sql语句,这些语句要么都执行,要么都不执行。作为一个关系型数据库,MySQL支持事务。

这里面有两个关键点,第一个,它是数据库最小的工作单元,是不可以再分的。第 二个,它可能包含了一个或者一系列的 DML 语句,包括 insert delete update。(单条 DDL(create drop) DCL(grant revoke)也会有事务)

 

1.2 哪些存储引擎支持事务

 

我们前面说到了mysql是由server层和存储引擎构成,server层不管理事务,事务是由存储引擎实现的MySQL支持事务的存储引擎有InnoDBNDB Cluster等,其中InnoDB的使用最为广泛,这个也是它成为默认的存储引擎 的一个重要原因;其他存储引擎不支持事务,如MyIsamMemory

https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

 

1.3事务的四大特性

 

ACID是衡量事务的四个特性:

  • 原子性(Atomicity,或称不可分割性)
  • 一致性(Consistency)
  • 隔离性(Isolation)
  • 持久性(Durability)

按照严格的标准,只有同时满足ACID特性才是事务;但是在各大数据库厂商的实现中,真正满足ACID的事务少之又少。例如MySQL的NDB Cluster事务不满足持久性和隔离性;InnoDB默认事务隔离级别是可重复读,不满足隔离性;Oracle默认的事务隔离级别为READ COMMITTED,不满足隔离性……因此与其说ACID是事务必须满足的条件,不如说它们是衡量事务的四个维度。

下面将详细介绍ACID特性及其实现原理;为了便于理解,介绍的顺序不是严格按照A-C-I-D

 

1.3.1.原子性

 

1. 定义

原子性是指一个事务是一个不可分割的工作单位,其中的操作要么都做,要么都不做;如果事务中一个sql语句执行失败,则已执行的语句也必须回滚,数据库退回到事务前的状态。

2. 实现原理:undo log

在说明原子性原理之前,首先介绍一下MySQL的事务日志。MySQL的日志有很多种,如二进制日志、错误日志、查询日志、慢查询日志等,此外InnoDB存储引擎还提供了两种事务日志:redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性;undo log则是事务原子性和隔离性实现的基础。

下面说回undo log。实现原子性的关键,是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚,靠的是undo log:当事务对数据库进行修改时,InnoDB会生成对应的undo log;如果事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。

undo log属于逻辑日志,它记录的是sql执行相关的信息。当发生回滚时,InnoDB会根据undo log的内容做与之前相反的工作:对于每个insert,回滚时会执行delete;对于每个delete,回滚时会执行insert;对于每个update,回滚时会执行一个相反的update,把数据改回去。

update操作为例:当事务执行update时,其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息,回滚时便可以使用这些信息将数据还原到update之前的状态。

 

1.3.2.一致性

 

1. 基本概念

一致性是指事务执行结束后,数据库的完整性约束没有被破坏,事务执行的前后都是合法的数据状态。数据库的完整性约束包括但不限于:实体完整性(如行的主键存在且唯一)、列完整性(如字段的类型、大小、长度要符合要求)、外键约束、用户自定义完整性(如转账前后,两个账户余额的和应该不变)。

2. 实现

可以说,一致性是事务追求的最终目标:前面提到的原子性、持久性和隔离性,都是为了保证数据库状态的一致性。此外,除了数据库层面的保障,一致性的实现也需要应用层面进行保障。

实现一致性的措施包括:

  • 保证原子性、持久性和隔离性,如果这些特性无法保证,事务的一致性也无法保证
  • 数据库本身提供保障,例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
  • 应用层面进行保障,例如如果转账操作只扣除转账者的余额,而没有增加接收者的余额,无论数据库实现的多么完美,也无法保证状态的一致

 

1.3.3.隔离性

 

1. 定义

与原子性、持久性侧重于研究事务本身不同,隔离性研究的是不同事务之间的相互影响。隔离性是指,事务内部的操作与其他事务是隔离的,并发执行的各个事务之间不能互相干扰,可以防止多个事务并发执行时由于交叉执行而导致数据的不一致(比如:A正在从一张银行卡里面取钱,在A取钱的过程中,B不能向这张银行卡打钱)。

事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。

2. 实现

隔离性追求的是并发情形下事务之间互不干扰。简单起见,我们仅考虑最简单的读操作和写操作(暂时不考虑带锁读等特殊操作),那么隔离性的探讨,主要可以分为两个方面:

  • (一个事务)写操作对(另一个事务)写操作的影响:LBCC(基于锁的并发控制)保证隔离性
  • (一个事务)写操作对(另一个事务)读操作的影响:MVCC(多版本的并发控制)保证隔离性

 

1.3.4.持久性

 

1. 定义

持久性是指事务一旦提交,它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

2. 实现原理:redo log

redo log和undo log都属于InnoDB的事务日志。下面先聊一下redo log存在的背景。

InnoDB作为MySQL的存储引擎,数据是存放在磁盘中的,但如果每次读写数据都需要磁盘IO,效率会很低。为此,InnoDB提供了缓存(Buffer Pool),Buffer Pool中包含了磁盘中部分数据页的映射,作为访问数据库的缓冲:当从数据库读取数据时,会首先从Buffer Pool中读取,如果Buffer Pool中没有,则从磁盘读取后放入Buffer Pool;当向数据库写入数据时,会首先写入Buffer Pool,Buffer Pool中修改的数据会定期刷新到磁盘中(这一过程称为刷脏)。

Buffer Pool的使用大大提高了读写数据的效率,但是也带了新的问题:如果MySQL宕机,而此时Buffer Pool中修改的数据还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。

于是,redo log被引入来解决这个问题:当数据修改时,除了修改Buffer Pool中的数据,还会在redo log记录这次操作;当事务提交时,会调用fsync接口对redo log进行刷盘。如果MySQL宕机,重启时可以读取redo log中的数据,对数据库进行恢复。redo log采用的是WAL(Write-ahead logging,预写式日志),所有修改先写入日志,再更新到Buffer Pool,保证了数据不会因MySQL宕机而丢失,从而满足了持久性要求。

既然redo log也需要在事务提交时将日志写入磁盘,为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢?主要有以下两方面的原因:

(1)刷脏是随机IO,因为每次修改的数据位置随机,但写redo log是追加操作,属于顺序IO。

2)刷脏是以数据页(Page)为单位的,MySQL默认页大小是16KB,一个Page上一个小修改都要整页写入;而redo log中只包含真正需要写入的部分,无效IO大大减少。

 

1.4如何开启事务

 

无论是我们在 Navicat 的这种工具里面去操作,还是在我们的 Java 代码里面通过 API 去操作,还是加上@Transactional 的注解或者 AOP 配置,其实最终都是发送一个 指令到数据库去执行,Java 的 JDBC 只不过是把这些命令封装起来了。

我们先来看一下我们的操作环境。版本(5.7),存储引擎(InnnoDB),事务隔离级别(RR)

查询数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)

查询数据库引擎

mysql> show variables like '%engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

查询事务级别

mysql> show global variables like "tx_isolation";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

执行这样一条更新语句的时候,它有事务吗?

update user set name = 'wangwu' where id=1;

实际上,它自动开启了一个事务,并且提交了,所以最终写入了磁盘。 这个是开启事务的第一种方式,自动开启和自动提交。
InnoDB 里面有一个 autocommit 的参数(分成两个级别, session 级别和 global级别)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

它的默认值是 ON。autocommit 这个参数是什么意思呢?是否自动提交。如果它的 值是 true/on 的话,我们在操作数据的时候,会自动开启一个事务,和自动提交事务。

否则,如果我们把 autocommit 设置成 false/off,那么数据库的事务就需要我们手 动地去开启和手动地去结束。

手动开启事务也有几种方式,一种是用 begin;一种是用 start transaction。

那么怎么结束一个事务呢?我们结束也有两种方式,第一种就是提交一个事务, commit;还有一种就是 rollback,回滚的时候,事务也会结束。还有一种情况,客户端 的连接断开的时候,事务也会结束。

后面我们会讲到,当我们结束一个事务的时候,事务持有的锁就会被释放,无论是 提交还是回滚。

我们用 begin 手工开启一个事务,执行第二个 update,但是数据没有写入磁盘,因 为事务还没有提交,这个时候 commit 一下,再刷新一下,OK,写入了。

这个就是我们开启和结束事务的两种方式。

 

1.5事务并发的问题

 

首先来看并发情况下,读操作可能存在的三类问题:

(1)脏读:当前事务(A)中可以读到其他事务(B)未提交的数据(脏数据),这种现象是脏读。举例如下(以账户余额表为例):

我们有两个事务,一个是 Transaction A,一个是 Transaction B,在第一个事务里 面,它首先通过一个 where id=1 的条件查询一条数据,返回 name=lisi,balance=100 的这条数据。然后第二个事务,它同样地是去操作 id=1 的这行数据,它通过一个 update 的语句,把这行 id=1 的数据的 balance 改成了 200,但是注意,它没有提交。

这个时候,在第一个事务里面,它再次去执行相同的查询操作,发现数据发生了变 化,获取到的数据 balance 变成了 200。那么,这种在一个事务里面,由于其他的时候修改了 数据并且没有提交,而导致了前后两次读取数据不一致的情况,这种事务并发的问题, 我们把它叫做脏读。

(2)不可重复读:在事务A中先后两次读取同一个数据,两次读取的结果不一样,这种现象称为不可重复读。脏读与不可重复读的区别在于:前者读到的是其他事务未提交的数据,后者读到的是其他事务已提交的数据。举例如下:

同样是两个事务,第一个事务通过 id=1 查询到了一条数据。然后在第二个事务里面 执行了一个 update 操作,这里大家注意一下,执行了 update 以后它通过一个 commit 提交了修改。然后第一个事务读取到了其他事务已提交的数据导致前后两次读取数据不 一致的情况,就像这里,balance 到底是等于 100还是 200,那么这种事务并发带来的问题, 我们把它叫做不可重复读。

(3)幻读:在事务A中按照某个条件先后两次查询数据库,两次查询结果的条数不同,这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为:前者是数据变了,后者是数据的行数变了。举例如下:

在第一个事务里面我们执行了一个范围查询,这个时候满足条件的数据只有一条。 在第二个事务里面,它插入了一行数据,并且提交了。重点:插入了一行数据。在第一 个事务里面再去查询的时候,它发现多了一行数据。这种情况,我们把它叫做什么呢?

一个事务前后两次读取数据数据不一致,是由于其他事务插入数据造成的,这种情况我们把它叫做幻读。

不可重复读是修改或者删除,幻读是插入。

小结:我们刚才讲了事务并发带来的三大问题,现在来给大家总结一下。无论是脏 读,还是不可重复读,还是幻读,它们都是数据库的读一致性的问题,都是在一个事务 里面前后两次读取出现了不一致的情况。

读一致性的问题,必须要由数据库提供一定的事务隔离机制来解决。就像我们去饭店吃饭,基本的设施和卫生保证都是饭店提供的。那么我们使用数据库,隔离性的问题 也必须由数据库帮助我们来解决。

 

1.6事务隔离级别

 

SQL92标准中定义了四种隔离级别,并规定了每种隔离级别下上述几个问题是否存在。一般来说,隔离级别越低,系统开销越低,可支持的并发越高,但隔离性也越差。隔离级别与读问题的关系如下:

我们详细地分析一下这 4 个隔离级别是怎么定义的。
第一个隔离级别叫做:Read Uncommitted(未提交读),一个事务可以读取到其

他事务未提交的数据,会出现脏读,所以叫做 RU,它没有解决任何的问题。 第二个隔离级别叫做:Read Committed(已提交读),也就是一个事务只能读取

到其他事务已提交的数据,不能读取到其他事务未提交的数据,它解决了脏读的问题, 但是会出现不可重复读的问题。

第三个隔离级别叫做:Repeatable Read (可重复读),它解决了不可重复读的问题, 也就是在同一个事务里面多次读取同样的数据结果是一样的,但是在这个级别下,没有 定义解决幻读的问题。

最后一个就是:Serializable(串行化),在这个隔离级别里面,所有的事务都是串行执行的,也就是对数据的操作需要排队,已经不存在事务的并发操作了,所以它解决了所有的问题。

在实际应用中,读未提交在并发时会导致很多问题,而性能相对于其他隔离级别提高却很有限,因此使用较少。可串行化强制事务串行,并发效率很低,只有当对数据一致性要求极高且可以接受没有并发时使用,因此使用也较少。因此在大多数数据库系统中,默认的隔离级别是读已提交(Oracle)可重复读(后文简称RR

 

1.7MySQL InnoDB 对隔离级别的支持

 

MySQL InnoDB 里面,不需要使用串行化的隔离级别去解决所有问题。那我们来 看一下 MySQL InnoDB 里面对数据库事务隔离级别的支持程度是什么样的。

InnoDB 支持的四个隔离级别和 SQL92 定义的基本一致,隔离级别越高,事务的并 发度就越低。唯一的区别就在于,InnoDB 在 RR 的级别就解决了幻读的问题。这个也是 InnoDB 默认使用 RR 作为事务隔离级别的原因,既保证了数据的一致性,又支持较高的 并发度。

 

2.事务隔离实现方案

 

LBCC

第一种,我既然要保证前后两次读取数据一致,那么我读取数据的时候,锁定我要 操作的数据,不允许其他的事务修改就行了。这种方案我们叫做基于锁的并发控制 Lock Based Concurrency Control(LBCC)。

如果仅仅是基于锁来实现事务隔离,一个事务读取的时候不允许其他时候修改,那就意味着不支持并发的读写操作,而我们的大多数应用都是读多写少的,这样会极大地影响操作数据的效率。

MVCC

所以我们还有另一种解决方案,如果要让一个事务前后两次读取的数据保持一致, 那么我们可以在修改数据的时候给它建立一个备份或者叫快照,后面再来读取这个快照 就行了。这种方案我们叫做多版本的并发控制 Multi Version Concurrency Control (MVCC)。

MVCC 的核心思想是: 我可以查到在我这个事务开始之前已经存在的数据,即使它在后面被修改或者删除了。在我这个事务之后新增的数据,我是查不到的。

 

2.1基于锁的并发控制 (LBCC)

 

传统的隔离级别是基于锁实现的,这种方式叫做 基于锁的并发控制(Lock-Based Concurrent Control,简写 LBCC)

首先来看两个事务的写操作之间的相互影响。隔离性要求同一时刻只能有一个事务对数据进行写操作,InnoDB通过锁机制来保证这一点。

锁机制的基本原理可以概括为:事务在修改数据之前,需要先获得相应的锁;获得锁之后,事务便可以修改数据;该事务操作期间,这部分数据是锁定的,其他事务如果需要修改数据,需要等待当前事务提交或回滚后释放锁。

 

2.1.1锁的粒度

 

按照粒度,锁可以分为表锁、行锁以及其他位于二者之间的锁。

表锁在操作数据时会锁定整张表,并发性能较差;

行锁则只锁定需要操作的数据,并发性能好。

但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源),因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的,例如MyIsam只支持表锁,而InnoDB同时支持表锁和行锁,且出于性能考虑,绝大多数情况下使用的都是行锁。

 

2.1.2锁的分类

 

共享锁(S Lock)

允许事务读一行数据

第一个行级别的锁就是我们在官网看到的 Shared Locks (共享锁),我们获取了 一行数据的读锁以后,可以用来读取数据,所以它也叫做读锁,注意不要在加上了读锁 以后去写数据,不然的话可能会出现死锁的情况。而且多个事务可以共享一把读锁。

那怎么给一行数据加上读锁呢?
我们可以用 select …… lock in share mode; 的方式手工加上一把读锁。 释放锁有两种方式,只要事务结束,锁就会自动事务,包括提交事务和结束事务。 我们也来验证一下,看看共享锁是不是可以重复获取。

建表如下

CREATE TABLE `user` (
`id` int(11) PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into user values(NULL,'zhangsan'); 
insert into user values(NULL,'lisi');

 

#Transaction 1 

begin; 
select * from user where id = 1 LOCK IN SHARE MODE;


Query OK, 0 rows affected (0.00 sec)

+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
#Transaction 2 

begin; 
select * from user where id = 1 LOCK IN SHARE MODE;


Query OK, 0 rows affected (0.00 sec)

+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

由上结果可以看出共享锁是可以重复获取的。

排他锁(X Lock)

允许事务删除或更新一行数据

第二个行级别的锁叫做 Exclusive Locks(排它锁),它是用来操作数据的,所以又 叫做写锁。只要一个事务获取了一行数据的排它锁,其他的事务就不能再获取这一行数据的共享锁和排它锁。
排它锁的加锁方式有两种,第一种是自动加排他锁。我们在操作数据的时候,包括增删改,都会默认加上一个排它锁。
还有一种是手工加锁,我们用一个 FOR UPDATE 给一行数据加上一个排它锁,这个 无论是在我们的代码里面还是操作数据的工具里面,都比较常用。
释放锁的方式跟前面是一样的。

排他锁的验证:

#Transaction 1 

begin; 
update user set name='zs' where id = 1;


Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
#Transaction 2 

begin; 
select * from user where id = 1 LOCK IN SHARE MODE; //BLOCKED

SELECT * FROM user where id=1 FOR UPDATE; // BLOCKED 

DELETE FROM user where id=1 ; // BLOCKED

以上结果可以看出当一个事务用排他锁锁住了一行数据的时候,其他的事务不能操作这一行数据。

由此得出行级锁的共享锁(S Lock)和排他锁(X Lock)的兼容性如下:(需要注意的是,这里的S锁和X锁都是行级锁,兼容性是指对同一记录锁的兼容情况)

. 排他锁(X Lock) 共享锁(S Lock)
排他锁(X Lock) 不兼容 不兼容
共享锁(S Lock) 不兼容 兼容

这个是两个行锁,接下来就是两个表锁。

意向锁

为了支持在不同粒度上进行加锁,InnoDB支持一种额外的加锁方式,称之为意向锁

加意向锁的目的是为了表明某个事务正在锁定一行或者将要锁定一行。表名加锁的“意图”。
要读写,直接加S锁或者X锁就好啦,为什么还要表名这样一个意图呢?

InnoDB支持两种意向锁:

1.意向共享锁(IS Lock):事务想要获得一张表中某几行的共享锁。

2.意向排它锁(IX Lock):事务想要获得一张表中某几行的排它锁。

我们先了解一下意向锁是在什么时候使用的。

  • 在一个事务对一张表的某行添加S锁之前,它必须对该表获取一个IS锁或者优先级更高的锁。
  • 在一个事务对一张表的某行添加X锁之前,它必须对该表获取一个IX锁。

那么为什么要这么做呢?这么做有什么好处呢?

这里就需要介绍一下表锁了。

以前我们说X锁或者S锁,都是在说给一行加上X锁或者S锁,如果我们用select * from user for update,是会user表加上X锁的。

数据库里面是同时允许锁表,或者锁行的。如果事务1需要修改某一行数据,则他会给该行加X锁。

这时事务2想申请整个表的X锁做某些操作。他能否申请成功呢?不能,因为申请成功则代表事务2可以对任意行做读写。显然这与事务1冲突了。

那这时数据库应该怎么判断呢?

可行方案就是,逐行判断是否加了X锁,如果都没加,就代表可以锁表,如果其中某一行加上了X锁,那么就不能整表加锁,如果数据量特别大,比如有上千万的数据的时候,加表锁的效率是不是很低 。

但是我们引入了意向锁之后就不一样了,可以看出他发挥了重要的作用:

事务1想对某行上X锁之前,必须要获得到表的IX锁,现在没有其他事务使用IX锁,所以事务1获取成功。

事务1——获得IX锁——对某行上X锁

这个时候事务2想对这个表上X锁,发现表已经被加了IX锁,证明目前有其他事务正在修改该表的某行或者多行,此时事务2被阻塞……

我们可以把它理解成一个标志。就像火车上厕所有没有人使用的灯,是用来提高加锁的效率的。

IX,IS是表级锁,不会和行级的X,S锁发生冲突。只会和表级的X,S发生冲突

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

意向共享锁(IS Lock) 意向排它锁(IX Lock)
意向共享锁(IS Lock) 兼容 兼容
意向排它锁(IX Lock) 兼容 兼容

为什么都是兼容呢?

举个例子验证下

#Transaction 1

begin; 
select * from user where id = 1 LOCK IN SHARE MODE;--这行加了S锁,这张表加了IS锁

select * from user where id = 1 for update;--这行加了X锁,这张表加了IX锁


Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | name |
+----+------+
|  1 | zs   |
+----+------+
1 row in set (0.00 sec)
#Transaction 2

begin; 
select * from user where id = 2 LOCK IN SHARE MODE;--这行加了S锁,这张表加了IS锁

select * from user where id = 2 for update;--这行加了X锁,这张表加了IX锁


Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | name |
+----+------+
|  2 | lisi |
+----+------+
1 row in set (0.00 sec)

由上可以看出事务1锁定了id为1的行(对这行加了S锁,或者X锁),同时加了表的IS锁,或者IX锁。事务2当然也可以锁定id为2的行(对这行加了S锁,或者X锁,和前面的不是同一个行锁),所以事务2肯定也是可以获得表的IS锁或者IX锁的。

意向锁和表锁(表级的X,S)兼容性如下

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

再次重申:这里的S锁和X锁是表级别的,意向锁不会与行级别的S锁和X锁冲突

下面举几个例子:

第一个例子

#Transaction 1

begin; 
select * from user where id = 1 LOCK IN SHARE MODE;--这行加了S锁,这张表加了IS锁

Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | name |
+----+------+
|  1 | zs   |
+----+------+
1 row in set (0.00 sec)
#Transaction 2

begin; 
select * from user LOCK IN SHARE MODE;--这张表加了IS锁

Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | name |
+----+------+
|  1 | zs   |
|  2 | lisi |
+----+------+
2 rows in set (0.00 sec)

事务1已经获得了IS锁,想要读取某行数据,事务2想要获得表的S锁,可以获得成功,因为读是兼容的.

第二个例子

#Transaction 1

begin; 
select * from user where id = 1 for update;--这行加了X锁,这张表加了IX锁

Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | name |
+----+------+
|  1 | zs   |
+----+------+
1 row in set (0.00 sec)
#Transaction 2

begin; 
select * from user for update;--这张表尝试加X锁  BLOCKED

事务1获得了表的IX锁,想要修改某行数据,事务2想要获得表的X锁,但是由于IX锁已被获取走,证明有其他事务正在修改某行数据,所以事务2获得失败,只能被塞住…

以上就是 MySQL 里面的 4 种基本的锁的模式,或者叫做锁的类型。

到这里我们要思考两个问题,首先锁的作用是什么?它跟 Java 里面的锁是一样的, 是为了解决资源竞争的问题,Java 里面的资源是对象,数据库的资源就是数据表或者数 据行。
所以锁是用来解决事务对数据的并发访问的问题的。

需要注意,在 InnoDB 事务隔离上, LBCC和MVCC是协同使用的,两种方案并不是互斥的。 第一大类解决方案是LBCC,那MVCC又是怎么实现读一致性的呢?

 

2.2 多版本的并发控制(MVCC)

 

所以我们还有另一种解决方案,如果要让一个事务前后两次读取的数据保持一致, 那么我们可以在修改数据的时候给它建立一个备份或者叫快照,后面再来读取这个快照 就行了。这种方案我们叫做多版本的并发控制 Multi Version Concurrency Control (MVCC)。

MVCC 的核心思想是: 我可以查到在我这个事务开始之前已经存在的数据,即使它在后面被修改或者删除了。在我这个事务之后新增的数据,我是查不到的。

问题:这个快照什么时候创建?读取数据的时候,怎么保证能读取到这个快照而不 是最新的数据?这个怎么实现呢?

InnoDB 为每行记录都实现了3个隐藏字段:

DB_ROW_ID,6 字节:行标识,单调递增的行ID。(如果没有明确定义聚集索引,那么会自动生成一个聚集索引,这个时候自动生成的聚集索引的值就是DB_ROW_ID

DB_TRX_ID,6 字节:插入或更新行的最后一个事务的事务 ID,事务编号是自动递 增的(我们把它理解为创建版本号,在数据新增或者修改为新数据的时候,记录当前事 务 ID)。

DB_ROLL_PTR,7 字节:回滚指针,指向回滚段中的一个undo log记录(我们把它理解为删除版本号,数据被删除或记录为旧数据的时候,记录当前事务 ID)。

id name DB_ROW_ID DB_TRX_ID DB_ROLL_PTR
1 zhangsan 1 01 undefined

我们把DB_TRX_ID, DB_ROLL_PTR理解为版本号。

下面来举个例子

1.第一个事务,初始化数据(检查初始数据)

#Transaction 1

begin;
insert into user values(NULL,'zhangsan'); 
insert into user values(NULL,'lisi'); 
commit;

此时的数据,创建版本是当前事务ID为01,删除版本为空:

id name DB_TRX_ID(创建版本) DB_ROLL_PTR(删除版本)
1 zhangsan 01 undefined
2 lisi 01 undefined

2.第二个事务,执行第 1 次查询,读取到两条原始数据,这个时候事务ID是 02:

#Transaction 2

begin;
select * from user; -- (1) 第一次查询


mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

3.第三个事务,插入数据:

#Transaction 3

begin;
insert into user values(NULL,'wangwu');
commit;

此时的数据,多了一条 wangwu,它的创建版本号是当前事务ID为03:

id name DB_TRX_ID(创建版本) DB_ROLL_PTR(删除版本)
1 zhangsan 01 undefined
2 lisi 01 undefined
3 wangwu 03 undefined

4.第二个事务,执行第 2 次查询:

#Transaction 2

select * from user; --(2) 第二次查询

mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

MVCC 的查找规则:只能查找创建时间小于等于当前事务ID的数据,和删除时间大
于当前事务ID的行(或未删除)。 也就是不能查到在我的事务开始之后插入的数据,wangwu 的创建事务ID大于 02,所以还是只能查到两条数据。

5.第四个事务,删除数据,删除了 id=2 lisi 这条记录:

#Transaction 4

begin;
delete from user where id=2;
commit

此时的数据,lisi 的删除版本被记录为当前事务ID 04,其他数据不变:

id name DB_TRX_ID(创建版本) DB_ROLL_PTR(删除版本)
1 zhangsan 01 undefined
2 lisi 01 04
3 wangwu 03 undefined

6.在第二个事务中,执行第 3 次查询:

#Transaction 2

select * from user; --(3) 第三次查询

mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

查找规则:只能查找创建时间小于等于当前事务ID的数据,和删除时间大于当前事
务ID的行(或未删除)。
也就是,在我事务开始之后删除的数据,所以 lisi 依然可以查出来。所以还是这两
条数据。

7.第五个事务,执行更新操作,这个事务ID是05:

#Transaction 5

begin;
update user set name ='zhaoliu' where id = 1;
commit;

此时的数据,更新数据的时候,旧数据的删除版本被记录为当前事务ID 05(undo), 产生了一条新数据,创建事务ID为当前事务ID 05:

id name DB_TRX_ID(创建版本) DB_ROLL_PTR(删除版本)
1 zhangsan 01 05
2 lisi 01 04
3 wangwu 03 undefined
1 zhaoliu 05 undefined

8.第二个事务,执行第 4 次查询:

#Transaction 2

select * from user; --(4) 第四次查询
mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

查找规则:只能查找创建时间小于等于当前事务ID的数据,和删除时间大于当前事
务ID的行(或未删除)。
因为更新后的数据 zhaoliu 创建版本大于 02,代表是在事务之后增加的,查不出
来。
而旧数据 zhangsan 的删除版本大于 2,代表是在事务之后删除的,可以查出来。
通过以上演示我们能看到,通过版本号的控制,无论其他事务是插入、修改、删除, 第一个事务查询到的数据都没有变化。
在 InnoDB 中,MVCC 是通过 Undo log 实现的。
Oracle、Postgres 等等其他数据库都有 MVCC 的实现。
需要注意,在 InnoDB 中,MVCC 和锁是协同使用的,这两种方案并不是互斥的。

 

2.3行锁的原理

顾名思义,行锁就是一锁锁一行或者多行记录,mysql的行锁是基于索引加载的,所以行锁是要加在索引响应的行上,即命中索引。

首先我们有三张表,一张没有索引的 t1,一张有主键索引的 t2,一张有唯一索引的 t3。下面来看看这3张表的行锁锁怎样的。

1 没有索引的表

t1表结构如下

CREATE TABLE `t1` ( 
`id` int(11), 
`name` varchar(255) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t1 values(1,'zhangsan'); 
insert into t1 values(2,'lisi'); 
insert into t1 values(3,'wangwu');

1.第一个事务里面,我们通过 where id =1 锁住第一行数据。

#Transaction 1

begin;
select * from t1 where id = 1 for update;

+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

2.第二个事务里面,我们尝试给 id=3 的这一行数据加锁,也尝试新增一条记录。

#Transaction 2

begin; 

select * from t1 where id = 3 for update;--BLOCKED
insert into t1 values(4,'zhaoliu');--BLOCKED

结果是给 id=3 的这一行数据加锁,和新增一条记录都阻塞了。

备注原理:如果用没有索引的数据,其会对所有聚簇索引上都加上 next-key 锁。

平常开发的时候如果对查询条件没有索引的,一定进行一致性读,也就是加锁读,会导致全表加上索引,会导致其他事务全部阻塞,数据库基本会处于不可用状态。

2主键索引的表

t2表结构如下

CREATE TABLE `t2` ( 
`id` int(11) PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t2 values(1,'zhangsan'); 
insert into t2 values(2,'lisi'); 
insert into t2 values(3,'wangwu');

t2 的表结构和t1的字段是一样的,不同的地方是 id 上创建了一个主键索引。

1.第一个事务里面,我们通过 where id =1 锁住第一行数据。

#Transaction 1

begin;
select * from t2 where id = 1 for update;

+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

2.第二个事务里面,我们尝试给 id=1 的这一行数据加锁,也尝试id=3的这一行数据加锁。

#Transaction 2

begin;
select * from t2 where id = 1 for update;--BLOCKED

select * from t2 where id = 3 for update;--OK

结果是给 id=1 的这一行数据加锁阻塞了, id=3 的这一行数据加锁正常。

3 唯一索引的表

t3表结构如下

CREATE TABLE `t3` ( 
`id` int(11),
`name` varchar(255) DEFAULT NULL,
 UNIQUE KEY nameindex(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t3 values(1,'zhangsan'); 
insert into t3 values(2,'lisi'); 
insert into t3 values(3,'wangwu');

t3 的表结构和t1的字段是一样的,不同的地方是name上创建了一个唯一索引。

1.第一个事务里面,我们通过 name = ‘zhangsan’锁住第一行数据。

#Transaction 1

begin;
select * from t3 where name = 'zhangsan' for update;

+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

2.第二个事务里面,我们尝试给name = ‘zhangsan’的这一行数据加锁,也尝试id=1的这一行数据加锁。

#Transaction 2

begin;
select * from t3 where name = 'zhangsan' for update;
--BLOCKED

select * from t3 where id = 1 for update;--BLOCKED

结果是给name = ‘zhangsan’的这一行数据和id=1的这一行数据加锁都阻塞了。

备注原理:唯一索引由于明确了唯一的数据行,所以不需要添加间隙锁解决幻读。

4 非唯一索引的表

t4表结构如下

CREATE TABLE `t4` ( 
`id` int(11),
`name` varchar(255) DEFAULT NULL,
 KEY nameindex(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t4 values(1,'111');

t4 的表结构和t1的字段是一样的,不同的地方是name上创建了一个唯一索引。

1.第一个事务里面,我们通过 name = ‘zhangsan’锁住第一行数据。

#Transaction 1

begin;
select * from t4 where name = '111' for update;

+------+----------+
| id   | name     |
+------+----------+
|    1 | 111      |
+------+----------+
1 row in set (0.00 sec)

2.第二个事务里面,我们尝试新增一条数据

#Transaction 2

begin;
insert into t4 values(2,'112'); --BLOCKED

结果是给name = ‘112’的这一行数据加锁阻塞了

备注原理:发现在事务 1 中给 111 加了 next-key 锁,事务 2插入112的时候会首先进行插入意向锁的插入。事务 2由于间隙锁和插入意向锁的冲突,导致了阻塞。

从上面3个例子可以看出InnoDB 的行锁,就是通过锁住索引来实现的。

1、为什么表里面没有索引的时候,锁住一行数据会导致锁表? 或者说,如果锁住的是索引,一张表没有索引怎么办? 所以,一张表有没有可能没有索引?
1)如果我们定义了主键(PRIMARY KEY),那么 InnoDB 会选择主键作为聚集索引。 2)如果没有显式定义主键,则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。
3)如果也没有这样的唯一索引,则 InnoDB 会选择内置 6 字节长的 ROWID 作

为隐藏的聚集索引,它会随着行记录的写入而主键递增。 所以,为什么锁表,是因为查询没有使用索引,会进行全表扫描,然后把每一个隐藏的聚集索引都锁住了。

2、为什么通过唯一索引给数据行加锁,主键索引也会被锁住?

大家还记得在 InnoDB 里面,当我们使用辅助索引的时候,它是怎么检索数据的吗? 辅助索引的叶子节点存储的是什么内容?

在辅助索引里面,索引存储的是二级索引和主键的值。比如name = ‘zhangsan’,存储的是 name 的索引和主键 id 的值 1。

而主键索引里面除了索引之外,还存储了完整的数据。所以我们通过辅助索引锁定一行数据的时候,它跟我们检索数据的步骤是一样的,会通过主键值找到主键索引,然后也锁定。

现在我们已经搞清楚 4 个锁的基本类型和锁的原理了,在官网上,还有 3 种锁,我们把它理解为锁的算法。我们也来看下 InnoDB 在什么时候分别锁住什么范围。

 

2.4锁的算法

我们先来看一下我们测试用的表,t5,这张表有一个主键索引。
我们插入了 4 行数据,主键值分别是 14710

CREATE TABLE `t5` ( 
`id` int(11) PRIMARY KEY,
`name` varchar(255) DEFAULT NULL,
 UNIQUE KEY nameindex(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t5 values(1,'zhangsan'); 
insert into t5 values(4,'lisi'); 
insert into t5 values(7,'wangwu');
insert into t5 values(10,'zhaoliu');

为了让大家真正理解这三种行锁算法的区别,我们需要了解一下三种范围的概念。因为我们用主键索引加锁,我们这里的划分标准就是主键索引的值。

这些数据库里面存在的主键值,我们把它叫做记录(Record),那么这里我们就有 4 个 记录(Record)。

根据主键,这些存在的 记录(Record) 隔开的数据不存在的区间,我们把它叫做间隙(Gap),它是一个左开右开的区间。

最后一个,间隙(Gap)连同它左边的记录(Record),我们把它叫做临键(Next-key)的区间, 它是一个左开右闭的区间。

t5 的主键索引,它是整型的,可以排序,所以才有这种区间。如果我的主键索引不 是整形,是字符怎么办呢?字符可以排序吗? 用 ASCII 码来排序。

我们已经弄清楚了三个范围的概念,这3个范围对应着3种锁的范围。

我们来看看3种锁的定义:

记录锁(Record Lock):单条索引记录上加锁,record lock锁住的永远是索引,而非记录本身,即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时,那么将会在每一条聚集索引后面加X锁,这个类似于表锁,但原理上和表锁应该是完全不同的。

间隙锁(Gap Lock):在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。gap lock的机制主要是解决可重复读模式下的幻读问题。

临键锁(Next-Key Lock):就是Record lock和gap lock的结合,即除了锁住记录本身,还要再锁住索引之间的间隙。

下面我们就来看一下在不同的隔离级别下范围下,这3种锁是怎么表现的。

2.4.1Read Committed隔离级别下

1.主键索引情况(RC)

TABLE定义:

CREATE TABLE `t6` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t6 values(1,'zhangsan'); 
insert into t6 values(4,'lisi'); 
insert into t6 values(7,'wangwu'); 
insert into t6 values(10,'zhaoliu');

1.第一个事务里面,我们通过 where id = 1 锁住id等于1的数据

#Transaction 1

begin;
select * from t6 where id = 1 for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面,我们尝试给 id=1 的这一行数据加锁,并尝试新增1条数据

#Transaction 2

begin;
select * from t6 where id = 1 for update; --BLOCKED

insert into t6 values(2,'wangba'); --OK

结果第二个事务 id=1 的这一行数据阻塞了。

所以在第一个事务里主键id = 1的记录已经加上Record锁。如下图所示:

结论:id是主键时,此SQL只需要在id=1这条记录上加Record锁即可。

2.唯一索引情况(RC)

TABLE定义:

CREATE TABLE `t7` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL, 
 UNIQUE KEY index_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t7 values(1,'zhangsan'); 
insert into t7 values(4,'lisi'); 
insert into t7 values(7,'wangwu'); 
insert into t7 values(10,'zhaoliu');

1.第一个事务里面,我们通过 where name = ‘lisi’ 锁住name等于 ‘lisi’ 的数据

#Transaction 1

begin;
select * from t7 where where name = 'lisi' for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面,我们尝试分别给name = ‘lisi’ 和id = 4的数据加锁

#Transaction 2

begin;
select * from t7 where name = 'lisi' for update; --BLOCKED

select * from t7 where id = 4 for update; --BLOCKED

结果第二个事务里name = ‘lisi’ 和id = 4都数据阻塞了。

所以在第一个事务里主键name = ‘lisi’的记录已经加上Record锁。如下图所示:

此情况中,name是unique索引,id是主键索引。由于name是unique索引,因此sql语句会选择走name列的索引进行where条件的过滤,在找到name = ‘lisi’ 的记录后,首先会将unique索引上的name = ‘lisi’ 索引记录加上Record锁,同时,会根据读取到的id列,回主键索引(聚簇索引),然后将聚簇索引上的id = 4 对应的主键索引项加Record锁。为什么聚簇索引上的记录也要加锁?试想一下,如果并发的一个SQL,是通过主键索引来更新:update t7 set name = ‘lihao’ where id = 4; 此时,如果事务1的sql语句没有将主键索引上的记录加锁,那么并发的update就会感知不到事务1的sql语句的存在,违背了同一记录上的更新/删除需要串行执行的约束。

结论:若name上有unique索引。那么SQL需要加两个Record锁,一个对应于name字段unique索引上的name = ‘lisi’的记录,另一把锁对应于聚簇索引上的[id=4,name= ‘lisi’]的记录。

3.非唯一索引情况(RC)

TABLE定义:

CREATE TABLE `t8` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL, 
 KEY index_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t8 values(1,'zhangsan'); 
insert into t8 values(2,'lisi'); 
insert into t8 values(4,'lisi'); 
insert into t8 values(7,'wangwu'); 
insert into t8 values(10,'zhaoliu');

1.第一个事务里面,我们通过 where name = ‘lisi’ 锁住name等于 ‘lisi’ 的数据

#Transaction 1

begin;
select * from t8 where where name = 'lisi' for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面,我们尝试分别给name = ‘lisi’ 和id = 2,id = 4的数据加锁

#Transaction 2

begin;
select * from t8 where name = 'lisi' for update; --BLOCKED

select * from t8 where id = 2 for update; --BLOCKED
select * from t8 where id = 4 for update; --BLOCKED

结果第二个事务里name = ‘lisi’ 和id = 2,id = 4都数据阻塞了。

所以在第一个事务里主键name = ‘lisi’的记录已经加上Record锁。如下图所示:

与唯一索引唯一的区别在于,唯一索引最多只有一个满足等值查询的记录,而非唯一索引情况会将所有满足查询条件的记录都加锁。

结论:若name列上有非唯一索引,那么对应的所有满足SQL查询条件的记录,都会被加锁。同时,这些记录在主键索引上的记录,也会被加锁。

4.无索引情况(RC)

TABLE定义:

CREATE TABLE `t9` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL, 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t9 values(1,'zhangsan'); 
insert into t9 values(4,'lisi'); 
insert into t9 values(7,'wangwu'); 
insert into t9 values(10,'zhaoliu');

1.第一个事务里面,我们通过 where name = ‘lisi’ 锁住name等于 ‘lisi’ 的数据

#Transaction 1

begin;
select * from t9 where where name = 'lisi' for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面,我们尝试分别给name = ‘lisi’ 和id = 1,id = 4,id = 7,id = 10的数据加锁

#Transaction 2

begin;
select * from t9 where name = 'lisi' for update; --BLOCKED

select * from t9 where id = 1 for update; --BLOCKED
select * from t9 where id = 4 for update; --BLOCKED
select * from t9 where id = 7 for update; --BLOCKED
select * from t9 where id = 10 for update; --BLOCKED

结果第二个事务里name = ‘lisi’ 和id = 1,id = 4,d = 4,id = 10都数据阻塞了。

所以在第一个事务里所有主键的记录已经加上Record锁。如下图所示:

由于name列上没有索引,因此只能走聚簇索引,进行全部扫描。从图中可以看到,满足条件的记录有1条,但是,聚簇索引上所有的记录,都被加上了Record锁。无论记录是否满足条件,全部被加上Record锁。既不是加表锁,也不是在满足条件的记录上加行锁。

有人可能会问?为什么不是只在满足条件的记录上加锁呢?这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由MySQL Server层进行过滤。因此也就把所有的记录,都锁上了。

注:在实际的实现中,MySQL有一些改进,在MySQL Server过滤条件,发现不满足后,会调用unlock_row方法,把不满足条件的记录放锁 (违背了2PL的约束)。这样做,保证了最后只会持有满足条件记录上的锁,但是每条记录的加锁操作还是不能省略的。

结论:若name列上没有索引,SQL会走聚簇索引的全扫描进行过滤,由于过滤是由MySQL Server层面进行的。因此每条记录,无论是否满足条件,都会被加上Record锁。但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省略。同时,优化也违背了2PL的约束。

2.4.2Repeatable Read隔离级别下

1.主键索引情况(RR)

加锁情况和主键索引情况(RC)一致

2.唯一索引情况(RR)

加锁情况和唯一索引情况(RC)一致

3.非唯一索引情况(RR)

TABLE定义:

CREATE TABLE `t10` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL, 
 KEY index_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t10 values(1,'a'); 
insert into t10 values(4,'d'); 
insert into t10 values(7,'g'); 
insert into t10 values(10,'j');

1.第一个事务里面,我们通过 where name = ‘d’ 锁住name等于 ‘d’ 的数据

#Transaction 1

begin;
select * from t10 where name = 'd' for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面,我们尝试分别给name = ‘d’ ,id = 4的数据加锁

#Transaction 2

begin;
select * from t10 where name = 'd' for update; --BLOCKED
 
select * from t10 where id = 4 for update; --BLOCKED

insert into t10 values(2,'b'); --BLOCKED

insert into t10 values(3,'d'); --BLOCKED

insert into t10 values(5,'e'); --BLOCKED

insert into t10 values(8,'h'); --OK

结果第二个事务里不仅name = ‘d’ 和id = 4都数据阻塞了,而且[2,b],[3,d],[5,e]都不能插入。

所以在第一个事务里主键name = ‘d’的记录已经加上Record锁,ad之间,dj之间加入了gap锁如下图所示:

此图,相对于非唯一索引情况(RC) 多出来一个GAP锁,就是RR隔离级别,相对于RC隔离级别,不会出现幻读的关键。确实,GAP锁锁住的位置,也不是记录本身,而是两条记录之间的GAP。所谓幻读,就是同一个事务,连续做两次当前读 (例如:select * from t10 where name= ‘d’ for update;),那么这两次当前读返回的是完全相同的记录 (记录数量一致,记录本身也一致),第二次的当前读,不会比第一次返回更多的记录 (幻象)。

如何保证两次当前读返回一致的记录,那就需要在第一次当前读与第二次当前读之间,其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能,GAP锁应运而生。

如图中所示,有哪些位置可以插入新的满足条件的项 (name = ‘d’),考虑到B+树索引的有序性,满足条件的项一定是连续存放的。记录[a,1]之前,不会插入记录;记录[a,1]与[d,4]之间,可能插入记录;记录[d,4]与[g,7]之间,可能插入记录; [g,7]之后不会插入记录。

因此,为了保证[a,1]与[d,4]间,d,4]与[g,7]间不会插入新的满足条件的记录,MySQL选择了用GAP锁,将这2个GAP给锁起来。

结论:Repeatable Read隔离级别下,name列上有一个非唯一索引,对应SQL:select * from t10 where name = ‘d’ for update; 首先,通过name索引定位到第一条满足查询条件的记录,加记录上的Record锁,加GAP上的GAP锁,然后加主键聚簇索引上的记录Record锁,然后返回;然后读取下一条,重复进行。直至进行到第一条不满足条件的记录[g,7],此时,不需要加记录X锁,但是仍旧需要加GAP锁,最后返回结束。

4.无索引情况(RR)

TABLE定义:

CREATE TABLE `t11` ( 
`id` int(11) PRIMARY KEY, 
`name` varchar(255) DEFAULT NULL, 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

insert into t11 values(1,'a'); 
insert into t11 values(4,'d'); 
insert into t11 values(7,'g'); 
insert into t11 values(10,'j');

1.第一个事务里面,我们通过 where name = ‘d’ 锁住name等于 ‘d’ 的数据

#Transaction 1

begin;
select * from t11 where name = 'd' for update;

Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)

2.第二个事务里面,我们尝试分别给name = ‘d’ ,id = 4的数据加锁

#Transaction 2

begin;
select * from t11 where name = 'd' for update; --BLOCKED
 
select * from t11 where id = 4 for update; --BLOCKED

insert into t11 values(2,'b'); --BLOCKED

insert into t11 values(3,'d'); --BLOCKED

insert into t11 values(5,'e'); --BLOCKED

insert into t11 values(8,'h'); --BLOCKED

insert into t11 values(9,'i'); --BLOCKED

结果第二个事务里不仅name = ‘d’ 和id = 4都数据阻塞了,而且[2,b],[3,d],[5,e],[8,h],[9,i]都不能插入。

所以在第一个事务里主键name = ‘d’的记录已经加上Record锁。小于a,ad之间,dj之间,gj之间.大于j加入了gap锁如下图所示:

如图,这是一个很恐怖的现象。首先,聚簇索引上的所有记录,都被加上了Record锁。其次,聚簇索引每条记录间的间隙(GAP),也同时被加上了GAP锁。这个示例表,只有4条记录,一共需要4个记录锁,5个GAP锁。

在这种情况下,这个表上,除了不加锁的快照度,其他任何加锁的并发SQL,均不能执行,不能更新,不能删除,不能插入,全表被锁死。

当然,跟无索引情况(RC)类似,这个情况下,MySQL也做了一些优化,就是所谓的semi-consistent read。semi-consistent read开启的情况下,对于不满足查询条件的记录,MySQL会提前放锁。针对上面的这个用例,就是除了记录[d,4]之外,所有的记录锁都会被释放,同时不加GAP锁。semi-consistent read如何触发:要么是read committed隔离级别;要么是Repeatable Read隔离级别,同时设置了innodb_locks_unsafe_for_binlog 参数。

结论:在Repeatable Read隔离级别下,如果进行全表扫描的当前读,那么会锁上表中的所有记录,同时会锁上聚簇索引内的所有GAP,杜绝所有的并发 更新/删除/插入 操作。当然,也可以通过触发semi-consistent read,来缓解加锁开销与并发影响,但是semi-consistent read本身也会带来其他问题,不建议使用。

2.4.3Serializable隔离级别下

针对前面提到的简单的SQL,最后一个情况:Serializable隔离级别。对于SQL:delete from t9 where id = 1; 来说,Serializable隔离级别与Repeatable Read隔离级别完全一致,因此不做介绍。

Serializable隔离级别,影响的是SQL:select * from t9 where id = 1; 这条SQL,在RC,RR隔离级别下,都是快照读,不加锁。但是在Serializable隔离级别,SQL会加读锁,也就是说快照读不复存在,MVCC并发控制降级为Lock-Based CC。

结论:在MySQL/InnoDB中,所谓的读不加锁,并不适用于所有的情况,而是隔离级别相关的。Serializable隔离级别,读不加锁就不再成立,所有的读操作,都是当前读。

2.5隔离级别的实现

 

所以,我们再回过头来看下这张图片,为什么 InnoDB 的 RR 级别能够解决幻读的 问题,就是用临键锁实现的。
我们再回过头来看下这张图片,这个就是 MySQL InnoDB 里面事务隔离级别的实现。

我们先了解2个概念 快照读和当前读:

快照读(snapshot read):简单的select操作,属于快照读,不加锁,底层使用 MVCC 来实现。(当然,也有例外,下面会分析)
select * from table where ?;

当前读(current read)):特殊的读操作,官方定义为locking read,插入/更新/删除操作,属于当前读,需要加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
所有以上的语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。

1.Read Uncommited (RU)  隔离级别下

快照读和当前读都不加锁

2.Read Commited(RC) 隔离级别下

普通select快照读不加锁。

当前读都使用记录锁(Record lock)。

除了两种特殊情况——外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会使用间隙锁(Gap lock)封锁区间。
所以 RC 会出现幻读的问题。

3.Repeatable Read(RR) 隔离级别下

普通select快照读不加锁。

当前读底层使用记录锁、或者间隙锁、 临键锁。

4.Serializable隔离级别下

普通select快照读会转成和当前读一样底层使用记录锁、或者间隙锁、 临键锁。

2.6查看锁信息(日志)

SHOW STATUS 命令中,包括了一些行锁的信息:

mysql> show status like 'innodb_row_lock_%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 4416  |
| Innodb_row_lock_time_avg      | 4416  |
| Innodb_row_lock_time_max      | 4416  |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
5 rows in set (0.00 sec)
Innodb_row_lock_current_waits:当前正在等待锁定的数量; Innodb_row_lock_time :从系统启动到现在锁定的总时间长度,单位 ms; Innodb_row_lock_time_avg :每次等待所花平均时间; Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间; Innodb_row_lock_waits :从系统启动到现在总共等待的次数。

SHOW 命令是一个概要信息。InnoDB 还提供了三张表来分析事务与锁的情况:

select * from information_schema.INNODB_TRX; -- 当前运行的所有事务 ,还有具体的语句
#当前出现的锁

mysql> select * from information_schema.INNODB_LOCKS;
+----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+
| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index | lock_space | lock_page | lock_rec | lock_data              |
+----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+
| 171801:424:4:1 | 171801      | X         | RECORD    | `test`.`t4` | nameindex  |        424 |         4 |        1 | supremum pseudo-record |
| 171799:424:4:1 | 171799      | X         | RECORD    | `test`.`t4` | nameindex  |        424 |         4 |        1 | supremum pseudo-record |
+----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)
#锁等待的对应关系

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 171802            | 171802:424:4:1    | 171799          | 171799:424:4:1   |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

如果一个事务长时间持有锁不释放,可以 kill 事务对应的线程 ID,也就是 INNODB_TRX 表中的 trx_mysql_thread_id,例如执行 kill 4,kill 7,kill 8。
当然,死锁的问题不能每次都靠 kill 线程来解决,这是治标不治本的行为。我们应该 尽量在应用端,也就是在编码的过程中避免。

 

2 Comments

jefy

2月 2, 2021, 2:55 上午 回复

文章写得太好了,知识点由浅入深,通俗易懂,网上这种精品的文章太缺乏了,对我有莫大的帮助。不知道为何只更新到2020年3月,好遗憾,期待博主能持续更新……

rainqiu

2月 2, 2023, 3:41 下午 回复

感谢大佬的分享,质量很高的文章。有些知识点和我的认知有出入,但我经过查证后,是我记错了,大佬NB。真的很希望大佬继续分享。

发表评论

 

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据