跳转至

MySQL锁机制

一、概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

按照数据操作类型分类： - 读锁（共享锁/S锁）：针对同一份数据，多个事务的读操作可以同时进行而不会互相影响 - 写锁（排他锁/X锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

按照锁的粒度分类： - 全局锁：锁定数据库中的所有表 - 表级锁：每次操作锁住整张表 - 行级锁：每次操作锁住对应的行数据

三、全局锁

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

应用场景：全库逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

语法： 

-- 加锁
flush tables with read lock;

-- 备份（在命令行执行）
mysqldump -u用户名 -p密码 数据库名 > 文件路径/文件名.sql

-- 解锁
unlock tables;

特点： - 如果在主库上备份，备份期间不能执行更新，业务停滞 - 如果在从库上备份，备份期间从库不能执行主库同步过来的binlog，导致主从延迟

避免方法：使用可重复读隔离级别，在备份前先开启事务 

mysqldump --single-transaction -u用户名 -p密码 数据库名 > 文件路径/文件名.sql

四、表级锁

表级锁每次操作锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低。应用于MyISAM、InnoDB、BDB等存储引擎中。

4.1 表锁

分类： - 表共享读锁（read lock）：读锁不会阻塞其他客户端的读，但会阻塞写 - 表独占写锁（write lock）：写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写

语法： 

-- 加锁
lock tables 表名 read/write;

-- 释放锁
unlock tables;
-- 或客户端断开连接自动释放

注意：表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作，应尽量避免使用表锁。

4.2 元数据锁（Meta Data Lock，MDL）

MDL是系统自动控制的锁，无需显式使用，在访问一张表时会自动加上。主要作用是维护表元数据的数据一致性。

加锁规则： - 对表进行增删改查时，加MDL读锁（共享） - 对表结构进行变更操作时，加MDL写锁（排他）

特性： - MDL读锁只允许读，不能做结构修改 - MDL写锁只允许写，修改表结构时不能通过CRUD读取数据 - MDL不需要显式调用，在事务提交后才会释放 - 申请MDL锁的操作会形成队列，写锁优先级高于读锁

安全变更表结构的方法：先检查是否有长事务持有MDL读锁，如有则kill掉长事务后再进行表结构变更。

查看元数据锁： 

select object_type, object_schema, object_name, lock_type, lock_duration 
from performance_schema.metadata_locks;

4.3 意向锁

意向锁的目的是快速判断表里是否有记录被加锁，避免DML执行时行锁与表锁的冲突。

分类： - 意向共享锁（IS）：由 select ... lock in share mode 添加 - 意向排他锁（IX）：由 insert、update、delete、select ... for update 添加

兼容性： - 意向锁之间不冲突 - 意向锁与行级的共享锁和排他锁不冲突 - 意向锁只和共享表锁（lock tables ... read）或排他表锁（lock tables ... write）发生冲突

查看锁的加锁情况： 

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data 
from performance_schema.data_locks;

4.4 AUTO-INC锁（自增锁）

表里的主键设置自增属性时，通过AUTO_INCREMENT实现。

机制： - 在插入数据时，加一个表级别的AUTO-INC锁 - 为被AUTO_INCREMENT修饰的字段赋值递增的值 - 插入语句执行完成后释放锁

优化：InnoDB提供了轻量级的锁来实现自增，只需在赋值完成后释放锁。

五、行级锁

行级锁每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用于InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

5.1 行锁（Record Lock）

定义：锁定单个行记录的锁，防止其他事务对此行进行update和delete。

分类： - 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得排他锁 - 排他锁（X锁）：允许获取排他锁的事务更新数据，阻止其他事务获得共享锁和排他锁

特性： - 在RC（读提交）和RR（可重复读）隔离级别下都支持 - 针对唯一索引进行等值匹配时，将自动优化为行锁 - 如果不通过索引条件检索数据，InnoDB将对表中所有记录加锁，此时会升级为表锁

5.2 间隙锁（Gap Lock）

定义：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。

特性： - 只存在于RR（可重复读）隔离级别 - 间隙锁唯一目的是防止其他事务插入间隙 - 间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

5.3 临键锁（Next-Key Lock）

