【MySQL】MySQL数据库锁使用与InnoDB加锁的原理解析

本文导读

本文将通过锁的分类，包括库锁、表锁、页锁、行锁等等，详细介绍MySQL锁的使用、以及MySQL的优化和MySQL InnoDB加锁原理。

一、MySQL中三种锁分类

这里直接给出结论，MySQL中有三种锁：页级锁、表级锁和行级锁。

表锁：低开销，快速锁定；无死锁；锁粒度大，锁冲突的概率最高，并发性最低。它出现在MyISAM、Memory、InnoDB、BDB和其他存储引擎中，基本都支持。

行锁：高开销，慢锁定；将出现死锁；锁粒度最小，锁冲突的概率最低，并发性最高。InnoDB存储引擎支持。

页锁：成本和锁定时间介于表锁和行锁之间；有可能出现死锁；锁定粒度介于表锁和行锁之间，并发性一般，仅有BDB存储引擎支持。

行锁、表锁、页锁分别对应存储引擎关系 ：

二、MySQL中存在的第四种锁——库锁

1、什么是库锁

库锁是锁定整个数据库实例。MySQL提供了一种添加全局读锁的方法。需要使整个库为只读时，可以使用此锁。

这时数据更新语句（数据添加、删除和修改）、数据定义语句（包括表创建、表结构修改等）以及更新类型事务的提交语句，都会被阻塞。

FLUSH TABLES WITH READ LOCK -- 启动库锁，这整个库只读

UNLOCK TABLES  -- 释放库锁

2、库锁的使用场景

库锁的典型使用场景是对整个数据库进行逻辑备份。但是官方的逻辑备份工具mysqldump使用参数 –single transaction 时，将在导入数据之前启动事务，以确保获得一致性视图（MVCC支持）。MVCC在，MySQLMVCC原理中详解：（链接待补充）。

所以库锁一般资料中很少提到，并且工作中也很少使用。

三、MySQL锁的使用

1、表锁

表锁通常处理并发问题。然而，支持行锁定的引擎InnoDB通常不使用 lock-tables 命令来控制并发。

-- MySQL表锁语法
LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
lock tables fork_business_detail read/write

unlock tables -- 释放锁

2、InnoDB 中的锁

在 MySQL InnoDB 存储引擎中，锁分为行锁和表锁。

2.1、共享锁、独占锁

行锁包括两种类型：共享锁、独占锁

共享锁（S）：可以同时读取多个事务，不互斥，但是共享锁会阻止独占锁；独占锁（X）：允许获得独占锁的事务更新数据，并防止其他事务获得同一数据集的共享读锁和独占写锁。

此外InnoDB还具有两种类型的内部意图锁，这两种类型都是表锁。

2.2、意向共享锁、意向独占锁、自增锁

表锁有三种类型：意向共享锁、意向独占锁（排他锁）、自增锁（自增计数器）

意向共享锁（IS）：事务计划将行共享锁添加到数据行。在向数据行添加共享锁之前，事务必须首先获得表的IS锁。

有意独占锁（IX）：事务打算向数据行添加独占锁。在向数据行添加独占锁之前，事务必须首先获得表的IX锁。

自增锁（AUTO-INC Locks）：表锁的一种。自增长计数器通过这个“锁”获得子增长计数器的最大计数值。

在添加行锁之前，您必须首先获得一个表级意图锁，或者等待innodb_lock_wait_timeout，根据innodb_ rollback_on_timeout确定是否回滚事务。

3、Innodb行锁

3.1、InnoDB行锁的三种类型

InnoDB行锁定是通过锁定索引数据页上的记录来实现的。有三种主要算法：Record Lock、Gap Lock 和 Next-key Lock。

行锁（Record Lock）锁：单行记录的锁定（锁定数据，而不是间隙）。锁被直接添加到索引记录而不是行数据，键被锁定。

