InnoDB是基于事务，用来锁定的对象是数据库中的对象，如表、页、行。一般锁仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）
可以通过information_schema库中的innodb_trx,innodb_locks, innodb_lock_waits来观察锁的信息
MySQL8里已经删除了
information_schema中的innodb_locks,innodb_lock_waits，改由performance_schema库中的data_lock和data_lock_waits
表锁：操作对象是数据表。Mysql大多数锁策略都支持，是系统开销最低但并发性最低的一个锁策略。事务t对整个表加读锁，则其他事务可读不可写，若加写锁，则其他事务增删改都不行。
行级锁：操作对象是数据表中的一行。是MVCC技术用的比较多的。行级锁对系统开销较大，但处理高并发较好。
InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB将使用表锁，也就是说，InnoDB的行锁是基于索引的
- 记录锁
- 共享/排它锁
- 意向锁
- 间隙锁
- 临键锁
- 插入意向锁
- 自增锁
1. 共享/排他锁
共享锁：也叫读锁、S锁（Shared Locks），若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S 锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
排他锁：又称写锁、X锁(Exclusive Locks)。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
所以我们说S锁和S锁是兼容的，S锁和X锁是不兼容的，X锁和X锁也是不兼容的
2. 表锁
测试表
CREATE TABLE USER (
id INT(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
username VARCHAR(20) NOT NULL COMMENT '用户名',
nickname VARCHAR(32) NOT NULL COMMENT '昵称',
gender TINYINT(4) NOT NULL COMMENT '1-男 2-女',
age INT(11) NOT NULL COMMENT '年龄',
PRIMARY KEY (id)
);
读锁
LOCK TABLES user READ;
查看锁
mysql> select * from performance_schema.data_locks;
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE|LOCK_STATUS|LOCK_DATA|
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
|INNODB|140622813695496:1168:140622732307136|422097790406152 |64 |30 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |S |GRANTED |NULL |
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
可以看到事务在表上增加了S锁，这是其他事务无法获得写锁
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 for update;
// 等待锁
写锁
mysql> LOCK TABLES user WRITE;
Query OK, 0 rows affected (0.00 sec)
查看锁
mysql> select * from performance_schema.data_locks;
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE|LOCK_STATUS|LOCK_DATA|
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
|INNODB|140622813695496:1168:140622732307136|25160178 |64 |33 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |X |GRANTED |NULL |
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
3. 记录锁
记录锁(Record-Lock)是锁住记录的，这里要说明的是这里锁住的是索引记录，而不是我们真正的数据记录：
- 如果锁的是非主键索引，会在自己的索引上面加锁之后然后再去主键上面加锁锁住。
- 如果没有表上没有索引(包括没有主键)，则会使用隐藏的主键索引进行加锁。
- 如果要锁的列没有索引，则会进行全表记录加锁。
3.1. 读操作
对读取的记录加S锁：
SELECT ... LOCK IN SHARE MODE;
示例
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 lock in share mode;
+----+-----------+-----------+--------+-----+
| id | username | nickname | gender | age |
+----+-----------+-----------+--------+-----+
| 1 | username0 | nickname0 | 1 | 10 |
+----+-----------+-----------+--------+-----+
1 row in set (0.00 sec)
查看锁
mysql> select * from performance_schema.data_locks;
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|INNODB|140622813692952:1168:140622732288576 |422097790403608 |61 |25 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IS |GRANTED |NULL |
|INNODB|140622813692952:111:5:2:140622732285472|422097790403608 |61 |25 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |S,REC_NOT_GAP|GRANTED |1 |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
可以看到事务422097790403608获取了两个锁，分别是表上的IS锁（意向共享锁），和记录上的S锁（共享锁）
另一个事务依然可以获取该记录的S锁
mysql> select * from user where id = 1 lock in share mode;
+----+-----------+-----------+--------+-----+
| id | username | nickname | gender | age |
+----+-----------+-----------+--------+-----+
| 1 | username0 | nickname0 | 1 | 10 |
+----+-----------+-----------+--------+-----+
1 row in set (0.00 sec)
查看锁
mysql> select * from performance_schema.data_locks;
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|INNODB|140622813692952:1168:140622732288576 |422097790403608 |61 |100 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IS |GRANTED |NULL |
|INNODB|140622813692952:111:5:301:140622732285472|422097790403608 |61 |100 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |S,REC_NOT_GAP|GRANTED |1 |
|INNODB|140622813695496:1168:140622732307136 |422097790406152 |64 |48 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |IS |GRANTED |NULL |
|INNODB|140622813695496:111:5:301:140622732304144|422097790406152 |64 |48 |ed_test |user |NULL |NULL |PRIMARY |140622732304144 |RECORD |S,REC_NOT_GAP|GRANTED |1 |
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
对读取的记录加X锁：
SELECT ... FOR UPDATE;
示例
mysql> set session autocommit = false;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 for update;
+----+-----------+-----------+--------+-----+
| id | username | nickname | gender | age |
+----+-----------+-----------+--------+-----+
| 1 | username0 | nickname0 | 1 | 10 |
+----+-----------+-----------+--------+-----+
1 row in set (0.00 sec)
查看锁
mysql> select * from performance_schema.data_locks;
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|INNODB|140622813692952:1168:140622732288576 |25160133 |61 |31 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:111:5:2:140622732285472|25160133 |61 |31 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X,REC_NOT_GAP|GRANTED |1 |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
可以看到事务25160133获取了两个锁，分别是表上的IX锁（意向排他锁），和记录上的X锁（排他锁）
其他事务无法获取该记录的X锁和S锁
事务2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 lock in share mode;
// 等待锁
事务3
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user where id = 1 for update;
// 等待锁
查看锁
mysql> select * from performance_schema.data_locks;
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|INNODB|140622813696344:1168:140622732313248 |25160182 |66 |21 |ed_test |user |NULL |NULL |NULL |140622732313248 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813696344:111:5:301:140622732310256|25160182 |66 |21 |ed_test |user |NULL |NULL |PRIMARY |140622732310256 |RECORD |X,REC_NOT_GAP|WAITING |1 |
|INNODB|140622813695496:1168:140622732307136 |25160181 |64 |54 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |IS |GRANTED |NULL |
|INNODB|140622813695496:111:5:301:140622732304488|25160181 |64 |55 |ed_test |user |NULL |NULL |PRIMARY |140622732304488 |RECORD |S,REC_NOT_GAP|WAITING |1 |
|INNODB|140622813692952:1168:140622732288576 |25160180 |61 |106 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:111:5:301:140622732285472|25160180 |61 |106 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X,REC_NOT_GAP|GRANTED |1 |
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
查看锁的等待情况
mysql> select * from performance_schema.data_lock_waits;
+------+-----------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+-----------------------------------------+------------------------------+------------------+-----------------+------------------------------+
|ENGINE|REQUESTING_ENGINE_LOCK_ID |REQUESTING_ENGINE_TRANSACTION_ID|REQUESTING_THREAD_ID|REQUESTING_EVENT_ID|REQUESTING_OBJECT_INSTANCE_BEGIN|BLOCKING_ENGINE_LOCK_ID |BLOCKING_ENGINE_TRANSACTION_ID|BLOCKING_THREAD_ID|BLOCKING_EVENT_ID|BLOCKING_OBJECT_INSTANCE_BEGIN|
+------+-----------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+-----------------------------------------+------------------------------+------------------+-----------------+------------------------------+
|INNODB|140622813696344:111:5:301:140622732310256|25160182 |66 |21 |140622732310256 |140622813695496:111:5:301:140622732304488|25160181 |64 |55 |140622732304488 |
|INNODB|140622813696344:111:5:301:140622732310256|25160182 |66 |21 |140622732310256 |140622813692952:111:5:301:140622732285472|25160180 |61 |106 |140622732285472 |
|INNODB|140622813695496:111:5:301:140622732304488|25160181 |64 |55 |140622732304488 |140622813692952:111:5:301:140622732285472|25160180 |61 |106 |140622732285472 |
+------+-----------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+-----------------------------------------+------------------------------+------------------+-----------------+------------------------------+
3.2. 写操作
DELETE
对一条记录做DELETE操作的过程其实是先在B+树中定位到这条记录的位置，然后获取一下这条记录的X锁，然后再执行delete mark操作。我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取X锁的锁定读。
示例
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from user where id = 1;
Query OK, 1 row affected (0.00 sec)
查看锁
mysql> select * from performance_schema.data_locks;
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|INNODB|140622813692952:1168:140622732288576 |25160134 |61 |37 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:111:5:2:140622732285472|25160134 |61 |37 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X,REC_NOT_GAP|GRANTED |1 |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
UPDATE
在对一条记录做UPDATE操作时分为三种情况：
- 如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后在原记录的位置进行修改操作。其实我们也可以把这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读。
- 如果未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在B+树中定位到这条记录的位置，然后获取一下记录的X锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由INSERT操作提供的隐式锁进行保护。
- 如果修改了该记录的键值，则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行了。
未修改键值，且更新的列所占用的存储空间未发生变化
mysql> update user set age = 11 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看锁
mysql> select * from performance_schema.data_locks;
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|INNODB|140622813692952:1168:140622732288576 |25160136 |61 |43 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:111:5:2:140622732285472|25160136 |61 |43 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X,REC_NOT_GAP|GRANTED |1 |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
INSERT
一般情况下，新插入一条记录的操作并不加锁，InnoDB通过隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问，当然在一些特殊情况下INSERT操作也是会获取锁的。
示例
mysql> insert user(username, nickname, gender, age) values('username2000000', 'nickname2000000', 1, 35);
Query OK, 1 row affected (0.00 sec)
查看锁
mysql> select * from performance_schema.data_locks;
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE|LOCK_STATUS|LOCK_DATA|
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
|INNODB|140622813692952:1168:140622732288576|25160142 |61 |62 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
可以看到insert仅在表上增加了IX锁
在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。
另外，在对某个表执行一些诸如ALTER TABLE、DROP TABLE这类的DDL语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞，同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server层使用一种称之为元数据锁（Metadata Locks，简称MDL）来实现的，一般情况下也不会使用InnoDB存储引擎自己提供的表级别的S锁和X锁。
4. 意向锁
InnoDB支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上的加锁操作，InnoDB提供了意向锁
我们对一个表可以加S锁和X锁。如果一个事务给表加了S锁，别的事务可以继续获得该表的S锁，也可以继续获得该表中的某些记录的S锁，但不能获取该表的X锁和某些记录的X锁；如果一个事务给表加了X锁，别的事务不可以继续获得该表的S锁和X锁，也不可以继续获得该表中的某些记录的S锁和X锁。
如果我们想获取表上的S锁，首先要保证表和表中的记录没有X锁，同样，如果我们想获取表上的X锁，我们要保证表和表中没有S锁或X锁。InnoDB通过意向锁来避免遍历表中的数据判断记录是否存在已经上锁的记录。
IS锁：意向共享锁(Intention Shared Lock)，当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁
IX锁：意向排他锁(Intention Exclusive Lock)，当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁
IS、IX锁是表级锁，而InnoDB支持的是行级别的锁，因此意向锁是不会阻塞除全表扫描以外的任何请求。所以IS、IX是兼容的。这一块可能会让人理解有点绕。
IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突，简单来说，IX,IS是用于事务获取表锁时候判断数据行是否存在锁。它并不会影响行锁
示例
事务 A 先获取了某一行的X锁：
mysql> select * from user where id = 1 for update;
+----+-----------+-----------+--------+-----+
| id | username | nickname | gender | age |
+----+-----------+-----------+--------+-----+
| 1 | username0 | nickname0 | 1 | 10 |
+----+-----------+-----------+--------+-----+
1 row in set (0.00 sec)
事务 B 想要获取表的S锁，检测到A持有IX锁，该请求被阻塞
LOCK TABLES user READ;
事务 C 也想获取 表中某一行的X锁，意向锁兼容，ID=5不存在锁，所以可以获取到X锁：
SELECT * FROM users WHERE id = 5 FOR UPDATE;
查看锁
mysql> select * from performance_schema.data_locks;
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|INNODB|140622813696344:1168:140622732313248 |25160176 |66 |15 |ed_test |user |NULL |NULL |NULL |140622732313248 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813696344:111:5:3:140622732310256 |25160176 |66 |15 |ed_test |user |NULL |NULL |PRIMARY |140622732310256 |RECORD |X,REC_NOT_GAP|GRANTED |2 |
|INNODB|140622813692952:1168:140622732288576 |25160175 |61 |81 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:111:5:301:140622732285472|25160175 |61 |81 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X,REC_NOT_GAP|GRANTED |1 |
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
在事务A，C释放锁后，事务B才能获取到锁
mysql> select * from performance_schema.data_locks;
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE|LOCK_STATUS|LOCK_DATA|
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
|INNODB|140622813695496:1168:140622732307136|422097790406152 |64 |25 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |S |GRANTED |NULL |
+------+------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
5. 间隙锁GAP
当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。
值得注意的是：间隙锁只会在Repeatable read隔离级别下使用 为什么呢?^_^
例子：假如user表中只有101条记录，其中id的值分别是1,2,…,100,101
select * from emp where empid > 100 for update;
上面是一个范围查询，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE|LOCK_STATUS|LOCK_DATA |
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
|INNODB|140622813692952:1169:140622732288576 |25160357 |61 |128 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:112:4:1:140622732285472 |25160357 |61 |128 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X |GRANTED |supremum pseudo-record|
|INNODB|140622813692952:112:4:102:140622732285472|25160357 |61 |128 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X |GRANTED |101 |
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
可以看到有一个X锁的LOCK_DATA未supremum pseudo-record，表示大于所有的索引值
此时在另一个事务插入ID为102的数据会卡住，等待X锁的释放。
mysql> insert user(username, nickname, gender, age) values('username102', 'nickname102', 1, 35);
// 等待锁
mysql> select * from performance_schema.data_locks;
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+------------------+-----------+----------------------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA |
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+------------------+-----------+----------------------+
|INNODB|140622813695496:1169:140622732307136 |25160358 |64 |61 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813695496:112:4:1:140622732304144 |25160358 |64 |61 |ed_test |user |NULL |NULL |PRIMARY |140622732304144 |RECORD |X,INSERT_INTENTION|WAITING |supremum pseudo-record|
|INNODB|140622813692952:1169:140622732288576 |25160357 |61 |128 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:112:4:1:140622732285472 |25160357 |61 |128 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X |GRANTED |supremum pseudo-record|
|INNODB|140622813692952:112:4:102:140622732285472|25160357 |61 |128 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X |GRANTED |101 |
+------+-----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+------------------+-----------+----------------------+
查看等待锁的状态
mysql> select * from performance_schema.data_lock_waits;
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
|ENGINE|REQUESTING_ENGINE_LOCK_ID |REQUESTING_ENGINE_TRANSACTION_ID|REQUESTING_THREAD_ID|REQUESTING_EVENT_ID|REQUESTING_OBJECT_INSTANCE_BEGIN|BLOCKING_ENGINE_LOCK_ID |BLOCKING_ENGINE_TRANSACTION_ID|BLOCKING_THREAD_ID|BLOCKING_EVENT_ID|BLOCKING_OBJECT_INSTANCE_BEGIN|
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
|INNODB|140622813695496:112:4:1:140622732304144|25160358 |64 |61 |140622732304144 |140622813692952:112:4:1:140622732285472|25160357 |61 |128 |140622732285472 |
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
可以看到事务25160358正在等待140622813692952:112:4:1:140622732285472 锁的释放，而这个锁是被事务25160357持有的间隙锁
InnoDB使用间隙锁的目的有两个：
- 为了防止幻读，Repeatable read隔离级别下再通过GAP锁即可避免了幻读，因为在事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上锁
- 满足恢复和复制的需要MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读
间隙锁要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
5.1. 案例1
准备数据
INSERT INTO user (id, username, nickname, gender, age) VALUES (1, 'username0', 'nickname0', 1, 10);
INSERT INTO user (id, username, nickname, gender, age) VALUES (2, 'username1', 'nickname1', 2, 11);
INSERT INTO user (id, username, nickname, gender, age) VALUES (3, 'username2', 'nickname2', 1, 12);
INSERT INTO user (id, username, nickname, gender, age) VALUES (4, 'username3', 'nickname3', 2, 13);
事务A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id between 10 and 30 for update;
Empty set (0.00 sec)
可以看到持有了大于所有索引值的间隙锁
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE|LOCK_STATUS|LOCK_DATA |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
|INNODB|140622813692952:1170:140622732288576 |25160389 |61 |137 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:113:4:1:140622732285472|25160389 |61 |137 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X |GRANTED |supremum pseudo-record|
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
事务B
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert user(username, nickname, gender, age) values('username5', 'nickname5', 1, 35);
// 等待锁
查看此时的锁
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+------------------+-----------+----------------------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+------------------+-----------+----------------------+
|INNODB|140622813695496:1170:140622732307136 |25160390 |64 |67 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813695496:113:4:1:140622732304144|25160390 |64 |67 |ed_test |user |NULL |NULL |PRIMARY |140622732304144 |RECORD |X,INSERT_INTENTION|WAITING |supremum pseudo-record|
|INNODB|140622813692952:1170:140622732288576 |25160389 |61 |137 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:113:4:1:140622732285472|25160389 |61 |137 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X |GRANTED |supremum pseudo-record|
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+------------------+-----------+----------------------+
事务B在等待事务A释放锁
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
|ENGINE|REQUESTING_ENGINE_LOCK_ID |REQUESTING_ENGINE_TRANSACTION_ID|REQUESTING_THREAD_ID|REQUESTING_EVENT_ID|REQUESTING_OBJECT_INSTANCE_BEGIN|BLOCKING_ENGINE_LOCK_ID |BLOCKING_ENGINE_TRANSACTION_ID|BLOCKING_THREAD_ID|BLOCKING_EVENT_ID|BLOCKING_OBJECT_INSTANCE_BEGIN|
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
|INNODB|140622813695496:113:4:1:140622732304144|25160390 |64 |67 |140622732304144 |140622813692952:113:4:1:140622732285472|25160389 |61 |137 |140622732285472 |
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
5.2. 案例2
表中的ID最大为17，缺少10，11，13，14
mysql> select * from user;
+----+------------+------------+--------+-----+
| id | username | nickname | gender | age |
+----+------------+------------+--------+-----+
| 1 | username0 | nickname0 | 1 | 10 |
| 2 | username1 | nickname1 | 2 | 11 |
| 3 | username2 | nickname2 | 1 | 12 |
| 4 | username3 | nickname3 | 2 | 13 |
| 5 | username4 | nickname4 | 1 | 14 |
| 6 | username5 | nickname5 | 2 | 15 |
| 7 | username6 | nickname6 | 1 | 16 |
| 8 | username7 | nickname7 | 2 | 17 |
| 9 | username8 | nickname8 | 1 | 18 |
| 12 | username11 | nickname11 | 2 | 21 |
| 15 | username14 | nickname14 | 1 | 24 |
| 16 | username15 | nickname15 | 2 | 25 |
| 17 | username16 | nickname16 | 1 | 26 |
+----+------------+------------+--------+-----+
13 rows in set (0.00 sec)
事务A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 19 for update;
Empty set (0.00 sec)
查看锁
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE|LOCK_STATUS|LOCK_DATA |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
|INNODB|140622813692952:1171:140622732288576 |25160430 |61 |143 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:114:4:1:140622732285472|25160430 |61 |143 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X |GRANTED |supremum pseudo-record|
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
id=19未匹配到任何数据,向左取得最靠近的值17作为左区间，向右由于没有记录因此取得无穷大作为右区间，因此，事务A的间隙锁的范围（17，无穷大）
5.3. 案例3
事务A
mysql> select * from user where id = 12 for update;
+----+------------+------------+--------+-----+
| id | username | nickname | gender | age |
+----+------------+------------+--------+-----+
| 12 | username11 | nickname11 | 2 | 21 |
+----+------------+------------+--------+-----+
1 row in set (0.00 sec)
查看锁
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|INNODB|140622813692952:1171:140622732288576 |25160431 |61 |146 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:114:4:11:140622732285472|25160431 |61 |146 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X,REC_NOT_GAP|GRANTED |12 |
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
id=12匹配到一条数据，事务仅持有记录锁12，不持有间隙锁
5.4. 案例4
事务A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 13 for update;
Empty set (0.00 sec)
查看锁状态
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE|LOCK_STATUS|LOCK_DATA|
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
|INNODB|140622813692952:1171:140622732288576 |25160432 |61 |152 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:114:4:12:140622732285472|25160432 |61 |152 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X,GAP |GRANTED |15 |
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
可以看到事务A持有了间隙锁(12,15)
此时事务B执行新增id=10或者id=18的操作可以成功，而id=14的会等待锁
mysql> INSERT INTO user (id, username, nickname, gender, age) VALUES (10, 'username10', 'nickname10', 2, 10);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO user (id, username, nickname, gender, age) VALUES (18, 'username18', 'nickname18', 1, 18);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO user (id, username, nickname, gender, age) VALUES (14, 'username14', 'nickname14', 1, 14);
查看锁
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
|INNODB|140622813695496:1171:140622732307136 |25160433 |64 |73 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813695496:114:4:12:140622732304144|25160433 |64 |75 |ed_test |user |NULL |NULL |PRIMARY |140622732304144 |RECORD |X,GAP,INSERT_INTENTION|WAITING |15 |
|INNODB|140622813692952:1171:140622732288576 |25160432 |61 |152 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:114:4:12:140622732285472|25160432 |61 |152 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X,GAP |GRANTED |15 |
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
5.5. 案例5
事务A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 13 for update;
Empty set (0.00 sec)
事务B
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set id = 18 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set id = 13 where id = 18;
// 等待锁
总结一下：间隙锁的提出仅仅是为了防止插入幻影记录而提出的（防止其他事务在间隔中插入数据），RR隔离级别就是通过间隙锁来减少幻读的发生
6. 临键锁
Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。
Next-Key Lock可以说是记录锁(Record Lock)和间隙锁（Gap Lock）的组合，既封锁了”缝隙”，又封锁了索引本身。
假设有如下表：
CREATE TABLE USER (
id INT(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
username VARCHAR(20) NOT NULL COMMENT '用户名',
nickname VARCHAR(32) NOT NULL COMMENT '昵称',
gender TINYINT(4) NOT NULL COMMENT '1-男 2-女',
age INT(11) NOT NULL COMMENT '年龄',
PRIMARY KEY (id),
KEY idx_age(age)
);
INSERT INTO ed_test.user (id, username, nickname, gender, age) VALUES (1, 'username0', 'nickname0', 1, 10);
INSERT INTO ed_test.user (id, username, nickname, gender, age) VALUES (3, 'username2', 'nickname2', 2, 24);
INSERT INTO ed_test.user (id, username, nickname, gender, age) VALUES (5, 'username4', 'nickname4', 2, 32);
INSERT INTO ed_test.user (id, username, nickname, gender, age) VALUES (7, 'username6', 'nickname6', 1, 45);
该表中 age 列潜在的临键锁有：
(-∞, 10],
(10, 24],
(24, 32],
(32, 45],
(45, +∞],
事务A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age = 24 for update;
+----+-----------+-----------+--------+-----+
| id | username | nickname | gender | age |
+----+-----------+-----------+--------+-----+
| 3 | username2 | nickname2 | 2 | 24 |
+----+-----------+-----------+--------+-----+
1 row in set (0.00 sec)
查看锁状态：
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
|INNODB|140622813692952:1172:140622732288576 |25160476 |61 |177 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:115:5:3:140622732285472|25160476 |61 |177 |ed_test |user |NULL |NULL |idx_age |140622732285472 |RECORD |X |GRANTED |24, 3 |
|INNODB|140622813692952:115:4:3:140622732285816|25160476 |61 |177 |ed_test |user |NULL |NULL |PRIMARY |140622732285816 |RECORD |X,REC_NOT_GAP|GRANTED |3 |
|INNODB|140622813692952:115:5:4:140622732286160|25160476 |61 |177 |ed_test |user |NULL |NULL |idx_age |140622732286160 |RECORD |X,GAP |GRANTED |32, 5 |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+-------------+-----------+---------+
可以看到事务A持有了3个记录锁
- 索引
idx_age上记录24的X锁 - 聚集索引上的记录3的X锁
- 索引
idx_age上记录（24，32]的间隙锁
6.1. 案例1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO user (username, nickname, gender, age) VALUES ('username24', 'nickname24', 2, 26);
// 等待锁
可以看到事务B正在等待事务A释放间隙锁
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
|INNODB|140622813695496:1172:140622732307136 |25160477 |64 |95 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813695496:115:5:4:140622732304144|25160477 |64 |95 |ed_test |user |NULL |NULL |idx_age |140622732304144 |RECORD |X,GAP,INSERT_INTENTION|WAITING |32, 5 |
|INNODB|140622813692952:1172:140622732288576 |25160476 |61 |177 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:115:5:3:140622732285472|25160476 |61 |177 |ed_test |user |NULL |NULL |idx_age |140622732285472 |RECORD |X |GRANTED |24, 3 |
|INNODB|140622813692952:115:4:3:140622732285816|25160476 |61 |177 |ed_test |user |NULL |NULL |PRIMARY |140622732285816 |RECORD |X,REC_NOT_GAP |GRANTED |3 |
|INNODB|140622813692952:115:5:4:140622732286160|25160476 |61 |177 |ed_test |user |NULL |NULL |idx_age |140622732286160 |RECORD |X,GAP |GRANTED |32, 5 |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
|ENGINE|REQUESTING_ENGINE_LOCK_ID |REQUESTING_ENGINE_TRANSACTION_ID|REQUESTING_THREAD_ID|REQUESTING_EVENT_ID|REQUESTING_OBJECT_INSTANCE_BEGIN|BLOCKING_ENGINE_LOCK_ID |BLOCKING_ENGINE_TRANSACTION_ID|BLOCKING_THREAD_ID|BLOCKING_EVENT_ID|BLOCKING_OBJECT_INSTANCE_BEGIN|
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
|INNODB|140622813695496:115:5:4:140622732304144|25160477 |64 |95 |140622732304144 |140622813692952:115:5:4:140622732286160|25160476 |61 |177 |140622732286160 |
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
6.2. 案例2
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO user (username, nickname, gender, age) VALUES ('username10', 'nickname10', 2, 10);
// 等待锁
查看锁状态
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
|INNODB|140622813695496:1172:140622732307136 |25160478 |64 |102 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813695496:115:5:3:140622732304144|25160478 |64 |102 |ed_test |user |NULL |NULL |idx_age |140622732304144 |RECORD |X,GAP,INSERT_INTENTION|WAITING |24, 3 |
|INNODB|140622813692952:1172:140622732288576 |25160476 |61 |177 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:115:5:3:140622732285472|25160476 |61 |177 |ed_test |user |NULL |NULL |idx_age |140622732285472 |RECORD |X |GRANTED |24, 3 |
|INNODB|140622813692952:115:4:3:140622732285816|25160476 |61 |177 |ed_test |user |NULL |NULL |PRIMARY |140622732285816 |RECORD |X,REC_NOT_GAP |GRANTED |3 |
|INNODB|140622813692952:115:5:4:140622732286160|25160476 |61 |177 |ed_test |user |NULL |NULL |idx_age |140622732286160 |RECORD |X,GAP |GRANTED |32, 5 |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
|ENGINE|REQUESTING_ENGINE_LOCK_ID |REQUESTING_ENGINE_TRANSACTION_ID|REQUESTING_THREAD_ID|REQUESTING_EVENT_ID|REQUESTING_OBJECT_INSTANCE_BEGIN|BLOCKING_ENGINE_LOCK_ID |BLOCKING_ENGINE_TRANSACTION_ID|BLOCKING_THREAD_ID|BLOCKING_EVENT_ID|BLOCKING_OBJECT_INSTANCE_BEGIN|
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
|INNODB|140622813695496:115:5:3:140622732304144|25160478 |64 |102 |140622732304144 |140622813692952:115:5:3:140622732285472|25160476 |61 |177 |140622732285472 |
+------+---------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+---------------------------------------+------------------------------+------------------+-----------------+------------------------------+
可以看到事务C尝试获取(10,24]的间隙锁，而事务A缺持有记录24的X锁
还有不明白的地方
6.3. 案例3
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO user (username, nickname, gender, age) VALUES ('username32', 'nickname32', 2, 32);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO user (username, nickname, gender, age) VALUES ('username33', 'nickname33', 2, 33);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO user (username, nickname, gender, age) VALUES ('username9', 'nickname9', 1, 9);
Query OK, 1 row affected (0.00 sec)
6.4. 案例4
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set age = 9 where age = 10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
我们在案例2中插入一个age=10的记录会等待锁，而这里更新age=10却不会等待锁
为什么更新age=10 的数据的时候不会被阻塞，因为它在区间(10,32)之外，但是如果你 INSERT 了一条 age =10的数据，就要分情况了，因为行锁是依赖于索引的性质，如果你插入的数据位于已存在的 age = 10 的索引的后面，那就属于（10，32）这个区间，所以插入会被阻塞，比如 INSERT 一条 id = -1，age = 10 的记录行。
事务 A 在对 age 为 24 的列进行 UPDATE 操作的同时，也获取了 (24, 32] 这个区间内的临键锁。
注意：临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。
在根据非唯一索引对记录行进行 UPDATE , FOR UPDATE, LOCK IN SHARE MODE 操作时，InnoDB 会获取该记录行的 临键锁 ，并同时获取该记录行下一个区间的间隙锁。即：临键锁也包括间隙锁
所谓的next-key lock就是record lock + gap lock。如果for update/锁住的索引存在，则可以看成 左gap lock + record lock + 右gap lock （左gap lock + record lock = next - key lock）；如果不存在，那就是一个纯粹的gap lock。
临键锁锁住的是索引区间，而不是数据区间，就是说同样比如锁住了 1-9 这个索引区间，这是一个实际的范围，那么插入一条 1 的索引的时候，可能插入到该区间内（被阻塞），也可能会落在区间外（插入成功），这个是会受到一些其他的索引排序策略的影响的，所以到底是谁开谁闭，实际上是会受到排列顺序的影响的。（网上看到的，不明确）
对于临键锁的区间还有疑问没解决
7. 插入意向锁
一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了间隙锁，如果有的话，插入操作需要等待，直到拥有间隙锁的那个事务提交。 插入意向锁(Insert Intention Locks)它是插入之前由插入操作设置的间隙锁的一种类型。这个锁表示插入的意图，如果插入到同一索引间隙中的多个事务没有插入到间隙中的同一位置，那么它们就不需要等待对方。如果是同一位置，但不要因为行不冲突而相互阻塞。
插入意向锁实际上是一个间隙锁，不是意向锁，在insert时产生
- 普通的间隙锁不允许 在 （上一条记录，本记录） 范围内插入数据
- 插入意向锁允许 在 （上一条记录，本记录） 范围内插入数据
插入意向锁的作用是为了提高并发插入的性能， 多个事务 同时写入 不同数据至同一索引范围（区间）内，并不需要等待其他事务完成，不会发生锁等待。
插入的过程
假设现在有记录 10， 30， 50， 70 ；且为主键 ，需要插入记录 25 。
- 找到 小于等于25的记录 ，这里是 10
- 找到 记录10的下一条记录 ，这里是 30
- 判断 下一条记录30 上是否有锁
- 判断 30 上面如果 没有锁 ，则可以插入
- 判断 30 上面如果有Record Lock，则可以插入
- 判断 30 上面如果有Gap Lock/Next-Key Lock，则无法插入，因为锁的范围是 (10, 30) /（10, 30] ；在30上增加insert intention lock（ 此时处于waiting状态），当 Gap Lock / Next-Key Lock 释放时，等待的事务（ transaction）将被唤醒 ，此时 记录30 上才能获得 insert intention lock ，然后再插入记录25
总结一下: 如果即将插入的间隙已经被其他事务加了间隙锁，那么本次INSERT操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁
事务A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 13 for update;
Empty set (0.00 sec)
事务B
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO user (id, username, nickname, gender, age) VALUES (14, 'username14', 'nickname14', 2, 14);
查看锁状态
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
|INNODB|140622813695496:1173:140622732307136 |25160536 |64 |146 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813695496:116:4:11:140622732304144|25160536 |64 |146 |ed_test |user |NULL |NULL |PRIMARY |140622732304144 |RECORD |X,GAP,INSERT_INTENTION|WAITING |15 |
|INNODB|140622813692952:1173:140622732288576 |25160535 |61 |189 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:116:4:11:140622732285472|25160535 |61 |189 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X,GAP |GRANTED |15 |
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
可以看到记录15上增加了一个插入意向锁
注意：一个事务 insert 25 且没有提交，另一个事务 delete 25 时，记录25上会有 Record Lock
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE |LOCK_STATUS|LOCK_DATA|
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
|INNODB|140622813696344:1173:140622732313248 |25160542 |66 |53 |ed_test |user |NULL |NULL |NULL |140622732313248 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813696344:116:4:11:140622732310256|25160542 |66 |53 |ed_test |user |NULL |NULL |PRIMARY |140622732310256 |RECORD |X,GAP |GRANTED |15 |
|INNODB|140622813695496:1173:140622732307136 |25160541 |64 |156 |ed_test |user |NULL |NULL |NULL |140622732307136 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813695496:116:4:11:140622732304144|25160541 |64 |156 |ed_test |user |NULL |NULL |PRIMARY |140622732304144 |RECORD |X,GAP,INSERT_INTENTION|WAITING |15 |
|INNODB|140622813692952:1173:140622732288576 |25160535 |61 |189 |ed_test |user |NULL |NULL |NULL |140622732288576 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813692952:116:4:11:140622732285472|25160535 |61 |189 |ed_test |user |NULL |NULL |PRIMARY |140622732285472 |RECORD |X,GAP |GRANTED |15 |
+------+----------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+----------------------+-----------+---------+
8. 自增锁
在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：
select max(auto_inc_col) from t for update;
插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称为AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。
虽然AUTO-INC Locking从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成，虽然不用等待事务的完成。其次，对于INSERT….SELECT的大数据的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。
InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且从该版本开始，InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该参数的默认值为1。在继续讨论新的自增长实现方式之前，需要对自增长的插入进行分类。如下说明：
insert-like：指所有的插入语句，如INSERT、REPLACE、INSERT…SELECT，REPLACE…SELECT、LOAD DATA等。
simple inserts：指能在插入前就确定插入行数的语句，这些语句包括INSERT、REPLACE等。需要注意的是：simple inserts不包含INSERT…ON DUPLICATE KEY UPDATE这类SQL语句。
bulk inserts：指在插入前不能确定得到插入行数的语句，如INSERT…SELECT，REPLACE…SELECT，LOAD DATA。
mixed-mode inserts：指插入中有一部分的值是自增长的，有一部分是确定的。入INSERT INTO t1(c1,c2) VALUES(1,’a’),(2,’a’),(3,’a’)；也可以是指INSERT…ON DUPLICATE KEY UPDATE这类SQL语句。
接下来分析参数innodb_autoinc_lock_mode以及各个设置下对自增长的影响，其总共有三个有效值可供设定，即0、1、2，具体说明如下：
-
0：这是MySQL 5.1.22版本之前自增长的实现方式，即通过表锁的AUTO-INC Locking方式，在对有自增属性的字段插入记录时，会持续持有一个表级别的自增锁，直到语句执行结束为止。比如说下面两条语句，SQL 1 在执行期间，一直持有对表 f1 的表级自增锁，接下来 SQL 2 执行时锁超时。因为有了新的自增长实现方式，0这个选项不应该是新版用户的首选了
-
1：这是该参数的默认值，对于”simple inserts”，该值会用互斥量（mutex）去对内存中的计数器进行累加的操作。在分配新的 ID 过程中持有一个轻量级的互斥锁（线程级别，而不是事务级别），而不是直到语句结束才释放的表锁。对于”bulk inserts”，还是使用传统表锁的AUTO-INC Locking方式。在这种配置下，如果不考虑回滚操作，对于自增值列的增长还是连续的。并且在这种方式下，statement-based方式的replication还是能很好地工作。需要注意的是，如果已经使用AUTO-INC Locking方式去产生自增长的值，而这时需要再进行”simple inserts”的操作时，还是需要等待AUTO-INC Locking的释放。
-
2：在这个模式下，对于所有”INSERT-LIKE”自增长值的产生都是通过互斥量，而不是AUTO-INC Locking的方式。显然，这是性能最高的方式。然而，这会带来一定的问题，因为并发插入的存在，在每次插入时，自增长的值可能不是连续的。此外，最重要的是，基于Statement-Base Replication会出现问题。因此，使用这个模式，任何时候都应该使用row-base replication。这样才能保证最大的并发性能及replication主从数据的一致。
完全摘自《MySQL技术内幕 InnoDB存储引擎》
假如我们插入的数据中有AUTO_INCREMENT列，InnoDB在RR（Repeatable Read）隔离级别下，能解决幻读问题。
//事务A先执行，还未提交：
insert into t(name) values(xxx);
//事务B后执行：
insert into t(name) values(ooo);
- 事务A先执行insert，会得到一条(4, xxx)的记录，由于是自增列，InnoDB会自动增长，注意此时事务并未提交；
- 事务B后执行insert，假设不会被阻塞，那会得到一条(5, ooo)的记录；
- 事务A继续insert：会得到一条(6, xxoo)的记录。
- 事务A再select：select * from t where id>3，在mvvc中当然可以得到(4, xxx)和(6, xxoo)
这对于事务A来说，就很奇怪了，对于AUTO_INCREMENT的列，连续插入了两条记录，ID缺不是连续的
自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。与此同时，InnoDB提供了innodb_autoinc_lock_mode配置，可以调节与改变该锁的模式与行为。
9. insert|select|update|delete的锁
9.1. select
普通的select是快照读，而select ... for update或select ... in share mode则会根据情况加不同的锁
- 如果在唯一索引上用唯一的查询条件时（ where id=1），加记录锁
- 否则，其他的查询条件和索引条件，加间隙锁（BETWEEN AND ）或Next-Key 锁（可重复隔离级别）
示例，在没有索引的查询条件上增加锁
数据
+--+---------+---------+------+---+
|id|username |nickname |gender|age|
+--+---------+---------+------+---+
|1 |username0|nickname0|1 |10 |
|3 |username2|nickname2|2 |24 |
|5 |username4|nickname4|2 |32 |
|7 |username6|nickname6|1 |45 |
+--+---------+---------+------+---+
mysql> select * from user where nickname = 'nickname1' for update;
Empty set (0.00 sec)
mysql> select * from performance_schema.data_locks;
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
|ENGINE|ENGINE_LOCK_ID |ENGINE_TRANSACTION_ID|THREAD_ID|EVENT_ID|OBJECT_SCHEMA|OBJECT_NAME|PARTITION_NAME|SUBPARTITION_NAME|INDEX_NAME|OBJECT_INSTANCE_BEGIN|LOCK_TYPE|LOCK_MODE|LOCK_STATUS|LOCK_DATA |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
|INNODB|140622813691256:1174:140622732276320 |25160579 |104 |15 |ed_test |user |NULL |NULL |NULL |140622732276320 |TABLE |IX |GRANTED |NULL |
|INNODB|140622813691256:117:4:1:140622732273216|25160579 |104 |15 |ed_test |user |NULL |NULL |PRIMARY |140622732273216 |RECORD |X |GRANTED |supremum pseudo-record|
|INNODB|140622813691256:117:4:2:140622732273216|25160579 |104 |15 |ed_test |user |NULL |NULL |PRIMARY |140622732273216 |RECORD |X |GRANTED |1 |
|INNODB|140622813691256:117:4:3:140622732273216|25160579 |104 |15 |ed_test |user |NULL |NULL |PRIMARY |140622732273216 |RECORD |X |GRANTED |3 |
|INNODB|140622813691256:117:4:4:140622732273216|25160579 |104 |15 |ed_test |user |NULL |NULL |PRIMARY |140622732273216 |RECORD |X |GRANTED |5 |
|INNODB|140622813691256:117:4:5:140622732273216|25160579 |104 |15 |ed_test |user |NULL |NULL |PRIMARY |140622732273216 |RECORD |X |GRANTED |7 |
+------+---------------------------------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+----------------------+
可以看到表中所有记录都被加上了记录锁，还有一个间隙锁，相当于整个表都被加上了X锁
即使我们的查询条件能查到数据也是一样
mysql> select * from user where nickname = 'nickname2' for update;
+----+-----------+-----------+--------+-----+
| id | username | nickname | gender | age |
+----+-----------+-----------+--------+-----+
| 3 | username2 | nickname2 | 2 | 24 |
+----+-----------+-----------+--------+-----+
1 row in set (0.00 sec)
9.2. update与delete
- 如果在唯一索引上使用唯一的查询条件来update/delete，加记录锁
- 否则，符合查询条件的索引记录之前，都会加Next-Key 锁
注：如果update的是聚集索引，则对应的普通索引记录也会被隐式加锁，这是由InnoDB索引的实现机制决定的：普通索引存储PK的值，检索普通索引本质上要二次扫描聚集索引。
加锁逻辑
-
找到满足条件的记录，并且记录有效，则对记录加X锁，No Gap 锁(lock_mode X locks rec but not gap);
-
找到满足条件的记录，但是记录无效(标识为删除的记录)，则对记录加 next key 锁(同时锁住记录本身，以及记录之前的Gap：lock_mode X);
-
未找到满足条件的记录，则对第一个不满足条件的记录加 Gap 锁，保证没有满足条件的记录插入(locks gap before rec)
9.3. insert
insert和update与delete不同，它会用排它锁封锁被插入的索引记录，同时，会在插入区间加插入意向锁，但这个并不会真正封锁区间，也不会阻止相同区间的不同KEY插入。
在多事务并发写入不同数据记录至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。假设有一个索引记录包含键值 4 和 7，不同的事务分别插入 5 和 6，每个事务都会产生一个加在 4-7 之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。
An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
对于 insert 操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加上 S Next-key Lock。从这里会发现，即使是 RC 事务隔离级别，也同样会存在 Next-Key Lock 锁，从而阻塞并发。对于检测到冲突的唯一索引，等待线程在获得S Lock之后，还需要对下一个记录进行加锁。
CREATE TABLE t8 ( a INT AUTO_INCREMENT PRIMARY KEY, b INT, c INT, UNIQUE KEY ub ( b ) ) ENGINE = INNODB;
insert into t8 values (NULL,1,2);
T1
mysql> delete from t8 where b = 1;
Query OK, 1 row affected (0.00 sec)
查看锁
---TRANSACTION 1379266, ACTIVE 3 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
mysql> select * from data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139920103509400:1110:139920035361616 | 1379266 | 1375327 | 102 | test | t8 | NULL | NULL | NULL | 139920035361616 | TABLE | IX | GRANTED | NULL |
| INNODB | 139920103509400:51:5:2:139920035358512 | 1379266 | 1375327 | 102 | test | t8 | NULL | NULL | ub | 139920035358512 | RECORD | X,REC_NOT_GAP | GRANTED | 1, 1 |
| INNODB | 139920103509400:51:4:2:139920035358856 | 1379266 | 1375327 | 102 | test | t8 | NULL | NULL | PRIMARY | 139920035358856 | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)
我们可以看到 delete 语句获取了唯一索引 ub 和主键两个行级锁(lock_mode X locks rec but not gap) 。
T2
mysql> insert into t8 values (NULL,1,2);
// 等待锁
再次查看锁
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 1375191, OS thread handle 139919972570880, query id 4126113 localhost root update
insert into t8 values (NULL,1,2)
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 51 page no 5 n bits 72 index ub of table `test`.`t8` trx id 1379271 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
mysql> select * from data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139920103510256:1110:139920035367776 | 1379271 | 1375328 | 88 | test | t8 | NULL | NULL | NULL | 139920035367776 | TABLE | IX | GRANTED | NULL |
| INNODB | 139920103510256:51:5:2:139920035364672 | 1379271 | 1375328 | 88 | test | t8 | NULL | NULL | ub | 139920035364672 | RECORD | S | WAITING | 1, 1 |
| INNODB | 139920103509400:1110:139920035361616 | 1379266 | 1375327 | 102 | test | t8 | NULL | NULL | NULL | 139920035361616 | TABLE | IX | GRANTED | NULL |
| INNODB | 139920103509400:51:5:2:139920035358512 | 1379266 | 1375327 | 102 | test | t8 | NULL | NULL | ub | 139920035358512 | RECORD | X,REC_NOT_GAP | GRANTED | 1, 1 |
| INNODB | 139920103509400:51:4:2:139920035358856 | 1379266 | 1375327 | 102 | test | t8 | NULL | NULL | PRIMARY | 139920035358856 | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)
insert into t8 values (NULL,1,2);在申请一把 S Next-key-Lock 显示 lock mode S waiting
Innodb 日志中如果提示 lock mode S/lock mode X ，其实都是 gap 锁，如果是行记录锁会提示 but not gap
T1提交后，再次查看锁
---TRANSACTION 1379271, ACTIVE 181 sec
3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
mysql> select * from data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
| INNODB | 139920103510256:1110:139920035367776 | 1379271 | 1375328 | 88 | test | t8 | NULL | NULL | NULL | 139920035367776 | TABLE | IX | GRANTED | NULL |
| INNODB | 139920103510256:51:5:1:139920035364672 | 1379271 | 1375328 | 88 | test | t8 | NULL | NULL | ub | 139920035364672 | RECORD | S | GRANTED | supremum pseudo-record |
| INNODB | 139920103510256:51:5:3:139920035365016 | 1379271 | 1375328 | 88 | test | t8 | NULL | NULL | ub | 139920035365016 | RECORD | S,GAP | GRANTED | 1, 2 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+
3 rows in set (0.00 sec)
从获取锁的状态上看 insert 获取一把 S Next-key Lock 锁和插入行之前的 S GAP 锁。
INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.
会对 insert 成功的记录加上一把X行锁，但是没看见
T2
update t8 set c=13 where b=1;
查看锁
---TRANSACTION 1379271, ACTIVE 257 sec
5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2
mysql> select * from data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+
| INNODB | 139920103510256:1110:139920035367776 | 1379271 | 1375328 | 88 | test | t8 | NULL | NULL | NULL | 139920035367776 | TABLE | IX | GRANTED | NULL |
| INNODB | 139920103510256:51:5:1:139920035364672 | 1379271 | 1375328 | 88 | test | t8 | NULL | NULL | ub | 139920035364672 | RECORD | S | GRANTED | supremum pseudo-record |
| INNODB | 139920103510256:51:5:3:139920035365016 | 1379271 | 1375328 | 88 | test | t8 | NULL | NULL | ub | 139920035365016 | RECORD | S,GAP | GRANTED | 1, 2 |
| INNODB | 139920103510256:51:5:3:139920035365360 | 1379271 | 1375328 | 89 | test | t8 | NULL | NULL | ub | 139920035365360 | RECORD | X,REC_NOT_GAP | GRANTED | 1, 2 |
| INNODB | 139920103510256:51:4:3:139920035365704 | 1379271 | 1375328 | 89 | test | t8 | NULL | NULL | PRIMARY | 139920035365704 | RECORD | X,REC_NOT_GAP | GRANTED | 2 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+
5 rows in set (0.00 sec)
可以看到T2持有的锁多了X,REC_NOT_GAP
对于并发 insert 造成唯一键冲突的时候 insert 的加锁策略是:
-
第一阶段唯一性约束检查，先申请 LOCKINSERTINTENTION
-
第二接入获取阶段一的锁并且 insert 成功之后插入的位置有 Gap 锁：LOCKS + LOCKORDINARY，为了防止其他 insert 唯一键冲突。
-
插入成功的记录：LOCK_X + LOCK_REC_NOT_GAP
10. 分析行锁定
通过检查InnoDB_row_lock 状态变量分析系统上的行锁的争夺情况 show status like 'innodb_row_lock%'
mysql> show status like 'innodb_row_lock%';
+-------------------------------+----------+
| Variable_name | Value |
+-------------------------------+----------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 95856315 |
| Innodb_row_lock_time_avg | 309 |
| Innodb_row_lock_time_max | 51959 |
| Innodb_row_lock_waits | 310172 |
+-------------------------------+----------+
5 rows in set (0.03 sec)
- innodb_row_lock_current_waits: 当前正在等待锁定的数量
- innodb_row_lock_time: 从系统启动到现在锁定总时间长度；非常重要的参数
- innodb_row_lock_time_avg: 每次等待所花平均时间；非常重要的参数
- innodb_row_lock_time_max: 从系统启动到现在等待最常的一次所花的时间
- innodb_row_lock_waits: 系统启动后到现在总共等待的次数；非常重要的参数。直接决定优化的方向和策略
11. 行锁优化
- 尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁
- 尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围
- 尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能
- 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。
12. 什么时候使用表锁
- 第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。
- 第二种情况：多表查询。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。
InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。MySQL这样设计并不是给你挖坑。它有自己的设计目的。即便你在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引(所以explain命令中会有possible_key 和 key)。如果MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
12.1. 分析表锁
通过检查table_locks_waited(表锁等待,无法立即获得数据)和table_locks_immediate(立即获得锁地查询数目)状态变量分析系统上表锁争夺情况。如果table_locks_waited 数值比较高,就说明存在着较严重的表级锁争用情况 ,性能有问题，并发高,需要优化.
mysql> show status like '%table_lock%';
+-----------------------------------------+----------+
| Variable_name | Value |
+-----------------------------------------+----------+
| Performance_schema_table_lock_stat_lost | 0 |
| Table_locks_immediate | 22435132 |
| Table_locks_waited | 3 |
+-----------------------------------------+----------+
3 rows in set (0.03 sec)
- table_locks_immediate: 表示立即释放表锁数。
- table_locks_waited: 表示需要等待的表锁数。此值越高则说明存在着越严重的表级锁争用情况。
# 13. 参考资料
https://mp.weixin.qq.com/s/FSyE7Tz5A-Rc1bkC-tDqvA
https://mp.weixin.qq.com/s/wGOxro3uShp2q5w97azx5A
https://zhuanlan.zhihu.com/p/29150809
《MySQL 是怎样运行的：从根儿上理解 MySQL》
