MySQL(二十三)-加锁分析

参考文献

解决死锁之路 - 常见 SQL 语句的加锁分析
何登成–管中窥豹——MySQL(InnoDB)死锁分析之道
MySQL · 引擎特性 · InnoDB隐式锁功能解析
Locks Set by Different SQL Statements in InnoDB
MySQL技术内幕 InnoDB存储引擎
27.12.13.1 The data_locks Table
MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁
一张图彻底搞懂 MySQL 的锁机制
mysql事务和锁 SELECT FOR UPDATE
MySQL 45 讲
MySQL 加锁规则！

加锁的目的

数据库中的锁: 确保并发更新场景下的数据正确性.
ACID中的I(Isolation)
锁要作的就是达到事务隔离的目的,即: 两个并发执行的事务T1和T2,如果T1正在修改某些行,那么,T2要并发读取/修改/插入满足T1查询条件的行时,T2就必须被阻塞,这是锁存在的根本原因.

加什么样的锁的判断因素

当前事务的隔离级别
SQL是一致性非锁定读(consistent nonlocking read)还是DML或锁定读(locking read)
SQL执行时是否使用了索引,所使用索引的类型(主键索引,辅助索引、唯一索引)

锁的持有周期

加锁:实际访问到某个待更新的行时,对其加锁(而非一开始就将所有的锁都一次性持有).
解锁:事务提交/回滚时(而非语句结束时,就释放).

锁粒度

数据库级别的锁
表级别的锁
页级别的锁
行级别的锁
锁定的粒度越细,并发级别越高.

加锁规则(《MySQL 45 讲》丁奇)

两个原则、两个优化、一个 bug

原则 1: 加锁的基本单位是 Next-Key Lock.其中 Next-Key Lock 是前开后闭区间,例如: (2, 5].

加锁的基本单位是 Next-Key 锁,意思是默认都是先加上 Next-Key,之后根据 2 个优化点选择性退化为行锁或间隙锁

原则 2: 查找过程中访问到的对象才会加锁.

访问到的对象才会加锁,意思是如果直接索引覆盖到了,不需要回表,那么就不会对聚簇索引加锁.这样的话,其他事务就可以对聚簇索引进行操作,而不会阻塞.

优化 1: 索引上的等值查询,给唯一索引加锁的时候,Next-Key Lock 退化为行锁.

针对唯一索引

优化 2: 索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,Next-Key Lock退化为间隙锁.

针对唯一索引

一个 bug: 唯一索引上的范围查询会访问到不满足条件的第一个值为止.

MySQL 加锁全局视角

我们知道 MySQL 分成了 Server 层和存储引擎两部分,每当执行一个查询时,Server 层负责生成执行计划,然后交给存储引擎去执行.其整个过程可以这样描述：

Server 层向 Innodb 获取到扫描区间的第 1 条记录.

Innodb 通过 B+ 树定位到扫描区间的第 1 条记录,然后返回给 Server 层.

Server 层判断是否符合搜索条件,如果符合则发送给客户端,不负责则跳过.接着继续向 Innodb 要下一条记录.

Innodb 继续根据 B+ 树的双向链表找到下一条记录,会执行具体的 row_search_mvcc 函数做加锁等操作,返回给 Server 层.

Server 层继续处理该条记录,并向 Innodb 要下一条记录.

继续不停执行上述过程,直到 Innodb 读到一条不符合边界条件的记录为止.

通过上面这个过程,我想让大家明白两个重要的认识：

Innodb 并不是一次性把所有数据找到,然后返回给 Server 层的,而是会循环很多次.

row_search_mvcc 这个函数是做具体的加锁、加什么锁的重要逻辑,并且由于 Server 层与 Innodb 会循环多次,因此该函数也是会执行多次的.

弄懂了上面两个认识,会对后续大家理解有很大帮助.例如：对于select * from user where id >= 5进行分析的时候,为什么会出现说第一次加锁是精确查询？它明明是范围查询呀！这是因为第一次是要寻找到 id = 5 的记录,对于 Innodb 来说,它就是精确查找,不是范围查找.随后找到 id = 5 的记录之后,就要找 id > 5 的记录了,此时就变成了范围查找了.

MySQL中会加锁的操作

常见加锁操作

SELECT ... 语句正常情况下为快照读,不加锁；
SELECT ... LOCK IN SHARE MODE 语句为当前读,加 S 锁；
SELECT ... FOR UPDATE 语句为当前读,加 X 锁；
常见的 DML 语句(如 INSERT、DELETE、UPDATE)为当前读,加 X 锁；
常见的 DDL 语句(如 ALTER、CREATE 等)加表级锁,且这些语句为隐式提交,不能回滚；
备份常用FLUSH TABLES WITH READ LOCK
唯一性约束检查(PRIMARY KEY/UNIQUE KEY)

表锁

表锁X(分 S 锁和 X 锁)
- LOCK TABLE...READ/WRITE
意向锁(分 IS 锁和 IX 锁)
自增锁(一般见不到,只有在 innodb_autoinc_lock_mode = 0 或者 Bulk inserts 时才可能有)

