深入 MySQL (五) 锁

本文我们介绍 MySQL 锁的相关内容。

• 根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。其中全局锁和表锁都是Server层支持的，而行锁是引擎层实现的，InnoDB支持行锁，而MyISAM不支持行锁。

• 加全局锁办法：flush table with read lock； （解锁使用unlock table;)会使得整个数据库实例处于只读状态。一般用在MySIAM引擎下进行全库逻辑备份，而在InnoDB下一般不采用这种办法，应该使用mysqldump –single-transaction 通过事务来获得一致性视图，而不用加全局锁。
• 不要使用 set global readonly=true； 来加全局锁。因为readonly值会被用在其他用途，并且当客户端发生异常，readonly值会持久化到数据库上，导致数据库锁不能释放。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

• MySQL支持表锁，一般使用 lock tables [tablename] read/write 进行加锁。该命令的加锁会对所有的线程生效。
• MDL（metadata lock)是另一类表锁，不需要显式使用，在访问一个表的时候会被自动加上。在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。因此，MDL锁作用是防止增删改数据（DML)和加字段等修改表结构的操作（DDL）互相冲突。

• 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。因此，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
• 行锁的算法可以分为三种：Record Lock（单行记录上的锁）、Gap Lock(间隙锁，锁定一个范围不包含记录本身)和Next-Key Lock(锁定一个范围并包含记录本身)。具体参考：MySQL探秘(七):InnoDB行锁算法
• InnoDB通过给索引项加锁来实现行锁，如果没有索引，则通过隐藏的聚簇索引来对记录加锁。如果操作不通过索引条件检索数据，InnoDB 则对表中的所有记录加锁，实际效果就和表锁一样。
• 当查询的索引是唯一索引(不存在两个数据行具有完全相同的键值)时，InnoDB存储引擎会将Next-Key Lock降级为Record Lock，即只锁住索引本身，而不是范围。如果是范围查询，则边界的Next-Key Lock也有可能不会降级。21 | 为什么我只改一行的语句，锁这么多？
• InnoDB对于辅助索引使用Next-Key Lock锁，也就是说不仅会锁住辅助索引值所在的范围以及记录本身，还会将其下一键值加上Gap LOCK。
• Next-Key Lock本质是Record Lock + Gap Lock；
• Next-Key Lock是前开后闭区间

两个“原则”、两个“优化”和一个“bug”。

• 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

意向排他锁

• InnoDB支持多粒度锁，允许行锁和表锁共存。而意向锁属于一种表锁，意向锁互相之间不会排斥，但意向锁和表级的X锁存在互斥关系。具体参考：详解 MySql InnoDB 中意向锁的作用

死锁检测策略

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。但注意，如果打开死锁检测，事务执行过程中发生阻塞，都会进行检测，从而CPU性能损耗。

• 尽管会带来性能损耗，但正常来说都应该开启死锁检测。

• 减少死锁的主要方向，就是控制访问相同资源的并发事务量。