zzys

本章介绍有关于MySQL锁的相关知识。

MySQL中，锁分为以下三种：

  • 全局锁
  • 表级锁
  • 行级锁（Innodb）

全局锁

image-20230908203459159

image-20230908203522385

表级锁

每次操作锁住整张表，粒度很大，并发低。分为以下三类：

  • 表锁
  • 元数据锁
  • 意向锁

表锁

元数据锁

image-20230908204149913

意向锁

防止在加入表锁时，需要全表扫描是否存在行锁，所以在加行锁时，会默认同时加上意向锁。

  • 意向共享锁（IS）：由select * from table lock in shared mode添加。
  • 意向排他锁（IX）：由update,delete,insert,select...fro update添加。
意向锁\表锁readwrite意向锁
IS共享互斥共享
IX互斥互斥共享

image-20230908204839835

行级锁

image-20230908204939478

Innodb默认运行在RR的隔离级别下，该级别下使用临建锁来防止幻读。

行锁

image-20230908205041267

image-20230908205126127

RR下的运行说明

在RR级别下，InnoDB默认使用next-key进行搜索和扫描，也就是说，在加行级锁时，默认会对记录和前面的间隙都加锁，以防止幻读的发生。

但是会有以下的特殊情况，假设有这么一张表：

CREATE TABLE `test` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_test_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 数据如下：

mysql> select * from test;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |    1 |
|  2 | b    |    2 |
|  3 | g    |    7 |
|  4 | s    |    5 |
|  6 | t    |   10 |
+----+------+------+
5 rows in set (0.00 sec)

InnoDB是对索引进行加锁，所以如果没有按照索引进行查询数据，就会升级为表锁，对所有记录加锁。

supremum pseudo-record 用来指代最后一条记录后面的间隙。

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

mysql> select * from test where name = 'a' for update;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |    1 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data              |
+---------------+-------------+------------+-----------+-----------+------------------------+
| test          | test        | NULL       | TABLE     | IX        | NULL                   |
| test          | test        | PRIMARY    | RECORD    | X         | supremum pseudo-record |
| test          | test        | PRIMARY    | RECORD    | X         | 1                      |
| test          | test        | PRIMARY    | RECORD    | X         | 2                      |
| test          | test        | PRIMARY    | RECORD    | X         | 4                      |
| test          | test        | PRIMARY    | RECORD    | X         | 3                      |
| test          | test        | PRIMARY    | RECORD    | X         | 6                      |
+---------------+-------------+------------+-----------+-----------+------------------------+
7 rows in set (0.00 sec)

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

对唯一索引的等值匹配，对于已存在的的记录，默认优化为行锁。

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

mysql> select * from test where id = 1 for update;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |    1 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| test          | test        | NULL       | TABLE     | IX            | NULL      |
| test          | test        | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

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

对唯一索引的等值匹配，对于不存在的记录，优化为间隙锁。

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

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

mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| test          | test        | NULL       | TABLE     | IS        | NULL      |
| test          | test        | PRIMARY    | RECORD    | S,GAP     | 6         |
+---------------+-------------+------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)	

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

对于非唯一索引的等值查询，采用临建锁和间隙锁共同加锁

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

--  测试单条数据

mysql> select * from test where age = 5 for update;
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | s    |    5 |
+----+------+------+
1 row in set (0.01 sec)

mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+--------------+-----------+---------------+-----------+
| object_schema | object_name | index_name   | lock_type | lock_mode     | lock_data |
+---------------+-------------+--------------+-----------+---------------+-----------+
| test          | test        | NULL         | TABLE     | IX            | NULL      |
| test          | test        | idx_test_age | RECORD    | X             | 5, 4      |
| test          | test        | PRIMARY      | RECORD    | X,REC_NOT_GAP | 4         |
| test          | test        | idx_test_age | RECORD    | X,GAP         | 7, 3      |
+---------------+-------------+--------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)

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

mysql> insert into test values(7,'n',5);
Query OK, 1 row affected (0.02 sec)

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

-- 测试多条数据

mysql> select * from test where age = 5 for update;
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | s    |    5 |
|  7 | n    |    5 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+--------------+-----------+---------------+-----------+
| object_schema | object_name | index_name   | lock_type | lock_mode     | lock_data |
+---------------+-------------+--------------+-----------+---------------+-----------+
| test          | test        | NULL         | TABLE     | IX            | NULL      |
| test          | test        | idx_test_age | RECORD    | X             | 5, 4      |
| test          | test        | idx_test_age | RECORD    | X             | 5, 7      |
| test          | test        | PRIMARY      | RECORD    | X,REC_NOT_GAP | 4         |
| test          | test        | PRIMARY      | RECORD    | X,REC_NOT_GAP | 7         |
| test          | test        | idx_test_age | RECORD    | X,GAP         | 7, 3      |
+---------------+-------------+--------------+-----------+---------------+-----------+
6 rows in set (0.00 sec)

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

唯一索引的范围查询，采用临建锁和间隙锁共同加锁。

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

mysql> select * from test where id > 2 and id < 5;
+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | g    |    7 |
|  4 | s    |    5 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> select * from test where id > 2 and id < 5 for update;
+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | g    |    7 |
|  4 | s    |    5 |
+----+------+------+
2 rows in set (0.01 sec)

mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| test          | test        | NULL       | TABLE     | IX        | NULL      |
| test          | test        | PRIMARY    | RECORD    | X         | 4         |
| test          | test        | PRIMARY    | RECORD    | X         | 3         |
| test          | test        | PRIMARY    | RECORD    | X,GAP     | 6         |
+---------------+-------------+------------+-----------+-----------+-----------+
4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
上次更新:
Contributors: YangZhang, zzys