阿里-美团-字节面试官必问的Mysql锁机制，你真的明白吗

id INT PRIMARY KEY auto_increment,
name VARCHAR(40),
money INT
)ENGINE MyISAM

INSERT INTO employee(name, money) VALUES(‘黎杜’, 1000);
INSERT INTO employee(name, money) VALUES(‘非科班的科班’, 2000);

查看一下，表结果如下图所示：

MyISAM表级写锁

（1）与此同时再开启一个session窗口，然后在第一个窗口执行下面的sql，在session1中给表添加写锁：

LOCK TABLE employee WRITE

（2）可以在session2中进行查询或者插入、更新该表数据，可以发现都会处于等待状态，也就是session1锁住了整个表，导致session2只能等待：

（3）在session1中进行查询、插入、更新数据，都可以执行成功：

「总结：」 从上面的测试结果显示**「当一个线程获取到表级写锁后，只能由该线程对表进行读写操作，别的线程必须等待该线程释放锁以后才能操作」**。

MyISAM表级共享读锁

（1）接下来测试一下表级共享读锁，同样还是利用上面的测试数据，第一步还是在session1给表加读锁。

（2）然后在session1中尝试进行插入、更新数据，发现都会报错，只能查询数据。

（3）最后在session2中尝试进行插入、更新数据，程序都会进入等待状态，只能查询数据，直到session1解锁表session2才能插入、更新数据。

「总结：」 从上面的测试结果显示**「当一个线程获取到表级读锁后，该线程只能读取数据不能修改数据，其它线程也只能加读锁，不能加写锁」**。

MyISAM表级锁竞争情况

MyISAM存储引擎中，可以通过查询变量来查看并发场景锁的争夺情况，具体执行下面的sql语句：

show status like ‘table%’;

主要是查看table_locks_waited和table_locks_immediate的值的大小分析锁的竞争情况。

Table_locks_immediate：表示能够立即获得表级锁的锁请求次数；Table_locks_waited表示不能立即获取表级锁而需要等待的锁请求次数分析，「值越大竞争就越严重」。

并发插入

通过上面的操作演示，详细的说明了表级共享锁和表级写锁的特点。但是在平时的执行sql的时候，这些**「解锁和释放锁都是Mysql底层隐式的执行的」**。

上面的演示只是为了证明显式的执行事务的过程共享锁和表级写锁的加锁和解锁的特点，实际并不会这么做的。

在我们平时执行select语句的时候就会隐式的加读锁，执行增、删、改的操作时就会隐式的执行加写锁。

MyISAM存储引擎中，虽然读写操作是串行化的，但是它也支持并发插入，这个需要设置内部变量concurrent_insert的值。

它的值有三个值0、1、2。可以通过以下的sql查看concurrent_insert的默认值为**「AUTO(或者1)」**。

