6. 锁

EnigmaGcl
78 阅读9分钟

6. 锁.png

1. 锁类型

1.1 performance_schema.data_locks 字段含义

*************************** 2. row ***************************
ENGINE_TRANSACTION_ID: 1820                  事务 id
            THREAD_ID: 53                    创建锁的线程 id
          OBJECT_NAME: t                     表
           index_name: PRIMARY               锁加的索引（这里也能说明为什么 MySQL 的锁都是加载索引上的）
OBJECT_INSTANCE_BEGIN: 140562695089696       锁的物理地址
            LOCK_TYPE: RECORD                锁类型
            LOCK_MODE: X,GAP                 锁模式
          LOCK_STATUS: GRANTED               当前请求锁的状态
            LOCK_DATA: 10                    锁住的行

1.2 IX/IS 意向锁

IX:准备给表里的行加行的写锁 IX:DML / select for update

IS:准备给表里的行加行的读锁 IS:select for share

意向锁的作用是协调表级锁和行级锁之间的关系，避免加锁时的冲突和死锁情况。

最开始 MySQL 只有表锁，这种时候在做表锁判断时很简单，只需要看当前表是否被持有即可，成本很低，但是 InnoDB 引入行锁后，事情就变得复杂了，此时如果想加表锁，那需要遍历整张表，看其中某行是否有行锁，效率低下，意向锁就是解决该问题的

在我们执行 DML 或者是加读写锁的 select 时，MySQL 会默认给表加上意向锁，表示有操作相对表中的数据加锁，这样其他语句想加表锁时，直接看该表是否有意向锁即可，如果有，等待意向锁解锁后才能添加表锁，否则可以直接加上

begin;
select * from t;    T1
select * from t where id = 0 lock in share mode;    T2

为了验证普通 select 不加意向锁，在上面 T1 和 T2 时刻，分别执行

select ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_NAME,index_name, OBJECT_INSTANCE_BEGIN,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;

结果为：

T1 时刻：

mysql> select ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_NAME,index_name, OBJECT_INSTANCE_BEGIN,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;

Empty set (0.00 sec)

T2 时刻： image.png

1.3 表锁

lock table t read, t2 write;

session1 执行了这个语句后,以下说法正确的是:

A. session1 可以对表 t 执行读操作;

B. session1 可以对表 t 执行写操作;

C. session1 可以对表 t2 执行读操作;

D. session1 可以对表 t2 执行写操作;

E. session1 可以对表 t3 执行读操作;

F. session2 可以对表 t 执行读操作;

G. session2 可以对表 t2 执行读操作;

正确的是 A、C、D、F

语句执行完成后，当前会话，在解锁前，只能对 t 进行读，t2 进行读写，其他表不能操作，其他会话也只能不加锁的读 t，对 t2，读写都不行，要等到解锁

表锁只能执行限定范围内的操作，包括自己也会限制，因此 B、E 不正确，然后写锁包括读，因此 C、D 正确

意向锁的作用前面有说明，因此意向锁和表锁的关系很简单，只要有意向写锁，那么跟表锁都是冲突的，意向读锁只会跟表锁写锁冲突，意向锁之间不冲突，如下：

XIXSIS
X❌❌❌❌
IX❌✅❌✅
S❌❌✅✅
IS❌✅✅✅

1.4 间隙锁

innodb RR 级别特有的锁，作用是锁住间隙，阻止插入，以 LOCK_MODE 为例， 可选的值有三个：

  • GAP（单纯间隙锁）
  • REC_NOT_GAP（单纯行锁）
  • 空（表示 NEXT_KEY_LOCK，即间隙锁+行锁）

执行语句

select * from t where id >= 15 and id <= 20 for update; 
select * from t where id > 5  and id < 10;

然后查询当前锁情况

select ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_NAME,index_name, OBJECT_INSTANCE_BEGIN,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks

结果为：

image.png

可以看到，两条语句总共加了三个锁，第一条语句加了 15、(15,20]这两个锁，一个行锁(X，REC_NOT_GAP)，一个 NEXT_KEY_LOCK（X），后一条语句加了一个间隙锁(5,10)（X，GAP）

1.5 MDL

MDL 是 MySQL 用于管理数据库对象元数据（如表结构）的锁机制，属于表级锁，server 层，对所有引擎生效，核心作用如下：

  1. 防止元数据冲突：确保在事务执行期间，表结构（如列、索引、表名）不会被其他会话修改（如 DDL 操作）。
  2. 保证数据一致性：避免因表结构变更导致事务读取到不一致的元数据（例如查询过程中表被重命名或删除）。
  3. 协调并发操作：控制 DML（数据操作，如 SELECT/UPDATE）与 DDL（表结构变更，如 ALTER TABLE）的并发冲突。

该锁是 MySQL 自动加的，在事务中执行 DML 语句时，会加共享读锁或者共享写锁，这两个锁是兼容的，执行 DDL 时加的是排他锁，跟共享读锁和写锁不兼容

可以通过下面语句查询 MDL 锁情况

SELECT    OBJECT_SCHEMA,    OBJECT_NAME,    LOCK_TYPE,    LOCK_STATUS,    OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_TYPE = 'TABLE';

2. 死锁

2.1 什么是死锁

简单来讲就是不同会话相互等待锁，之间形成了等待环，就成死锁了

