MySQL/MariaDB的锁超详细讲解

共计 18525 个字符，预计需要花费 47 分钟才能阅读完成。

1. 事务提交的方式

在 MariaDB/MySQL 中有 3 种事务提交的方式。

1. 显式开启和提交。

使用 begin 或者 start transaction 来显式开启一个事务，显式开启的事务必须使用 commit 或者 rollback 显式提交或回滚。几种特殊的情况除外：行版本隔离级别下的更新冲突和死锁会自动回滚。

在存储过程中开启事务时必须使用 start transaction，因为 begin 会被存储过程解析为 begin…end 结构块。

另外，MariaDB/MySQL 中的 DDL 语句会自动提交前面所有的事务（包括显示开启的事务），而在 SQL Server 中 DDL 语句还是需要显式提交的，也就是说在 SQL Server 中 DDL 语句也是可以回滚的。

2. 自动提交。(MySQL 默认的提交方式)

不需要显式 begin 或者 start transaction 来显式开启事务，也不需要显式提交或回滚事务，每次执行 DML 和 DDL 语句都会在执行语句前自动开启一个事务，执行语句结束后自动提交或回滚事务。

3. 隐式提交事务

隐式提交事务是指执行某些语句会自动提交事务，包括已经显式开启的事务。

会隐式提交事务的语句主要有：

(1).DDL 语句(其中有 truncate table)。

(2). 隐式修改 mysql 数据库架构的操作:create user,drop user,grant,rename user,revoke,set password。

(3). 管理语句：analyze table、cache index、check table、load index into cache、optimize table、repair table。

通过设置 auto_commit 变量值为 1 或 0 来设置是否自动提交，为 1 表示自动提交，0 表示关闭自动提交，即必须显式提交。但是不管设置为 0 还是 1，显式开启的事务必须显式提交，而且隐式提交的事务不受任何人为控制。

2.MariaDB/MySQL 中的锁

锁和事务的实现是存储引擎内的组件管理的，而 MariaDB/MySQL 是插件式的存储引擎实现方式，所以不同的存储引擎可以支持不同级别的锁和事务。

2.1 不同存储引擎支持的锁级别

MariaDB/MySQL 相比其他数据产品来说，支持的锁比较简单。

1.MyISAM、Aria(MariaDB 中对 myisam 的改进版本)和 memory 存储引擎只支持表级别的锁。

2.innodb 支持行级别的锁和表级别的锁，默认情况下在允许使用行级别锁的时候都会使用行级别的锁。

3.DBD 存储引擎支持页级别和表级别的锁。

2.2 锁类型

在 MariaDB/MySQL 中只有简单的几种锁类型：

1. 共享锁(S)：即读锁，不涉及修改数据，在检索数据时才申请的锁。

2. 独占锁(X)：增、删、改等涉及修改操作的时候，都会申请独占锁。

以上是支持表锁的存储引擎都会有的锁类型。以下两种是支持行锁或页锁才会有的锁类型，也就是说 myisam 没有下面的锁，而 innodb 有。

3. 意向共享锁(IS)：获取低级别共享锁的同时，在高级别上也获取特殊的共享锁，这种特殊的共享锁是意向共享锁。

4. 意向独占锁(IX)：获取低级别独占锁的同时，在高级别上也获取特殊的独占锁，这种特殊的独占锁是意向独占锁。

低级别锁表示的是行锁或页锁，意向锁可能是多条记录组成的范围锁，也可能直接就是表意向锁。

2.3 锁兼容性

如下表：

MySQL/MariaDB 的锁超详细讲解

独占锁和所有的锁都冲突，意向共享锁和共享锁兼容(这是肯定的)，还和意向独占锁兼容。所以加了意向共享锁的时候，可以修改行级非共享锁的记录。同理，加了意向独占锁的时候，可以检索这些加了独占锁的记录。

3.MyISAM 的表级锁(lock tables 和 unlock 语句)

