MySQL2️⃣锁

锁：计算机协调多个进程或线程并发访问某一资源的机制。

按照锁的粒度分，MySQL 锁的分类如下：

  • 全局锁：锁住当前数据库实例（数据库中的所有表）。
  • 表级锁：锁住当前表。
  • 行级锁：锁住当前行记录。

1、全局锁

锁住当前数据库实例（数据库中的所有表）

  1. 加锁后整个实例处于只读状态，期间的 DML、DDL 语句都会被阻塞。
  2. 场景：数据库逻辑备份。此时锁定所有的表，获取一致性视图，以保证数据的完整性。

1.1、语法

  1. 加锁

    flush tables with read lock ;

  2. 数据备份

    mysqldump -uroot –p1234 itcast > itcast.sql

  3. 释放

    unlock tables;

1.2、数据库备份

有加锁和不加锁两种方式

  1. 加全局锁：

    • 主库备份：备份期间都不能执行更新，基本上业务无法进行。
    • 从库备份：备份期间从库不能执行主库同步过来的二进制日志（binlog），导致主从延迟。

  2. 不加锁：使用参数 --single-transaction

    # 语法
mysqldump --single-transaction -u用户名 –p密码 数据库 > SQL文件

# 示例：将student数据库备份到student.sql文件中
mysqldump --single-transaction -uroot –p123456 student > student.sql

2、表级锁

锁住当前表。

（MyISAM、InnoDB、BDB 等存储引擎都支持）

  • 特点：锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 类型：表锁、元数据锁、意向锁

2.1、表锁

分类

  • 表共享读锁（read lock）

  • 表独占写锁（write lock）

    # 加锁
LOCK TABLES 表名... READ;
LOCK TABLES 表名... WRITE;
# 释放锁
UNLOCK TABLES;
# 客户端断开连接时，锁也会释放

示例

  • 读锁：对一个表加读锁，则任意事务对该表只读（包括当前事务）

    image-20220316153042036

  • 写锁：对一个表加写锁，则阻塞其它事务的读写。

    image-20220316153219627

2.2、元数据锁（MDL）

meta data（元数据）

可理解为表的结构。

当表上有活动事务时，此时不应该对元数据进行写操作。

也就是说，当前表存在未提交的事务时（DML），不应该修改表结构（DDL）。

meta data lock（MySQL 5.5 引入）

  • 作用：维护表元数据的数据一致性，避免 DML 和 DDL 冲突。
  • 类型：在访问一张表的时候，自动根据操作控制 MDL 加锁。
    • 增删改查：MDL 读锁（共享）
    • 更改表结构：MDL 写锁（排他）

理解

  1. 事务 A 执行 UPDATE 语句：对记录添加行锁，并且自动为该表添加一个 MDL 读锁

    SELECT * FROM tb_user WHERE id = 7;

  2. 事务 B 对该表添加表锁时，会阻塞

    LOCK TABLES tb_user READ;

  3. 事务 A 提交事务，释放行锁后，MDL 读锁也随之释放。

  4. 事务 B 才能获得 MDL 写锁。

常见元数据锁

在访问数据库表时，系统会根据操作自动加上相应的元数据锁。

对应元数据锁 互斥性
加表锁 SHARED_READ_ONLY
SHARED_NO_READ_WRITE
SELECT
SELECT IN SHARE MODE		 SHARED_READ 只与 EXCLUSIVE 互斥
增删改
SELECT FOR UPDATE		 SHARED_WRITE 只与 EXCLUSIVE 互斥
修改表结构 EXCLUSIVE 与其它 MDL 互斥

查看元数据锁情况

SELECT object_type,object_schema,object_name,lock_type,lock_duration
FROM performance_schema.metadata_locks;

2.3、意向锁

没有意向锁的场景

  1. 假设事务 A 对表中记录添加了行锁。
  2. 假设事务 B 要添加表锁，需要遍历全表（效率低），确定每行记录都没有行锁才能加表锁。

