MySQL中的锁事

一、概述

锁是计算机在执行多线程或线程时用于并发访问同一共享资源时的同步机制，MySQL中的锁是在服务器层或者存储引擎层实现的，保证了数据访问的一致性与有效性。

事务的隔离性是由的锁来实现。

二、MySQL并发事务访问的问题

我们已经知道事务并发执行时可能带来的各种问题，最大的一个难点是：一方面要最大程度地利用数据库的并发访问，另外一方面还要确保每个用户能以一致的方式读取和修改数据，尤其是一个事务进行读取操作，另一个同时进行改动操作的情况下。

一个事务进行读取操作，另一个进行改动操作,我们前边说过，这种情况下可能发生脏读、不可重复读、幻读的问题。怎么解决脏读、不可重复读、幻读这些问题呢？其实有两种可选的解决方案：

方案一：读操作MVCC，写操作进行加锁

事务利用MVCC进行的读取操作称之为一致性读，或者一致性无锁读，也称之为快照读，但是往往读取的是历史版本数据。所有普通的SELECT语句（plain SELECT）在READ COMMITTED、REPEATABLE READ隔离级别下都算是一致性读。

一致性读并不会对表中的任何记录做加锁操作，其他事务可以自由的对表中的记录做改动。

普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。

• 在READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象；
• 在REPEATABLE READ 隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读和幻读的问题。

方案二：读、写操作都采用加锁的方式

适用场景：

业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本，

比方在银行存款的事务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。这样在读取记录的时候也就需要对其进行加锁操作，这样也就意味着读操作和写操作也像写-写操作那样排队执行。

小结对比发现：

• 采用MVCC 方式的话， 读-写操作彼此并不冲突， 性能更高。
• 采用加锁方式的话， 读-写操作彼此需要排队执行，影响性能。

很明显，采用MVCC方式的话，读-写操作彼此并不冲突，性能更高，采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。一般情况下我们当然愿意采用MVCC来解决读-写操作并发执行的问题，但是业务在某些情下，要求必须采用加锁的方式执行。下属的所有内容讲MySQL的锁事：

三、锁的不同角度的分类

3.1、从数据操作的类型划分

共享锁（也称为读锁，英文名：Shared Locks，简称S锁）：在事务要读取一条记录时，需要先获取该记录的S锁。假如事务E1首先获取了一条记录的S锁之后，事务E2接着也要访问这条记录：如果事务E2想要再获取一个记录的S锁，那么事务E2也会获得该锁，也就意味着事务E1和E2在该记录上同时持有S锁。

排他锁（也称为独占锁，写锁，英文名：Exclusive Locks，简称X锁）：在事务要改动一条记录时，需要先获取该记录的X锁。如果事务E2想要再获取一个记录的X锁，那么此操作会被阻塞，直到事务E1提交之后将S锁释放掉。如果事务E1首先获取了一条记录的X锁之后，那么不管事务E2接着想获取该记录的S锁还是X锁都会被阻塞，直到事务E1提交。

所以我们说S锁和S锁是兼容的，S锁和X锁是不兼容的，X锁和X锁也是不兼容的，画个表表示一下就是这样：

X 不兼容X 不兼容S

S 不兼容X 兼容S

数据环境准备：

CREATE DATABASE test_db;
USE test_db;

CREATE TABLE products
(
    id    INT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(100),
    price DECIMAL(10, 2)
);

-- 随机插入一些数据
INSERT INTO products (name, price)
VALUES ('Product A', 10.99),
       ('Product B', 20.49),
       ('Product C', 15.75);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

演示共享锁（S锁）

-- 事务E1：获取S锁
START TRANSACTION;
1
2

SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;
1

-- 事务E2：再次获取S锁
START TRANSACTION;
1
2

SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;
1

此时，两个事务都可以读取 id = 1 的记录，因为S锁是互相兼容的。

COMMIT;
1

演示排他锁（X锁）

接下来，演示排他锁（X锁）。

-- 事务E1：获取X锁
START TRANSACTION;
1
2

SELECT * FROM products WHERE id = 1 FOR UPDATE;
1

-- 事务E2：尝试获取S锁或X锁
START TRANSACTION;
1
2

-- 这个操作会被阻塞
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE; 
1
2

-- 或者:这个操作也会被阻塞
SELECT * FROM products WHERE id = 1 FOR UPDATE; 
1
2

当事务E1已经获取了 id = 1 的X锁时，任何其他事务E2尝试获取该记录的S锁或X锁时都会被阻塞，直到事务E1提交或回滚。

提交或回滚事务

当E1事务提交或回滚后，E2事务才能继续进行。

-- 事务E1提交
COMMIT;
1
2

-- 事务E2现在可以继续
COMMIT;
1
2

3.2、从数据操作的粒度划分：表级锁

MySQL支持多种存储引擎，不同存储引擎对锁的支持也是不一样的。当然，我们重点还是讨论InnoDB存储引擎中的锁，其他的存储引擎只是稍微看看。

InnoDB存储引擎既支持表锁，也支持行锁。表锁实现简单，占用资源较少，不过粒度很粗，有时候你仅仅需要锁住几条记录，但使用表锁的话相当于为表中的所有记录都加锁，所以性能比较差。行锁粒度更细，可以实现更精准的并发控制。下边我们详细看一下。

3.2.1、表级别的S锁与X锁

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。在对某个表执行一些诸如ALTER TABLE 、DROP TABLE 这类的DDL 语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行DDL 语句也会发生阻塞。这个过程其实是通过在server层使用一种称之为元数据锁（英文名： Metadata Locks ，简称MDL ）结构来实现的。

一般情况下，不会使用InnoDB存储引擎提供的表级别的S锁和X锁。只会在一些特殊情况下，比方说崩溃恢复过程中用到。比如，在系统变量autocommit=0，innodb_table_locks = 1 时， 手动获取InnoDB存储引擎提供的表t 的S锁或者X锁可以这么写：

• LOCK TABLES t READ ：InnoDB存储引擎会对表t 加表级别的S锁。
• LOCK TABLES t WRITE ：InnoDB存储引擎会对表t 加表级别的X锁。

不过尽量避免在使用InnoDB存储引擎的表上使用LOCK TABLES 这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。InnoDB的厉害之处还是实现了更细粒度的行锁，关于InnoDB表级别的S锁和X锁大家了解一下就可以了。

生活中的实例：

为了更好的理解这个表级别的S锁和X锁和后面的意向锁，我们举一个现实生活中的例子。我们用曾经很火爆的互联网风口项目共享Office来说明加锁：

共享Office有栋大楼，楼自然有很多层。办公室都是共享的，客户可以随便选办公室办公。每层楼可以容纳客户同时办公，每当一个客户进去办公，就相当于在每层的入口处挂了一把S锁，如果很多客户进去办公，相当于每层的入口处挂了很多把S锁（类似行级别的S锁）。

