前言

这是一篇关于MySQL数据库索引的文章。该文章是黑马程序员—MySQL数据库入门到精通视频的归纳总结和补充，同时也引用了公共仓库 - 智云知识和Java Guide的部分内容，仅用于个人技术归档和技术分享。

概述

锁机制，是处理并发场景下保持数据一致性的使用的机制。我们要清楚一点，锁机制一般都是对于并发事务而言的，下面说到阻塞，一般是多事务操纵数据库时发生的

在MySQL数据库中，按照功能性来分，又可以分为:共享锁和排他锁

按照锁的粒度分类可以分为：全局锁，表级锁，行级锁

共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

  • 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
  • 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。（读读兼容，读写冲突，写写冲突）

S 锁 X 锁
S 锁 不冲突 冲突
X 锁 冲突 冲突

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过， 你可以通过以下语句显式加共享锁或排他锁。

1
2
3
4
# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

全局锁

全局锁就是对整个数据库实例加锁，加锁后数据库处于只读状态，后续的DML的写语句，DDL语句，以及更新操作的事务提交语句都会阻塞

经典场景：做全库的逻辑备份时，添加全局锁，锁住所有的表。

PS：数据库备份 具体操作可以在终端输入： mysqldump -h host_name -u username -p password table_name>table_name.sql

🔧 语法

1
2
3
4
-- 添加全局锁
flush tables with read lock;
-- 解锁
unlock tables;

📍特点

  1. 如果在主库上备份，备份期间不能执行更新，业务停摆
  2. 如果在从库上备份，备份期间不能执行主库同步过来的二进制日志（binlog）,导致主从延迟

在InnoDB引擎中，可以在备份时加上--single-transaction参数，实现不加锁完成一致性数据备份（底层通过快照读实现），例如：

mysqldump --single-transaction -h host_name -u username -p password table_name>table_name.sql

表级锁

表级锁，每次锁住整张表。锁粒度大，锁冲突概率最高，并发度最低。

表级锁主要分为以下三类：

  • 表锁
  • 元数据锁（meta data lock，MDL）
  • 意向锁

表锁

表锁可以分为：

  • 读锁（read lock）,又名：表共享读锁。加了读锁，所有事务都不能写，但是所有事务都可以读
  • 写锁（write lock），又名：表独占写锁。加了写锁，只有加锁的事务可以读写，其他事务不能读不能写

🔧 语法

1
2
3
4
5
-- 加锁
lock tables 表名 read
lock tables 表名 write
-- 解锁
unlock tables

元数据锁

元数据MDL加锁的过程是系统自动控制的，无需显式使用。主要作用是维护表元数据的一致性，在表上有活动的事务时，不能对元数据进行写入操作。为了避免DML与DDL冲突，保证读写正确性（也就是避免数据库定义和操作的语句冲突）

对应SQL 锁类型 说明
lock tables xxx read / write SHARED_READ_ONLY/SHARED_NO_READ_WRITE
select 、select ...lock in share mode SHARED_READ 与SHARED READ、SHARED WRITE兼容，与EXCLUSIVE互斥与SHARED READ
insert 、 update、delete、 select ... for update SHARED_WRITE 与SHARED WRITE兼容，与EXCLUSIVE互斥
alter table ... EXCLUSIVE 与其他的MDL都互斥

🔧 语法

1
2
-- 查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;

意向锁

为了避免DML在执行时，加的行锁和表锁冲突，InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加了行锁。使用意向锁可以减少表锁的检查

意向锁又可以分为以下两类：

  1. 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
  2. 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁之间是互相兼容的。

IS 锁 IX 锁
IS 锁 兼容 兼容
IX 锁 兼容 兼容

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

IS 锁 IX 锁
S 锁 兼容 互斥
X 锁 互斥 互斥

表锁和意向锁总结一下还是：读读兼容，读写互斥，写写互斥

🔧 语法

1
2
3
4
5
6
7
8
9
-- 锁跟事务有关，要想看到锁情况的请看，我们先手动开启一个事务
begin
-- 加锁 实际上这里我们加了行锁，但是在我们添加行锁锁时，表会添加上一个意向锁IS/IX，以此防止行锁和表锁冲突
select ... lock in share mode 
insert 或 update 或 delete 或 select ... for update
-- 查看锁
select object_schema,index_name,lock_type,lock_data,lock_mode from performance_schema.data_locks;
-- 事务提交后，相应阻塞就会解除了
commit

行级锁

行级锁，每次操作锁住操作的数据行，锁的粒度最小，冲突概率最低，并发度最高。

InnoDB的数据是基于索引组织的（聚集索引与数据放一起，二级（辅助）索引叶子节点最终存储主键值，然后回表查询聚集索引），所以行锁是对索引项枷锁，而不是对记录（数据）加锁

行级锁主要分为三类：

  1. 行锁(Record Lock): 锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持

  2. 间隙锁(Gap Lock):锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持

  3. 临键锁(Next-KeyLock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

行锁

InnoDB中实现了两种类型的行锁：行共享锁和行排他锁

默认情况下，lnnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

  1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
  2. lnnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么lnnoDB将对表中的所有记录加锁，此时行锁就会升级为表锁。（例如一个事务中，更新某个字段，检索时where的条件并不是索引，那么整个表都会加锁了）

InnoDB中默认给SQL语句自动加锁，详情可以看下表。

SQL 行锁类型 说明
INSERT ... 排他锁 自动加锁
UPDATE ... 排他锁 自动加锁
DELETE ... 排他锁 自动加锁
SELECT(正常) 不加任何锁
SELECT(正常) WHERE 唯一索引=值 共享锁 自动加锁
SELECT ... LOCK IN SHARE MODE 共享锁 需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT ... FOR UPDATE 排他锁 需要手动在SELECT之后加FOR UPDATE

🔧 语法

1
2
3
4
5
6
7
8
9
10
11
-- 锁跟事务有关，要想看到锁情况的请看，我们先手动开启一个事务
begin
-- 加锁
select ... lock in share mode
select ...(正常) where 唯一索引=值
select ... for update
insert 或 update 或 delete 
-- 查看锁
select object_schema,index_name,lock_type,lock_data,lock_mode from performance_schema.data_locks;
-- 事务提交后，相应阻塞就会解除了
commit

间隙锁和临键锁

默认情况下，InnoDB在REPEATABLE READ（RR，可重复读）事务隔离级别运行，InnoDB使用next-key（临建锁）进行搜索和索引扫描，以防止幻读。

  1. 索引上的等值查询(唯一索引)，给不存在的记录且该记录在两个记录的间隙之间加锁时,优化为间隙锁
  2. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，临建锁退化为间隙锁
  3. 索引上的范围查询(唯一索引)—会访问到不满足条件的第一个值为止，都会加上临建锁

注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存。一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

🔧 语法

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 锁跟事务有关，要想看到锁情况的请看，我们先手动开启一个事务
begin
-- 加锁
update 表 set 字段=xxx where 唯一索引=不存在的值且该值在两个记录之间
update 表 set 字段=xxx where 非唯一索引=存在的值且后面还有记录
update 表 set 字段=xxx where 唯一索引>=值 
select ... lock in share mode 
insert 或 update 或 delete 或 select ... for update

-- 查看锁
select object_schema,index_name,lock_type,lock_data,lock_mode from performance_schema.data_locks;
-- 事务提交后，相应阻塞就会解除了
commit