MySQL锁机制详解全局锁、表级锁、行级锁1.锁的介绍数据库中的数据是共享资源如何保证并发访问的一致性和有效性是所有数据库必须解决的问题。上篇 MySQL事务详解 介绍了通过提高事务隔离级别解决并发问题本篇从锁的角度切入。锁是控制多线程访问共享资源的工具。MySQL 按锁的颗粒度分为三类全局锁、表级锁、行级锁下面逐一讲解。2. 全局锁Global Lock2.1 全局锁介绍全局锁颗粒度最大锁住整个数据库。加锁后数据库只读其他线程的所有修改操作都会被阻塞。典型应用场景全库逻辑备份。备份期间禁止修改避免备份数据与主数据不一致。2.2 全局锁使用加全局锁flushtableswithreadlock;执行后整个数据库只读。MySQL 备份工具 mysqldumpmysqldump-u root-p[指定备份文件].sql释放全局锁unlocktables;注意当前会话断开全局锁也会自动释放。2.3 全局锁的问题全局锁是重量级操作加锁期间整个数据库只读业务无法修改。如果数据量大备份耗时长业务修改会被长时间阻塞。如何避免全局锁的影响InnoDB 引擎下mysqldump 加--single-transaction参数即可规避加上该参数后备份前先开启事务。InnoDB 默认隔离级别是可重复读备份前创建 Read View整个事务期间通过 MVCC 保证备份数据一致性同时其他事务可正常修改数据业务不受影响。3. 表级锁Table Lock表级锁对整张表加锁颗粒度比全局锁小。分为三类表锁、元数据锁MDL、意向锁。3.1 表锁表锁介绍表锁作用于整张表分为读锁和写锁。读锁共享锁允许本线程和其他线程读该表阻塞本线程和其他线程写该表写锁独占锁只允许本线程读和写该表其他线程的读写操作全部阻塞表锁使用对用户表加表锁//加读类型的表锁locktablesuserread//加写类型的表锁locktablesuserwrite;释放表锁unlocktables;注意当前会话断开表锁也会自动释放。3.2 元数据锁MDL元数据锁介绍元数据锁用于维护表结构一致性避免DDL 语句和 DML 语句冲突。简单说对表字段增删改查时不能同时修改表结构。元数据锁无需手动创建系统对表操作时自动加锁对表字段执行增删改查 → 加MDL 读锁对表结构执行修改 → 加MDL 写锁元数据锁使用查看是否使用了元数据锁selectobject_type,object_schema,object_name,lock_type,lock_duration,lock_statusfromperformance_schema.metadata_locks;此时系统给这张表加的就是MDL 读锁。事务提交后元数据锁自动释放。3.3 意向锁意向锁介绍意向锁像一个警示牌用于标识表中记录是否被加了行锁。为什么需要意向锁没有意向锁时加表锁前需扫描表中所有数据检查是否存在行锁表锁和行锁冲突性能极低。引入意向锁后对数据加行锁前先对表加意向锁。加表锁时只需检查是否存在意向锁无需逐条扫描性能大幅提升。意向锁分为意向共享锁和意向独占锁对记录执行增删改→ 先加意向独占锁再加独占锁对记录执行select ... lock in share mode→ 先加意向共享锁再加共享锁意向锁虽是表级锁但与行锁不冲突意向锁之间也不冲突只和表锁冲突。4. 行级锁Row Lock行级锁对表中记录加锁颗粒度最小。分为三类行锁、间隙锁、临键锁。4.1 行锁行锁介绍行锁作用于某一条记录颗粒度最小分为读锁共享锁和写锁独占锁。读锁共享锁允许本线程和其他线程读该记录阻塞本线程和其他线程写该记录写锁独占锁只允许本线程读和写该记录其他线程的读写操作全部阻塞关键特性行锁在事务中生效事务提交或回滚后自动释放行锁基于索引实现。查询条件未走索引时行锁会升级为表锁颗粒度变大影响并发性能行锁使用对 user 表中 id1 的记录加行锁// 开启事务begin;// 加写类型的行锁select*fromuserwhereid1forupdate;// 加读类型的行锁select*fromuserwhereid1lockinsharemode;// 提交事务释放行锁commit;执行select ... for update→ 加写锁其他事务对该记录的读写操作全部阻塞直到当前事务提交或回滚执行select ... lock in share mode→ 加读锁其他事务可读该记录但写操作被阻塞对记录执行增删改操作时系统也会自动加行锁。4.2 间隙锁Gap Lock间隙锁介绍间隙锁锁住的是索引记录之间的间隙范围而非某条具体记录主要用于解决幻读问题。核心机制可重复读隔离级别下事务对某范围加间隙锁后其他事务不能在该间隙插入新记录从而避免幻读。示例user 表 id 索引值为 1、3、5间隙锁可锁住(1,3)、(3,5)、(5, ∞)。其他事务试图在(3,5)间隙插入 id4 的记录时会被阻塞。间隙锁之间不冲突可同时存在间隙锁只和插入操作冲突。间隙锁使用间隙锁自动加锁无需手动创建。使用范围条件查询时InnoDB 自动对范围内间隙加锁//开启事务begin;//对 id 大于 1 且小于 5 的范围加间隙锁select*fromuserwhereid1andid5forupdate;//提交事务释放间隙锁commit;执行后其他事务无法在(1,5)间隙插入 id 为 2、3、4 的新记录直到当前事务提交或回滚。4.3 临键锁Next-Key Lock临键锁介绍临键锁是行锁和间隙锁的组合既锁住某条索引记录又锁住该记录前面的间隙。锁范围是左开右闭区间如(1,3]表示锁住 id 大于 1 且小于等于 3 的记录及其前面的间隙。关键特性临键锁是 InnoDB 在可重复读隔离级别下的默认锁同时解决幻读和脏读问题。示例user 表 id 索引值为 1、3、5临键锁锁住(-∞,1]、(1,3]、(3,5]、(5,∞)。其他事务试图在(1,3]插入 id2 的记录时会被阻塞。临键锁使用临键锁自动加锁无需手动创建。等值或范围条件查询时InnoDB 自动对命中的记录及其前面间隙加锁//开启事务begin;//对 id 小于等于 3 的记录及其前面的间隙加临键锁select*fromuserwhereid3forupdate;//提交事务释放临键锁commit;执行后其他事务无法修改 id 为 1、3 的记录也无法在(-∞,1]和(1,3]间隙插入新记录直到当前事务提交或回滚。注意查询条件命中的记录不存在时临键锁会退化为间隙锁只锁间隙不锁记录。