MySQL数据库中有哪些锁?
本文的测试环境为MySQL8.0.34。
1. 锁
1.1. 数据库中的锁有什么作用?
锁是一种并发控制手段,避免多个事务同时对同一条记录进行修改,用来解决线程安全问题。
1.2. 数据库中的锁有哪些分类?
按照锁的功能来划分:
按照锁的粒度来划分:
- 全局锁(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级别:
- 首先在主键索引树上找到第一条满足搜索条件的记录,并为其添加临键锁(一般情况下);
- 重复上述步骤直到找到第一条不满足搜索条件的记录。
- RU/RC级别:
- 首先在主键索引树上找到第一条满足搜索条件的记录,并为其添加记录锁;
- 然后判断查询条件是否满足,如果不满足则释放锁;
- 重复上述步骤直到找到第一条不满足搜索条件的记录。
通过主键索引等值查询存在的记录
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级别:
首先在二级索引树上找到第一条满足搜索条件的记录,并为其添加临键锁(一般情况下)
然后回表找到主键索引树上的记录并为其添加记录锁;
重复上述步骤直到找到第一条不满足搜索条件的记录。如果适用索引条件下推且并不满足查询条件,则无需回表。
RU/RC级别:
- 首先在二级索引树上找到第一条满足搜索条件的记录,并为其添加记录锁;
- 然后回表在主键索引树上找到记录,并为其添加记录锁;判断查询条件是否满足,如果不满足则释放二级索引树上和主键索引树上的锁;
- 重复上述步骤直到找到第一条不满足搜索条件的记录。如果适用索引条件下推且并不满足查询条件,则无需回表。
通过普通二级索引等值查询存在的记录
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. 参考文档
- 1.MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking - Shared & Exclusive Locks ↩
- 2.MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking - Intention Locks ↩
- 3.MySQL :: MySQL 8.0 Reference Manual :: 8.11.4 Metadata Locking ↩
- 4.MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking - AUTO-INC Locks ↩
- 5.MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking - Record Locks ↩
- 6.MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking - Gap Locks ↩
- 7.MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking - Next-Key Locks ↩
- 8.MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking - Insert Intention Locks ↩
- 9.MySQL :: MySQL 8.0 Reference Manual :: 15.7.5 Deadlocks in InnoDB ↩