[MySQL]感受InnoDB引擎中的行锁，表锁，记录锁，Next-Key锁

目录

先下结论

再感受锁的存在

1.感受共享锁、排它锁的存在

2.感受记录锁的存在

3.感受Next-Key Lock的存在

4.感受聚簇索引（聚集索引），唯一索引使用的是记录锁

5.当查找为范围查找时，无论是聚集索引还是普通索引，锁定的是一个范围

6.将MySQL的隔离级别设为Read Committed，演示幻读现象。

7.当对非索引字段进行加锁时，使用的是表锁。

---

先下结论

①InnoDB中锁包括表锁、行锁。MyISAM引擎与InnoDB引擎最大区别之一就是MyISAM引擎仅支持表级锁，而InnoDB引擎可以支持更小粒度的行级锁。

InnoDB中的行锁有共享锁（读锁）和排它锁（写锁）两种类型。

②InnoDB有三种锁的算法实现行锁，分别为：

1.Record Lock：单个行记录上的锁。

2.Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。

3.Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。

③加锁的方式：InnoDB行锁是通过给索引上的索引项加锁来实现的，如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。那如何给索引项加锁：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：

共享锁：select * from tableName where … + lock in share more

排他锁：select * from tableName where … + for update

④InnoDB行锁的特点：

（1）在不通过索引条件查询时，InnoDB会锁定表中的所有记录。

（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行。

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的。

⑤那么什么时候使用记录锁，什么时候使用Next-key锁呢？由两方面的因素决定：1.数据库的隔离级别2.索引项的索引类型，例如：

在REPEATABLE READ（可重复读）隔离级别下，

1.如果查询条件能使用上唯一索引或是聚簇索引，或者是一个唯一的查询条件，那么仅加记录锁;

2.如果是一个范围查询，那么就会给这个范围加上 Next-Key锁 (行锁+Gap锁)；

3.如果是普通索引，那么加Next-Key锁（InnoDB使用这个锁解决可重复读隔离级别下的幻读问题）；

4.如果是非索引，那么使用表锁。

在(READ COMMITTED)读已提交隔离级别下

1.即使是普通索引，也采用Record Lock（记录锁）锁住一行的数据。

⑥行锁的劣势：开销大；加锁慢；会出现死锁

行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

再感受锁的存在

1.感受共享锁、排它锁的存在

建立如下testlock表，其中id为主键，也是聚集索引。

创建事务1，不提交，显示加排它锁，并运行，如下所示

创建事务2，不提交，显示加共享锁，共享锁和排它锁是不兼容的，并运行，如下所示：

可以看到事务2获取锁失败（因为锁被事务1占有，事务1的事务没提交，就会一直占有锁，事务2就一直获取不到锁），最终事务2超时退出。

---

2.感受记录锁的存在

事务1锁住的是id为3的那行记录，事务2请求访问的也是id为3的记录，所以存在锁竞争，那如果访问id为4的记录呢？

可以看到事务2直接查询到了id为4的记录，所以聚集索引（唯一索引）使用的是记录锁，仅锁住id为3的这一行记录。

---

3.感受Next-Key Lock的存在

为testlock的name字段添加普通索引

事务1为name字段为4的记录显示加排它锁

事务2，执行插入name字段为5的操作，并运行，结果如下所示：

获取锁超时，Next-Key锁住的是一个索引范围。可见，在可重复读的隔离级别下，为普通索引所加的是Next-Key Lock，以上面的两个事务为例，Next-Key Lock锁住的是（3,4）（4,6）这个范围，所以插入name记录为5时，获取锁失败。

 

修改事务2如下，操作name字段为4的记录，获取锁超时，可见这是一个Next-Key Lock，不仅锁住本身，同时锁住下一个索引范围。

查看是否会锁住上一个索引范围

删除表中的name字段为3的记录

查看Next-Key Lock是否锁住了（2,4）这个范围，修改事务2如下，向表中添加name字段为3的记录

可以看到，索引范围（2,4）被锁住。

综上所述：聚集索引，唯一索引使用的是记录锁

                 普通索引（辅助索引），使用的是Next-Key Lock：锁定一个范围，并且锁定记录本身。

---

4.感受聚簇索引（聚集索引），唯一索引使用的是记录锁

这里可以说是锁的一种降级。锁的降级：当查询的索引含有唯一属性时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock,即仅锁住索引本身，而不是范围。

将以上事务中的name字段（普通索引）更换为id字段（聚集索引）,查看范围内的索引是否可以操作。

数据库中的数据

结论是可以操作范围索引，因此聚集索引使用的是记录锁。

---

5.当查找为范围查找时，无论是聚集索引还是普通索引，锁定的是一个范围

事务1

事务2

当查询name字段为6的记录时，被阻塞，不仅如此，name字段为4,8的记录都会被阻塞，锁住的范围为4到正无穷（包括4）。

当查询name字段为1时，可以顺利进行查询。

---

6.将MySQL的隔离级别设为Read Committed，演示幻读现象。

幻读：是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

在默认的事务隔离级别下，即REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking机制来避免幻读问题。

 

设置innodb的事务级别方法是：set 作用域 transaction isolation level 事务隔离级别，例如~

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

mysql> set global transaction isolation level read committed; //全局的

mysql> set session transaction isolation level read committed; //当前会话

 

查看MySQL的事务隔离级别：SELECT @@tx_isolation;

 

事务隔离级别READ COMMITTED下，其仅采用Record Lock，无法解决幻读问题，如下所示：

事务1，在name字段为6的记录上加排它锁，设置事务的隔离界别为读已提交。

事务2，插入name字段为7的记录，可以正常执行。

综上可知，在事务隔离级别为READ COMMITTED下，仅采用Record Lock锁住一行的数据。

---

7.当对非索引字段进行加锁时，使用的是表锁。

testlock表中的数据如下

这里我把name字段的索引删除了，创建事务1，锁住name字段为6的记录，如下所示