行锁

记录锁(分 S 锁和 X 锁)
间隙锁(分 S 锁和 X 锁)
Next-key 锁(分 S 锁和 X 锁)
插入意向锁

行锁分析

行锁都是加在索引上的,最终都会落在聚簇索引上；
加行锁的过程是一条一条记录加的；

MySQL特有的加锁操作

Purge操作加锁

不同隔离级别下的锁

在`Read Uncommitted`级别下

在Read Uncommitted级别下,读取数据不需要加共享锁S,这样就不会跟被修改的数据上的排他锁X冲突

在`Read Committed`级别下

在Read Committed级别下,读操作需要加共享锁S,但是在语句执行完以后释放共享锁S；
RC时,InnoDB只在完全满足WHERE条件的行上设置Record Lock.
RC时,禁用了Gap Lock.
- 因为Gap Lock是用来解决Phantom Problem,隔离级别RC是允许Phantom Problem,因此RC时Gap Lock是被禁用的.
- 此时Gap Lock仅用于外键约束检查(FOREIGN KEY)和重复键检查(UNIQUE KEY)(主要是唯一性检查)

在`Repeatable Read`级别下

在Repeatable Read级别下,读操作需要加共享锁S,但是在事务提交之前并不释放共享锁,也就是必须等待事务执行完毕以后才释放共享锁.

加锁范围

Innodb默认的索引数据结构为B+树,B+树的叶子结点包含指向下一个叶子结点的指针.在查询过程中,会按照B+树的搜索方式来进行查找,其底层原理类似二分查找.故在加锁过程中会按照以下两条原则进行加锁：
- 只会对满足查询目标附近的区间加锁,并不是对搜索路径中的所有区间都加锁.最终可以定位到满足该搜索条件的区域(左开右闭)

加锁原则

RR时,如果使用非唯一索引进行搜索或扫描,则在所扫描的每一个索引记录上都设置Next-Key Lock

这里“所扫描的每一个索引记录”是指当扫描执行计划中所使用的索引时,搜索遇到的每一条记录.WHERE条件是否排除掉某个数据行并没有关系,InnoDB并不记得确切的WHERE条件,InnoDB倔强的只认其扫描的索引范围(index range) .
- 加的锁一般是Next-Key Lock,这种锁住了索引记录本身,还锁住了每一条索引记录前面的间隙,从而阻止其他事务向索引记录前面紧接着的间隙中插入记录.
- 如果在搜索中使用了辅助索引(secondary index),并且在辅助索引上设置了行锁,InnoDB还会在相应的聚集索引上设置锁；
  - 表未定义聚集索引时,InnoDB自动创建隐藏的聚集索引(索引名字是GEN_CLUST_INDEX),当需要在聚集索引上设置锁时,就设置到此自动创建的索引上.
RR时,如果使用了唯一索引的唯一搜索条件,InnoDB只在满足条件的索引记录上设置Record Lock,不锁定索引记录前面的间隙；如果用唯一索引作范围搜索,依然会锁定每一条被扫描的索引记录前面的间隙,并且再在聚集索引上设置锁
RR时,在第一个不满足搜索条件的索引记录上设置Gap Lock或Next-Key Lock.
- 一般情况下,等值条件时设置Gap Lock,范围条件时设置Next-Key Lock.此Gap Lock或Next-Key Lock锁住第一个不满足搜索条件的记录前面的间隙.
RR时,INSERT在插入新行之前,必须首先为表上的每个索引设置Insert Intention Lock.
- 每个Insert Intention Lock的范围都是(待插入行的某索引列的值, 此索引上从待插入行给定的值向下的第一个索引值).只有当Insert Intention Lock与某个Gap Lock或Next-Key Lock冲突时,才能在performance_schema.data_locks看到Insert Intention Lock.
RR或RC时,对于主键或唯一索引,当有重复键错误(duplicate-key error)时,会在重复的索引记录上设置 shared next-key lock或shared index record lock.这可能会导致死锁.

在`Serializable`级别下

Serializable是限制性最强的隔离级别,因为该级别锁定整个范围的键,并一直持有锁,直到事务完成.

MySQL锁模式

常规锁模式

LOCK_S(读锁,共享锁,2)
LOCK_X(写锁,排它锁,3)

锁的属性

LOCK_REC_NOT_GAP(锁记录,1024)
LOCK_GAP(锁记录前的GAP,512)
LOCK_ORDINARY(同时锁记录+记录前的GAP,Next-Key锁,0)
LOCK_INSERT_INTENTION(插入意向锁,2048)

锁组合(属性+模式)

锁的属性可以与锁模式任意组合,如LOCK_REC_NOT_GAP(1023)+LOCK_X(3)

锁冲突矩阵

S 锁和 S 锁兼容,X 锁和 X 锁冲突,X 锁和 S 锁冲突；

