> MySQL 总结图解

MySQL数据库中有哪些锁？

作者：徐梦旗，发布于：2024-01-22 20:00，字数：6.8k，预计阅读：34分钟

本文的测试环境为MySQL8.0.34。

1. 锁

1.1. 数据库中的锁有什么作用？

锁是一种并发控制手段，避免多个事务同时对同一条记录进行修改，用来解决线程安全问题。

1.2. 数据库中的锁有哪些分类？

按照锁的功能来划分：

读锁（Read Locks），也叫共享锁^[1]（Shared Locks，S锁）。
写锁（Write Locks），也叫排他锁^[1]（Exclusive Locks，X锁）。

按照锁的粒度来划分：

全局锁（Global Read Locks）。
表级锁（Table Level Locks）。
行级锁（Row Level Locks）。

1.3. 什么是锁的兼容性？

锁的兼容性（Compatibility）是指当一个事务添加了某种锁之后，另一个事务能否添加锁；如果能加代表这两种锁是兼容的，如果不能加代表这两种锁是不兼容的。读锁（S锁）和写锁（X锁）的兼容性如下表：

	S锁	X锁
S锁	兼容	不兼容
X锁	不兼容	不兼容

1.4. 什么是两阶段锁？

在事务执行的过程中添加的锁会在事务提交或回滚时统一释放，即加锁和释放锁分为两个阶段，也就是两阶段锁（Two-Phase Locking，2PL）。

2. 全局锁

2.1. 全局锁有什么作用？

全局读锁是由MySQL服务层实现的锁，添加全局读锁会让整库只读，可以用来备份数据库。

可以通过flush tables with read lock命令为数据库添加全局读锁。
可以通过unlock tables命令或关闭会话来释放全局读锁。

如下示例，当会话s1对数据库添加全局读锁后，会话s1能对表t进行读操作，不能对表t进行写操作；会话s2能对表t进行读操作，对表t的写操作会被阻塞。

mysql> /**s1**/flush tables with read lock;
Query OK, 0 rows affected (0.03 sec)

mysql> /**s1**/select count(*) from t\G
*************************** 1. row ***************************
count(*): 8
1 row in set (0.01 sec)

mysql> /**s1**/update t set value = 1 where id = 1;
ERROR 1223 (HY000): Cant execute the query because you have a conflicting read lock

mysql> /**s2**/select count(*) from t\G
*************************** 1. row ***************************
count(*): 8
1 row in set (0.01 sec)

mysql> /**s2**/update t set value = 1 where id = 1; # BLOCKED

2.2. 如何进行全库备份？

基于MVCC的备份，采用不加锁的方式读取一致性视图，备份过程中业务能正常进行。
基于LBCC的备份，采用加全局读锁的方式使全库只读，备份过程中业务不能正常进行。

mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables > /root/db.sql。

mysqldump -uroot -p --host=localhost --all-databases --single-transcation > /root/db.sql。

3. 表级锁

3.1. 表级读写锁有什么作用？

表级读写锁是由MySQL服务层实现的锁，锁粒度是表级。

可以通过lock tables <table_name> read命令为指定的表添加表级读锁。
可以通过lock tables <table_name> write命令为指定的表添加表级写锁。
可以通过unlock tables命令或关闭会话来释放表级锁。
可以通过show open tables命令查看表级锁的状态。

如下示例，当会话s1对表t添加表级读锁后，另一个会话s2对表t添加表级读锁成功，这说明表级读锁和表级读锁是兼容的。

mysql> /**s1**/lock table t read;
Query OK, 0 rows affected (0.00 sec)

mysql> /**s2**/lock table t read;
Query OK, 0 rows affected (0.00 sec)

mysql> /**s1**/show open tables like 't'\G
*************************** 1. row ***************************
   Database: test
      Table: t
     In_use: 1
Name_locked: 0
1 row in set (0.00 sec)

如下示例，当会话s1对表t添加表级读锁后，另一个会话s2对表t添加表级写锁会被阻塞，这说明表级读锁和表级写锁是不兼容的。

mysql> /**s1**/lock table t read;
Query OK, 0 rows affected (0.00 sec)

mysql> /**s2**/lock table t write; # BLOCKED

如下示例，当会话s1对表t添加表级写锁后，另一个会话s2对表t添加表级写锁会被阻塞，这说明表级写锁和表级写锁是不兼容的。

