MySQL 锁

• 快照读：普通的 select 语句，基于 MVCC 和 undo log 实现，不加锁
• 当前读：update、delete、insert，select…lock in share mode (共享读锁) 等操作，基于锁实现

当我们执行 update、delete 操作时，首先需要找到对应的记录再操作、insert 操作类似，需找到最后一条数据，确保两条并行的 insert 不会混合插入到同一行。此时的读操作就采用当前读实现，当前读每次读取最新的数据，基于锁实现。

---

根据机制不同，锁可以分为以下两种：

• 共享锁：多个线程可以同时占有锁
• 独占锁：每个锁同时最多只能被一个线程占有

MySQL 读操作采用共享锁，写操作采用独占锁，也就是说多个线程可以同时读某个数据、但不允许修改。写操作时单个线程占用锁资源，其余线程不能访问。

---

根据粒度，锁又可以分为以下几种：

• 行锁：锁粒度最小，锁定某张表的某一行数据，会出现死锁
• 页面锁：锁粒度居中，锁定某个表的某一页，会出现死锁
• 表锁：锁粒度最大，直接锁定某个表，MyISAM 表锁不会出现死锁

死锁：不同线程分别占有对方需要获取的锁资源，都不释放锁资源就会产生死锁，多个线程互相请求锁形成环也会导致死锁

当用到表锁时，MyISAM 通过一次性获取所有需要的锁来避免死锁。InnoDB 存储引擎能检测到死锁的循环依赖并立即返回错误。当死锁涉及外部锁或表锁时，InnoDB 可能无法检测到死锁，此时可以通过设置锁最大超时等待参数（innodb_lock_wait_timeout）来解决。

总得来说，锁冲突越少，产生死锁的概率也就越小。InnoDB 可以通过以下方法减少死锁：

1. 在事务开启时首先通过 select … for update 直接获取必要的锁
2. 如果存在更新操作，直接获得独占锁，而不是先申请共享锁，防止第二次申请独占锁时其它事务占有共享锁
3. 如果事务需要修改多个表，则每个事务中以相同的顺序访问表使用加锁语句
4. 改变事务隔离级别

出现死锁可以通过 SHOW INNODB STATUS 命令确定最后一个死锁产生的原因，据此分析死锁产生的原因和改进措施。

死锁会严重影响数据库性能，在高并发系统上，当许多线程阻塞等待同一个锁时，死锁检测可能导致数据库处理速度变慢。 有时禁用死锁检测可能性能更高，此时可以设置 innodb_lock_wait_timeout 进行事务回滚。要想打破死锁，必须强制回滚部分事务打破环，InnoDB 默认回滚持有最少行级锁的事务。

其中锁粒度越大，锁操作的消耗越小，并发性越低。锁粒度越小，锁操作消耗越大，并发性越高。不同的存储引擎支持不同的锁粒度，实现原理也大不相同，常见的存储引擎锁实现有：

• InnoDB：支持行级锁和表级锁，默认采用行级锁
• MyISAM：支持表级锁

---

InnoDB 实现了两种类型的行锁：

• 行共享锁：允许事务去读一行，阻止其它事务获取当前行的独占锁
• 行独占锁：允许事务去修改一行，阻止其它事务获取当前行的共享锁及独占锁

为了确保行锁及表锁共存，InnoDB 还提供了两种意向锁，两种意向锁都是表锁：

• 意向共享锁：事务打算获取某行的共享锁前，必须先获取该表的意向共享锁
• 意向独占锁：事务打算获取某行的独占锁前，必须先获取该表的意向独占锁

锁之间的兼容关系如下：

1. 共享锁兼容共享锁和意向共享锁，不兼容独占锁和意向独占锁
2. 独占锁和任何锁都不兼容
3. 意向共享锁兼容共享锁、意向共享锁和意向独占锁
4. 意向独占锁兼容意向独占锁和意向共享锁

事务请求的锁如果和当前锁兼容，InnoDB 就分配锁资源给当前事务，否则只能等待锁资源释放。实际场景有以下几种：

1. 事务1 执行写操作，占有表的意向独占锁和行的独占锁。此时事务2 执行读操作，占有表的意向共享锁，意向共享锁和意向独占锁兼容，此时根据读的是否同一行判断，如果是同一行则阻塞，否则可以正常读取
2. 事务 1 执行读操作，占有表的意向共享锁和行的共享锁。此时事务2 执行写操作，栈有表的意向独占锁，意向共享锁和意向独占锁兼容，此时根据写的是否同一行判断，如果是同一行则阻塞，否则可以正常写
3. … ，其它类似