列:存在锁行:待加锁	S(Not Gap)	S(Gap)	S(Ordinary)	X(Not Gap)	X(Gap)	X(Ordinary)	Next-Key Lock	Insert Intention Shared	Insert Intention Exclusive
S(Not Gap)	OK	X	X	X	X	X	X	OK	X
S(Gap)	X	OK	OK	X	X	X	OK	OK	OK
S(Ordinary)	X	OK	OK	X	X	X	X	OK	X
X(Not Gap)	X	X	X	OK	X	X	X	X	X
X(Gap)	X	X	X	X	OK	X	X	X	X
X(Ordinary)	X	X	X	X	X	OK	X	X	X
Next-Key Lock	X	OK	OK	X	X	X	OK	OK	X
Insert Intention Shared	OK	OK	OK	X	X	X	OK	OK	X
Insert Intention Exclusive	X	OK	X	X	X	X	X	OK	OK

操作与加锁的对照关系

注: 以隔离级别为RC为例

`INSERT`

源码路径:

sql/sql_parse.cc -> do_command->dispatch_command->mysql_execute_command->execute->

sql/sql_select.cc->execute->execute_inner

sql/sql_insert.cc->execute_inner->write_record

…待补充

加锁流程:

首先对插入的间隙加插入意向锁(Insert Intension Locks)

如果该间隙已被加上了 GAP 锁或 Next-Key 锁,则加锁失败进入等待；

如果没有,则加锁成功,表示可以插入；

然后判断插入记录是否有唯一键,如果有,则进行唯一性约束检查

如果不存在相同键值,则完成插入

如果存在相同键值,则判断该键值是否加锁

如果没有锁, 判断该记录是否被标记为删除

如果标记为删除,说明事务已经提交,还没来得及 purge,这时加 S 锁等待；

如果没有标记删除,则报 1062 duplicate key 错误；

如果有锁,说明该记录正在处理(新增、删除或更新),且事务还未提交,加 S 锁等待；

插入记录并对记录加 X 记录锁；

无`UNIQUE KEY`:

LOCK_X+LOCK_REC_NOT_GAP(3+1024=1027)

有`UNIQUE KEY`

唯一性约束检查: LOCK_S+LOCK_ORDINARY(0+2=2)
插入的位置有GAP锁:LOCK_INSERT_INTENTION(2048)
新数据插入:LOCK_X+LOCK_REC_NOT_GAP(3+1024=1027)

`DELETE`

满足删除条件的所有记录:LOCK_X+LOCK_REC_NOT_GAP

`UPDATE`

`UPDATE`操作分解

定位到下一条满足查询条件的记录(查询过程,类似于Select/Delete)
删除当前定位到的记录(标记为删除状态)
拼装更新后项,根据更新后项定位到新的插入位置
在新的插入位置,判断是否存在UNIQUE冲突(有UNIQUE KEY时)
插入更新后项(不存在UNIQUE冲突时)
重复1~5的操作,直到扫描完整个查询范围

`UPDATE`操作分析

1~2: DELETE
3,4,5: INSERT

`UPDATE`操作与加锁对照关系

无`UNIQUE KEY`

查询范围中的所有记录:LOCK_X+LOCK_REC_NOT_GAP

有`UNIQUE KEY`

查询满足条件的记录: 查询范围内的所有记录加锁,LOCK_X+LOCK_REC_NOT_GAP
更新后项存在唯一性冲突: 冲突项上的加锁,LOCK_S+LOCK_ORDINARY
更新后项不存在唯一性冲突: 更新位置后项加锁,LOCK_S+LOCK_GAP
实际更新操作:可看作插入了一条新记录,LOCK_X+LOCK_REC_NOT_GAP

加`GAP`锁的情况

READ COMMITTED(RC): UNIQUE KEY唯一性约束检查,Purge操作;
REPEATABLE READ(RR): RC的基础上,所有需要加锁的索引范围扫描;

原则

GAP锁很复杂,为了减少GAP锁,减少GAP导致的死锁,尽量选择READ COMMITTED隔离级别(RC+row based binlog,基本上能够解决所有问题,无需使用REPEATABLE READ)

适当的减少UNIQUE索引,能够减少GAP锁导致的死锁(根据业务情况而定).

查看当前数据库中锁的请求

表锁

查看表锁情况

mysql> show open tables;
+--------------------+----------------------------+--------+-------------+
| Database           | Table                      | In_use | Name_locked |
+--------------------+----------------------------+--------+-------------+
| test               | s                          |      1 |           0 |
| test               | user                       |      0 |           0 |

Database：表所在的数据库名称.
Table：表名称.
In_use：表示该表当前被多少个连接使用.
Name_locked：表示该表是否被当前连接锁定,如果被锁定则值为 1,否则为 0.
Type：表示该表的类型,包括 BASE TABLE（基础表）和 VIEW（视图）.