mysql> /**s1**/lock table t write;
Query OK, 0 rows affected (0.00 sec)

mysql> /**s2**/lock table t write; # BLOCKED

3.2. 意向锁有什么作用？

意向锁^[2]（Intention Locks）是由InnoDB存储引擎层实现的锁，用来提高表级读写锁和行级锁互斥判断的速度。

当对记录添加行级读锁（S锁）时，会自动为该表添加意向读锁（Intention Share Lock，IS锁）。
当对记录添加行级写锁（X锁）时，会自动为该表添加意向写锁（Intention Exclusive Lock，IX锁）。
当事务提交或回滚时，会自动释放当前事务持有的意向锁。

如下示例，当事务t1对表t中id为1的记录添加行级读锁时（会自动为表t添加意向读锁），另一个事务t2对表t添加表级读锁成功，这说明意向读锁和表级读锁是兼容的。

mysql> /**t1**/begin;
Query OK, 0 rows affected (0.00 sec)

mysql> /**t1**/select * from t where id = 1 lock in share mode;
+------+-------+
| id   | value |
+------+-------+
|    1 |     1 |
+------+-------+
1 row in set (0.01 sec)

mysql> /**t1**/select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 't';
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|       422088515302184 | t           | NULL       | TABLE     | IS            | GRANTED     | NULL      |
|       422088515302184 | t           | PRIMARY    | RECORD    | S,REC_NOT_GAP | GRANTED     | 1         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

mysql> /**t2**/lock table t read;
Query OK, 0 rows affected (0.00 sec)

如下示例，当事务t1对表t中id为1的记录添加行级读锁时（会自动为表t添加意向读锁），另一个事务t2对表t添加表级写锁会被阻塞，这说明意向读锁和表级写锁是不兼容的。

mysql> /**t1**/begin;
Query OK, 0 rows affected (0.00 sec)

mysql> /**t1**/select * from t where id = 1 lock in share mode;
+------+-------+
| id   | value |
+------+-------+
|    1 |     1 |
+------+-------+
1 row in set (0.01 sec)

mysql> /**t2**/lock table t write; # BLOCKED

如下示例，当事务t1对表t中id为1的记录添加行级写锁时（会自动为表t添加意向写锁），另一个事务t2对表t添加表级读锁会被阻塞，这说明意向写锁和表级读锁是不兼容的。

mysql> /**t1**/begin;
Query OK, 0 rows affected (0.00 sec)

mysql> /**t1**/select * from t where id = 1 for update;
+------+-------+
| id   | value |
+------+-------+
|    1 |     1 |
+------+-------+
1 row in set (0.00 sec)

mysql> /**t1**/select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 't';
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                141707 | t           | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                141707 | t           | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

mysql> /**t2**/lock table t read; # BLOCKED

如下示例，当事务t1对表t中id为1的记录添加行级写锁时（会自动为表t添加意向写锁），另一个事务t2对表t添加表级写锁会被阻塞，这说明意向写锁和表级写锁是不兼容的。

mysql> /**t1**/begin;
Query OK, 0 rows affected (0.00 sec)

mysql> /**t1**/select * from t where id = 1 for update;
+------+-------+
| id   | value |
+------+-------+
|    1 |     1 |
+------+-------+
1 row in set (0.00 sec)

mysql> /**t2**/lock table t write; # BLOCKED

表级读写锁和意向锁的兼容性如下表：

	表级S锁	表级X锁	IS锁	IX锁
表级S锁	兼容	不兼容	兼容	不兼容
表级X锁	不兼容	不兼容	不兼容	不兼容
IS锁	兼容	不兼容	兼容	兼容
IX锁	不兼容	不兼容	兼容	兼容

3.3. 元数据锁有什么作用？

元数据锁^[3]（Metadata Locking，MDL）是由MySQL服务层实现的锁，用来保证元数据的线程安全。

当对表中的数据进行增删改查时（也就是执行DML时），会自动为表添加元数据读锁。
当对表结构进行修改时（也就是执行DDL时），会自动为表添加元数据写锁。
当DDL执行完毕时，会自动释放元数据写锁。
当事务提交或回滚时，会自动释放当前事务持有的元数据锁。