有的时候楼层会进行检修，比方说换地板，换天花板，检查水电啥的，这些维修项目并不能同时开展。如果楼层针对某个项目进行检修，就不允许客户来办公，也不允许其他维修项目进行，此时相当于楼层门口会挂一把X锁（类似行级别的X锁）。

上边提到的这两种锁都是针对楼层而言的，不过有时候我们会有一些特殊的需求：

A、有投资人要来考察Office的环境。

投资人和公司并不想影响客户进去办公，但是此时不能有楼层进行检修，所以可以在大楼门口放置一把S锁（类似表级别的S锁）。此时：

来办公的客户们看到大楼门口有S锁，可以继续进入大楼办公。

修理工看到大楼门口有S锁，则先在大楼门口等着，啥时候投资人走了，把大楼的S锁撤掉再进入大楼维修。

B、公司要和房东谈条件。

此时不允许大楼中有正在办公的楼层，也不允许对楼层进行维修。所以可以在大楼门口放置一把X锁（类似表级别的X锁）。此时：

来办公的客户们看到大楼门口有X锁，则需要在大楼门口等着，啥时候条件谈好，把大楼的X锁撤掉再进入大楼办公。

修理工看到大楼门口有X锁，则先在大楼门口等着，啥时候谈判结束，把大楼的X锁撤掉再进入大楼维修。

3.2.2、意向锁 （intention lock）

但是在上面的例子这里头有两个问题：

如果我们想对大楼整体上S锁，首先需要确保大楼中的没有正在维修的楼层，如果有正在维修的楼层，需要等到维修结束才可以对大楼整体上S锁。

如果我们想对大楼整体上X锁，首先需要确保大楼中的没有办公的楼层以及正在维修的楼层，如果有办公的楼层或者正在维修的楼层，需要等到全部办公的同学都办公离开，以及维修工维修完楼层离开后才可以对大楼整体上X锁。

我们在对大楼整体上锁（表锁）时，怎么知道大楼中有没有楼层已经被上锁（行锁）了呢？依次检查每一楼层门口有没有上锁？那这效率也太慢了吧！于是InnoDB提出了一种意向锁（英文名：Intention Locks）：

意向共享锁 ，英文名：Intention Shared Lock，简称IS锁。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。

意向独占锁 ，英文名：Intention Exclusive Lock，简称IX锁。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

视角回到大楼和楼层上来：

如果有客户到楼层中办公，那么他先在整栋大楼门口放一把IS锁（表级锁），然后再到楼层门口放一把S锁（行锁）。

如果有维修工到楼层中维修，那么它先在整栋大楼门口放一把IX锁（表级锁），然后再到楼层门口放一把X锁（行锁）。

之后：

如果有投资人要参观大楼，也就是想在大楼门口前放S锁（表锁）时，首先要看一下大楼门口有没有IX锁，如果有，意味着有楼层在维修，需要等到维修结束把IX锁撤掉后才可以在整栋大楼上加S锁。

如果有谈条件要占用大楼，也就是想在大楼门口前放X锁（表锁）时，首先要看一下大楼门口有没有IS锁或IX锁，如果有，意味着有楼层在办公或者维修，需要等到客户们办完公以及维修结束把IS锁和IX锁撤掉后才可以在整栋大楼上加X锁。

注意： 客户在大楼门口加IS锁时，是不关心大楼门口是否有IX锁的，维修工在大楼门口加IX锁时，是不关心大楼门口是否有IS锁或者其他IX锁的。IS和IX锁只是为了判断当前时间大楼里有没有被占用的楼层用的，也就是在对大楼加S锁或者X锁时才会用到。

下面我们详细的来讲述意向锁：

概念

意向锁是表锁，为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。

InnoDB 支持多粒度锁（multiple granularity locking） ，它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

意向锁分为两种：

• 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

  -- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
  SELECT column FROM table ... LOCK IN SHARE MODE;
  1
  2

• 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

  -- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
  SELECT column FROM table ... FOR UPDATE;
  1
  2

即：意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前InooDB 会先获取该数据行所在数据表的对应意向锁。

作用

当有事务A有行锁时，MySQL会自动为该表添加意向锁，事务B如果想申请整个表的写锁，那么不需要遍历每一行判断是否存在行锁，而直接判断是否存在意向锁，增强性能。

为什么意向锁是表级锁

当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）；

（1）如果意向锁是行锁，则需要遍历每一行数据去确认；

（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

意向锁怎么支持表锁和行锁并存？
（1）首先明确并存的概念是指数据库同时支持表、行锁，而不是任何情况都支持一个表中同时有一个事务A持有行锁、又有一个事务B持有表锁，因为表一旦被上了一个表级的写锁，肯定不能再上一个行级的锁。

（2）如果事务A对某一行上锁，其他事务就不可能修改这一行。这与“事务B锁住整个表就能修改表中的任意一行”形成了冲突。所以，没有意向锁的时候，让行锁与表锁共存，就会带来很多问题。于是有了意向锁的出现，如前面所言，数据库不需要在检查每一行数据是否有锁，而是直接判断一次意向锁是否存在即可，能提升很多性能。

意向锁的兼容互斥性

锁的组合性：（意向锁没有行锁）

总结：

当我们在对使用InnoDB存储引擎的表的某些记录加S锁之前，那就需要先在表级别加一个IS锁，当我们在对使用InnoDB存储引擎的表的某些记录加X锁之前，那就需要先在表级别加一个IX锁。

IS锁和IX锁的使命只是为了后续在加表级别的S锁和X锁时判断表中是否有已经被加锁的记录，以避免用遍历的方式来查看表中有没有上锁的记录。我们并不能手动添加意向锁，只能由InnoDB存储引擎自行添加。

3.2.3、自增锁（AUTO-INC锁）

在使用MySQL过程中，我们可以为表的某个列添加AUTO_INCREMENT 属性。举例：