意向锁由 InnoDB 控制，自动加锁，无须用户干预。insert、update、delete 自动加独占锁，普通的 select 语句不会加锁，按快照读实现，常见的 select 显式加锁有两种方案：

• 共享锁：select * from table_name where … lock in share mode
• 独占锁：select * from table_name where … for update

即使没有显式加锁，InnoDB 在事务执行过程中会根据设置的隔离级别自动加锁，这种加锁方式也叫隐式加锁。锁在执行 commit 或者 rollback 时释放，所有锁在同一时间释放。

InnoDB 行锁通过对索引上的索引项加锁实现，也就是说只有通过索引查询时可能使用行锁，否则仍然使用表锁。无论使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁。只有真正用到索引查询时才会使用行锁，这块由执行计划决定：当表数据很少时，InnoDB 认为全表查询效率更高不使用索引就不会使用行锁。由于基于索引实现，可能存在不同行基于多个相同的索引键，此时即使不同行，仍有可能冲突。

间隙锁：当我们使用范围查询作为检索条件，InnoDB 会给符合记录的索引项加锁，对于键值在条件范围内但并不存在的记录，我们称之为间隙，InnoDB 默认会对间隙加锁，这种锁机制就是所谓的间隙锁。

InnoDB 默认使用间隙锁可以解决幻读问题，满足隔离级别，间隙锁也方便恢复和复制的需要：MySQL 的主从复制（恢复）基于 binlog 实现，binlog 是 SQL 语句级别的，恢复时重新执行 SQL，其中 binlog 日志根据事务提交的前后顺序记录。要想恢复数据一致，其他并发事务不能插入当前事务满足其锁定条件的任何记录，也就是不允许出现幻读。

但间隙锁会阻塞符合条件范围内键值的并发插入，在实际场景中我们应优先使用相等条件判断，少使用范围查询，减少间隙锁，降低锁冲突，提高性能。

---

MyISAM 表锁包含两种类型：

• 表共享读锁：不会阻塞其它线程对当前表的读操作，但会阻塞写操作
• 表独占写锁：会阻塞其它用户对当前表的读写操作

默认写锁比读锁具有更高的优先级，也就是说锁释放时，锁会优先分配给写锁队列中阻塞的线程，这也就导致当写操作非常频繁时，读操作可能永远无法抢占到锁了，此时可以通过配置修改优先级解决

MyISAM 永远不会导致死锁，因为 MyISAM 在执行查询操作前自动加读锁，在执行更新操作前直接加写锁，无须用户干预。在自动加锁的场景下，MyISAM 总是一次性获取当前 sql 需要的所有锁，因此不会死锁

MyISAM 支持并发插入，以减少锁的竞争消耗：如果 MyISAM 表中没有空白块，则 insert 操作和 select 操作可以并发执行不需要加锁，每次插入到文件末尾。当存在空白块时则不行，空白块可能由删除或者移动造成，当所有空白块都填充有新数据时，恢复并发插入。

并发插入特性通过 concurrent_insert 参数配置：

• concurrent_insert 为 0 时：不允许并发插入
• concurrent_insert 为 1 时：不存在空白块时允许并发插入，否则不允许，默认配置
• concurrent_insert 为 2 时：无论是否有空白块，都支持并发插入

---

上面提到的都是存储引擎层面的锁，MySQL 服务层提供 lock tables 和 unlock tables 执行锁：

• lock tables：锁定当前线程需要访问的表
• unlock tables：释放当前线程获取到的所有锁

在 InnoDB 表中使用数据库锁时需要注意以下几点：

1. 将 autocommit 设为0，否则不会加锁
2. 必须在事务结束后执行 unlock tables，因为 unlock tables 会隐式提交事务
3. commit 和 rollback 不能释放 lock tables 加的表锁，必须通过 unlock tables 显式解锁

---

根据是否乐观，锁又可以分为乐观锁和悲观锁：

• 乐观锁：假设不会发生冲突，只在提交操作时判断期间是否被修改，不会上锁，不能解决脏读。如果数据库提供 write_condition 机制都是通过乐观锁
• 悲观锁：认为一定发生冲突，必须加锁。行锁、表锁、读锁、写锁都是悲观锁。

---

关于锁优化的建议：

1. 尽量使用低隔离级别
2. 建立索引，尽量使用索引作为查询条件查询
3. 对于修改操作，直接获取独占锁，不要先申请共享锁，修改时再申请独占锁，降低锁冲突和死锁的概率
4. 对于多表操作，约定表的访问顺序，减少表之间死锁的概率
5. 少使用 MySQL 服务层提供的锁，innoDB 下普通 select 有 MVCC 保证同步