如下示例，当事务t1查询表t中数据时（会自动为表t添加元数据读锁），另一个事务t2修改表t的结构会被阻塞，这说明元数据读锁和元数据写锁是不兼容的。

mysql> /**t1**/begin;
Query OK, 0 rows affected (0.00 sec)

mysql> /**t1**/select count(*) from t\G
*************************** 1. row ***************************
count(*): 8
1 row in set (0.01 sec)

mysql> /**t1**/select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140611663024208
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6139
      OWNER_THREAD_ID: 3158975
       OWNER_EVENT_ID: 107
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140611663272320
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6139
      OWNER_THREAD_ID: 3158975
       OWNER_EVENT_ID: 106
2 rows in set (0.00 sec)

mysql> /**t2**/alter table t add column n char(1); #BLOCKED

元数据锁的兼容性如下表：

	元数据S锁	元数据X锁
元数据S锁	兼容	不兼容
元数据X锁	不兼容	不兼容

3.4. 自增锁有什么作用？

自增锁^[4]（AUTO-INC Locks）是由InnoDB存储引擎层实现的锁，用来保证自增列的连续性。

当插入含有自增列的记录时，会自动为表添加自增锁。
当插入语句执行完毕时，会自动释放自增锁。

4. 行级锁

4.1. 行级锁相比于表级锁有什么优点？

与表级锁相比，行级锁的锁粒度更小，锁竞争的概率更低，性能更好。与MyISAM等存储引擎相比，InnoDB存储引擎支持多粒度的锁，不仅支持表级锁，还支持行级锁。

4.2. 记录锁有什么作用？

记录锁^[5]（Record Locks）是添加在索引记录上的锁，用来锁住当前记录。

当更新、删除记录时，会自动为记录添加写锁（X锁）。
可以通过<sql> for update命令，手动为记录添加写锁（X锁）。
可以通过<sql> lock in share mode命令，手动为记录添加读锁（S锁）。
当事务提交或回滚时，会自动释放当前事务持有的记录锁。

4.3. 间隙锁有什么作用？

间隙锁^[6]（Gap Locks）是添加在索引记录之间的间隙上的锁，用来避免幻读。当事务的隔离级别是不可重复读（RR）才会使用间隙锁。

4.4. 临键锁是什么？

临键锁^[7]（Next-Key Locks）由间隙锁和临键锁组成，锁定区间左开右闭。当事务的隔离级别是不可重复读（RR）才会使用临键锁。为了进一步提高性能，减小锁的粒度，临键锁在以下几种情况会进行降级：

当使用唯一索引进行等值查询并且记录存在时，临键锁会降级为记录锁。
当使用唯一索引进行等值查询并且记录不存在时，临键锁会降级为间隙锁。

4.5. 插入意向锁有什么作用？

插入意向锁^[8]（Insert Intention Locks）用来提高插入的性能。插入意向锁和间隙锁不兼容，插入意向锁和插入意向锁兼容。

5. 语句加锁分析

5.1. 加锁分析整体思路

语句加锁的情况与多种因素有关，如MySQL的版本、事务的隔离级别、索引的创建和选择、表中存在的数据、以及查询条件等。对语句加锁分析的过程中，我们重点关注一下几个问题：

对哪些记录加锁？
对记录加什么类型的锁？
什么时候释放记录上的锁？

下面会以表cus为例，对具体语句示例的加锁过程进行分析。表cus有三个索引：

id：为id列添加主键索引。
idx_name：为name列添加普通二级索引。
uk_id_no：为id_no列添加唯一二级索引。