mysql> SHOW FULL PROCESSLIST;
+----+-----------------+-----------+------+---------+------+---------------------------------+----------------------------+
| Id | User            | Host      | db   | Command | Time | State                           | Info                       |
+----+-----------------+-----------+------+---------+------+---------------------------------+----------------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 1715 | Waiting on empty queue          | NULL                       |
|  8 | root            | localhost | test | Query   |  648 | Waiting for table metadata lock | alter table s add  xid int |
| 10 | root            | localhost | NULL | Query   |    0 | init                            | SHOW FULL PROCESSLIST      |
| 11 | root            | localhost | test | Sleep   |  660 |                                 | NULL                       |
+----+-----------------+-----------+------+---------+------+---------------------------------+----------------------------+
4 rows in set (0.00 sec)

表锁分析

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 13    |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 59    |
| Table_open_cache_misses    | 26    |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.09 sec)

Table_locks_immediate: 产生表级锁定次数,不是可以立即获取锁的查询次数,每立即获取锁加1
Table_locks_waited : 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次值加1),此值高说明存在着较严重的表级锁争用情况

行锁

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 125711 |
| Innodb_row_lock_time_avg      | 31427  |
| Innodb_row_lock_time_max      | 53591  |
| Innodb_row_lock_waits         | 4      |
+-------------------------------+--------+

innodb_row_lock_current_waits :当前正在等待锁定的数量
innodb_row_lock_time: 从系统启动到现在锁定总时间长度
innodb_row_lock_time_avg : 每次等待所花平均时间
innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花时间
innodb_row_lock_waits: 系统启动后到现在总共等待的次数

从InnoDB1.0(MySQL5.1.x )开始,在INFORMATION_SCHEMA架构下添加了表INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS.

INFORMATION_SCHEMA.INNODB_LOCKS表在8.0.13版本中由PERFORMANCE_SCHEMA.DATA_LOCKS表所代替,

Table 27.4 Mapping from INNODB_LOCKS to data_locks Columns

INNODB_LOCKS Column	data_locks Column
`LOCK_ID`	`ENGINE_LOCK_ID`
`LOCK_TRX_ID`	`ENGINE_TRANSACTION_ID`
`LOCK_MODE`	`LOCK_MODE`
`LOCK_TYPE`	`LOCK_TYPE`
`LOCK_TABLE` (combined schema/table names)	`OBJECT_SCHEMA` (schema name), `OBJECT_NAME` (table name)
`LOCK_INDEX`	`INDEX_NAME`
`LOCK_SPACE`	None
`LOCK_PAGE`	None
`LOCK_REC`	None
`LOCK_DATA`	`LOCK_DATA`

INFORMATION_SCHEMA.INNODB_LOCK_WAITS表则由PERFORMANCE_SCHEMA.DATA_LOCK_WAITS表代替.

mysql> desc INFORMATION_SCHEMA.INNODB_TRX;
+----------------------------+-----------------+------+-----+---------+-------+
| Field                      | Type            | Null | Key | Default | Extra |
+----------------------------+-----------------+------+-----+---------+-------+
| trx_id                     | bigint unsigned | NO   |     |         |       |
| trx_state                  | varchar(13)     | NO   |     |         |       |
| trx_started                | datetime        | NO   |     |         |       |
| trx_requested_lock_id      | varchar(105)    | YES  |     |         |       |
| trx_wait_started           | datetime        | YES  |     |         |       |
| trx_weight                 | bigint unsigned | NO   |     |         |       |
| trx_mysql_thread_id        | bigint unsigned | NO   |     |         |       |
| trx_query                  | varchar(1024)   | YES  |     |         |       |
| trx_operation_state        | varchar(64)     | YES  |     |         |       |
| trx_tables_in_use          | bigint unsigned | NO   |     |         |       |
| trx_tables_locked          | bigint unsigned | NO   |     |         |       |
| trx_lock_structs           | bigint unsigned | NO   |     |         |       |
| trx_lock_memory_bytes      | bigint unsigned | NO   |     |         |       |
| trx_rows_locked            | bigint unsigned | NO   |     |         |       |
| trx_rows_modified          | bigint unsigned | NO   |     |         |       |
| trx_concurrency_tickets    | bigint unsigned | NO   |     |         |       |
| trx_isolation_level        | varchar(16)     | NO   |     |         |       |
| trx_unique_checks          | int             | NO   |     |         |       |
| trx_foreign_key_checks     | int             | NO   |     |         |       |
| trx_last_foreign_key_error | varchar(256)    | YES  |     |         |       |
| trx_adaptive_hash_latched  | int             | NO   |     |         |       |
| trx_adaptive_hash_timeout  | bigint unsigned | NO   |     |         |       |
| trx_is_read_only           | int             | NO   |     |         |       |
| trx_autocommit_non_locking | int             | NO   |     |         |       |
| trx_schedule_weight        | bigint unsigned | YES  |     |         |       |
+----------------------------+-----------------+------+-----+---------+-------+
25 rows in set (0.01 sec)