CREATE TABLE `teacher`
(
    `id`   int          NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;
1
2
3
4
5
6
7
8

由于这个表的id字段声明了AUTO_INCREMENT，意味着在书写插入语句时不需要为其赋值，SQL语句修改如下所示:

INSERT INTO `teacher` (name)
VALUES ('zhangsan'),
       ('lisi');
1
2
3

select * from teacher;
1

现在我们看到的上面插入数据只是一种简单的插入模式，所有插入数据的方式总共分为三类，分别是“ Simple inserts ”，“ Bulk inserts ”和“ Mixed-mode inserts ”。

1. “Simple inserts” （简单插入）
   可以预先确定要插入的行数（当语句被初始处理时）的语句。包括没有嵌套子查询的单行和多行INSERT…VALUES() 和REPLACE 语句。比如我们上面举的例子就属于该类插入，已经确定要插入的行
   数。
2. “Bulk inserts” （批量插入）
   事先不知道要插入的行数（和所需自动递增值的数量）的语句。比如INSERT … SELECT ， REPLACE… SELECT 和LOAD DATA 语句，但不包括纯INSERT。 InnoDB在每处理一行，为AUTO_INCREMENT列分配一个新值。
3. “Mixed-mode inserts” （混合模式插入）
   这些是“Simple inserts”语句但是指定部分新行的自动递增值。例如INSERT INTO teacher (id,name)
   VALUES (1,‘a’), (NULL,‘b’), (5,‘c’), (NULL,‘d’); 只是指定了部分id的值。另一种类型的“混合模式插入”是 INSERT … ON DUPLICATE KEY UPDATE 。

InnoDB提供了一个称之为innodb_autoinc_lock_mode的系统变量来控制到底使用上述两种方式中的哪种来为AUTO_INCREMENT修饰的列进行赋值，当innodb_autoinc_lock_mode值为0时，一律采用AUTO-INC锁；当innodb_autoinc_lock_mode值为1时，一律采用轻量级锁；当innodb_autoinc_lock_mode值为2时，两种方式混着来（也就是在插入记录数量确定时采用轻量级锁，不确定时使用AUTO-INC锁）。

不过当innodb_autoinc_lock_mode值为2时，可能会造成不同事务中的插入语句为AUTO_INCREMENT修饰的列生成的值是交叉的，在有主从复制的场景中是不安全的。

详细的进行说明一下：

innodb_autoinc_lock_mode有三种取值，分别对应与不同锁定模式：

（1）innodb_autoinc_lock_mode = 0(“传统”锁定模式)

在此锁定模式下，所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁，用于插入具有AUTO_INCREMENT列的表。这种模式其实就如我们上面的例子，即每当执行insert的时候，都会得到一个表级锁(AUTO-INC锁)，使得语句中生成的auto_increment为顺序，且在binlog中重放的时候，可以保证master与slave中数据的auto_increment是相同的。因为是表级锁，当在同一时间多个事务中执行insert的时候，对于AUTO-INC锁的争夺会限制并发能力。

（2）innodb_autoinc_lock_mode = 1(“连续”锁定模式)

在 MySQL 8.0 之前，连续锁定模式是默认的。

在这个模式下，“bulk inserts”仍然使用AUTO-INC表级锁，并保持到语句结束。这适用于所有INSERT …
SELECT，REPLACE … SELECT和LOAD DATA语句。同一时刻只有一个语句可以持有AUTO-INC锁。对于“Simple inserts”（要插入的行数事先已知），则通过在mutex（轻量锁） 的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁， 它只在分配过程的持续时间内保持，而不是直到语句完成。不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。如果另一个事务保持AUTO-INC锁，则“Simple inserts”等待AUTO-INC锁，如同它是一个“bulk inserts”。

（3）innodb_autoinc_lock_mode = 2(“交错”锁定模式)

从 MySQL 8.0 开始，交错锁模式是默认设置。

在此锁定模式下，自动递增值保证在所有并发执行的所有类型的insert语句中是唯一且单调递增的。但是，由于多个语句可以同时生成数字（即，跨语句交叉编号），为任何给定语句插入的行生成的值可能不是连续的。

show variables like 'innodb_autoinc_lock_mode' ;
1

MySQL5.7.X中缺省为1。

3.2.4、元数据锁（MDL锁）

MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。MDL 的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

3.2.5、表锁的总结

表级锁是MySQL中最基本的锁策略，是MySQL最早采用的锁策略。表级锁的特点是开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

在MySQL中，对MyISAM表的读操作，会自动加上读锁，对MyISAM表的写操作，会自动加上写锁。InnoDB引擎在必要情况下会使用表锁，但主要是使用行锁来实现多版本并发控制（MVCC），它能提供更好的并发性能和更少的锁冲突。

总的来说，表锁适用于读操作多、写操作少的应用，当并发争用不是特别激烈，以及记录级锁并发控制开销大于访问冲突开销的情况。在并发度高，或者写操作较多的情况下，表锁可能会成为瓶颈。

表级锁有哪些使用场景

1. 读密集型应用：如果你的应用主要进行读取操作，很少进行写入操作，那么使用表级锁可能是一个好选择。因为表级读锁不会阻塞其他的读锁，所以这种场景下表级锁能够提供很高的性能。
2. 写操作不频繁的场景：表级锁对写操作的处理并不高效，因为一个写锁会阻塞所有其他的锁，无论它们是读锁还是写锁。但是，如果你的应用不需要频繁地进行写操作，或者可以容忍写操作的延迟，那么使用表级锁可能是可行的。
3. 数据量不大的简单应用：如果数据库的数据量不大，那么即使在写操作中，由于锁定整张表，对性能的影响也不大。
4. 全表更新或者删除：在某些情况下，可能需要对一张表进行全表的更新或者删除操作，例如，删除表中的所有记录，或者更新表中所有记录的某个字段的值。在这种情况下，使用表级锁是合适的。

但要注意，虽然表级锁的开销较小，但由于其锁定粒度大，可能会导致并发度下降，特别是在写操作较多或者并发度较高的场景下。所以，如果应用的并发度较高，或者需要频繁进行写操作，那么可能需要考虑使用更精细粒度的锁，如行锁。

MySQL哪些命令会发生表级锁

1. ALTER TABLE：这个命令用于更改表的结构，如添加列、删除列、改变列的类型等。执行这个命令的时候，MySQL需要锁定整个表以防止在更改过程中有新的数据写入。
2. DROP TABLE 和 TRUNCATE TABLE：这两个命令都会导致表级锁。DROP TABLE命令会删除整个表，而TRUNCATE TABLE命令会删除表中的所有数据。在执行这些命令的时候，MySQL需要锁定整个表以防止在删除过程中有新的数据写入。
3. LOCK TABLES：这个命令可以显式地为一个或多个表加上读锁或写锁。LOCK TABLES命令后面可以跟上一系列的表名和锁模式，用来指定需要锁定哪些表，以及使用什么样的锁模式。例如，LOCK TABLES t1 WRITE, t2 READ;命令会给表t1加上写锁，给表t2加上读锁。
4. 全表扫描或大范围扫描：对于MyISAM存储引擎，全表扫描或大范围扫描会触发表级锁。
5. FLUSH TABLES WITH READ LOCK(FTWRL)：这个命令可以给所有表加上全局读锁，其他会话在此期间不能对数据进行修改。

请注意，InnoDB存储引擎主要使用行级锁，并在一些情况下使用表级锁，比如在执行某些ALTER TABLE命令或者LOCK TABLES命令时。MyISAM存储引擎只支持表级锁。

MySQL表锁风险点

1. 性能下降：因为表锁会锁定整个表，所以在高并发的环境中，它可能导致大量的请求阻塞，从而降低性能。对于读取和写入混合密集的负载，表锁可能会成为一个性能瓶颈。
2. 并发性能差：表锁的最大问题在于其并发性能。一旦一个线程对表获得了写锁，其他线程的任何读写操作都会被阻塞，直到写锁被释放。同样的，如果一个读锁被持有，那么其他的写操作将被阻塞。这就使得并发性能大大降低。
3. 可能导致锁等待和超时：在高并发的环境中，由于表级锁的粒度较大，可能会有很多线程在等待锁，如果等待的时间过长，可能会导致锁超时，进一步影响应用的性能和可用性。
4. 写操作影响大：如果一个长时间运行的写操作（比如大数据量的UPDATE或者INSERT语句）获取了写锁，那么会阻塞所有其他的读操作和写操作，直到这个写操作完成。
5. 死锁的可能性：虽然表锁本身不会出现死锁，但在多表操作中，如果没有按照一定的顺序获得锁，可能会导致死锁。

为了避免这些问题，我们通常会选择InnoDB存储引擎，它主要使用行级锁，可以提供更好的并发性能，并且在一定程度上减少了锁争用的问题。而且，InnoDB还支持事务，可以保证数据的一致性和完整性。在实际应用中，我们应该根据具体的业务需求和系统负载，选择合适的存储引擎和锁策略。

3.3、从数据操作的粒度划分：行锁

mysql数据库 innoDB存储引擎 锁的是索引

锁的算法

t2 这张表 id 有一个主键索引。我们插入了 4 行数据，主键 id 分别是 1、4、7、10。

我们这里的划分标准是主键 id。

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

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

假设我们有 N 个 Record，那么所有的数据会被划分成多少个 Gap 区间？答案是 N+1，就像我们把一条绳子砍 N 刀，它最后肯定是变成 N+1 段。

最后一个，间隙（Gap）连同它左边的记录（Record），我们把它叫做临键的区间，它是一个左开右闭的区间。

如果主键索引不是整型，是字符怎么办呢？字符可以排序吗？ 基于 ASCII 码

记录锁

第一种情况，当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一

条记录的时候，这个时候使用的就是记录锁。

比如 where id = 1 4 7 10 。

间隙锁

第二种情况，当我们查询的记录不存在，无论是用等值查询还是范围查询的时候，它使用的都是间隙锁。

临键锁

第三种情况，当我们使用了范围查询，不仅仅命中了 Record 记录，还包含了 Gap 间隙，在这种情况下我们使用的就是临键锁，它是 MySQL 里面默认的行锁算法，相当于记录锁加上间隙锁。

比如我们使用>5 <9 ， 它包含了不存在的区间，也包含了一个 Record 7。

锁住最后一个 key 的下一个左开右闭的区间。

select * from t2 where id >5 and id <=7 for update; 锁住(4,7]和(7,10]

select * from t2 where id >8 and id <=10 for update; 锁住 (7,10]，(10,+∞)**

总结：为什么要锁住下一个左开右闭的区间？——就是为了解决幻读的问题。

下面来详细讲述这些锁：

3.3.1、记录锁（Record Locks）

记录锁（Record Lock）是MySQL数据库中InnoDB存储引擎的一种锁定机制，主要用于锁定和控制对单个行记录的访问。记录锁是在索引记录上设置的，对于表没有主键或唯一索引的表，InnoDB会生成一个隐藏的聚簇索引，并在这个隐藏索引上加锁。

在实际操作中，记录锁通常会在进行数据查询、更新或删除等操作时自动被数据库引擎应用。例如，当执行以下查询时，MySQL会在Orders表的OrderID为1的行上设置记录锁：

SELECT * FROM Orders WHERE OrderID = 1 FOR UPDATE;
1

记录锁也就是仅仅把一条记录锁上，官方的类型名称为： LOCK_REC_NOT_GAP 。比如我们把id值为8的那条记录加一个记录锁的示意图如图所示。仅仅是锁住了id值为8的记录，对周围的数据没有影响。

记录锁是有S锁和X锁之分的，称之为S型记录锁和X型记录锁。

• 当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；
• 当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁。

记录锁有什么使用场景？

记录锁（Record Lock）在数据库的许多常见场景中都会用到，以下是几个主要的使用场景：

1. 单行数据修改：当你需要修改一行数据并且希望在修改期间防止其他事务修改同一行数据时，可以使用记录锁。例如，如果你正在更新一个订单状态，你可能不希望其他事务在更新过程中修改这个订单。

   UPDATE Orders SET Status = 'Processed' WHERE OrderID = 1;
   1

2. 单行数据查询：在某些情况下，你可能需要查询一行数据，并且希望在查询过程中防止其他事务修改这行数据。例如，如果你正在读取一个订单的状态并做出相应的操作，你可能希望在读取过程中订单的状态不被改变。

   SELECT * FROM Orders WHERE OrderID = 1 FOR UPDATE;
   1

3. 保持数据一致性：在某些需要保证数据一致性的场景中，也需要使用记录锁。例如，在转账操作中，你可能需要在两个账户之间转移资金，这需要对两个账户的余额进行修改。在修改期间，你可能希望防止其他事务修改这两个账户的余额，以防止数据不一致。

以上例子中的UPDATE和SELECT … FOR UPDATE语句在执行时会自动获取记录锁。在操作完成或事务提交（或回滚）后，数据库会自动释放记录锁，无需手动操作。

创建数据库与表结构

-- 创建数据库
CREATE DATABASE IF NOT EXISTS demo_db;
USE demo_db;

-- 创建示例表
CREATE TABLE student
(
    id    INT PRIMARY KEY,
    name  VARCHAR(50),
    class VARCHAR(50)
);

-- 插入随机数据
INSERT INTO student (id, name, class)
VALUES (1, '张三', '一班'),
       (3, '李四', '一班'),
       (8, '王五', '二班'),
       (15, '赵六', '二班'),
       (20, '钱七', '三班');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

演示记录锁

为了演示记录锁，假设我们有两个会话（事务）。在第一个事务中，我们获取一条记录的锁，然后在第二个事务中尝试访问相同的记录。

-- 事务1：获取id为8的记录的X锁
START TRANSACTION;
1
2

SELECT * FROM student WHERE id = 8 FOR UPDATE;
1

在此时开启另一个会话（事务2）并执行以下操作：

-- 事务2：尝试获取id为8的记录的X锁（会被阻塞，直到事务1提交）
START TRANSACTION;
1
2

SELECT * FROM student WHERE id = 8 FOR UPDATE;
1

回到事务1，可以选择提交或回滚事务，然后事务2将解除阻塞：

-- 事务1提交或回滚
COMMIT;  -- 或 ROLLBACK;
1
2

3.3.2、间隙锁（Gap Locks）

间隙锁（Gap Locks）是MySQL InnoDB存储引擎提供的一种锁定机制。它锁定的不是具体的行记录，而是两个索引之间的间隙（或者说区间），这样可以防止新的记录插入到该间隙，确保数据的一致性和事务的隔离性。
间隙锁常常与记录锁（Record Locks）一起使用，共同形成Next-Key锁，保护索引记录的范围查询和扫描操作。
以下是间隙锁的主要类型：

1. 区间-区间间隙锁：锁定两个索引键之间的间隙，或者是第一个索引键之前的间隙。
2. 区间-记录间隙锁：锁定一个索引键和一个记录之间的间隙。
3. 记录-区间间隙锁：锁定一个记录和一个索引键之间的间隙。

MySQL 在REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用MVCC 方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。InnoDB提出了一种称之为Gap Locks 的锁，官方的类型名称为： LOCK_GAP ，我们可以简称为gap锁。比如，把id值为8的那条记录加一个gap锁的示意图如下。

图中id值为8的记录加了gap锁，意味着不允许别的事务在id值为8的记录前边的间隙插入新记录，其实就是id列的值(3, 8)这个区间的新记录是不允许立即插入的。比如，有另外一个事务再想插入一条id值为4的新记录，它定位到该条新记录的下一条记录的id值为8，而这条记录上又有一个gap锁，所以就会阻塞插入操作，直到拥有这个gap锁的事务提交了之后，id列的值在区间(3, 8)中的新记录才可以被插入。

gap锁的提出仅仅是为了防止插入幻影记录而提出的。

间隙锁有哪些使用场景
间隙锁（Gap Locks）在MySQL数据库的InnoDB存储引擎中主要用于以下场景：

1. 防止幻读：间隙锁的主要目的是防止其他事务在已经锁定的范围内插入新的行。这可以避免"幻读"问题，即一个事务在读取某个范围内的所有行时，另一个事务插入了一个新行，当第一个事务再次读取该范围时，会发现有一个"幻影"行。
2. 范围查询：在执行范围查询时，如果事务需要对查询结果进行更新或删除，那么间隙锁可以保证在事务执行期间，不会有新的行插入到查询范围中。

例如，以下事务会在Orders表的OrderID列值在1到100之间的所有行上设置排他锁，并在这些行的间隙上设置间隙锁：

START TRANSACTION;
SELECT * FROM Orders WHERE OrderID BETWEEN 1 AND 100 FOR UPDATE;
COMMIT;
1
2
3

3. 防止死锁：在某些情况下，间隙锁可以帮助防止死锁。如果没有间隙锁，那么两个事务可能都会试图在同一位置插入一个新行，导致彼此等待对方释放锁，从而形成死锁。

需要注意的是，间隙锁在可重复读（REPEATABLE READ）和序列化（SERIALIZABLE）这两个隔离级别下才会使用，在读已提交（READ COMMITTED）和读未提交（READ UNCOMMITTED）这两个隔离级别下，InnoDB不会使用间隙锁。

间隙锁有什么缺点？
间隙锁（Gap Locks）是MySQL的InnoDB存储引擎用于防止幻读问题的一种锁定机制，虽然它在某些场景下非常有用，但也存在一些潜在的缺点，包括：

1. 性能影响：间隙锁会阻止其他事务在已经锁定的范围内插入新的行，这可能会影响到数据库的并发性能，尤其在需要大量插入操作的高并发场景下。
2. 死锁风险：虽然间隙锁可以在某些情况下防止死锁，但在其他情况下，它可能会增加死锁的风险。比如，两个事务都想在同一间隙中插入新的行，就可能发生死锁。
3. 复杂性：理解间隙锁及其对事务的影响可能需要相当深入的数据库知识，尤其是在处理并发问题和调优数据库性能时。
4. 锁定范围可能过大：间隙锁锁定的是索引之间的间隙，这可能会比实际需要锁定的行要多。如果一个事务需要锁定的只是表中的一小部分行，但由于间隙锁的存在，可能会锁定更大范围的数据，导致不必要的锁定冲突。

请注意，以上所述的缺点主要取决于具体的使用场景和工作负载，有时候，为了保持数据的一致性和防止并发问题，这些缺点可能是可以接受的。

创建数据库与表结构

-- 创建数据库
CREATE DATABASE IF NOT EXISTS demo_gap_lock_db;
USE demo_gap_lock_db;

-- 创建示例表
CREATE TABLE student
(
    id    INT PRIMARY KEY,
    name  VARCHAR(50),
    class VARCHAR(50)
);

-- 插入随机数据
INSERT INTO student (id, name, class)
VALUES (1, '张三', '一班'),
       (3, '李四', '一班'),
       (8, '王五', '二班'),
       (15, '赵六', '二班'),
       (20, '钱七', '三班');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

1. 设置事务隔离级别为REPEATABLE READ

-- 设置事务隔离级别为REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1
2

2. 事务1：对id为8的记录加锁

-- 事务1：对id为8的记录前的间隙加锁（Gap Lock）
START TRANSACTION;
1
2

SELECT * FROM student WHERE id = 8 FOR UPDATE;
1

此时，MySQL会在id为8的记录上加上Gap Lock，锁住id为3和id为8之间的间隙，防止其他事务在此间隙中插入新记录。

3. 事务2：尝试在间隙中插入新记录

在另一个会话（事务2）中尝试插入一个新记录：

-- 事务2：尝试在id值为3和8之间插入新记录（会被阻塞）
START TRANSACTION;
1
2

INSERT INTO student (id, name, class) VALUES (4, '新生', '一班');
1

由于事务1在id为3和id为8之间加了Gap Lock，事务2的插入操作会被阻塞(理想情况下)，直到事务1提交或回滚。

没有出现间隙锁的原因：

小范围内的数据量：如果表的数据量非常小，MySQL有时可能不会对某些范围自动使用Gap Lock。尝试增加数据量或测试多个区间。

索引的使用：Gap Lock一般会出现在使用索引的查询上。如果上述查询没有引发Gap Lock，可能是因为MySQL没有用到索引。确保id列是主键或具有索引。

独立的会话：确保事务1和事务2在不同的会话中运行，并且在同一个会话中，不要同时运行这些事务。

4.提交或回滚事务1

-- 事务1：提交或回滚
COMMIT;  -- 或 ROLLBACK;
1
2

在事务1提交或回滚后，事务2将被解除阻塞（理想情况），可以完成插入操作。

3.3.3、临键锁（Next-Key Locks）

有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，所以InnoDB就提出了一种称之为Next-Key Locks 的锁，官方的类型名称为： LOCK_ORDINARY ，我们也可以简称为next-key锁。Next-Key Locks是在存储引擎innodb 、事务级别在可重复读的情况下使用的数据库锁，innodb默认的锁就是Next-Key locks。

Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

临键锁有哪些使用场景？
临键锁（Next-Key Lock）在MySQL数据库的InnoDB存储引擎中主要用于以下场景：

1. 防止幻读：幻读是指在一个事务执行过程中，同样的查询操作返回了不同的结果集，这是由于其他事务在这两次查询操作之间插入了新的记录。临键锁可以防止在锁定范围内插入新的行，从而避免了幻读问题。
2. 范围查询和修改：在执行范围查询和修改操作时，临键锁可以确保数据的一致性。比如，当你在一个事务中执行了如下查询，并且请求了一个排他锁：

SELECT * FROM Orders WHERE OrderID BETWEEN 100 AND 200 FOR UPDATE;
1

在这种情况下，InnoDB会对OrderID值在100到200之间的所有记录加上行锁，并且对这个范围内的间隙加上间隙锁，合在一起就形成了临键锁。这样，直到事务结束，其他事务都不能在这个范围内插入新的记录。

防止死锁：临键锁可以在某些情况下防止死锁。比如，两个事务都想在同一间隙中插入新的行，由于临键锁的存在，这可能防止了死锁的发生。

需要注意的是，临键锁只在可重复读（REPEATABLE READ）和序列化（SERIALIZABLE）这两个隔离级别下使用，在读已提交（READ COMMITTED）和读未提交（READ UNCOMMITTED）这两个隔离级别下，InnoDB不会使用临键锁。

临键锁有什么缺点？

临键锁（Next-Key Lock）是一种强大的锁定机制，可以帮助保持数据库的一致性并防止幻读（phantom reads）。然而，它也有一些潜在的缺点，包括：

1. 性能影响：临键锁可以防止其他事务在已经锁定的范围内插入新的行。这种锁定级别可能会影响到数据库的并发性能，尤其是在高并发的插入操作中。
2. 过度锁定：临键锁可能导致比实际需要锁定的行更多的数据被锁定。因为临键锁不仅锁定了数据行，还锁定了索引间隙，这可能会导致更多的数据被锁定，从而影响到其他事务。
3. 复杂性：理解临键锁及其对数据库操作的影响可能需要相当深入的数据库知识，特别是在处理并发问题和调优数据库性能时。
4. 死锁风险：虽然临键锁可以在某些情况下防止死锁，但在其他情况下，它可能会增加死锁的风险。比如，如果两个事务都试图在同一个间隙中插入新行，就可能发生死锁。

请注意，这些潜在的缺点主要取决于具体的使用场景和工作负载，有时候，为了保持数据的一致性和防止并发问题，这些缺点可能是可以接受的。

3.3.4、插入意向锁（Insert Intention Locks）

我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了gap锁（ next-key锁也包含gap锁），如果有的话，插入操作需要等待，直到拥有gap锁的那个事务提交。但是InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。InnoDB就把这种类型的锁命名为Insert Intention Locks ，官方的类型名称为：LOCK_INSERT_INTENTION ，我们称为插入意向锁。插入意向锁是一种Gap锁，不是意向锁，在insert操作时产生。

插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。

事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

3.3.5、行锁的总结

行级锁是MySQL中的一种锁定机制，它可以对数据库表中的单独一行进行锁定。相比于表级锁和页锁，行级锁的粒度更小，因此在处理高并发事务时，能提供更好的并发性能和更少的锁冲突。然而，行级锁也需要更多的内存和CPU资源，因为需要对每一行都进行管理。

在MySQL中，行级锁主要由InnoDB存储引擎提供。InnoDB支持两种类型的行级锁：共享锁（S锁）和排他锁（X锁）。在实际使用中，InnoDB还提供了一种名为“间隙锁”（Gap Lock）的特性。间隙锁不仅锁定一个具体的行，还锁定它前后的“间隙”，即这一行之前的行和之后的行之间的空间。间隙锁可以防止其他事务插入新的行到已锁定行的前后，从而可以解决一些并发问题。

值得注意的是，行级锁只在事务中有效，也就是说，只有在一个事务开始（BEGIN）后并在事务提交（COMMIT）或回滚（ROLLBACK）之前，才能对数据行进行锁定。如果在非事务环境中执行SQL语句，那么InnoDB会在语句执行结束后立即释放所有的锁。

MySQL行锁有哪些使用场景
MySQL中的行级锁（Row Level Locks）通常在以下几种场景中被使用：

1. 高并发读写操作：在需要高并发读写操作的场景中，行级锁可以提高性能和并发性，因为它允许多个事务并发地操作不同的行。
2. 单行操作：对于需要操作单行数据的SQL语句（例如基于主键或者唯一索引的UPDATE、DELETE和INSERT语句），行级锁可以提供较好的并发性和性能。
3. 短期锁：在需要对数据行进行短时间锁定的情况下，行级锁可以防止长时间阻塞其他事务。
4. 实现并发控制：在需要确保数据一致性和隔离性的事务中，行级锁是实现并发控制的重要机制。
5. 复杂的事务处理：在需要对多行数据进行复杂处理的事务中，可以使用行级锁来锁定这些行，防止在事务处理过程中数据被其他事务修改。

使用行级锁需要注意，由于行级锁的锁定粒度较小，它可能会消耗更多的系统资源（例如内存和CPU），特别是在处理大量数据时。此外，使用行级锁也可能导致死锁，需要使用合适的策略来避免死锁，例如在事务中按照一定的顺序锁定行。

MySQL那些命令会导致发生行锁？
在MySQL中，主要是InnoDB存储引擎提供了行级锁（Row Level Locking）。一般来说，以下这些类型的操作会导致InnoDB对数据行进行加锁：

• SELECT … FOR UPDATE：这种查询会对选定的行添加一个排他锁（X锁），这意味着其他事务不能修改这些行，也不能对这些行添加共享锁。
• SELECT … LOCK IN SHARE MODE：这种查询会对选定的行添加一个共享锁（S锁），这意味着其他事务不能修改这些行，但可以对这些行添加共享锁。
• INSERT：插入操作会对新添加的行添加一个排他锁（X锁）。
• UPDATE：更新操作会对被更新的行添加一个排他锁（X锁）。
• DELETE：删除操作会对被删除的行添加一个排他锁（X锁）。

这些加锁操作都是在事务中进行的，即只有在事务开始（BEGIN）后并在事务提交（COMMIT）或回滚（ROLLBACK）之前，才会对数据行进行加锁。如果在非事务环境中执行上述SQL语句，那么InnoDB会在语句执行结束后立即释放所有的锁。

请注意，加锁的粒度和范围取决于WHERE子句中用到的索引。如果WHERE子句中用到了唯一索引（例如主键索引），那么InnoDB只会锁定匹配的行。如果没有用到唯一索引，那么InnoDB可能会锁定更多的行，甚至是整个表，这就可能导致锁冲突和性能问题。

此外，InnoDB还支持间隙锁（Gap Locks）和临键锁（Next-Key Locks），这两种锁都可以在某些情况下提供更好的并发控制。

MySQL行锁有什么风险点？

尽管行级锁（Row-Level Locking）可以提供高并发性并减少锁冲突，但在使用过程中也可能遇到一些风险和问题，主要包括以下几点：

1. 死锁：当两个或更多的事务相互等待对方释放资源时，就会发生死锁。例如，事务1锁定了行A并试图锁定行B，同时事务2锁定了行B并试图锁定行A，这就形成了死锁。MySQL会检测到死锁并终止其中一个事务，但这仍可能导致性能问题和事务失败。
2. 锁升级：如果一个事务试图锁定的行过多，InnoDB可能会将锁从行级升级为表级，这就可能导致更多的锁冲突。
3. 锁等待：如果一个事务已经锁定了某行，其他试图访问这行的事务就必须等待，这可能导致性能下降。如果有大量的事务在等待锁，就可能导致系统出现性能瓶颈。
4. 资源消耗：行级锁需要更多的内存来存储锁信息，而且需要更多的CPU时间来处理锁请求和释放锁。如果数据库中的行数非常多，或者并发事务的数量非常多，这可能会导致显著的资源消耗。
5. 难以调试和排查：由于行级锁的粒度较小，如果出现性能问题或锁冲突，可能需要复杂的调试和排查工作来找出问题的原因。
6. 事务隔离级别：不同的事务隔离级别会影响锁的行为和性能，可能需要根据具体的应用场景来调整事务隔离级别。

为了避免上述问题，需要合理地设计数据库表和索引，合理地编写SQL语句，合理地管理事务，以及合理地设置事务隔离级别。

3.4、从数据操作的粒度划分：页级锁

页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

每个层级的锁数量是有限制的，因为锁会占用内存空间， 锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

3.5、从对待锁的态度划分:乐观锁、悲观锁

悲观锁

正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

3.6、按加锁的方式划分：显式锁、隐式锁

3.6.1、隐式锁

**情景一：**对于聚簇索引记录来说，有一个trx_id 隐藏列，该隐藏列记录着最后改动该记录的事务id 。那么如果在当前事务中新插入一条聚簇索引记录后，该记录的trx_id 隐藏列代表的的就是当前事务的事务id ，如果其他事务此时想对该记录添加S锁或者X锁时，首先会看一下该记录的trx_id 隐藏列代表的事务是否是当前的活跃事务，如果是的话，那么就帮助当前事务创建一个X锁（也就是为当前事务创建一个锁结构， is_waiting 属性是false ），然后自己进入等待状态（也就是为自己也创建一个锁结构， is_waiting 属性是true ）。

**情景二：**对于二级索引记录来说，本身并没有trx_id 隐藏列，但是在二级索引页面的PageHeader 部分有一个PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的事务id ，如果PAGE_MAX_TRX_ID 属性值小于当前最小的活跃事务id ，那么说明对该页面做修改的事务都已经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记录，然后再重复情景一的做法。

数据库环境准备：

CREATE DATABASE locking_demo;
USE locking_demo;

CREATE TABLE student
(
    id    INT PRIMARY KEY,
    name  VARCHAR(50),
    class VARCHAR(20)
) ENGINE = InnoDB;

CREATE TABLE grades
(
    id         INT PRIMARY KEY,
    student_id INT,
    subject    VARCHAR(50),
    grade      CHAR(1),
    FOREIGN KEY (student_id) REFERENCES student (id)
) ENGINE = InnoDB;
INSERT INTO student (id, name, class)
VALUES (1, 'Alice', 'Class A'),
       (2, 'Bob', 'Class A'),
       (3, 'Charlie', 'Class B'),
       (4, 'David', 'Class B');

INSERT INTO grades (id, student_id, subject, grade)
VALUES (1, 1, 'Math', 'A'),
       (2, 2, 'Math', 'B'),
       (3, 3, 'Math', 'C'),
       (4, 4, 'Math', 'D');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

场景一：聚簇索引中的隐式锁

1. Session 1: 在第一个事务中插入一个新记录，该记录会自动获得一个隐式的X锁。

BEGIN;
1

INSERT INTO student VALUES(5, 'Eve', 'Class C');
-- 事务保持未提交状态
1
2

2. Session 2: 在第二个事务中，尝试查询所有记录并添加共享锁 (LOCK IN SHARE MODE)。由于 Eve 的记录持有隐式X锁，查询将被阻塞。

BEGIN;
1

SELECT * FROM student LOCK IN SHARE MODE; 
-- 这将被阻塞，直到Session 1提交或回滚
1
2

场景二：二级索引中的隐式锁

1. Session 1: 在第一个事务中更新 grades 表中的一条记录。这会更新 PAGE_MAX_TRX_ID，并锁住对应的二级索引记录。

BEGIN;
1

UPDATE grades SET grade = 'A' WHERE student_id = 2;
-- 事务保持未提交状态
1
2

2. Session 2: 在第二个事务中，尝试对 grades 表进行共享锁查询。由于二级索引记录锁被占用，这个查询将被阻塞。

BEGIN;
1

SELECT * FROM grades WHERE student_id = 2 LOCK IN SHARE MODE; 
-- 这将被阻塞，直到Session 1提交或回滚
1
2

查看锁信息

使用 performance_schema.data_lock_waits 来查看锁的详细信息：

SELECT * FROM performance_schema.data_lock_waits\G;
1

隐式锁的逻辑过程如下：

• A. InnoDB的每条记录中都一个隐含的trx_id字段，这个字段存在于聚簇索引的B+Tree中。
• B. 在操作一条记录前，首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活
• 动的事务，首先将隐式锁转换为显式锁(就是为该事务添加一个锁)。
• C. 检查是否有锁冲突，如果有冲突，创建锁，并设置为waiting状态。如果没有冲突不加锁，跳到E。
• D. 等待加锁成功，被唤醒，或者超时。
• E. 写数据，并将自己的trx_id写入trx_id字段。

3.6.2、显式锁

通过特定的语句进行加锁，我们一般称之为显示加锁，例如：

显示加共享锁：

select .... lock in share mode
1

显示加排它锁：

select .... for update
1

3.7、其它锁

3.7.1、全局锁

全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是：做全库逻辑备份。

全局锁的命令：

Flush tables with read lock
1

MySQL全局锁的典型使用场景是，进行一些需要确保整个数据库一致性的操作，例如全库备份、全库导出等。

在MySQL中，可以使用FLUSH TABLES WITH READ LOCK(FTWRL)语句来添加全局读锁，这将阻止其他线程进行更新操作。使用UNLOCK TABLES语句来释放锁定。

请注意，全局锁的开销非常大，因为它会阻止其他所有的数据修改操作，并且在高并发情况下可能导致大量的线程等待锁定。因此，你应该尽量避免在生产环境中使用全局锁，或者尽量减少全局锁的持有时间。

虽然全局锁有其应用场景，但是过度使用或不正确使用全局锁可能导致性能问题。因此，根据应用的特性和需求选择适合的锁策略是很重要的。对于大多数应用，优先使用更精细粒度的锁，如行锁和表锁，可以更有效地处理并发请求，同时避免全局锁的开销。

全局锁有哪些应用场景

1. 备份全库：使用全局锁可以确保在备份过程中，数据库的所有表都保持一致的状态。例如，可以使用FLUSH TABLES WITH READ LOCK命令在备份期间阻止任何写操作，以确保备份数据的一致性。
2. 整体数据迁移：如果你需要将整个数据库从一个服务器迁移到另一个服务器，那么在迁移过程中，你可能希望阻止任何写操作，以确保所有的数据都被正确地迁移到新的服务器。
3. 全库只读：在某些情况下，你可能希望将整个数据库设置为只读模式。例如，如果你在进行一些可能破坏数据完整性的操作，或者在进行系统维护时，可能需要将数据库设置为只读模式。在这种情况下，可以使用全局读锁

请注意，全局锁会对整个数据库进行锁定，可能会导致大量的请求阻塞，对性能影响很大。因此，尽管全局锁在某些情况下可能有用，但通常应当尽量避免在需要高并发处理能力的生产环境中使用全局锁。尤其是在处理大量并发写请求的应用中，过度使用全局锁可能会导致严重的性能问题。

3.7.2、死锁

3.7.2.1、概念

是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁。

举个例子：A和B去按摩洗脚，都想在洗脚的时候，同时顺便做个头部按摩，13技师擅长足底按摩，14擅长头部按摩。

这个时候A先抢到14，B先抢到13，两个人都想同时洗脚和头部按摩，于是就互不相让，扬言我死也不让你，这样的话，A抢到14，想要13，B抢到13，想要14，在这个想同时洗脚和头部按摩的事情上A和B就产生了死锁。怎么解决这个问题呢？

第一种，假如这个时候，来了个15，刚好也是擅长头部按摩的，A又没有两个脑袋，自然就归了B，于是B就美滋滋的洗脚和做头部按摩，剩下A在旁边气鼓鼓的，这个时候死锁这种情况就被打破了，不存在了。

第二种，C出场了，用武力强迫A和B，必须先做洗脚，再头部按摩，这种情况下，A和B谁先抢到13，谁就可以进行下去，另外一个没抢到的，就等着，这种情况下，也不会产生死锁。

所以总结一下：

1、死锁是必然发生在多操作者（M>=2个）情况下，争夺多个资源（N>=2个，且N<=M）才会发生这种情况。很明显，单线程自然不会有死锁，只有B一个去，不要2个，打十个都没问题；单资源呢？只有13，A和B也只会产生激烈竞争，打得不可开交，谁抢到就是谁的，但不会产生死锁。同时，死锁还有几个要求，1、争夺资源的顺序不对，如果争夺资源的顺序是一样的，也不会产生死锁；

2、争夺者拿到资源不放手。

3.7.2.2、MySQL中的死锁

MySQL中的死锁的成因是一样的。

数据库环境与准备：

首先，创建一个名为 school_db 的数据库，并在其中创建一张 teacher 表，用于存储教师的编号和姓名。

CREATE DATABASE school_db;
USE school_db;

CREATE TABLE teacher
(
    number INT PRIMARY KEY,
    name   VARCHAR(50)
);

-- 随机插入一些数据
INSERT INTO teacher (number, name)
VALUES (1, 'Teacher A'),
       (2, 'Teacher B'),
       (3, 'Teacher C'),
       (4, 'Teacher D');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

模拟死锁

接下来，我们通过两个会话来模拟死锁的产生。假设有两个事务，分别在不同的会话中操作。

-- 事务1：锁定 number=1 的记录
START TRANSACTION;
1
2

SELECT * FROM teacher WHERE number = 1 FOR UPDATE;
1

-- 事务2：锁定 number=3 的记录
START TRANSACTION;
1
2

SELECT * FROM teacher WHERE number = 3 FOR UPDATE;
1

此时，会话1锁定了 number=1 的记录，会话2锁定了 number=3 的记录。

-- 事务1：尝试锁定 number=3 的记录（此操作将被阻塞）
SELECT * FROM teacher WHERE number = 3 FOR UPDATE;
1
2

-- 事务2：尝试锁定 number=1 的记录（此操作将导致死锁）
SELECT * FROM teacher WHERE number = 1 FOR UPDATE;
1
2

在这个场景中，由于会话1已经锁定了 number=1 的记录，而会话2锁定了 number=3 的记录，因此当它们各自尝试获取对方已经持有的锁时，便产生了死锁。MySQL会自动检测到这一死锁，并选择其中一个事务回滚，以打破死锁。

检查死锁情况

在会话2中，MySQL会检测到死锁，并终止其中一个事务（通常是会话2）。这时会话1中的阻塞操作会继续执行。

要查看死锁的详细情况，可以使用以下命令：

SHOW ENGINE INNODB STATUS\G
1

此命令将输出当前InnoDB存储引擎的状态，包括死锁的详细信息。

在执行 SHOW ENGINE INNODB STATUS\G 后，在 LATEST DETECTED DEADLOCK 部分找到关于死锁的详细信息。具体而言：

1. LATEST DETECTED DEADLOCK: 这是死锁的最新检测信息。此部分会显示最后一次检测到的死锁事件。

2. TRANSACTION: 这里列出了导致死锁的事务，包括它们的活动状态、持有的锁以及等待的锁。

   • HOLDS THE LOCK(S): 这部分信息表示当前事务持有的锁。
   • WAITING FOR THIS LOCK TO BE GRANTED: 这部分表示当前事务在等待的锁。

3. WE ROLL BACK TRANSACTION: 这个部分会说明MySQL决定回滚哪个事务来解除死锁。

关键部分如下：

• LATEST DETECTED DEADLOCK 标记了死锁发生的时间：

  LATEST DETECTED DEADLOCK
  2024-08-13 08:15:02 140591473407744
  1
  2

• TRANSACTION 84777 和 TRANSACTION 84778 是导致死锁的两个事务。详细信息包括：

  • 事务 84777 持有 number = 1 的锁，并等待 number = 3 的锁。
  • 事务 84778 持有 number = 3 的锁，并等待 number = 1 的锁。

• 最终，MySQL 决定回滚 TRANSACTION 84778 来解除死锁：

  *** WE ROLL BACK TRANSACTION (2)
  1

查看事务加锁情况

为了更好地理解事务加锁的情况，可以开启 innodb_status_output_locks 变量来查看哪些锁被哪些事务持有：

SHOW VARIABLES LIKE 'innodb_status_output_locks';
1

-- 将其设置为ON
SET GLOBAL innodb_status_output_locks = ON;
1
2

3.8、其他存储引擎中的锁

对于MyISAM、MEMORY、MERGE这些存储引擎来说，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。比方说在Session 1中对一个表执行SELECT操作，就相当于为这个表加了一个表级别的S锁，如果在SELECT操作未完成时，Session 2中对这个表执行UPDATE操作，相当于要获取表的X锁，此操作会被阻塞，直到Session 1中的SELECT操作完成，释放掉表级别的S锁后，Session 2中对这个表执行UPDATE操作才能继续获取X锁，然后执行具体的更新语句。

因为使用MyISAM、MEMORY、MERGE这些存储引擎的表在同一时刻只允许一个会话对表进行写操作，所以这些存储引擎实际上最好用在只读，或者大部分都是读操作，或者单用户的情景下。
另外，在MyISAM存储引擎中有一个称之为Concurrent Inserts的特性，支持在对MyISAM表读取时同时插入记录，这样可以提升一些插入速度。关于更多Concurrent Inserts的细节，详情可以参考文档。

说明：