定义：行锁和间隙锁的组合，同时锁住数据，并锁住数据前面的间隙Gap。

特性： - 只存在于RR（可重复读）隔离级别 - 是前开后闭区间 (n, k] - 既能保护记录，又能阻止其他事务将新记录插入到被保护记录前面的间隙中 - 如果一个事务获取了X型的next-key lock，另一个事务获取相同范围的X型next-key lock时会被阻塞

5.4 插入意向锁

定义：插入意向锁是一种特殊的间隙锁，属于行级别锁。存在间隙锁时，执行Insert语句时会用到。

特性： - 如果间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点 - 插入意向锁与间隙锁是冲突的 - 当其他事务持有间隙锁时，需要等待释放后才能获取插入意向锁

六、MySQL加锁机制

6.1 从语句角度看加锁

普通的SELECT：默认不加锁，属于快照读，使用MVCC方式实现。

锁定读的语句： 

-- 对读取的记录加共享锁（S型锁）
select ... lock in share mode;

-- 对读取的记录加排他锁（X型锁）
select ... for update;

UPDATE/DELETE：都会加行级锁，且锁的类型都是排他锁（X型锁）。

INSERT语句： - 正常执行时不会生成锁结构，靠聚簇索引记录自带的trx_id隐藏列作为隐式锁 - 如果已有间隙锁，会生成插入意向锁，状态设置为等待状态 - 如果记录存在唯一键冲突，会对记录加S型锁

6.2 从索引角度看加锁

MySQL加锁的对象是索引，加锁的基本单位是next-key lock。

唯一索引等值查询： - 查询记录存在：next-key lock退化为记录锁 - 查询记录不存在：next-key lock退化为间隙锁

唯一索引范围查询： - > 范围：next-key lock不退化 - >= 等值查询记录存在：next-key lock退化为记录锁 - < 范围：next-key lock退化为间隙锁

非唯一索引等值查询： - 查询记录存在：扫描到的二级索引记录加next-key lock，第一个不符合条件的记录退化为间隙锁，同时在主键索引上加记录锁 - 查询记录不存在：next-key lock退化为间隙锁，不对主键索引加锁

非唯一索引范围查询：对扫描到的二级索引记录加锁都是加next-key lock，主键索引加记录锁。

全表扫描： - 如果where条件没有使用索引，会对全表每条记录加next-key锁，相当于锁住整个表 - 即使使用了索引，优化器最终选择全表扫描也会对全表加锁

七、死锁问题

7.1 死锁的产生原因

死锁的四个必要条件： 1. 互斥 2. 占有且等待 3. 不可强占用 4. 循环等待

案例：两个事务都持有间隙锁(1006, +∞)，同时执行insert操作时，都需要获取插入意向锁，但插入意向锁与间隙锁冲突，导致相互等待。

7.2 避免死锁的方法

超时回滚： 

set innodb_lock_wait_timeout = 50;  -- 默认50秒

主动死锁检测（默认开启）： 

set innodb_deadlock_detect = on;

减少死锁的技巧：

  1. 发出 SHOW ENGINE INNODB STATUS 命令确定死锁原因

  2. 启用详细死锁日志： 

    set innodb_print_all_deadlocks = on;

  3. 保持事务较小且持续时间较短

  4. 按照一致的顺序执行操作

  5. 添加精心选择的索引，减少锁的使用

  6. 使用较低隔离级别（如READ COMMITTED）

  7. 尽量用相等条件访问数据，避免间隙锁对并发插入的影响

  8. 不要申请超过实际需要的锁级别

  9. 除非必须，查询时不要显示加锁

  10. 给记录集显式加锁时，最好一次性请求足够级别的锁

总结

MySQL锁机制是保证并发数据一致性的核心，主要包括：

粒度 锁类型 说明
全局锁 Flush Tables With Read Lock 整个数据库只读
表级锁 表锁、MDL锁、意向锁、AUTO-INC锁 锁定整张表
行级锁 行锁、间隙锁、临键锁、插入意向锁 锁定行或行区间

理解锁机制对于优化SQL查询、避免死锁、处理并发问题至关重要。

评论