MySQL InnoDB 锁机制

经典问题

Q：能说说MySQL InnoDB 有几种锁吗？能不能结合不同的事务隔离级别，说明一下数据库是如何加锁的？

Q：结合几种锁，在某种隔离级别下，能否举出一个死锁的例子？

A: 下文锁简介与实验，提供详细解答，选择一两种自己熟悉的情况记住，回答即可。

追根溯源

Mysql锁简介

1、共享锁/排他锁

InnoDB
implements standard row-level locking where there are two types of locks, shared (S
) locks and exclusive (X
) locks.

• A shared (S
  ) lock permits the transaction that holds the lock to read a row.

• An exclusive (X
  ) lock permits the transaction that holds the lock to update or delete a row.

说明：
    1、共享锁和排它锁都是行级锁
    2、共享锁在事务进行读记录时触发
    3、排它锁在事务进行更新和删除记录时触发

排它锁与共享锁之间关系如下图：

2、意向锁

InnoDB
supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES ... WRITE
takes an exclusive lock (an X
lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB
uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:

• An intention shared lock (IS
  ) indicates that a transaction intends to set a shared lock on individual rows in a table.

• An intention exclusive lock (IX
  ) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

For example, SELECT ... FOR SHARE
sets an IS
lock, and SELECT ... FOR UPDATE
sets an IX
lock.

The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

说明：
    1、 InnoDB可支持包括表级锁和行级锁在内的不同级别锁粒度控制。
    2、支持行锁与表锁共存，因而能够进行锁的粒度分级，提高并发读写的性能。

The intention locking protocol is as follows:  

• Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS
  lock or stronger on the table.

• Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX
  lock on the table.

说明：
    1、获得共享锁前必须获得共享意向锁或者更高级锁
    2、获得排它锁前必须获得意向排它锁或者更高级锁
具体意向锁、排它锁、共享锁之间的关系如下图：

#两个事务都要写的时候，冲突了锁等待。（不冲突 分别获取IX 与 对应行锁/间隙锁/临键锁 更新/删除/插入即可）
---TRANSACTION 103180, ACTIVE 29 sec
TABLE LOCK table `test`.`t` trx id 103180 lock mode IX
RECORD LOCKS space id 42 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 103180 lock_mode X waiting


---TRANSACTION 103179, ACTIVE 29 sec
TABLE LOCK table `test`.`t` trx id 103179 lock mode IX
RECORD LOCKS space id 42 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 103179 lock_mode X

3、行锁

A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

说明：
    1、行锁一定是作用在索引上的
    2、当不存在索引时，默认创建索引，并在该索引上加锁

4、间隙锁

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record。

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)

Gap locks in InnoDB
are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

说明：
    1、间隙锁是加在两条索引记录之间，来避免其他事务在间隙中添加记录
    2、当使用唯一索引能够确定某个特定记录时，并不需要间隙锁
    3、同一间隙，可以被不同事务多次加锁，且能够共存。
间隙锁共存情况：两事务均对（10,20）间隙加锁，并同时插入造成死锁，如下图所示：
+----+------+
| id | name |
+----+------+
| 10 | 10   |
+----+------+
| 20 | 20   |
+----+------+
| 30 | 30   |
+----+------+
锁类型：lock_mode X locks gap before rec

5、临键锁

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

行锁+间隙锁（before index）

建表语句
create table t(id int default null,name char(20) default null);
insert into t values(10,'10'),(20,'20'),(30,'30');
表记录
+----+------+
| id | name |
+----+------+
| 10 | 10   |
+----+------+
| 20 | 20   |
+----+------+
| 30 | 30   |
+----+------+
select * from t for update;
lock_mode X 临键锁（间隙锁+行锁）
    (negative infinity, 10]
    (10, 20]
    (20, 30]
    (30, positive infinity)

#日志如下
TABLE LOCK table `test`.`t` trx id 103178 lock mode IX
RECORD LOCKS space id 42 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 103178 lock_mode X;;

6、插入意向锁

An insert intention lock is a type of gap lock set by INSERT
operations prior to row insertion.

说明：
    1、插入意向锁是一种特殊的间隙锁，只用于并发插入操作。
    2、插入意向锁锁住的就是一个点，插入意向锁与间隙锁重叠，则互斥。

mysql> begin ;
mysql> select * from t2 where id >20 for update;
+----+------+
| id | name |
+----+------+
| 30 | c   |
+----+------+
1 row in set (0.00 sec)

mysql> begin;
mysql> insert into t2 values(25,'q');
#锁等待
RECORD LOCKS space id 43 page no 3 n bits 72 index PRIMARY of table `test`.`t2` trx id 103691 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000001e; asc     ;;
1: len 6; hex 0000000192c6; asc       ;;
2: len 7; hex e5000001e00128; asc       ;;
3: len 20; hex 6320202020202020202020202020202020202020; asc c    ;;

总结：

1、增：插入意向锁、

     删/改：排他锁、

     查：共享锁

    涵盖了常用的增删改查四个动作。

2、设置排它锁时，根据事务隔离级别、查询条件（是否唯一主键与索引）分别设置间隙锁、临键锁、行锁。

参考文档：

1、MySQL 8.0 参考手册 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

2、《MySQL技术内幕》4.3节4.4节