MySQL 默认开启死锁检测，每次锁等待时，就会看等待的锁的事务是否有等待锁，如果有，检查该事务等待锁的事务是否有等待锁，直到检查到等待锁的事务没有等待锁，就表明没有死锁，或者最终发现事务重复了，表示有死锁存在，可以看到，如果同一行涉及的事务过多，死锁检测耗费会较大

在秒杀扣减库存这种情况下很容易出现死锁检测耗费大量 CPU 的情况，这种情况可以从以下方面考虑优化：

  • MySQL 方面

    • 优化事务，尽可能将事务无关的操作移除事务，使事务尽可能短小
    • 优化索引

  • 业务方面

    • 限流/熔断
    • 缓存预热，在秒杀活动开始前，将热门商品的库存信息加载到缓存（如 Redis）中，用户请求先在缓存中进行库存扣减，当缓存中的库存扣减成功后，再异步更新数据库中的库存。
    • 消息队列异步处理

  • 架构方面

    • 分布式锁，使用 Redis 的原子操作（如 SETNX）实现分布式锁，确保同一时间只有一个请求能对某个商品的库存进行扣减操作，避免并发冲突。
    • 读写分离，采用 MySQL 的主从复制架构，将读操作（如查询商品信息）分发到从库，将写操作（如扣减库存）集中到主库，减轻主库的压力，提高系统的并发处理能力

2.2 锁等待超时

MySQL 中如果关闭死锁检测，那么如果碰到死锁，线程会等到，直到获取锁或者超时，默认超时时间为 50 秒

2.3 死锁检测后，选谁回滚

基本思想是谁持有资源少，回滚谁，资源包括锁记录、权重、undo 数量等，有点复杂，略过

3 二级索引上的锁

普通索引上的锁都是 Next-Key Lock 或者是 GAP，唯一索引跟主键索引表现一致，区别是唯一索引可能还要回表

3.1 MySQL 加锁的原则：用到才加

这句话的意思是语句执行过程中扫描到的行才会加锁，如果是二级索引，能用上覆盖索引不回表，那么只会加二级索引的锁，主键索引不加锁，该规则对读锁有效，如果是二级索引加写锁，那么一定会回表在主键上也加写锁

begin;
select id from t where c = 15 for share;

加锁结果为： image.png 可以看到，只对二级索引加了锁

begin;
select id from t where c = 15 for share;
select id from t where c = 20 for update;

加锁结果为： image.png 可以看到，当加写锁时，会回表给主键上行数据加上写锁

begin;
select id from t where c = 15 for share;
select id from t where c = 20 for update;
select * from t where c = 5 for share;

加锁结果为： image.png 可以看到，如果需要回表，读锁也会给主键索引加上锁

3.2 二级索引上的数据要判断版本

这句话的意思是 undo 只作用于主键索引，如果查询仅涉及二级索引，并且主键被修改过，那么此时还是需要回表查询的

                                            begin;
                                            select id from t where c = 5;
update t set id = id * 10 where c = 5;
                                            select id from t where c = 5;

如上所示，查到的结果还是 5，但是二级索引上是没有 undo 的，因此这个 5 是回表到主键上查询得到的

这是怎么实现的呢？我们知道主键上是每行都有一个 trx_id 用来决定是否应用 undo 的，二级索引上没有，但是二级索引的页上有最新更新的事务 id，所以在判断覆盖索引时，有一个额外的判断，如何二级索引所在页上 id 可见，就能用覆盖索引，否则需要回表查询，是否可见就看事务 id 是否在事务可见列表中

4 备份和锁

4.1 主库备份

MySQL 逻辑备份，常用 FTWRL（flush table with read lock），原因是库中可能还存在表没有用 innodb，因此需要使用表锁来获取一个一致性的视图，具体逻辑如下：

flush tables with read lock;
select my1;
select my2;
start transaction with consistent snapshot;
unlock tables;
select InnoDB tables;
commit;

如果能保证所有表都用的 InnoDB 引擎，那就一定能不用 FTWRL 了吗？

不行，如果有开 GTID，那么需要这样执行：

flush tables with read lock;
select @@global.gtid_execute;
start transaction with consistent snapshot;
unlock tables;
select InnoDB tables;
commit;

如果没开 GTID，但是使用命令 mysqldump --master -data，那么也需要用 FTWRL，如下：

flush tables with read lock;
show master status;
start transaction with consistent snapshot;
unlock tables;
select InnoDB tables;
commit;

4.2 优化备份逻辑

  1. 判断是否有长时间的 select 语句，有的话稍后再试
  2. 没有的话，启动 mysqldump ，加 ftwrl，然后 kill 掉超过 N 秒的查询语句
  3. ftwrl 配置超时退出

问答

  1. 事务期间修改隔离级别，会怎样？

    set transaction_isolation='repeatable-read';

begin;  
select * from t where id>5 and id <10 for update; (Q1)  
set transaction_isolation='read-committed'; (Q2) 
select * from t where id>15 and id <20; for update (Q3)  
commit

    如上，执行完 Q2 后，事务继续，整体保留RR，直到提交，如何验证呢？可以新起一个连接，执行语句 select * from information_schema.innodb_trx\G 查看隔离级别，这个例子说明一个事务只会有一个隔离级别

    还可以通过语句

    select ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_NAME,index_name,
OBJECT_INSTANCE_BEGIN,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA
from performance_schema.data_locks;

    查看加锁情况，如果 Q2 执行完事务就提交了，那么事务持有的锁应该都会被释放，可以看到事务持有的锁并没有释放，如下所示： image.png

avatar
文章
阅读
粉丝