data_lock表分析

在 MySQL 中，performance_schema.data_locks 表用于显示与当前持有或等待的锁相关的信息。这些信息对于诊断锁争用和性能问题非常有用。以下是 performance_schema.data_locks 表中的各字段及其含义：

1. ENGINE：锁所属的存储引擎的名称，例如 InnoDB。
2. ENGINE_LOCK_ID：存储引擎生成的锁的唯一标识符。
3. THREAD_ID：持有或等待锁的线程的标识符。
4. EVENT_ID：与锁相关的事件的标识符。
5. OBJECT_SCHEMA：锁定对象所在的数据库名称。
6. OBJECT_NAME：锁定对象的名称，如表名。
7. PARTITION_NAME：锁定对象的分区名称，如果适用。
8. SUBPARTITION_NAME：锁定对象的子分区名称，如果适用。
9. INDEX_NAME：锁定对象的索引名称，如果适用。
10. OBJECT_INSTANCE_BEGIN：锁定对象实例的内存地址。
11. LOCK_TYPE：锁的类型，例如 RECORD（记录锁），TABLE（表锁）。
12. LOCK_MODE：锁的模式，例如 S（共享锁），X（排他锁）。
13. LOCK_STATUS：锁的状态，例如 GRANTED（已授予），PENDING（等待中）。
14. LOCK_DATA：锁定的数据的详细信息，对于记录锁来说，通常是被锁定的记录的主键值。

以下是一个简单的查询示例，用于查看 performance_schema.data_locks 表中的数据：

SELECT * FROM performance_schema.data_locks;

此查询将返回当前数据库中所有锁的信息。通过分析这些信息，可以帮助诊断和解决锁争用问题，提高数据库性能。

下面让我们来实战一下：

比如有一张表如下：

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

其中数据如下：

mysql> select * from user;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | 路飞      |  19 |
|  5 | 索隆      |  21 |
| 10 | 山治      |  22 |
| 15 | 乌索斯    |  20 |
| 20 | 香克斯    |  39 |
+----+-----------+-----+
5 rows in set (0.00 sec)

我们开启一个事务：

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

mysql> select * from user where age = 25 for update;
Empty set (0.00 sec)

mysql>

然后查询data_lock表：

mysql> select * from performance_schema.data_locks \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4853501848:1065:5015390424
ENGINE_TRANSACTION_ID: 5382051
            THREAD_ID: 88
             EVENT_ID: 24
        OBJECT_SCHEMA: test
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5015390424
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4853501848:4:5:6:5293450776
ENGINE_TRANSACTION_ID: 5382051
            THREAD_ID: 88
             EVENT_ID: 24
        OBJECT_SCHEMA: test
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: index_age
OBJECT_INSTANCE_BEGIN: 5293450776
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 39, 20
2 rows in set (0.00 sec)

mysql>

可以看到，出现了两个锁。一个是表锁，一个是行锁。

先看第一个锁，锁类型是表锁，锁模式是IX，代表的是意向排它锁。意向锁是表级别的锁，用于指示当前事务即将或已经在表的某些行上加了更细粒度的行级锁。意向锁的主要目的是提高锁的效率，避免全表扫描来检查是否存在行级锁。

再看第二个锁，锁类型是行锁，INDEX_NAME为index_age表示该锁锁住的是index_age这个索引树，LOCK_MODE为X,GAP表示是一个间隙排它锁，再看看LOCK_DATA为39, 20，表示该间隙锁锁住的范围为(22, 39)。

注意不要被39, 20中的20所迷惑了，这个20代表的是id值，二级索引存储的都是索引列+主键！