一文掌握MySQL锁机制（共享锁/排他锁/意向锁/间隙锁/临键锁等)

1、数据库为什么要有锁机制

对于系统来说，数据是最核心的资产，需要提供强一致性和有效性。

当“数据库 + 并发读写访问”场景时，为保持强一致性和有效性，那么数据库中细分的数据区域，就是一个个的临界区。

为具备数据临界区的能力，就引入了数据库的锁机制。

（锁的理念和本质思想都是共性的，数据库锁只是将锁的运用做了场景化的适配优化）

2、锁的分类

MySQL官方文档：https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-intention-locks

数据库中是以“表”为一个数据体的单位，那么“表锁”是自然存在的一类锁

表锁可分为：

• 表 共享锁（S）
• 表 排他锁（X）
• 表 意向共享锁（IS）
• 表 意向排他锁（IX）
• 自增锁

行锁可分为：

• 行 共享锁 + 行 排他锁
  • 实现方式：记录锁 / 间隙锁 / 临键锁
• 行 插入意向锁

核心助读： 《《《《《《 重点！重点！重点！

1、先存在：表锁

2、然后为更好的并发需要，做了细粒度的锁：行锁

3、为了表锁和行锁间更高效的配合，出现了：意向锁

4、为了针对范围型数据操作的有效性，出现了：间隙锁、临键锁

5、为了插入查询并发的优化，出现了：插入意向锁

3、锁详细介绍

3.1、表-共享锁（S）

用途： 表共享锁之间不互斥，读读可以并行。

原理： N/A

操作方式：

• 自动（默认）
  • MySQL的执行优化判断（锁升级）：1、需要更新大部分数据时，不走索引的全表扫；2、事务涉及多个表，比较复杂，很可能引起死锁的；
• 手动
  • lock table tableName read;
  • unlock table tableName;
  • select * from XX lock in share mode的锁升级

3.2、表-排他锁（X）

用途： 表排他锁与任何锁都互斥，写读和谢谢都不可并行。

原理： N/A

操作方式：

• 自动（默认）
  • insert / update / delete 的锁升级
• 手动
  • lock table tableName write;
  • unlock table tableName;
  • select * from XX for update的锁升级

3.3、表-自增锁（Auto-Inc Lock）

自增锁是一种特殊的表级锁。

专门针对事务插入AUTO_INCREMENT类型的列。

最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

MySQL官方解释：
AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.

In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

举个例子：表中有如下三条数据，id为AUTO_INCREMENT：

1, zhangsan
2, lisi
3, wangwu

事务A先执行，但还未提交： insert into t(name) values(‘lindan’);

事务B后执行： insert into t(name) values(‘lizongwei’);

此时事务B插入操作会阻塞，直到事务A提交。

3.4、行 - 记录锁（Record Lock）

（同时支持“共享锁和排他锁”，根据SQL语义区分）

行记录锁是作用在索引记录（Key）上的锁。（B+树上的Key节点）

语义上是“锁定一行数据”，做并发操作的保护。

注意点：

1、记录锁是作用在索引上的锁，如果建表时候没有设置任何一个索引，InnoDB引擎会使用隐式的主键来进行锁定。

2、InnoDB行锁是作用在索引上的锁，只有查询走索引时才会用到。如果SQL查询时没有使用任何一个索引，则会升级为“表锁”。

3、InnoDB行锁是作用在索引上的锁，即使是访问不同行的数据记录，但如果使用的是同一个索引键（KEY），会发生锁冲突。

4、如果数据表建有多个索引，可以通过不同的索引锁定不同的行。

常用SQL：

select ... for update;

or

select ... lock