间隙锁（Gap Lock）锁：间隙锁，锁定一个范围，不包括记录本身（不锁定数据，只锁定数据前面的间隙），锁定索引记录的间隙，并确保索引记录的间距保持不变。

间隙锁用于隔离处于或高于可重复读取级别的事务。

Next-key Lock 锁：同时锁定数据，并锁定数据前面的间隙。行锁和间隙锁的组合称为下一个键锁。

3.2、Innodb默认使用 Next-Key Lock

默认情况下，Innodb 工作在可重复读取隔离级别，并以 Next-Key Lock 的方式锁定数据，这可以有效地防止幻读。

Next Key Lock 是行锁和间隙锁的组合。

当InnoDB扫描索引记录时，它首先对索引记录应用行锁（Record Lock），然后对索引记录两侧的间隙应用间隙锁（Gap Lock），添加间隙锁定后，其他事务无法在此间隙中修改或插入记录。

-- 注：普通查询是快照读，不需要加锁
-- for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。
begin;
select * from user_info where name = 'xiaoming' for update;
commit;

3.3、Innodb对Next-Key Lock的优化

优化1：对于索引的等效查询，当唯一索引被锁定时，下一个键锁退化为行锁。

优化2：对于索引上的等价查询，当向右遍历且最后一个值不满足等价条件时，锁退化为间隙锁。当“唯一索引”用于“搜索唯一行”语句时，不需要间隙锁。

begin;
select name from user_info where name = 'xiaoming' for update;
commit;

例如，如果name是唯一索引，并且只搜索 name，那么只有此行将与记录锁一起使用。
如果名称列没有索引或是非唯一索引，则语句将生成间隙锁。如果搜索条件中有多个查询条件（即使每列都有一个唯一的索引），也会有间隙锁。

3.4、排查 InnoDB 锁问题

通常有两种方法来解决InnoDB锁问题。

1、打开 innodb_lock_monitor 表记住在使用后关闭监视器表，否则会影响性能。

2、在information_schema 库下面的 innodb_locks、innodb_lock_waits、innodb_trx排查

3、间隙锁不是互斥的。两个事务加上间隙锁不是互斥的。事务A可以锁定相同的数据以阻止操作，而事务B可以锁定相同数据以防止操作。这可能导致死锁问题。

4、可以禁用间隙锁的两种方法，一是把隔离级别降为读已提交（read committed），二开启参数innodb_locks_unsafe_for_binlog。

可以通过 show variables like ‘innodb_locks_unsafe_for_binlog’; （默认不开启，如果发现有long 事务可以排查下间隙锁）命令查看该库是否开启间隙锁。

四、Innodb的 Next-lock 加锁工作原理

分析锁时需要跟隔离级别联系起来，我们以可重复读 RR（REPEATABLE-READ） 为例，首先开启两个事务

左边执行 select * from fork_business_detail where sub_odr_id=‘xiaoming’ ，会加 next-key lock。

右边执行insert语句就会阻塞。

加锁是要基于索引的。

1、主键，加锁行为仅在 主键索引记录上加排他（X）锁。

2、唯一索引，先在唯一索引 id 上加排他（X）锁，再在的主键索引记录上加排他（X）锁。若记录不存在，那么加间隙锁。

3、普通索引，先通过索引上定位到第一个满足的记录，对该记录加 X 锁，而且要在主键上面，之间加上 Gap lock，为了防止幻读，然后在主键索引 name 上加对应记录的X 锁；再通过该索引上定位，有没有其他满足的记录，同上。最后直到发现没有满足的记录了，此时不需要加 X 锁，但要再加一个 Gap lock（间隙锁），这个锁扩到该数据的下一位。

也就是说满足条件的数据之间上下一位都会别锁住。

4、无索引，表里所有行和间隙均排他（X）锁，直接锁表了，所以在使用的时候一定要走索引。

总结

本文将通过锁的分类，包括库锁、表锁、页锁、行锁等等，详细介绍MySQL锁的使用、以及MySQL的优化和MySQL InnoDB加锁原理。