mysql> desc PERFORMANCE_SCHEMA.DATA_LOCKS;
+-----------------------+-----------------+------+-----+---------+-------+
| Field                 | Type            | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------+-------+
| ENGINE                | varchar(32)     | NO   | PRI | NULL    |       |
| ENGINE_LOCK_ID        | varchar(128)    | NO   | PRI | NULL    |       |
| ENGINE_TRANSACTION_ID | bigint unsigned | YES  | MUL | NULL    |       |
| THREAD_ID             | bigint unsigned | YES  | MUL | NULL    |       |
| EVENT_ID              | bigint unsigned | YES  |     | NULL    |       |
| OBJECT_SCHEMA         | varchar(64)     | YES  | MUL | NULL    |       |
| OBJECT_NAME           | varchar(64)     | YES  |     | NULL    |       |
| PARTITION_NAME        | varchar(64)     | YES  |     | NULL    |       |
| SUBPARTITION_NAME     | varchar(64)     | YES  |     | NULL    |       |
| INDEX_NAME            | varchar(64)     | YES  |     | NULL    |       |
| OBJECT_INSTANCE_BEGIN | bigint unsigned | NO   |     | NULL    |       |
| LOCK_TYPE             | varchar(32)     | NO   |     | NULL    |       |
| LOCK_MODE             | varchar(32)     | NO   |     | NULL    |       |
| LOCK_STATUS           | varchar(32)     | NO   |     | NULL    |       |
| LOCK_DATA             | varchar(8192)   | YES  |     | NULL    |       |
+-----------------------+-----------------+------+-----+---------+-------+
15 rows in set (0.01 sec)