MariaDB/MySQL 中 myisam 和 innodb 都支持表级锁。表级锁分为两种：读锁 (read lock) 和写锁(write lock)。本节所述均为 myisam 支持的，同样 innodb 也一样支持。

可以通过语句来实现表级锁的锁定和解锁，这些语句的操作环境是当前客户端会话(即作用范围是会话)。锁表的时候可以一次性锁定多张表，并使用不同的锁，而解锁的时候只能一次性解锁当前客户端会话的所有表。

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] …
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE

UNLOCK TABLES

lock tables 命令可以锁表或锁视图，锁视图的时候会自动将视图内的基表加上对应类型的锁。由于 MariaDB/MySQL 中触发器是基于表的，所以 lock tables 锁定表的时候，触发器内使用的表也都会被锁定。

例如：table1 上有一个如下触发器：

CREATE TRIGGER trigger1 AFTER INSERT ON table1 FOR EACH ROW
BEGIN
INSERT INTO table2 VALUES (1);
UPDATE table3 SET writes = writes+1
WHERE id = NEW.id AND EXISTS (SELECT id FROM table4);
END;

如果为 table1 加上写锁，则 table2、table3 都会加上写锁，而 table4 会加上读锁。

lock tables 命令会隐式释放当前客户端会话中之前的所有锁。

现在创建 3 张表作为测试表。

DROP TABLE IF EXISTS t1,t2,t3;
CREATE TABLE t1(a INT,b CHAR(5))ENGINE=MYISAM;
CREATE TABLE t2(a INT,b CHAR(5))ENGINE=MYISAM;
CREATE TABLE t3(a INT,b CHAR(5))ENGINE=MYISAM;
INSERT INTO t1 VALUES(1,’a’);
INSERT INTO t2 VALUES(1,’a’);
INSERT INTO t3 VALUES(1,’a’);

给 t1 加上读锁。

LOCK TABLES t1 READ;

此时当前会话将无法操作 t1 以外的任何表，连查询也不允许，因为只有 t1 表加了锁。而其他会话则可以进行查询，但不能进行更新。

MySQL/MariaDB 的锁超详细讲解

当再次使用 lock tables 命令的时候，会先释放当前会话之前所有的锁，再对 lock tables 命令中的表申请锁。

例如，上面会话 1 锁了表 t1，此时无法操作 t2 表。现在对 t2 表 lock table。

lock tables t2 read;

此时就可以操作 t2 表而不能操作 t1 表了，因为对 t1 表的锁已经释放了。

使用 lock tables 给表加读锁的时候，还有一个选项 local，该选项表示对当前现有的记录加上锁，不影响其他会话的插入记录语句。但是否真的能插入，由变量 concurrent_insert 决定，该变量默认值为 auto。关于并发插入，见我翻译的官方手册：https://mariadb.com/kb/zh-cn/concurrent-inserts/。

MySQL/MariaDB 的锁超详细讲解

如果设置为 2，那么对 myisam 表的并发插入有一定提升。

现在测试默认的情况，即 concurrent_insert=auto 的情况。

insert into t1 values(2,’c’),(3,’d’),(4,’e’),(5,’f’);

lock tables t1 read local;

在另一个会话中插入一条记录，这是允许的操作。当然，在锁表的会话中肯定是不能插入的。

insert into t1 values(8,’h’);

解锁，并删除中间的两条记录，形成空洞。然后再锁定表。

mysql> unlock tables;
mysql> delete from t1 where a=3 or a=4;
mysql> lock tables t1 read local;

在其他会话中插入记录。会发现被阻塞。当表解锁后立即成功插入。

insert into t1 values(3,’h’),(9,’i’),(8,’g’);

将 concurrent_insert 设置为 2，即 always，此时不管是否有空洞都允许向 myisam 表尾部插入。

delete from t1 where a=3 or a=8 or a=9;
set @@global.concurrent_insert=2;
lock tables t1 read local;
insert into t1 values(3,’d’),(8,’g’),(9,’i’);

此时发现能够正常插入，且查询 t1 表发现，这些记录都插入在表的尾部。

