Mysql锁机制面试点总结

前言

数据库是一个多用户使用的共享资源。当多个用户并发的存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加以控制就可能会读取和存储不正确的数据，破坏数据的一致性。

锁就是用于管理对公共资源的并发控制。

锁的出现，就是用于解决不同事物对共享资源并发访问所引起的脏读、不可重复读和幻读

MySQL中InnoDB锁类型，大体可分为: 共享锁 (Shard Lock) 、排它锁(Exclusive Lock)、意向共享锁(Intention Shared Lock)、意向排它锁(Intention Exclusive Lock)、自增锁(Auto-INC LOCK)、临键锁(Next-key Lock)、间隙所(Gap Lock)、记录锁(Record Lock)这8种。(其中记录锁、间隙锁、临键锁都是行锁的具体实现)。

接下来锁相关介绍，均以事务关闭状态，手工开启为前提来进行demo演示。

orders 表数据：

1 共享锁(S锁)

又称为读锁。简称S锁。行级锁，未命中索引将会退化变为表锁。

共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。添加共享锁方式如下：

// 在查询语句末尾，需要通过LOCK IN SHARE MODE的方式添加共享锁select * from users where id = 1 LOCK IN SHARE MODE;

说明：

• where name这里不是索引，为该行加一把共享锁之后，其它数据再次查询该行可以正常查询；
• where name这里不是索引，为该行加一把共享锁之后，其它数据修改该行 会被阻塞；
• where name这里不是索引，修改其它数据，会被阻塞，因为不走索引，会被升级为表锁 从而阻塞；
• where name这里是索引的情况，修改这条数据，命中索引，不会被阻塞；

2 排它锁(X锁)

又称为写锁，简称X锁。

如果一个事务获取了一个数据行的排它锁，那么其它事务就不能再获取该行的锁(共享锁、排它锁)，只有获取了该排它锁的事务是可以对这一数据行进行读取和修改。(其它事务可以读取数据，数据来自快照，即Mysql的MVCC机制)。

添加排它锁的方式如下：

//删除、修改、插入,默认会加上X锁delete /update / insert //查询语句，需要通过 FOR UPDATE 的方式添加排他锁select * from users where id = 1 FOR UPDAT

• 添加排它锁之后，不命中索引，就会升级为表锁。
• 但是其实查询数据是可以正常进行的，因为Innodb可以在行锁未释放之前，其它事务读取该行数据读取的是快照数据，并不会与行锁冲突。

3 意向锁

意向锁的由来，我们来分析下面这个示例：

多个事务：A、B、C三个事务为例：

• A事务添加了行锁，此时其它事务无法再添加表锁，但是可以添加其它行的行锁；
• A事务添加了表锁，那么其它事务则无法再添加行锁、表锁；
• 即一个表，不同行可以有多个行锁。但是行锁和表锁只能二选一存在。

3.1 意向共享锁(IS锁)

表锁。表示事务准备给数据行加入共享锁时，即一个数据行加共享锁前必须先取得该表的IS锁。

3.2 意向排它锁(IX锁)

表锁。表示事务准备数据行加入排它锁时，即一个数据行加排它锁前必须先取得该表的IX锁。

Tips：意向锁相当于Java中的flag，就是一个标记，在加锁前会判断该表是否有锁。

例子来通俗易懂的理解：

事务A锁住了表中的一行，让这一行只能读，不能写。

之后事务B申请整张表的写锁。

如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。

那么数据库应该怎么判断这个冲突呢？主要有两种办法：

a.其中一个办法是判断表中的每一行是否已经被行锁锁住；

b.另外一个方法判断表是否已经被其它事务用表锁锁住；

注意方法a，这样的判断方法效率实在不高，因为需要遍历整个表。

于是在这样的场景下，意向锁出现了。

在意向锁存在的情况下，事务A必须先申请表的意向互斥锁，成功后再申请这一行的行锁。

而在意向锁的情况下，上面的事务B判断就发现表中有意向互斥锁，说明表中有些行被行锁锁住了。因此，事务B申请表的写锁会被阻塞；

4 自增锁(AUTO-INC LOCK)

自增锁，是针对自增列自增长的一个特殊的表级别锁。

比如说我们建表时，通常会指定某一列为id自增长列，该列便会使用到这个自增锁。

自增锁有一个默认值，是从1开始的。通常情况下，我们在数据库表中会发现自增id并不连续，这就是自增锁在搞事情。

当我们插入数据时，自增锁会+1，但是此时如果事务执行了rollback等其他操作，导致数据并没有插入成功，此时zizengsuoid并不会随之回退，会永久丢失，从而导致的自增id列不连续。

/** 当我们进行插入操作的时候，该操作会根据这个自增长的计数器的值+1赋予自增长的列，这个操作我们称之为auto-inc Locking，也就是自增长锁，这种锁其实采用的是特殊的表锁机制，如果insert操作出现在一个事务中，这个锁是在insert操作完成之后立即释放，而不是等待事务提交。**/

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

那么为什么表级别的锁，我们还能够在会话1中的事务没有结束的时候，在另外一个会话2上成功执行insert呢？不应该直接锁表么？它的本质其实是在参数innodb_autoinc_lock_mode上，这个参数设置为1的时候，相当于将这种auto_inc lock弱化为了一个更轻量级的互斥自增长机制去实现，官方称之为mutex。