ENGINE: 持有或请求锁定的存储引擎
ENGINE_LOCK_ID: 存储引擎持有或请求的锁的ID,锁ID格式是内部的,随时可能更改.
ENGINE_TRANSACTION_ID: 请求锁定的事务存储引擎内部ID,可以将其视为锁的所有者
THREAD_ID: 对应事务的线程ID,要获取有关线程的详细信息,请将此列与 performance_schema.threads表的THREAD_ID`列连接.
EVENT_ID: 指明造成锁的EVENT_ID,THREAD_ID+EVENT_ID对应parent EVENT,可以在以下几张表内获得信息
- events_waits_xx表查看等待事件
- events_stages_xxx查看到了哪个阶段
- events_statements_xx表查看对应的SQL语句
- events_transactions_current对应查看事务信息
OBJECT_SCHEMA: 对应锁表的schema名称
OBJECT_NAME: 对应锁的表名
PARTITION_NAME: 对应锁的分区名
SUBPARTITION_NAME: 对应锁的子分区名
INDEX_NAME: 锁对应的索引名称,InnoDB表不会为NULL
OBJECT_INSTANCE_BEGIN: 锁对应的内存地址
LOCK_TYPE: 对应的锁类型,
- 该值取决于存储引擎.对于 InnoDB,允许的值 RECORD适用于行级锁、 TABLE表级锁.

LOCK_MODE: 锁模式,对应值可能为S[,GAP], X[, GAP], IS[,GAP], IX[,GAP], AUTO_INC和UNKNOWN.除AUTO_INC和UNKNOWN以外的锁模式表示间隙锁(如果存在的话).

	共享锁	排它锁
意向锁(Intention Locks)	`IS`	`IX`
索引记录锁(Record Locks)	`S,REC_NOT_GAP`	`X,REC_NOT_GAP`
间隙锁(Gap Locks)	`S,GAP`	`X,GAP`
Next-Key Locks	`S`	`X`
插入意向锁(Insert Intention Locks)	`S,GAP,INSERT_INTENTION`	`X,GAP,INSERT_INTENTION`

LOCK_STATUS: 锁状态,与存储引擎相关.对于InnoDB,允许的值是GRANTED(锁被持有)和WAITING(锁正在等待).
LOCK_DATA: 锁对应的数据,例如如果锁定的是主键,那么该列对应的就是加锁的主键值

mysql> desc PERFORMANCE_SCHEMA.DATA_LOCK_WAITS;
+----------------------------------+-----------------+------+-----+---------+-------+
| Field                            | Type            | Null | Key | Default | Extra |
+----------------------------------+-----------------+------+-----+---------+-------+
| ENGINE                           | varchar(32)     | NO   |     | NULL    |       |
| REQUESTING_ENGINE_LOCK_ID        | varchar(128)    | NO   | MUL | NULL    |       |
| REQUESTING_ENGINE_TRANSACTION_ID | bigint unsigned | YES  | MUL | NULL    |       |
| REQUESTING_THREAD_ID             | bigint unsigned | YES  | MUL | NULL    |       |
| REQUESTING_EVENT_ID              | bigint unsigned | YES  |     | NULL    |       |
| REQUESTING_OBJECT_INSTANCE_BEGIN | bigint unsigned | NO   |     | NULL    |       |
| BLOCKING_ENGINE_LOCK_ID          | varchar(128)    | NO   | MUL | NULL    |       |
| BLOCKING_ENGINE_TRANSACTION_ID   | bigint unsigned | YES  | MUL | NULL    |       |
| BLOCKING_THREAD_ID               | bigint unsigned | YES  | MUL | NULL    |       |
| BLOCKING_EVENT_ID                | bigint unsigned | YES  |     | NULL    |       |
| BLOCKING_OBJECT_INSTANCE_BEGIN   | bigint unsigned | NO   |     | NULL    |       |
+----------------------------------+-----------------+------+-----+---------+-------+
11 rows in set (0.01 sec)

ENGINE: 请求的锁的引擎
REQUESTING_ENGINE_LOCK_ID: 请求的锁在存储引擎中的锁ID
REQUESTING_ENGINE_TRANSACTION_ID: 请求锁的事务对应的事务ID
REQUESTING_THREAD_ID: 请求锁的线程ID
REQUESTING_EVENT_ID: 请求锁的EVENT ID
REQUESTING_OBJECT_INSTANCE_BEGIN: 请求的锁的内存地址
BLOCKING_ENGINE_LOCK_ID: 阻塞的锁的ID,对应data_locks表的ENGINE_LOCK_ID列
BLOCKING_ENGINE_TRANSACTION_ID: 锁阻塞的事务ID
BLOCKING_THREAD_ID: 锁阻塞的线程ID
BLOCKING_EVENT_ID: 锁阻塞的EVENT ID
BLOCKING_OBJECT_INSTANCE_BEGIN: 阻塞的锁内存地址

`MySQL5.X`锁分析

SHOW FULL PROCESSLIST;
SELECT * FROM  INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM  INFORMATION_SCHEMA.INNODB_TRX;
SELECT * FROM  INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
SELECT * FROM  performance_schema.threads;
SELECT * FROM  performance_schema.mutex_instances;
# 该步骤建议打开锁的详细信息 innodb_status_output_locks设置为ON
SHOW ENGINE INNODB STATUS;

开启`SHOW ENGINE INNODB STATUS`中锁的详细信息

mysql> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output_locks | OFF   |
+----------------------------+-------+
1 row in set (0.02 sec)

mysql> set global innodb_status_output_locks = on;
Query OK, 0 rows affected (0.00 sec)

开启前

------------
TRANSACTIONS
------------
Trx id counter 662084
Purge done for trx's n:o < 662083 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479795810064, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795805312, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795808480, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795807688, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795806896, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795809272, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795804520, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795803728, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 662083, ACTIVE 65 sec
4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 1638, OS thread handle 6182219776, query id 1700004 localhost root
Trx read view will not see trx with id >= 662083, sees < 662083

开启后

------------
TRANSACTIONS
------------
Trx id counter 662084
Purge done for trx's n:o < 662083 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479795810064, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795805312, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795808480, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795807688, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795806896, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795809272, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795804520, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281479795803728, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 662083, ACTIVE 397 sec
4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 1638, OS thread handle 6182219776, query id 1700004 localhost root
Trx read view will not see trx with id >= 662083, sees < 662083
TABLE LOCK table `test`.`s2` trx id 662083 lock mode IX
RECORD LOCKS space id 9389 page no 6 n bits 80 index code of table `test`.`s2` trx id 662083 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 31303135; asc 1015;;
 1: len 4; hex 8000001e; asc     ;;

RECORD LOCKS space id 9389 page no 4 n bits 80 index PRIMARY of table `test`.`s2` trx id 662083 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 8000001e; asc     ;;
 1: len 6; hex 0000000a1a43; asc      C;;
 2: len 7; hex 01000000e32f0a; asc      / ;;
 3: len 4; hex 30303035; asc 0005;;
 4: len 4; hex 45726963; asc Eric;;
 5: len 4; hex 31303135; asc 1015;;
 6: len 4; hex 8000000a; asc     ;;

外键和锁

外键主要用于引用完整性的约束检查.在InnoDB存储引擎中,对于一个外键列,如果没有显示地对这个列加索引,InnoDB存储引擎自动对其加一个索引.因为这样可以避免表锁.

对于外键值的插入或更新,首先需要查询父表中记录,即SELECT父表.但是对于父表的SELECT操作,不是使用一致性非锁定读的方式,因为这样会发生数据不一致的问题.,因此这时使用的是SELECT ...LOCK IN SHARE MODE方式,即主动对父表加上一个S锁,如果这是父表上已经加上了X锁,子表的操作会被阻塞.

mysql> show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

-- 前置准备的语句
DROP TABLE IF EXISTS `parent`;
CREATE TABLE `parent` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of parent
-- ----------------------------
BEGIN;
INSERT INTO `parent` VALUES (1);
INSERT INTO `parent` VALUES (2);
INSERT INTO `parent` VALUES (3);
COMMIT;

DROP TABLE IF EXISTS `child`;
CREATE TABLE `child` (
  `id` int NOT NULL AUTO_INCREMENT,
  `p_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `p_id` (`p_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

操作序号	Session A	Session B
1	begin;
2	delete from parent where id = 3;
3		begin;
4		insert into child select 2,3 lock in share mode;

若两个session都没有进行COMMIT或ROLLBACK操作,操作4会被阻塞,时间长了后,SessionB会被中断提示锁等待超时.
1
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

此时通过查询PERFORMANCE_SCHEMA.DATA_LOCKS可以观察到两个会话的锁信息.

mysql> select * from PERFORMANCE_SCHEMA.DATA_LOCKS\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4566259936:5767:5039513856
ENGINE_TRANSACTION_ID: 507960
            THREAD_ID: 59
             EVENT_ID: 20
        OBJECT_SCHEMA: dicom
          OBJECT_NAME: parent
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5039513856
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4566259936:5768:5039513768
ENGINE_TRANSACTION_ID: 507960
            THREAD_ID: 59
             EVENT_ID: 20
        OBJECT_SCHEMA: dicom
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5039513768
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4566259936:4706:4:1:5042268872
ENGINE_TRANSACTION_ID: 507960
            THREAD_ID: 59
             EVENT_ID: 20
        OBJECT_SCHEMA: dicom
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5042268872
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4566258352:5768:5039511808
ENGINE_TRANSACTION_ID: 507955
            THREAD_ID: 55
             EVENT_ID: 123
        OBJECT_SCHEMA: dicom
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5039511808
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4566258352:5767:5039511720
ENGINE_TRANSACTION_ID: 507955
            THREAD_ID: 55
             EVENT_ID: 123
        OBJECT_SCHEMA: dicom
          OBJECT_NAME: parent
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5039511720
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4566258352:4705:4:4:5042258968
ENGINE_TRANSACTION_ID: 507955
            THREAD_ID: 55
             EVENT_ID: 123
        OBJECT_SCHEMA: dicom
          OBJECT_NAME: parent
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5042258968
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
*************************** 7. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4566258352:4706:5:1:5042259312
ENGINE_TRANSACTION_ID: 507955
            THREAD_ID: 55
             EVENT_ID: 123
        OBJECT_SCHEMA: dicom
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: p_id
OBJECT_INSTANCE_BEGIN: 5042259312
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
7 rows in set (0.01 sec)

从上述信息可以看出
- supremum pseudo-record :相当于比索引中所有值都大,但却不存在索引中,相当于最后一行之后的间隙锁
- SessionA的事务ID为507960,该事务首先对parent加上了的IS锁,然后对child表加上了IX锁,继而对child加上了Next-Key锁
- SessionB的事务ID为507955,该事务首先对child加上了IS锁,然后对parent表加上了IX锁,然后对parent表的记录ID为3的主键索引加上了记录锁(X,REC_NOT_GAP),最后对child的p_id索引加上了S锁.

加锁实战分析

对于唯一键值的锁定,Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列.若唯一索引由多个列组成,而查询仅是查找多个唯一索引列中的其中一个,那么查询其实是range类型,而不是point类型,因此InnoDB存储引擎依然会使用Next-Key Lock进行锁定.

操作序号 SessionA SessionB

1 begin;

2 select * from t where id=5 for update;

3 begin;

4 insert into t select 4;

5 commit;#成功,不需要等待

6 commit;

若是辅助索引,则情况会完全不同

mysql> show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)


create table z(a int ,b int,primary
key(a),key(b));
insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;

表z的列b是辅助索引,若在SessionA中执行下面SQL
1
select * from z where b=3 for update;

观察PERFORMANCE_SCHEMA.DATA_LOCKS表信息

mysql> select * from PERFORMANCE_SCHEMA.DATA_LOCKS\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4566258352:5769:5039511720
ENGINE_TRANSACTION_ID: 507987
            THREAD_ID: 55
             EVENT_ID: 143
        OBJECT_SCHEMA: dicom
          OBJECT_NAME: z
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5039511720
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4566258352:4707:5:4:5042258968
ENGINE_TRANSACTION_ID: 507987
            THREAD_ID: 55
             EVENT_ID: 143
        OBJECT_SCHEMA: dicom
          OBJECT_NAME: z
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: b
OBJECT_INSTANCE_BEGIN: 5042258968
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3, 5
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4566258352:4707:4:4:5042259312
ENGINE_TRANSACTION_ID: 507987
            THREAD_ID: 55
             EVENT_ID: 143
        OBJECT_SCHEMA: dicom
          OBJECT_NAME: z
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5042259312
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4566258352:4707:5:5:5042259656
ENGINE_TRANSACTION_ID: 507987
            THREAD_ID: 55
             EVENT_ID: 143
        OBJECT_SCHEMA: dicom
          OBJECT_NAME: z
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: b
OBJECT_INSTANCE_BEGIN: 5042259656
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 6, 7
4 rows in set (0.01 sec)

可以看到首先对辅助索引值为3这条记录加上了Next-Key Lock(即锁定范围(1,3]),然后辅助索引对应的主键索引加上了Record Lock(即锁定a=5的聚簇索引),最后对辅助索引值为3的下一个键值加上Gap Lock(即锁定范围(3,6))

MySQL中`SELECT...FOR UPDATE`加锁分析

隔离级别与准备工作

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.03 sec)

mysql> show create table z;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| z     | CREATE TABLE `z` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from z;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
| 3 |    3 |
| 5 |    5 |
| 9 |   10 |
+---+------+
4 rows in set (0.00 sec)

明确指定主键并且数据存在,在查询到的数据上加Record Lock
明确指定主键但数据不存在,
- 条件小于当前索引值最小值,在索引最小值上加Gap Lock
- 条件在当前索引值区间内,在该区间的右边界索引值上加Gap Lock
- 条件大于当前索引值最大值,在supremum pseudo-record伪记录上加上Next-Key Lock

不使用主键(不管查询的数据存不存在),加表锁,由下方data_locks表中的信息看出,MySQL对每个主键索引都加上了Next-Key Lock

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from z where b = 2 for update;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 5407970096:1065:5518680984
ENGINE_TRANSACTION_ID: 6973
            THREAD_ID: 48
             EVENT_ID: 125
        OBJECT_SCHEMA: debug
          OBJECT_NAME: z
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5518680984
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 5407970096:4:4:1:5528285720
ENGINE_TRANSACTION_ID: 6973
            THREAD_ID: 48
             EVENT_ID: 125
        OBJECT_SCHEMA: debug
          OBJECT_NAME: z
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5528285720
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 5407970096:4:4:2:5528285720
ENGINE_TRANSACTION_ID: 6973
            THREAD_ID: 48
             EVENT_ID: 125
        OBJECT_SCHEMA: debug
          OBJECT_NAME: z
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5528285720
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 5407970096:4:4:4:5528285720
ENGINE_TRANSACTION_ID: 6973
            THREAD_ID: 48
             EVENT_ID: 125
        OBJECT_SCHEMA: debug
          OBJECT_NAME: z
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5528285720
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 5407970096:4:4:5:5528285720
ENGINE_TRANSACTION_ID: 6973
            THREAD_ID: 48
             EVENT_ID: 125
        OBJECT_SCHEMA: debug
          OBJECT_NAME: z
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5528285720
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 9
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 5407970096:4:4:6:5528285720
ENGINE_TRANSACTION_ID: 6973
            THREAD_ID: 48
             EVENT_ID: 125
        OBJECT_SCHEMA: debug
          OBJECT_NAME: z
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5528285720
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
6 rows in set (0.01 sec)

锁与锁之前的阻塞

因为不同锁之前的兼容性关系,在有些时刻一个事务的锁需要等待另一个事务中的锁释放它所占用的资源,这就是阻塞.
阻塞并不是坏事,其是为了确保事务可以并发且正常地运行.

在InnoDB存储引擎中,参数innodb_lock_wait_timeout用来控制等待的时间(默认是50秒,动态参数),innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作(默认是OFF,代表不回滚,静态参数).

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)

-- 超时提示信息
mysql> select * from test where a=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

需要注意的是,在默认情况下InnoDB存储引擎不会回滚超时引发的错误异常,其实InnoDB存储引擎在大部分情况下都不会对异常进行回滚.

操作序号	SessionA	SessionB
1	begin;
2	select * from t where id=5 for update;
3		begin;
4		insert into t select 4;
5		commit;#成功,不需要等待
6	commit;

参考文献

加锁的目的

加什么样的锁的判断因素

锁的持有周期

锁粒度

加锁规则(《MySQL 45 讲》丁奇)

MySQL 加锁全局视角

MySQL中会加锁的操作

常见加锁操作

表锁

行锁

行锁分析

MySQL特有的加锁操作

不同隔离级别下的锁

在Read Uncommitted级别下

在Read Committed级别下

在Repeatable Read级别下

加锁范围

加锁原则

在Serializable级别下

MySQL锁模式

常规锁模式

锁的属性

锁组合(属性+模式)

锁冲突矩阵

操作与加锁的对照关系

INSERT

无UNIQUE KEY:

有UNIQUE KEY

DELETE

UPDATE

UPDATE操作分解

UPDATE操作分析

UPDATE操作与加锁对照关系

无UNIQUE KEY

有UNIQUE KEY

加GAP锁的情况

原则

查看当前数据库中锁的请求

表锁

行锁

MySQL5.X锁分析

开启SHOW ENGINE INNODB STATUS中锁的详细信息

外键和锁

加锁实战分析

MySQL中SELECT...FOR UPDATE加锁分析

隔离级别与准备工作

锁与锁之前的阻塞

在`Read Uncommitted`级别下

在`Read Committed`级别下

在`Repeatable Read`级别下

在`Serializable`级别下

`INSERT`

无`UNIQUE KEY`:

有`UNIQUE KEY`

`DELETE`

`UPDATE`

`UPDATE`操作分解

`UPDATE`操作分析

`UPDATE`操作与加锁对照关系

无`UNIQUE KEY`

有`UNIQUE KEY`

加`GAP`锁的情况

`MySQL5.X`锁分析

开启`SHOW ENGINE INNODB STATUS`中锁的详细信息

MySQL中`SELECT...FOR UPDATE`加锁分析