4.innodb 中的锁

innodb 支持行级锁，也是在允许的情况下默认申请的锁。

SQL Server 中的锁是一种稀有资源，且会在需要的时候锁升级，所以锁越多性能越差。而 MariaDB/MySQL 中的锁不是稀有资源，不会进行锁升级，因此锁的多少不会影响性能，1 个锁和 1000000 个锁性能是一样的(不考虑锁占用的内存)，锁的多少只会影响并发性。

4.1 查看锁信息的几种方法

现在人为造成一个锁等待。

会话 1 执行：

begin;
update tt set b=’h’ where a=1;

会话 2 执行：

begin;
update tt set b= ‘x’ where a=1;

此时会话 2 被阻塞，进入锁等待状态。

要查看锁信息。有几种方法：

1. 通过 show engine innodb status 来查看，其中的 transactions 片段可以看到事务，其中包括锁等待。

以下是没有激活任何事务的信息：

mysql> show engine innodb status;
————
TRANSACTIONS
————
Trx id counter 2856
Purge done for trx’s n:o < 2856 undo n:o < 0 state: running
History list length 25
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 421383739060216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421383739059200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421383739057168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

三个 ”—TRANSACTION” 表示当前开启了 3 个 mysql 会话，但这 3 个会话都没有任何事务。

以下是某会话开启一个事务，但没有任何锁等待的事务信息：

mysql> show engine innodb status;
————
TRANSACTIONS
————
Trx id counter 2857
Purge done for trx’s n:o < 2856 undo n:o < 0 state: running
History list length 25
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 421383739060216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421383739057168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 2856, ACTIVE 10 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 39, OS thread handle 139909209945856, query id 1814112 localhost root Reset for next command

不难看出，这个事务是一个需要写日志的 DML 事务。

以下是有锁等待的事务信息：

mysql> show engine innodb status;
————
TRANSACTIONS
————
Trx id counter 14915
Purge done for trx’s n:o < 14912 undo n:o < 0 state: running but idle
History list length 896
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 14909, not started
MySQL thread id 36, OS thread handle 0x7f5d57e4b700, query id 961 localhost root init
show engine innodb status
—TRANSACTION 14914, ACTIVE 465 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1184, 1 row lock(s)
MySQL thread id 34, OS thread handle 0x7f5d57e8c700, query id 959 localhost root updating
update tt set b= ‘x’ where a=1
——- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 184 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`tt` trx id 14914 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000000601; asc ;;
1: len 6; hex 000000003a41; asc :A;;
2: len 7; hex 2f000001580feb; asc / X ;;
3: len 4; hex 80000001; asc ;;
4: len 5; hex 6820202020; asc h ;;

——————
—TRANSACTION 14913, ACTIVE 490 sec
2 lock struct(s), heap size 360, 6 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f5d57f4f700, query id 900 localhost root

从上面的结果可以看到锁等待的信息。

“TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED” 表示该事务申请锁已经等待了 13 秒。

“RECORD LOCKS space id 184 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`tt` trx id 14914 lock_mode X waiting” 表示 test.tt 表上的记录要申请的行锁 (recode lock) 是独占锁并且正在 waiting，并且标明了该行记录所在表数据文件中的物理位置：表空间 id 为 184，页码为 3。

关于这些信息的详细解释，后文会逐渐说明。

2. 使用 show processlist 查看。

MySQL/MariaDB 的锁超详细讲解

show full processlist;

从上面的结果可以看出，update 语句一直处于 updating 状态。所以，该方法查出来的并不一定是锁等待，有可能是更新的记录太多或者其他问题，总之这里看出来的是该语句还没有执行完成。

3. 查看 information_schema 中的数据字典

在 information_schema 架构下，有 3 个表记录了事务和锁相关的信息。分别是 INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS。

这三个表可能相对复杂，以下分别说明这 3 张表的各列。

MySQL/MariaDB 的锁超详细讲解