DROP TABLE IF EXISTS `cus`;
CREATE TABLE `cus`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `id_no` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `gender` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_id_no`(`id_no` ASC) USING BTREE,
  INDEX `idx_name`(`name` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `cus` VALUES (1, 'tom', '#001', 1);
INSERT INTO `cus` VALUES (3, 'jerry', '#009', 1);
INSERT INTO `cus` VALUES (7, 'foo', '#006', 0);
INSERT INTO `cus` VALUES (8, 'bar', '#015', 0);
INSERT INTO `cus` VALUES (15, 'john', '#010', 1);
INSERT INTO `cus` VALUES (19, 'tom', '#007', 1);

mysql> select * from cus;
+----+-------+-------+--------+
| id | name  | id_no | gender |
+----+-------+-------+--------+
|  1 | tom   | #001  |      1 |
|  3 | jerry | #009  |      1 |
|  7 | foo   | #006  |      0 |
|  8 | bar   | #015  |      0 |
| 15 | john  | #010  |      1 |
| 19 | tom   | #007  |      1 |
+----+-------+-------+--------+
6 rows in set (0.01 sec)

5.2. 通过主键索引查询

RR级别：
1. 首先在主键索引树上找到第一条满足搜索条件的记录，并为其添加临键锁（一般情况下）；
2. 重复上述步骤直到找到第一条不满足搜索条件的记录。
RU/RC级别：
1. 首先在主键索引树上找到第一条满足搜索条件的记录，并为其添加记录锁；
2. 然后判断查询条件是否满足，如果不满足则释放锁；
3. 重复上述步骤直到找到第一条不满足搜索条件的记录。

通过主键索引等值查询存在的记录

RR级别：

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from cus where id = 3 for update;
+----+-------+-------+--------+
| id | name  | id_no | gender |
+----+-------+-------+--------+
|  3 | jerry | #009  |      1 |
+----+-------+-------+--------+
1 row in set (0.00 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                141562 | cus         | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                141562 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 3         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

RC级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where id = 3 for update;
+----+-------+-------+--------+
| id | name  | id_no | gender |
+----+-------+-------+--------+
|  3 | jerry | #009  |      1 |
+----+-------+-------+--------+
1 row in set (0.01 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                141564 | cus         | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                141564 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 3         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

通过主键索引等值查询不存在的记录

RR级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where id = 4 lock in share mode;
Empty set (0.00 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
|       422088515302992 | cus         | NULL       | TABLE     | IS        | GRANTED     | NULL      |
|       422088515302992 | cus         | PRIMARY    | RECORD    | S,GAP     | GRANTED     | 7         |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

RC级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where id = 4 lock in share mode;
Empty set (0.00 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
|       422088515303800 | cus         | NULL       | TABLE     | IS        | GRANTED     | NULL      |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

通过主键索引范围查询存在的记录

RR级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where id >= 7 and id < 15 for update;
+----+------+-------+--------+
| id | name | id_no | gender |
+----+------+-------+--------+
|  7 | foo  | #006  |      0 |
|  8 | bar  | #015  |      0 |
+----+------+-------+--------+
2 rows in set (0.00 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                141565 | cus         | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                141565 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 7         |
|                141565 | cus         | PRIMARY    | RECORD    | X             | GRANTED     | 8         |
|                141565 | cus         | PRIMARY    | RECORD    | X,GAP         | GRANTED     | 15        |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)

RC级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where id >= 7 and id < 15 for update;
+----+------+-------+--------+
| id | name | id_no | gender |
+----+------+-------+--------+
|  7 | foo  | #006  |      0 |
|  8 | bar  | #015  |      0 |
+----+------+-------+--------+
2 rows in set (0.00 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                141566 | cus         | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                141566 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 8         |
|                141566 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 7         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)

通过主键索引范围查询不存在的记录

RR级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where id > 3 and id <= 4 for update;
Empty set (0.00 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
|                141568 | cus         | NULL       | TABLE     | IX        | GRANTED     | NULL      |
|                141568 | cus         | PRIMARY    | RECORD    | X,GAP     | GRANTED     | 7         |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

RC级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where id > 3 and id <= 4 for update;
Empty set (0.00 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
|                141567 | cus         | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

5.3. 通过二级索引查询或更新

RR级别：
1. 首先在二级索引树上找到第一条满足搜索条件的记录，并为其添加临键锁（一般情况下）
2. 然后回表找到主键索引树上的记录并为其添加记录锁；
3. 重复上述步骤直到找到第一条不满足搜索条件的记录。如果适用索引条件下推且并不满足查询条件，则无需回表。
RU/RC级别：
1. 首先在二级索引树上找到第一条满足搜索条件的记录，并为其添加记录锁；
2. 然后回表在主键索引树上找到记录，并为其添加记录锁；判断查询条件是否满足，如果不满足则释放二级索引树上和主键索引树上的锁；
3. 重复上述步骤直到找到第一条不满足搜索条件的记录。如果适用索引条件下推且并不满足查询条件，则无需回表。

通过普通二级索引等值查询存在的记录

RR级别：

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from cus where name = 'tom' for update;
+----+------+-------+--------+
| id | name | id_no | gender |
+----+------+-------+--------+
|  1 | tom  | #001  |      1 |
| 19 | tom  | #007  |      1 |
+----+------+-------+--------+
2 rows in set (0.01 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+---------------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |
+-----------------------+-------------+------------+-----------+---------------+-------------+------------------------+
|                141686 | cus         | NULL       | TABLE     | IX            | GRANTED     | NULL                   |
|                141686 | cus         | idx_name   | RECORD    | X             | GRANTED     | supremum pseudo-record |
|                141686 | cus         | idx_name   | RECORD    | X             | GRANTED     | 'tom', 1               |
|                141686 | cus         | idx_name   | RECORD    | X             | GRANTED     | 'tom', 19              |
|                141686 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1                      |
|                141686 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 19                     |
+-----------------------+-------------+------------+-----------+---------------+-------------+------------------------+
6 rows in set (0.01 sec)

RC级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where name = 'tom' for update;
+----+------+-------+--------+
| id | name | id_no | gender |
+----+------+-------+--------+
|  1 | tom  | #001  |      1 |
| 19 | tom  | #007  |      1 |
+----+------+-------+--------+
2 rows in set (7.24 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                141687 | cus         | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                141687 | cus         | idx_name   | RECORD    | X,REC_NOT_GAP | GRANTED     | 'tom', 1  |
|                141687 | cus         | idx_name   | RECORD    | X,REC_NOT_GAP | GRANTED     | 'tom', 19 |
|                141687 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
|                141687 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 19        |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
5 rows in set (0.01 sec)

通过普通二级索引等值查询不存在的记录

RR级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where name = 'mic' for update;
Empty set (0.00 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
|                141689 | cus         | NULL       | TABLE     | IX        | GRANTED     | NULL      |
|                141689 | cus         | idx_name   | RECORD    | X,GAP     | GRANTED     | 'tom', 1  |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

RC级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where name = 'mic' for update;
Empty set (0.00 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
|                141692 | cus         | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
1 row in set (0.00 sec)

通过普通二级索引范围查询存在的记录

RR级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where name >= 'bar' and name < 'mic' and gender = 0 for update;
+----+------+-------+--------+
| id | name | id_no | gender |
+----+------+-------+--------+
|  8 | bar  | #015  |      0 |
|  7 | foo  | #006  |      0 |
+----+------+-------+--------+
2 rows in set (0.00 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+---------------+-------------+------------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA  |
+-----------------------+-------------+------------+-----------+---------------+-------------+------------+
|                141698 | cus         | NULL       | TABLE     | IX            | GRANTED     | NULL       |
|                141698 | cus         | idx_name   | RECORD    | X             | GRANTED     | 'tom', 1   |
|                141698 | cus         | idx_name   | RECORD    | X             | GRANTED     | 'bar', 8   |
|                141698 | cus         | idx_name   | RECORD    | X             | GRANTED     | 'foo', 7   |
|                141698 | cus         | idx_name   | RECORD    | X             | GRANTED     | 'john', 15 |
|                141698 | cus         | idx_name   | RECORD    | X             | GRANTED     | 'jerry', 3 |
|                141698 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 8          |
|                141698 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 7          |
|                141698 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 15         |
|                141698 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 3          |
+-----------------------+-------------+------------+-----------+---------------+-------------+------------+
10 rows in set (0.01 sec)

RC级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where name >= 'bar' and name < 'mic' and gender = 0 for update;
+----+------+-------+--------+
| id | name | id_no | gender |
+----+------+-------+--------+
|  8 | bar  | #015  |      0 |
|  7 | foo  | #006  |      0 |
+----+------+-------+--------+
2 rows in set (4.86 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                141699 | cus         | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                141699 | cus         | idx_name   | RECORD    | X,REC_NOT_GAP | GRANTED     | 'bar', 8  |
|                141699 | cus         | idx_name   | RECORD    | X,REC_NOT_GAP | GRANTED     | 'foo', 7  |
|                141699 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 8         |
|                141699 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 7         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)

通过普通二级索引范围查询不存在的记录

RR级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where name > 'zoo' for update;
Empty set (0.00 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+-----------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+-----------------------+-------------+------------+-----------+-----------+-------------+------------------------+
|                141695 | cus         | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
|                141695 | cus         | idx_name   | RECORD    | X         | GRANTED     | supremum pseudo-record |
+-----------------------+-------------+------------+-----------+-----------+-------------+------------------------+
2 rows in set (0.00 sec)

RC级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cus where name > 'zoo' for update;
Empty set (0.01 sec)

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
|                141697 | cus         | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+-----------------------+-------------+------------+-----------+-----------+-------------+-----------+
1 row in set (0.00 sec)

5.4. 通过主键索引更新

RR级别：和通过主键索引查询类似，不过在为主键索引树上的记录加锁时，会同步为二级索引树上的记录添加记录锁。
RU/RC级别：和通过主键索引查询类似，不过在为主键索引树上的记录加锁时，会同步为二级索引树上的记录添加记录锁，释放主键索引树上的锁时，会同步释放二级索引树上的锁。

RR级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update cus set name = 'foo1' where id = 7;
Query OK, 1 row affected (5.48 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                141700 | cus         | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                141700 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 7         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

RC级别：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update cus set name = 'foo1' where id = 7;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks where OBJECT_NAME = 'cus';
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                141706 | cus         | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                141706 | cus         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 7         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.01 sec)

6. 死锁

6.1. 什么是死锁？

死锁^[9]（Deadlock）是指两个或多个事务互相持有对方等待的锁，导致所有事务都处于被阻塞的状态。如下示例

事务t1获取到id为1的记录的写锁；
事务t2获取到id为2的记录的写锁；
事务t1获取id为2的记录的写锁被阻塞；
事务t2获取id为1的记录的写锁被阻塞，事务t2被回滚。

mysql> /**t1**/begin;
Query OK, 0 rows affected (0.00 sec)

mysql> /**t1**/update user set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> /**t2**/begin;
Query OK, 0 rows affected (0.00 sec)

mysql> /**t2**/update user set balance = balance - 20 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> /**t1**/update user set balance = balance + 10 where id = 2; # BLOCKED

mysql> /**t2**/update user set balance = balance + 20 where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

6.2. 如何进行死锁的排查？

通过show engine innodb status命令可以查看死锁信息。如下示例：


mysql> show engine innodb status\G
...
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-02-16 15:39:04 140612928214784
*** (1) TRANSACTION:
TRANSACTION 141716, ACTIVE 23 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 5095085, OS thread handle 140612341798656, query id 3899944 localhost root updating
update user set balance = balance + 10 where id = 2

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 8 n bits 152 index PRIMARY of table `test`.`user` trx id 141716 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000022994; asc     ) ;;
 2: len 7; hex 020000018e04f1; asc        ;;
 3: len 6; hex e590b4e998b3; asc       ;;
 4: len 18; hex 353432343239313939373035313335353331; asc 542429199705135531;;
 5: len 11; hex 3135393333363234383534; asc 15933624854;;
 6: len 9; hex e58c85e8a385e5b7a5; asc          ;;
 7: len 12; hex e5ad9fe58aa0e68b89e59bbd; asc             ;;
 8: len 9; hex e99995e8a5bfe79c81; asc          ;;
 9: len 30; hex e8b4b5e5b79ee79c81e5ae9ce983bde5b882e8939fe5b79ee69da8e8a197; asc                               ; (total 41 bytes);
 10: len 22; hex 7869756c616e6368656e406578616d706c652e6e6574; asc xiulanchen@example.net;;
 11: len 8; hex 80000000000017b3; asc         ;;
 12: len 5; hex 9959aa275f; asc  Y '_;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 8 n bits 152 index PRIMARY of table `test`.`user` trx id 141716 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 000000022996; asc     ) ;;
 2: len 7; hex 01000002020614; asc        ;;
 3: len 6; hex e69d8ee790b3; asc       ;;
 4: len 18; hex 343331323032313933373035323832353239; asc 431202193705282529;;
 5: len 11; hex 3135363736333432363332; asc 15676342632;;
 6: len 21; hex e5bbbae7ad91e69cbae794b5e5b7a5e7a88be5b888; asc                      ;;
 7: len 18; hex e4b98ce585b9e588abe5858be696afe59da6; asc                   ;;
 8: len 9; hex e99995e8a5bfe79c81; asc          ;;
 9: len 30; hex e58fb0e6b9bee79c81e6b481e5b882e585b4e5b1b1e6ada6e6b189e8b7af; asc                               ; (total 41 bytes);
 10: len 17; hex 706d656e67406578616d706c652e6f7267; asc pmeng@example.org;;
 11: len 8; hex 8000000000000bdb; asc         ;;
 12: len 5; hex 9934ef4a33; asc  4 J3;;


*** (2) TRANSACTION:
TRANSACTION 141718, ACTIVE 14 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 5095092, OS thread handle 140612339685120, query id 3899959 localhost root updating
update user set balance = balance + 20 where id = 1

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 8 n bits 152 index PRIMARY of table `test`.`user` trx id 141718 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 000000022996; asc     ) ;;
 2: len 7; hex 01000002020614; asc        ;;
 3: len 6; hex e69d8ee790b3; asc       ;;
 4: len 18; hex 343331323032313933373035323832353239; asc 431202193705282529;;
 5: len 11; hex 3135363736333432363332; asc 15676342632;;
 6: len 21; hex e5bbbae7ad91e69cbae794b5e5b7a5e7a88be5b888; asc                      ;;
 7: len 18; hex e4b98ce585b9e588abe5858be696afe59da6; asc                   ;;
 8: len 9; hex e99995e8a5bfe79c81; asc          ;;
 9: len 30; hex e58fb0e6b9bee79c81e6b481e5b882e585b4e5b1b1e6ada6e6b189e8b7af; asc                               ; (total 41 bytes);
 10: len 17; hex 706d656e67406578616d706c652e6f7267; asc pmeng@example.org;;
 11: len 8; hex 8000000000000bdb; asc         ;;
 12: len 5; hex 9934ef4a33; asc  4 J3;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 8 n bits 152 index PRIMARY of table `test`.`user` trx id 141718 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000022994; asc     ) ;;
 2: len 7; hex 020000018e04f1; asc        ;;
 3: len 6; hex e590b4e998b3; asc       ;;
 4: len 18; hex 353432343239313939373035313335353331; asc 542429199705135531;;
 5: len 11; hex 3135393333363234383534; asc 15933624854;;
 6: len 9; hex e58c85e8a385e5b7a5; asc          ;;
 7: len 12; hex e5ad9fe58aa0e68b89e59bbd; asc             ;;
 8: len 9; hex e99995e8a5bfe79c81; asc          ;;
 9: len 30; hex e8b4b5e5b79ee79c81e5ae9ce983bde5b882e8939fe5b79ee69da8e8a197; asc                               ; (total 41 bytes);
 10: len 22; hex 7869756c616e6368656e406578616d706c652e6e6574; asc xiulanchen@example.net;;
 11: len 8; hex 80000000000017b3; asc         ;;
 12: len 5; hex 9959aa275f; asc  Y '_;;

*** WE ROLL BACK TRANSACTION (2)
...

6.3. 如何避免死锁？

从SQL语句层面，通过调整事务中SQL语句的顺序来确保不会发生死锁。
从MySQL数据库层面，开启死锁检测，发现死锁时自动回滚事务。

7. 参考文档

上一篇：InnoDB存储引擎的索引是如何实现的？下一篇：MySQL数据库如何调优？

xuMengqi's Blog

MySQL数据库中有哪些锁？

1. 锁

1.1. 数据库中的锁有什么作用？

1.2. 数据库中的锁有哪些分类？

1.3. 什么是锁的兼容性？

1.4. 什么是两阶段锁？

2. 全局锁

2.1. 全局锁有什么作用？

2.2. 如何进行全库备份？

3. 表级锁

3.1. 表级读写锁有什么作用？

3.2. 意向锁有什么作用？

3.3. 元数据锁有什么作用？

3.4. 自增锁有什么作用？

4. 行级锁

4.1. 行级锁相比于表级锁有什么优点？

4.2. 记录锁有什么作用？

4.3. 间隙锁有什么作用？

4.4. 临键锁是什么？

4.5. 插入意向锁有什么作用？

5. 语句加锁分析

5.1. 加锁分析整体思路

5.2. 通过主键索引查询

5.3. 通过二级索引查询或更新

5.4. 通过主键索引更新

6. 死锁

6.1. 什么是死锁？

6.2. 如何进行死锁的排查？

6.3. 如何避免死锁？

7. 参考文档

目录