按照粒度,MySQL 中的锁分为:
-
全局锁,锁定数据库中所有的表。
-
表级锁,锁定整张表。
-
行级锁,锁定一行数据。
对数据库使用全局锁,会导致整个数据库不能写入数据,只能读取数据。
典型的使用场景是在执行数据库备份时使用全局锁,以确保数据的完整性和一致性。
语法:
-- 添加全局锁
flush tables with read lock ;
-- 释放全局锁
unlock tables ;
示例,使用全局锁备份数据库:
mysql> use itcast;
Database changed
mysql> flush tables with read lock ;
Query OK, 0 rows affected (0.53 sec)
此时如果通过其它客户端对数据库执行写操作:
mysql> delete from student where id=11;
执行会被阻塞,因为有全局锁。
使用 mysqldump 工具进行备份:
mysqldump -u root -p itcast > ~/download/itcast.sql
完成备份后释放全局锁:
mysql> unlock tables;
虽然通过这种方式可以完成备份,但存在一些问题:
-
如果通过主库备份,在备份期间业务不可用。
-
如果通过从库备份,备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟。
对于 innoDB 引擎,可以在备份时使用参数--single-transaction
实现不加全局锁的一致性数据备份:
mysqldump --single-transaction -u root -p itcast > ~/download/itcast2.sql
表级锁
主要分为三类:
-
表锁
-
元数据锁(meta data lock,MDL)
-
意向锁
表锁
分为两类:
-
表共享读锁(read lock)
-
表独占写锁(write lock)
语法:
-- 添加锁
lock tables 表名... read/write
-- 释放锁
unlock tables/客户端断开
如果使用读锁,所有会话只能读取表数据,但不能写入。
示例,通过客户端1为表添加读锁:
mysql> lock tables student read;
Query OK, 0 rows affected (0.01 sec)
此时客户端 1 可以正常读取数据:
mysql> select * from student limit 5;
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | 黛绮丝 | 2000100101 |
| 2 | 谢逊 | 2000100102 |
| 3 | 殷天正 | 2000100103 |
| 4 | 韦一笑 | 2000100104 |
| 12 | Tom | NULL |
+----+-----------+------------+
客户端 2 也可以正常读取:
mysql> select * from student limit 5;
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | 黛绮丝 | 2000100101 |
| 2 | 谢逊 | 2000100102 |
| 3 | 殷天正 | 2000100103 |
| 4 | 韦一笑 | 2000100104 |
| 12 | Tom | NULL |
+----+-----------+------------+
如果客户端 1 试图写入数据就会报错:
mysql> update student set name='Jack' where id=12;
ERROR 1100 (HY000): Table 'student_course' was not locked with LOCK TABLES
其它客户端如果试图写入数据(比如客户端 2)就会被阻塞,直到读锁被释放:
mysql> unlock tables;
如果使用写锁,当前会话可以读写数据,其它会话不能读写数据。
通过客户端 1 添加写锁并读取数据:
mysql> lock tables student write;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student limit 5;
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | 黛绮丝 | 2000100101 |
| 2 | 谢逊 | 2000100102 |
| 3 | 殷天正 | 2000100103 |
| 4 | 韦一笑 | 2000100104 |
| 12 | Jack | NULL |
+----+-----------+------------+
5 rows in set (0.00 sec)
如果试图通过其它客户端读取数据,SQL 执行会被阻塞:
mysql> select * from student limit 5; ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted
通过当前会话(客户端1)同样可以写入数据:
mysql> update student set name='Lucy' where id=12;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
但如果通过其它客户端写入,同样会被阻塞:
mysql> update student set name='Lili' where id=12;;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
元数据锁
元数据锁用于保护元数据(表结构)的一致性,在表上有活动事务时,不可以对元数据进行写入操作,以避免 DML 与 DDL 冲突,保证读写的正确性。
MySQL 从 5.5 开始引入元数据锁,对一张表进行增删改查时,加 MDL 读锁(共享),对表结构进行变更操作时,加 MDL 写锁(排它)。
元数据锁由系统自动控制,不需要显式使用。
对应 SQL | 锁类型 | 说明 |
---|---|---|
lock table xxx read/write | SHARED_READ_ONLY/SHARED_NO_READ_WRITE | |
select、select... lock in share mode | SHARED_READ | 与 SHARED_READ、SHARED_WRITE 兼容,与 EXCLUSIVE 互斥 |
insert、update、delete、select ... for update | SHARED_WRITE | 与 SHARED_READ、SHARED_WRITE 兼容,与 EXECLUSIVE 互斥 |
alter table ... | EXCLUSIVE | 与其它的 MDL 都互斥 |
可以通过 SQL 查询元数据锁:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;
示例,创建一个分数表:
create table score
(
id int unsigned auto_increment comment '主键'
primary key,
name varchar(10) not null,
math int unsigned not null comment '数学',
english int unsigned not null comment '英语'
)
comment '分数表';
添加数据:
INSERT INTO itcast.score (name, math, english) VALUES ('Tom', 80, 88);
INSERT INTO itcast.score (name, math, english) VALUES ('Jack', 87, 66);
在客户端 1中开启一个事务,并查询这张表:
mysql> begin;
mysql> select * from score;
+----+------+------+---------+
| id | name | math | english |
+----+------+------+---------+
| 1 | Tom | 80 | 88 |
| 2 | Jack | 87 | 66 |
+----+------+------+---------+
mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;
+-------------+--------------------+----------------+---------------------+---------------+
| object_type | object_schema | object_name | lock_type | lock_duration |
+-------------+--------------------+----------------+---------------------+---------------+
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION |
| SCHEMA | performance_schema | NULL | INTENTION_EXCLUSIVE | TRANSACTION |
| TABLE | itcast | score | SHARED_READ | TRANSACTION |
+-------------+--------------------+----------------+---------------------+---------------+
会发现添加了 itcast.score 表的元数据锁,类型是 SHARED_READ。
在客户端2中开启事务,并执行一个更新操作:
mysql> begin;
mysql> update score set name='LiLei' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;
+-------------+--------------------+----------------+---------------------+---------------+
| object_type | object_schema | object_name | lock_type | lock_duration |
+-------------+--------------------+----------------+---------------------+---------------+
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION |
| SCHEMA | performance_schema | NULL | INTENTION_EXCLUSIVE | TRANSACTION |
| TABLE | itcast | score | SHARED_READ | TRANSACTION |
| TABLE | itcast | score | SHARED_READ | TRANSACTION |
| TABLE | itcast | score | SHARED_WRITE | TRANSACTION |
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION |
+-------------+--------------------+----------------+---------------------+---------------+
可以看到此时添加了一个新的元数据锁,类型是 SHARED_WRITE。但因为这两种元数据锁不是互斥的,可以共存,因此它们不会影响彼此,都可以执行成功。
但如果同时在客户端2中执行的不是数据更新操作,而是表结构变更操作:
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> alter table score add column java int unsigned;
此时执行将被阻塞,因为它将产生一个 EXCLUSIVE 类型的元数据锁,这个锁和 SHARED_READ 类型的元数据锁是互斥的,所以会被阻塞,除非客户端 1 的事务提交并释放因为该事务产生的元数据锁。
意向锁
我们已经知道,当使用索引更新数据时,MySQL 会对受影响的数据行添加行锁,此时如果其他会话尝试对表添加表锁,就需要逐行进行检查,以确认是否存在与其冲突的行锁。
这种设计的效率很低,因此 MySQL 设计了一种意向锁,当产生行锁时,会给表添加意向锁,其他会话对表加表锁的时候,只需要检查表锁和意向锁是否冲突就可以了(如果冲突,将阻塞,直到冲突的意向锁消失)。
有两种意向锁:
-
意向共享锁(IS):
-
与表锁共享锁(read)兼容,与表锁排它锁(write)互斥。
-
由语句 select ... lock in share mode 添加。
-
-
意向排它锁(IX):
-
与表锁共享锁(read)及排它锁(write)都互斥。此外,意向锁之间不会互斥。
-
由语句 insert/update/delete/select ... for update 添加。
-
可以通过以下 SQL 查看表上的意向锁:
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
示例,在客户端1开启一个事务,查询并添加一个共享锁:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from score where id=1 lock in share mode;
+----+-------+------+---------+------+
| id | name | math | english | java |
+----+-------+------+---------+------+
| 1 | LiLei | 80 | 88 | NULL |
+----+-------+------+---------+------+
这里的select ... lock in share mode
语句将产生一个共享行锁以及一个共享表锁:
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 |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast | score | NULL | TABLE | IS | NULL |
| itcast | score | PRIMARY | RECORD | S,REC_NOT_GAP | 1 |
+---------------+-------------+------------+-----------+---------------+-----------+
lock_mode 中的 IS 表示意向共享锁,S 表示共享锁,lock_type 中 TABLE 表示表锁,RECORD 表示行锁。
此时在客户端 2 中试图获取一个共享表锁是可以执行成功的:
mysql> lock tables score read;
Query OK, 0 rows affected (0.00 sec)
因为共享表锁和意向共享锁(IS)不互斥。
如果通过客户端1开启一个事务并执行更新操作:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update score set math=77 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
如果发生阻塞,可能是之前申请的表锁没有释放。
这会导致产生一个行级排它锁以及一个意向排它锁:
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 |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast | score | NULL | TABLE | IX | NULL |
| itcast | score | PRIMARY | RECORD | X,REC_NOT_GAP | 1 |
+---------------+-------------+------------+-----------+---------------+-----------+
X 表示排它锁,IX 表示意向排它锁。
此时无论客户端 2 创建共享表锁或排它表锁都会失败:
mysql> lock tables score read;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
因为它们都是和意向排它锁互斥的。
行级锁
每次操作锁住的是数据航,锁定力度最小,发生锁冲突概率最低,并发性能好。应用于 InnoDB 存储引擎。
InnoDB 的数据是基于索引组织的,行锁是通过对索引上的索引项加锁实现的,而不是对记录加的锁。
对于行锁,主要分为以下三类:
-
行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此进行 update和 delete。在 RC,RR 隔离级别下都支持。
-
间隙锁(Gap Lock):锁定索引记录间隙(不包含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行 insert,产生幻读。在 RR 隔离级别下都支持。
-
临键锁(Next-Key Lock):行锁和间隙锁的组合,同时锁住数据和数据前的间隙,在 RR 隔离级别下支持。
行锁:
间隙锁:
临键锁:
行锁
InnoDB 实现了以下两种类型的行锁:
-
共享锁(S):允许一个事务去读一行,组织其他事务获得相同数据集的排它锁。
-
排它锁(X):允许获取排它锁的事务更新数据,组织其他事务获得相同数据集的共享锁和排它锁。
SQL 和添加的行锁之间的关系:
SQL | 行锁类型 | 说明 |
---|---|---|
INSERT ... | 排它锁 | 自动加锁 |
UPDATE ... | 排它锁 | 自动加锁 |
DELETE ... | 排它锁 | 自动加锁 |
SELECT (正常) | 不加锁 | |
SELECT ... LOCK IN SHARE MODE | 共享锁 | 需要手动添加 LOCK IN ... |
SELECT ... FOR UPDATE | 排它锁 | 需要手动添加 FOR UPDATE ... |
可以通过下面的 SQL 查询已经添加的行锁:
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
示例,通过下面的示例说明行锁之间的互斥关系。
首先确保没有任何已有的行锁:
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.02 sec)
在客户端1中通过查询语句产生一个共享行锁:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where id=12 lock in share mode;
+----+------+------+
| id | name | no |
+----+------+------+
| 12 | Lucy | NULL |
+----+------+------+
1 row in set (0.00 sec)
可以看到已经产生了一个共享行锁(S),且对应的数据行是 ID=12:
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 |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast | student | NULL | TABLE | IS | NULL |
| itcast | student | PRIMARY | RECORD | S,REC_NOT_GAP | 12 |
+---------------+-------------+------------+-----------+---------------+-----------+
共享行锁之间是可以共存的,所以我们可以在客户端 2 中也获取同一行数据的共享行锁:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where id=12 lock in share mode;
+----+------+------+
| id | name | no |
+----+------+------+
| 12 | Lucy | NULL |
+----+------+------+
SQL 可以正常执行,并且通过查询可以发现,的确产生了一个新的共享行锁,锁住的是同一条数据(lock_data):
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 |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast | student | NULL | TABLE | IS | NULL |
| itcast | student | NULL | TABLE | IS | NULL |
| itcast | student | PRIMARY | RECORD | S,REC_NOT_GAP | 12 |
| itcast | student | PRIMARY | RECORD | S,REC_NOT_GAP | 12 |
+---------------+-------------+------------+-----------+---------------+-----------+
但如果在客户端 2 中试图通过 UPDATE 语句获取排它行锁:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update student set name='LiLei' where id=12;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
SQL 执行会被阻塞,因为排它行锁和共享行锁是互斥的。反之(先获取排它行锁,再试图获取共享行锁)也是不行的。
在讨论索引的我们提到过,如果更新数据时没有通过索引进行检索,实际上不是使用行锁,而是使用表锁。现在我们可以说明这一点。
student 表当前没有主键以外的索引:
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student | 0 | PRIMARY | 1 | id | A | 8 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.07 sec)
通过 name 字段更新数据:
mysql> select * from student;
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | 黛绮丝 | 2000100101 |
| 2 | 谢逊 | 2000100102 |
| 3 | 殷天正 | 2000100103 |
| 4 | 韦一笑 | 2000100104 |
| 12 | LiLei | NULL |
| 22 | Tom | NULL |
| 23 | Tom | NULL |
| 24 | Tom | NULL |
| 31 | Tom | NULL |
+----+-----------+------------+
9 rows in set (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update student set name='Lucy' where name='LiLei';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看行锁会发现对所有数据行都添加了排它行锁(X):
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 |
+---------------+-------------+------------+-----------+-----------+------------------------+
| itcast | student | NULL | TABLE | IX | NULL |
| itcast | student | PRIMARY | RECORD | X | supremum pseudo-record |
| itcast | student | PRIMARY | RECORD | X | 1 |
| itcast | student | PRIMARY | RECORD | X | 2 |
| itcast | student | PRIMARY | RECORD | X | 3 |
| itcast | student | PRIMARY | RECORD | X | 4 |
| itcast | student | PRIMARY | RECORD | X | 31 |
| itcast | student | PRIMARY | RECORD | X | 23 |
| itcast | student | PRIMARY | RECORD | X | 22 |
| itcast | student | PRIMARY | RECORD | X | 24 |
| itcast | student | PRIMARY | RECORD | X | 12 |
+---------------+-------------+------------+-----------+-----------+------------------------+
此时通过其它客户端修改该表的任意数据行都将阻塞:
mysql> update student set name='java' where id=1;
要解决这一点,需要添加索引:
mysql> create index idx_name on student(name);
通过索引更新数据:
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> update student set name='php' where name='java';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
再查看行锁,会发现只会对特定行进行加锁:
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 |
+---------------+-------------+------------+-----------+---------------+------------+
| itcast | student | NULL | TABLE | IX | NULL |
| itcast | student | idx_name | RECORD | X,GAP | 'Lucy', 12 |
| itcast | student | idx_name | RECORD | X | 'java', 1 |
| itcast | student | PRIMARY | RECORD | X,REC_NOT_GAP | 1 |
+---------------+-------------+------------+-----------+---------------+------------+
此时就不影响其它客户端对其它行的数据更新操作了。
间隙锁/临键锁
默认情况下,InnoDB 在 REPEATABLE READ(RR)事务隔离级别运行,使用 next-key 锁进行搜索和索引扫描,以防止幻读。
索引上的等值查询(唯一索引),给不存在的记录加锁时,优化为间隙锁。
示例,student 目前的数据:
mysql> select * from student;
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | php | 2000100101 |
| 2 | 谢逊 | 2000100102 |
| 3 | 殷天正 | 2000100103 |
| 4 | 韦一笑 | 2000100104 |
| 12 | Lucy | NULL |
| 22 | Tom | NULL |
| 23 | Tom | NULL |
| 24 | Tom | NULL |
| 31 | Tom | NULL |
+----+-----------+------------+
通过客户端1试图更新 id 不存在的数据:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update student set name='Bruce' where id=30;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
查看产生的行锁:
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 |
+---------------+-------------+------------+-----------+-----------+-----------+
| itcast | student | NULL | TABLE | IX | NULL |
| itcast | student | PRIMARY | RECORD | X,GAP | 31 |
+---------------+-------------+------------+-----------+-----------+-----------+
这里的 GAP 表示这是一个间隙锁,lock_data=31 表示锁住了 31 之前的间隙(24~31)。
也就是说,现在 24~31 之间的部分(不包含 24 和 31 自身)被排它锁锁住了,其它会话无法插入数据,比如通过客户端 2 执行插入操作:
mysql> insert into student values(25,'Spring',null);
执行被阻塞。
索引上的范围查询(唯一索引)-- 会访问到不满足条件的第一个值为止。
比如,当前 student 表数据:
mysql> select * from student;
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | php | 2000100101 |
| 2 | 谢逊 | 2000100102 |
| 3 | 殷天正 | 2000100103 |
| 4 | 韦一笑 | 2000100104 |
| 12 | Lucy | NULL |
| 22 | Tom | NULL |
| 23 | Tom | NULL |
| 24 | Tom | NULL |
| 31 | Tom | NULL |
+----+-----------+------------+
执行查询:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where id>=24 lock in share mode;
+----+------+------+
| id | name | no |
+----+------+------+
| 24 | Tom | NULL |
| 31 | Tom | NULL |
+----+------+------+
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 |
+---------------+-------------+------------+-----------+---------------+------------------------+
| itcast | student | NULL | TABLE | IS | NULL |
| itcast | student | PRIMARY | RECORD | S | supremum pseudo-record |
| itcast | student | PRIMARY | RECORD | S | 31 |
| itcast | student | PRIMARY | RECORD | S,REC_NOT_GAP | 24 |
+---------------+-------------+------------+-----------+---------------+------------------------+
这里产生了三个行级锁:
-
S,REC_NOT_GAP|24
是一个行锁,锁住了 ID=24 这条记录。 -
S|31
是一个临键锁,锁住了 ID=31这条记录,以及 24~31之间的间隙。 -
S|supremum pseudo-record
是一个临键锁,可以看做是锁住 ID=正无穷记录,以及 31~正无穷之间的间隙。
索引上的等值查询(普通索引),向右遍历到最后一个不满足查询条件的值时,next-key lock 退化为间隙锁。
比如,为 student 增加一列表示年龄,并且添加索引:
mysql> select * from student;
+----+-----------+------------+-----+
| id | name | no | age |
+----+-----------+------------+-----+
| 1 | php | 2000100101 | 3 |
| 2 | 谢逊 | 2000100102 | 15 |
| 3 | 殷天正 | 2000100103 | 20 |
| 4 | 韦一笑 | 2000100104 | 20 |
| 12 | Lucy | NULL | 16 |
| 22 | Tom | NULL | 22 |
| 23 | Tom | NULL | 35 |
| 24 | Tom | NULL | 20 |
| 31 | Tom | NULL | 99 |
+----+-----------+------------+-----+
执行查询:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where age=20 lock in share mode;
+----+-----------+------------+-----+
| id | name | no | age |
+----+-----------+------------+-----+
| 3 | 殷天正 | 2000100103 | 20 |
| 4 | 韦一笑 | 2000100104 | 20 |
| 24 | Tom | NULL | 20 |
+----+-----------+------------+-----+
此时行级锁的添加情况:
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 |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast | student | NULL | TABLE | IS | NULL |
| itcast | student | idx_age | RECORD | S,GAP | 22, 22 |
| itcast | student | idx_age | RECORD | S | 20, 3 |
| itcast | student | idx_age | RECORD | S | 20, 4 |
| itcast | student | idx_age | RECORD | S | 20, 24 |
| itcast | student | PRIMARY | RECORD | S,REC_NOT_GAP | 3 |
| itcast | student | PRIMARY | RECORD | S,REC_NOT_GAP | 4 |
| itcast | student | PRIMARY | RECORD | S,REC_NOT_GAP | 24 |
+---------------+-------------+------------+-----------+---------------+-----------+
可以看到,索引index_age
上添加了三个行锁(S),分别是 ID=3,4,24。还添加了一个间隙锁(S,GAP),锁住了 age 为 20~22 之间的间隙。主键索引上有三个临键锁(S,REC_NOT_GAP),锁住了 ID=3,4,24的数据行,还锁住了 2~3,3~4,23~24 这几个间隙。
文章评论