根据上面实验过程中的锁查看该表的部分结果如下：

mysql> select * from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 14914
trx_state: LOCK WAIT
trx_started: 2017-03-30 06:07:51
trx_requested_lock_id: 14914:184:3:2
trx_wait_started: 2017-03-30 06:39:25
trx_weight: 2
trx_mysql_thread_id: 34
trx_query: update tt set b= ‘x’ where a=1
*************************** 2. row ***************************
trx_id: 14913
trx_state: RUNNING
trx_started: 2017-03-30 06:07:26
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 1
trx_query: NULL

从结果中可以看出 id 为 14914 的事务正处于锁等待状态，该事务中要申请锁的语句是 update 语句，也就是说是因为该语句而导致的锁等待。

MySQL/MariaDB 的锁超详细讲解

从 innodb_trx 表中只能查看到事务的信息，而不能看到锁相关的信息。要看锁的信息，需要查看表 innodb_locks。

mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 14914:184:3:2
lock_trx_id: 14914
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tt`
lock_index: GEN_CLUST_INDEX
lock_space: 184
lock_page: 3
lock_rec: 2
lock_data: 0x000000000601
*************************** 2. row ***************************
lock_id: 14913:184:3:2
lock_trx_id: 14913
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tt`
lock_index: GEN_CLUST_INDEX
lock_space: 184
lock_page: 3
lock_rec: 2
lock_data: 0x000000000601
2 rows in set (0.00 sec)

从上面的结果中看出，锁所在的事务 ID 为 14914，并且锁模式为独占锁，类型为 record 即行锁，申请锁的表为 tt 表，而且锁定的页数为 3 页，锁定的行有 2 行，锁定行的主键值为 0x000000000601。也许会奇怪，在前面实验过程中根本就没有建立主键，这里为什么会有主键值，这是因为 MySQL 在加锁的时候判断是否有索引，没有索引的时候会自动隐式的添加索引(聚集索引)，从上面锁的索引为 ”GEN_CLUST_INDEX” 可以看出。

所以我们可以知道，MariaDB/MySQL 中的行锁是通过键锁 (Key) 来实现的(在 SQL Server 中有堆表的概念，SQL Server 对于没有索引的表，其行锁通过 rid 锁来实现)。

并且从上面的两段结果也可以看到，它们的申请锁资源所处位置是相同的，正因为位置相同，所以才有了锁等待。

现在在会话 1 上创建索引，然后人为造成锁等待再来查看 innodb_locks 表。

在会话 1 和会话 2 执行：

rollback;

在会话 1 执行：

create index idx_tt on tt(a);
begin;
update tt set b=’h’ where a=1;

在会话 2 执行：

begin;
update tt set b=’x’ where a=1;

查看 innodb_locks 表。

mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 14925:184:4:2
lock_trx_id: 14925
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tt`
lock_index: ind_tt
lock_space: 184
lock_page: 4
lock_rec: 2
lock_data: 1, 0x000000000601
*************************** 2. row ***************************
lock_id: 14924:184:4:2
lock_trx_id: 14924
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tt`
lock_index: ind_tt
lock_space: 184
lock_page: 4
lock_rec: 2
lock_data: 1, 0x000000000601
2 rows in set (0.00 sec)

此处发现，锁的索引类型为 ind_tt，而锁住行的主键值已经变为 1 个 1 了。

查出了锁的信息后，就可以人为的判断出锁等待信息。但是当事务比较大的时候，锁的信息非常繁杂，这时候通过上面的两张表无法轻易判断相关锁信息。由此要借助第三张表 innodb_lock_waits，该表只有 4 列，且意义直观明了。

MySQL/MariaDB 的锁超详细讲解

还是上面试验过程中造成的锁等待，查看那 innodb_lock_waits 表结果如下：

mysql> select * from information_schema.INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 14914
requested_lock_id: 14914:184:3:2
blocking_trx_id: 14913
blocking_lock_id: 14913:184:3:2
1 row in set (0.00 sec)