5 临建锁(next-key lock)&间隙锁(gap-key lock)和记录锁(record lock)

Mysql中锁大体可分为表级锁和行级锁。

在Mysql的常用引擎中InnoDB支持行锁；而MyISAM只能使用表锁。

Mysql的表锁

表锁由Mysql Server实现，一般在执行DDL语句时会对整张表进行加锁，比如说ALTER TABLE等操作。

表锁使用的是一次性锁技术，也就是说，在会话开始的地方使用lock命令将后续需要用到的表都加上锁，在表释放前，只能访问这些加锁的表，不能访问其它表，直到最后通过unlock tables释放所有表锁。

Mysql的行锁

行锁中按照粒度划分为：

• 记录锁(Record Lock)：单个行记录上的锁；
• 间隙锁(Gap Lock)：锁定一个范围，但不包括记录本身；
• Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

注意：行锁加锁是在索引上加锁的。

5.1 临键锁(Next-key lock) (InnoD引擎默认行锁算法)

临键锁(Next-key Lock), 作为InnoDB引擎默认的行锁算法，本质就是用来解决幻读的问题。当SQL执行按照索引进行数据查询时，查询条件为范围查找(between and > <等)并由数据命中时，此时SQL语句加上的锁为Next-key Lock，锁住的是索引的记录+下一个区间(左开右闭)

即使用临建锁的条件：

• 查询命中索引；
• 范围查询；
• 有数据命中时；

我们使用 SQL 进行查询。针对 orders 表中的数据，InnoDB 引擎会将数据进行如下区间划分：并锁住当前数据所在区间+下一个区间。

此时，我们对 id = 4 进行查询，由于不在锁住区间内，并不会阻塞；id = 7,10 进行查询，插入id = 8,9 的数据。发现都会被阻塞。当我们插入 id = 11 时，可以成功插入，说明上述查询锁住的区间为 (4,7],(7,10]

那么，临键锁，为什么要锁住下一个区间呢？

这和索引底层 B+Tree 有关系。MySQL 索引底层选择 B+Tree。B+Tree 数据都保存在叶子节点，并且具有顺序性，从左到右依次增大。临键锁锁住相邻区间，此时 insert 插入数据时，我们并不能够将数据成功的插入到该区间，就能够满足每次查询的数据一致，从而解决幻读问题。

5.2 间隙锁(Gap Lock)

间隙锁(Gap Lock)。当SQL执行按照索引进行数据查询时，查询条件的数据不存在，此时SQL语句加上的锁即为Gap Lock，锁住的是数据不存在的区间。(左开右开)。

即使用间隙锁条件：

• 查询命中索引；
• 范围查询；
• 无数据命中时；

使用：同临建锁类似，当查询无数据命中时，临建锁(Next-key lock)便会退变成为Gap锁。Gap锁锁住的是数据不存在的区间(左开右开)。

由于锁住(7,10)这个区间，此时我们无法插入id=8,9的数据，但是我们可以插入id=11的数据，说明上述 查询使用的是Gap锁，锁住的区间为(7,10)。

因为只有可重复的隔离级别下，InnoDB引擎才能够解决数据幻读的问题。临键锁(Next-key) lock=间隙锁(Gap Lock)+记录锁(Record Lock).所以Gap锁只在可重复隔离级别下存在。

5.3 记录锁(Record Lock)

记录锁(Record Lock)。当SQL执行按照唯一性(Primary key,Unique key)索引 进行数据查询时，查询条件等值匹配且查询的数据存在，此时SQL语句加上的锁即为Record Lock，锁住的是具体的索引项。

即使用记录锁条件：

• 唯一性索引查询(主键索引、唯一性索引)；
• 精确等值查询；
• 有数据命中时；

由于锁住 id = 7 这个索引，此时我们可以查询 id = 4 的数据，但是无法查询 id = 7 的数据。说明：锁住的区间为 id = 7 这个索引。

6 事务并发问题解决方案

6.1 +X锁(排它锁) 解决脏读问题

6.2 +S锁(共享锁)，解决不可重复读问题

6.3 +Next key锁，解决幻读问题

7 如何避免出现事务死锁？

• 类似的业务逻辑以固定的顺序访问表和行；
• 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小；
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率；
• 降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择；
• 为表添加合理的索引。可以看到如果不走索引将会表的每一行记录添加锁。(或者说是表锁)。

总结：

select * from users where id = 1 LOCK IN SHARE MODE; //共享锁，该行只能读不能修改
delete /update/insert 以及select * from users where id=1 FOR UPDATE //加入排它锁，这一行不允许和别的锁共存。
select * from orders where id>5 and id<9 for update; next-key lock；//命中数据；命中索引；范围查询；锁住(4,7] (7,10]
select * from orders where id>8 and id<10 for update; //查询记录不存在，命中索引，范围查询，锁住(7,10)
select * from order where id=7 for update; //唯一性索引查询(主键索引、唯一性索引) 精确等值查询；数据命中；
InnoDB中的行锁实现依赖与索引，一旦某个加锁操作没有使用到索引，那么该所就会退化成为表锁；