concurrent_insert的值为NEVER (or 0)表示不支持比并发插入；值为AUTO(或者1）表示在MyISAM表中没有被删除的行，运行另一个线程从表尾插入数据；值为ALWAYS (or 2)表示不管是否有删除的行，都允许在表尾插入数据。

锁调度

MyISAM存储引擎中，「假如同时一个读请求，一个写请求过来的话，它会优先处理写请求」，因为MyISAM存储引擎中认为写请求比都请求重要。

这样就会导致，「假如大量的读写请求过来，就会导致读请求长时间的等待，或者"线程饿死"，因此MyISAM不适合运用于大量读写操作的场景」，这样会导致长时间读取不到用户数据，用户体验感极差。

当然可以通过设置low-priority-updates参数，设置请求链接的优先级，使得Mysql优先处理读请求。

InnoDB

InnoDB和MyISAM不同的是，InnoDB支持**「行锁」和「事务」**，行级锁的概念前面以及说了，这里就不再赘述，事务的四大特性的概述以及实现的原理可以参考这一篇[]。

InnoDB中除了有**「表锁」和「行级锁」的概念，还有Gap Lock（间隙锁）、Next-key Lock锁，「间隙锁主要用于范围查询的时候，锁住查询的范围，并且间隙锁也是解决幻读的方案」**。

InnoDB中的行级锁是**「对索引加的锁，在不通过索引查询数据的时候，InnoDB就会使用表锁」**。

「但是通过索引查询的时候是否使用索引，还要看Mysql的执行计划」，Mysql的优化器会判断是一条sql执行的最佳策略。

若是Mysql觉得执行索引查询还不如全表扫描速度快，那么Mysql就会使用全表扫描来查询，这是即使sql语句中使用了索引，最后还是执行为全表扫描，加的是表锁。

若是对于Mysql的sql执行原理不熟悉的可以参考文章。最后是否执行了索引查询可以通过explain来查看，我相信这个大家都是耳熟能详的命令了。

InnoDB行锁和表锁

InnoDB的行锁也是分为行级**「共享读锁（S锁）**「和」排它写锁（X锁）」，原理特点和MyISAM的表级锁两种模式是一样的。

若想显式的给表加行级读锁和写锁，可以执行下面的sql语句：

// 给查询sql显示添加读锁
select … lock in share mode;
// 给查询sql显示添加写锁
select … for update；

（1）下面我们直接进入锁机制的测试阶段，还是创建一个测试表，并插入两条数据：

// 先把原来的MyISAM表给删除了
DROP TABLE IF EXISTS employee;
CREATE TABLE IF NOT EXISTS employee (
id INT PRIMARY KEY auto_increment,
name VARCHAR(40),
money INT
)ENGINE INNODB;
// 插入测试数据
INSERT INTO employee(name, money) VALUES(‘黎杜’, 1000);
INSERT INTO employee(name, money) VALUES(‘非科班的科班’, 2000);

（2）创建的表中可以看出对表中的字段只有id添加了主键索引，接着就是在session1窗口执行begin开启事务，并执行下面的sql语句：

// 使用非索引字段查询，并显式的添加写锁
select * from employee where name=‘黎杜’ for update;

（3）然后在session2中执行update语句，上面查询的式id=1的数据行，下面update的是id=2的数据行，会发现程序也会进入等待状态：

update employee set name=‘ldc’ where id =2;

可见若是**「使用非索引查询，直接就是使用的表级锁」**，锁住了整个表。

（4）若是session1使用的是id来查询，如下图所示：

（5）那么session2是可以成功update其它数据行的，但是这里我建议使用数据量大的表进行测试，因为前面我说过了**「是否执行索引还得看Mysql的执行计划，对于一些小表的操作，可能就直接使用全表扫描」**。

（6）还有一种情况就是：假如我们给name字段也加上了普通索引，那么通过普通索引来查询数据，并且查询到多行数据，拿它是锁这多行数据还是锁整个表呢？

下面我们来测试一下，首先给**「name字段添加普通索引」**，如下图所示：

并插入一条新的数据name值与id=2的值相同，并显式的加锁，如下若是：

（7）当update其它数据行name值不是ldc的也会进入等待状态，并且通过explain来查看是否name='ldc’有执行索引，可以看到sql语句是有执行索引条件的。

结论：从上面的测试锁机制的演示可以得出以下几个结论：

1. 执行非索引条件查询执行的是表锁。
2. 执行索引查询是否是加行锁，还得看Mysql的执行计划，可以通过explain关键字来查看。
3. 用普通键索引的查询，遇到索引值相同的，也会对其他的操作数据行的产生影响。

InnoDB间隙锁

当我们使用范围条件查询而不是等值条件查询的时候，InnoDB就会给符合条件的范围索引加锁，在条件范围内并不存的记录就叫做"间隙（GAP）"

大家大概都知道在事务的四大隔离级别中，不可重复读会产生幻读的现象，只能通过提高隔离级别到串行化来解决幻读现象。

但是Mysql中的不可重复是已经解决了幻读问题，它通过引入间隙锁的实现来解决幻读，通过给符合条件的间隙加锁，防止再次查询的时候出现新数据产生幻读的问题。

例如我们执行下面的sql语句，就会对id大于100的记录加锁，在id>100的记录中肯定是有不存在的间隙：

Select * from employee where id> 100 for update;

（1）接着来测试间隙锁，新增一个字段num，并将num添加为普通索引、修改之前的数据使得num之间的值存在间隙，操作如下sql所示：