可以看到，申请锁的事务 ID 为 14914，阻塞在前方的事务 ID 为 14913。

有了这 3 张表，还可以将它们联接起来更直观的显示想要的结果。如下：

SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM
information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G
*************************** 1. row ***************************
waiting_trx_id: 14925
waiting_thread: 34
waiting_query: update tt set b=’x’ where a=1
blocking_trx_id: 14924
blocking_thread: 1
blocking_query: NULL

现在可以直观的看到 14925 事务被阻，语句为 update，阻塞它的事务为 14924。

还可以从以下联接语句中查看锁和事务的相关信息。

MySQL/MariaDB 的锁超详细讲解

4.2 innodb 表的外键和锁

在 innodb 表中，创建外键的时候若外键列上没有索引，则会在创建过程中自动在外键列上隐式地创建索引。

存在这样一种情况，当向子表中插入数据的时候，会向父表查询该表中是否存在对应的值以判断将要插入的记录是否满足外键约束，也就是说会对父表中对应的记录加上依赖性的共享锁，并在表上加意向共享锁。如果此时父表上对应的记录正好有独占锁，那么插入就会失败。同理，从子表中删除或更新记录也是一样的。

现在创建父表 parent 和子表 child，并不要在外键列 (pid) 上显式创建索引。

create table parent(pid int primary key);
create table child(cid int primary key,pid int,foreign key(pid) references parent(pid));
show create table child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`cid` int(11) NOT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`cid`),
KEY `pid` (`pid`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `parent` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

从 show 的结果中可以发现，已经自动添加了索引列 pid。

插入一些测试记录。

insert into parent values(1),(2),(3);

在会话 1 中执行：

begin;
delete from parent where pid=3;

在会话 2 中执行：

begin;
insert into child select 3,3;

这时会发现会话 2 被阻塞了。通过 innodb_trx 和 innodb_locks 表的联合，得到如下结果：

SELECT
trx_id,
trx_state,
lock_id,
lock_mode,
lock_type,
lock_table,
trx_mysql_thread_id,
trx_query
FROM
information_schema.innodb_trx t
JOIN information_schema.innodb_locks l ON l.lock_trx_id = t.trx_id\G
*************************** 1. row ***************************
trx_id: 14951
trx_state: LOCK WAIT
lock_id: 14951:185:3:4
lock_mode: S
lock_type: RECORD
lock_table: `test`.`parent`
trx_mysql_thread_id: 34
trx_query: insert into child select 3,3
*************************** 2. row ***************************
trx_id: 14946
trx_state: RUNNING
lock_id: 14946:185:3:4
lock_mode: X
lock_type: RECORD
lock_table: `test`.`parent`
trx_mysql_thread_id: 1
trx_query: NULL

不难看出，insert 语句想要在父表 parent 上的资源 ”14951:185:3:4″ 加共享锁，但是此时父表上该资源已经有了独占锁，所以被阻塞了。

并且也可以判断出，通过外键读取父表时的模式是 lock in share mode，而不是基于快照的行版本读(什么是 lock in share mode 和行版本快照读见事务隔离级别内容)，假如是基于行版本的快照读，那么就可以查出存在 pid= 3 的记录而导致子表插入成功，这样也可能导致父表和子表不满足外键约束。

4.3 innodb 锁算法

innodb 支持行级锁，但是它还支持范围锁。即对范围内的行记录加行锁。

有三种锁算法：
•1.record lock：即行锁
•2.gap lock：范围锁，但是不锁定行记录本身
•3.next-key lock：范围锁加行锁，即范围锁并锁定记录本身，gap lock + record lock。

record lock 是行锁，但是它的行锁锁定的是 key，即基于唯一性索引键列来锁定(SQL Server 还有基于堆表的 rid 类型行锁)。如果没有唯一性索引键列，则会自动在隐式列上创建索引并完成锁定。

next-key lock 是行锁和范围锁的结合，innodb 对行的锁申请默认都是这种算法。如果有索引，则只锁定指定范围内的索引键值，如果没有索引，则自动创建索引并对整个表进行范围锁定。之所以锁定了表还称为范围锁定，是因为它实际上锁的不是表，而是把所有可能的区间都锁定了，从主键值的负无穷到正无穷的所有区间都锁定，等价于锁定了表。

以下示例过程将演示范围锁的情况。

1. 有索引的情况

首先创建一个有索引的表 t。然后插入几个被分隔的记录。

create table t(id int);
create unique index idx_t on t(id);
insert into t values(1),(2),(3),(4),(7),(8),(12),(15);

在会话 1 执行：无需知道 lock in share mode 是什么意思，只需知道它的作用是在读取的时候加上共享锁并且不释放，具体内容在事务章节中会说明。

begin;
select * from t where id<5 lock in share mode;

在会话 2 执行：

insert into t values(9);
insert into t values(6);

这时发现第一条插入语句是正常插入的，而第二条语句被阻塞。show engine innodb status 看结果。

mysql> show engine innodb status;
————
TRANSACTIONS
————
Trx id counter 14992
Purge done for trx’s n:o < 14987 undo n:o < 0 state: running but idle
History list length 914
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0, not started
MySQL thread id 50, OS thread handle 0x7f5d57e0a700, query id 1495 localhost root init
show engine innodb status
—TRANSACTION 14991, ACTIVE 17 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 49, OS thread handle 0x7f5d57d88700, query id 1491 localhost root update
insert into t values(6)
——- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 187 page no 4 n bits 80 index `idx_t` of table `test`.`t` trx id 14991 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 00000000060c; asc ;;

——————
—TRANSACTION 14989, ACTIVE 32 sec
2 lock struct(s), heap size 360, 5 row lock(s)
MySQL thread id 43, OS thread handle 0x7f5d57f0e700, query id 1489 localhost root

其中 ”locks gap” 就表示阻塞 insert 语句的锁是 gap 锁，即范围锁。锁定的范围包括(-∞,4],(4,7](锁到操作行的下一个 key，此处插入 id=6，由于存在 id= 7 的 key，所以锁到 7 为止，这就是 next-key 的意思)。当测试插入或修改 -1,0,5,6 等小于 7 的值都会被阻塞，而插入或修改大于 7 的值就不会被阻塞。

如何判断锁定的范围大小？可以通过下面的查询语句：

mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 2856:109:4:6
lock_trx_id: 2856
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`t`
lock_index: idx_t
lock_space: 109
lock_page: 4
lock_rec: 6
lock_data: 7
*************************** 2. row ***************************
lock_id: 421383739058184:109:4:6
lock_trx_id: 421383739058184
lock_mode: S
lock_type: RECORD
lock_table: `test`.`t`
lock_index: idx_t
lock_space: 109
lock_page: 4
lock_rec: 6
lock_data: 7
2 rows in set (0.000 sec)

lock_mode 为 ”X+GAP”，表示 next-key lock 算法。其中 lock_data 值为 7，表示锁定了值为 7 的记录，这是最大锁定范围边界。lock_rec 的值为 6，表示锁定了 6 行记录，其中 1,2,3,4,7 共 5 行记录是通过 gap 锁锁定的范围，加上待插入的 id=6(该行为 key 锁锁定)，共锁定 6 行记录。

而如果使用的是大于号，由于操作任何一条记录，它的下一个 key 都会被锁定，这等价于锁定了整个无穷区间，即实现了表锁的功能。如下：

在会话 1 上执行：

# 首先回滚
rollback;
begin;
select * from t where id>10 lock in share mode;

在会话 2 执行：

insert into t values(0);
insert into t values(5);
insert into t values(100);

会发现任何插入都是阻塞的。即锁定的范围为(-∞,+∞)，等价于锁定了整张表。

但是如果使用的等于号，那么在查找索引的时候发现只需锁定一条记录和下一条记录中间的范围即可。

在会话 1 执行：

# 首先回滚
rollback;
begin;
select * from t where id=5 lock in share mode;

在会话 2 执行：

insert into t values(0);
insert into t values(10);

会发现上述插入都是允许的。

但如果插入 id= 6 的记录，则阻塞，因为锁定的范围为 [5,7] 区间。

也就是说，在有索引的情况下，如果是非具体的行锁，那么就会将能扫描到的索引键值内的所有范围加锁。

下面测试没有索引的情况。

2. 无索引的情况

首先创建没有索引的表，然后插入一些分隔的记录。

create table ttt(id int);
insert into ttt values(1),(2),(3),(4),(7),(8),(12),(15);

在会话 1 上执行：

begin;
select * from ttt where id=4 lock in share mode;

在会话 2 上执行：

insert into ttt values(5);
insert into ttt values(100);
insert into ttt values(0);

会发现不管是插入哪些记录，都会被阻塞。因为没有索引键值的时候，自动隐式创建索引会锁定整个区间。查看下 innodb 的事务状态。

mysql> show engine innodb status;
————
TRANSACTIONS
————
Trx id counter 15102
Purge done for trx’s n:o < 15096 undo n:o < 0 state: running but idle
History list length 944
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 15066, not started
MySQL thread id 53, OS thread handle 0x7f5d57d47700, query id 1615 localhost root
—TRANSACTION 15065, not started
MySQL thread id 52, OS thread handle 0x7f5d57dc9700, query id 1590 localhost root
—TRANSACTION 15097, not started
MySQL thread id 51, OS thread handle 0x7f5d57ecd700, query id 1637 localhost root
—TRANSACTION 0, not started
MySQL thread id 50, OS thread handle 0x7f5d57e0a700, query id 1642 localhost root init
show engine innodb status
—TRANSACTION 15101, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 49, OS thread handle 0x7f5d57d88700, query id 1641 localhost root update
insert into ttt values(0)
——- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 190 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`ttt` trx id 15101 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

——————
—TRANSACTION 15087, ACTIVE 215 sec
2 lock struct(s), heap size 360, 9 row lock(s)
MySQL thread id 43, OS thread handle 0x7f5d57f0e700, query id 1631 localhost root

可以发现，这时的锁不是范围锁，因为没有了 locks gap，但却仍然是行锁而不是表锁，只不过此时等价于表锁。如下

mysql> select * from information_schema.innodb_locks\G
*************************** 1. row ***************************
lock_id: 15102:190:3:1
lock_trx_id: 15102
lock_mode: X
lock_type: RECORD
lock_table: `test`.`ttt`
lock_index: GEN_CLUST_INDEX
lock_space: 190
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
*************************** 2. row ***************************
lock_id: 15087:190:3:1
lock_trx_id: 15087
lock_mode: S
lock_type: RECORD
lock_table: `test`.`ttt`
lock_index: GEN_CLUST_INDEX
lock_space: 190
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record

发现确实是行锁而非表锁。并且索引键值那里为 ”supermum pseudo-record”，这表示锁定的是 ” 最大上界伪记录 ”，即锁定的是无穷值。

没索引的时候，哪怕查询具体的行记录都会锁定整个区间，更不用说锁定范围(例如：where id>5)。其实它们的结果都是一样的：锁定整个区间。

4.4 innodb 中的锁等待超时

在 innodb 存储引擎中，当出现锁等待时，如果等待超时，将会结束事务，超时时长通过动态变量 innodb_lock_wait_timeout 值来决定，默认是等待 50 秒。关于锁等待超时，可以直接在语句中设置超时时间。可以设置锁等待超时时间的语句包括：wait n 的 n 单位为秒，nowait 表示永不超时。

超时后结束事务的方式有中断性结束和回滚性结束两种方式，这也是通过变量来控制的，该变量为 innodb_rollback_on_timeout，默认为 off，即超时后不回滚，也即中断性结束。

mysql> show variables like “innodb%timeout”;
+—————————–+——-+
| Variable_name | Value |
+—————————–+——-+
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
+—————————–+——-+