Intent lock

针对添加表锁的优化机制，

避免在 DML 执行期间，加的行锁与表锁冲突。

  1. 机制：

    1. 执行 DML 时添加行锁，此时系统自动添加对应的意向锁。
    2. 事务提交后，意向锁会自动释放。

  2. 类型：意向锁之间相互兼容，不会互斥。

    对应意向锁 互斥性
    SELECT LOCK IN SHARE MODE 意向共享锁（IS） 与表独占写锁互斥
    增删改、SELECT FOR UPDATE 意向排它锁（IX） 与表锁互斥

有意向锁的场景

  1. 事务 A 对表中记录添加了行锁，此时自动添加一个意向锁。
  2. 假设事务 B 要添加表锁，只需判断该表是否有意向锁，无需遍历全表。

查看意向锁、行锁的加锁情况

SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data 
FROM performance_schema.data_locks;

3、行级锁（❗）

锁住当前行记录。

（InnoDB 支持）

  • 特点：锁定粒度小，发生锁冲突的概率最低，并发度最高。
  • 注意：
    • 针对索引项加锁，而不是对记录本身加锁。
    • 如果对应列没有索引或索引失效，则行级锁会升级表锁。

3.1、行锁

Record Lock：锁定单个行记录，阻塞其它事务的删改。

  1. 隔离级别支持：RC、RR

  2. InnoDB 行锁类型：

    1. 共享锁（S）：允许获取共享锁的事务执行读操作，阻止其它事务获得相同数据集的排它锁。

    2. 排它锁（X）：允许获取排它锁的事务更新数据，阻止其它事务获得相同数据集的共享锁和排它锁。

      image-20220315235132991

常见 SQL 语句的行锁

类型 说明
增删改 排它锁 自动加锁
SELECT 不加锁
SELECT ... LOCK IN SHARE MODE 共享锁 手动添加 LOCK IN SHARE MODE
SELECT ... FOR UPDATE 排它锁 手动添加 FOR UPDATE

3.2、间隙锁、临键锁

隔离级别支持：RR

作用：阻塞其它事务的 INSERT，防止幻读。

  • Gap Lock：针对目标记录行，在其上一条记录和目标记录本身之间的间隙加锁。
    • 假设表中有 id 为 1，3，7，8 的记录，执行 UPDATE tb_user WHERE id = 5
    • 此时在 3 和 7 之间添加间隙锁。
  • Next-Key Lock：针对存在的记录，锁定当前记录+之后所有记录的间隙。
    • 假设表中有 id 为 1，3，7，8 的记录，执行 UPDATE tb_user WHERE id = 3
    • 此时对 3 添加行锁，在 3和 7 之间、7 和 8 之间添加间隙锁。

4、不同场景的使用情况

InnoDB 默认在 RR 隔离级别运行，

且使用临键锁进行搜索和索引扫描，以防止幻读。

4.1、没有索引

当记录没有索引或索引失效时，无法添加行锁，使用表锁。

4.2、唯一索引

  1. 等值查询：
    1. 对于存在的记录，使用临键锁。
    2. 对于不存在的记录，使用间隙锁。
  2. 范围查询：使用临键锁。

4.3、普通索引

针对等值查询：临键锁 + 间隙锁

  • 说明：
    • InnoDB 使用 B+tree 索引，叶子节点是有序的双向链表。
    • 由于普通索引非唯一，可能存在多个相同值的记录。
  • 加锁步骤：
    • 向右遍历叶子节点，直到找到不满足查询条件的值，加临键锁。
    • 在添加临键锁的记录之后加间隙锁。

示例：假设等值查询条件为 age = 16

  1. MySQL 匹配到第一个 16，向右遍历直到不满足查询条件的值 38

  2. 对 B 位置的 16 加临键锁，在 B 和 C 之间加间隙锁。

    image-20220317114722651
posted @ 2022-10-13 11:09  Jaywee  阅读(37)  评论(0)  编辑  收藏  举报

☝

👇