MySQL InnoDB 锁 - For Update 加锁分析：

1. InnoDB锁 简单介绍

2. 当前读加锁分析：REPEATABLE-READ 可重复读、READ-COMMITTED 读已提交

3. 锁模式说明及8.0的data_locks表

一、InnoDB锁

1、全局锁

全局读锁，flush tables with read lock，整库处于只读状态。全局锁的一个典型场景：全库逻辑备份，--single-transaction实现一致性读。

2、表级锁

表锁，lock tables…read/write，主动在表上加读锁或写锁；

元数据锁（meta data lock，MDL），访问表时自动加上，防止DDL和DML并发的冲突，保证读写正确性；

自增锁，表中auto_increment字段的自增值生成控制，内存自增计数器，8.0之后通过redo进行持久化；

意向锁，只会和表级锁发生冲突，不会阻止除表锁请求之外的锁，表明有事务即将、或正锁定某N行；

  1. 意向共享锁（IS）：SELECT ... LOCK IN SHARE MODE，在对应记录行上加锁之前，在表上加意向共享锁；

  2. 意向排它锁（IX）：SELECT .. FOR UPDATE，悲观锁，对表所有扫描过的行都会被加上意向排它锁，若扫描行其中有行锁，则会被阻塞；对SELECT索引加排它锁，阻塞其他事务修改或SELECT ... FOR SHARE（在8.0新增的方式）；

3、行锁

行锁，事务锁，只有发生行锁冲突，才会出现事务锁的排队等待。

两阶段锁协议：行锁在需要时加上，事务结束时释放。

行锁的3种算法：record lock、gap lock、next-key lock

  1. 记录锁 record lock：添加在索引上，表中没有索引时会默认添加在默认创建的聚集索引上；

  2. 间隙锁 gap lock：锁定一个范围，可重复读 隔离级别下，行锁会变成gap锁（范围锁），降低并发性，当前读(dml、select for update)，若where条件列上有索引，加gap lock在索引上，实现可重复读；

  3. Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身；

　　　　① 唯一索引或主键，Next-Key Lock 降为 Record Lock，即仅锁住索引本身，而不是范围。

　　　　② 辅助索引（二级索引），默认使用Next-Key Locking加锁，锁定范围是前一个索引到后一个索引之间范围，左开右闭。

### session 1

root@test 15:51 > begin;

root@test 15:51 > show create table student;

+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table   | Create Table                                                                                                                                                                                                                                                                   |

+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| student | CREATE TABLE `student` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(20) DEFAULT NULL,

  `birthday` datetime DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `ix_name` (`name`),

  KEY `ix_birthday` (`birthday`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |

+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

root@test 15:52 > select * from student;

+----+-------+---------------------+

| id | name  | birthday            |

+----+-------+---------------------+

|  1 | abcd  | 1995-06-27 00:00:00 |

|  2 | abef  | 1995-01-24 00:00:00 |

|  3 | abg   | 1995-07-26 00:00:00 |

|  4 | cdmn  | 1995-06-13 00:00:00 |

+----+-------+---------------------+

root@test 15:52 > select * from student where birthday > '1995-06-27 00:00:00' and birthday < '1995-07-26 00:00:00' for update;

Empty set (0.02 sec)

### session 2

root@test 15:51 > begin;

# 左开

root@test 15:54 > update student set name = 'abcd' where birthday = "1995-06-27 00:00:00";

# 右闭(阻塞更新)

root@test 15:55 > update student set name = 'abg' where birthday = '1995-07-26 00:00:00';

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

二、当前读加锁分析

分析不同隔离级别，当前读（dml、select … for update）的加锁情况

1、REPEATABLE-READ 可重复读

root@test 15:06 > show global variables like "%iso%";

+-----------------------+-----------------+

| Variable_name         | Value           |

+-----------------------+-----------------+

| transaction_isolation | REPEATABLE-READ |

| tx_isolation          | REPEATABLE-READ |

+-----------------------+-----------------+
1.1、表无显式主键和索引
root@test 15:30 > show create table t_student;

+-----------+----------------------------------------------------------------------------------------------------------------------------+

| Table     | Create Table                                                                                                               |

+-----------+----------------------------------------------------------------------------------------------------------------------------+

| t_student | CREATE TABLE `t_student` (

  `id` int(11) NOT NULL,

  `name` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-----------+----------------------------------------------------------------------------------------------------------------------------+

root@test 15:30 > begin;

root@test 15:31 > select * from t_student for update;

+----+-------+

| id | name  |

+----+-------+

|  1 | jack  |

|  2 | kuzma |

|  3 | linda |

+----+-------+

如上，可重复读，表上没有主键（会自动生成隐式主键聚集组织表），也没有索引，全表SELECT的当前读的加锁情况：

　　① 对表添加 IX 锁

　　② 在"supremum"上添加 Next-Key Lock（supremum表示高于表中任何一个索引的值），即最大索引值之后的间隙锁住

　　③ 在三条记录上分别添加 Next-Key Lock

root@test 16:08 > begin;

# where上带条件 id = 3

root@test 16:08 > select * from t_student where id = 3 for update;

+----+-------+

| id | name  |

+----+-------+

|  3 | linda |

+----+-------+

root@test 16:09 > show engine innodb status\G

# 锁信息如上，表上IX，supremum 和 三条记录上添加 Next-Key Lock

# 另开一个session

root@test 16:09 > begin;

root@test 16:13 > insert into t_student values(2,"tom");

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

root@test 16:15 > insert into t_student values(4,"tom");

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

如上，insert为什么会被阻塞呢？delete呢？update呢？

每次插入记录时，所生成的聚集索引(DB_ROW_ID)是自增的，每次都会在表的最后插入，因此就有可能插入id=10这条记录，所以需要添加"supremum pseudo-record"防止数据插入。

---TRANSACTION 582122, ACTIVE 3874 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 6 row lock(s)

MySQL thread id 12529, OS thread handle 123145486712832, query id 94463 localhost root update

insert into t_student values(5,"tom")

Trx read view will not see trx with id >= 582123, sees < 582121

------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 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;;

------------------

TABLE LOCK table `test`.`t_student` trx id 582122 lock mode IX

RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 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;;

即使是不满足where条件的记录上，也会添加Next-Key Lock，目的是为了防止幻读。因此的，其他会话事务执行delete或者update都会造成幻读，也就被阻塞的。

---TRANSACTION 582122, ACTIVE 3788 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 5 row lock(s)

MySQL thread id 12529, OS thread handle 123145486712832, query id 94461 localhost root updating

update t_student set name = "linda" where id = 3

Trx read view will not see trx with id >= 582123, sees < 582121

------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 6; hex 00000000090a; asc       ;;

 1: len 6; hex 00000008e1c8; asc       ;;

 2: len 7; hex f4000001e40110; asc        ;;

 3: len 4; hex 80000001; asc     ;;

 4: len 4; hex 6a61636b; asc jack;;

------------------

TABLE LOCK table `test`.`t_student` trx id 582122 lock mode IX

RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 6; hex 00000000090a; asc       ;;

 1: len 6; hex 00000008e1c8; asc       ;;

 2: len 7; hex f4000001e40110; asc        ;;

 3: len 4; hex 80000001; asc     ;;

 4: len 4; hex 6a61636b; asc jack;;
1.2、表有显式主键无索引

  1. 不带where条件，表上IX，主键最大索引值之后的间隙锁住，表所有记录对应主键加上记录锁

  2. where条件是主键，表上IX，主键添加记录锁（X，REC_NOT_GAP），并且只锁住条件记录，因为主键保证唯一，不需要间隙锁

---TRANSACTION 582139, ACTIVE 12 sec

2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 12528, OS thread handle 123145486434304, query id 94472 localhost root

TABLE LOCK table `test`.`t_student` trx id 582139 lock mode IX

RECORD LOCKS space id 169 page no 3 n bits 72 index PRIMARY of table `test`.`t_student` trx id 582139 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000002; asc     ;;

 1: len 6; hex 00000008e1c8; asc       ;;

 2: len 7; hex f4000001e4011f; asc        ;;

 3: len 5; hex 6b757a6d61; asc kuzma;;
1.3、表无显式主键有索引

  1. 不带where条件，表上IX，id最大索引值之后的间隙锁住，表所有记录对应索引加上记录锁

  2. where条件是普通索引字段

root@test 22:01 > show create table t_student;

+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------+

| Table     | Create Table                                                                                                                                     |

+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------+

| t_student | CREATE TABLE `t_student` (

  `id` int(11) NOT NULL,

  `name` varchar(10) DEFAULT NULL,

  KEY `ix_id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------+

root@test 22:10 > begin;

root@test 22:11 > select * from t_student where id = 2 for update;

+----+-------+

| id | name  |

+----+-------+

|  2 | kuzma |

+----+-------+

root@test 22:11 > show engine innodb status\G

---TRANSACTION 582176, ACTIVE 14 sec

4 lock struct(s), heap size 1136, 3 row lock(s)

MySQL thread id 12534, OS thread handle 123145487269888, query id 94485 localhost root

TABLE LOCK table `test`.`t_student` trx id 582176 lock mode IX

RECORD LOCKS space id 170 page no 4 n bits 72 index ix_id of table `test`.`t_student` trx id 582176 lock_mode X

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: len 4; hex 80000002; asc     ;;

 1: len 6; hex 000000000912; asc       ;;

RECORD LOCKS space id 170 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582176 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 6; hex 000000000912; asc       ;;

 1: len 6; hex 00000008e200; asc       ;;

 2: len 7; hex be00000146011f; asc     F  ;;

 3: len 4; hex 80000002; asc     ;;

 4: len 5; hex 6b757a6d61; asc kuzma;;

RECORD LOCKS space id 170 page no 4 n bits 72 index ix_id of table `test`.`t_student` trx id 582176 lock_mode X locks gap before rec

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: len 4; hex 80000003; asc     ;;

 1: len 6; hex 000000000913; asc       ;;

如上，过滤一个普通索引时的加锁情况是：

　　① 对表添加 IX锁

　　② 对id=2对应的索引添加 Next-Key Lock锁，区间是(-∞, 2]

　　③ 对索引对应的聚集索引添加 X记录锁

　　④ 为防止幻读（因为是普通索引，可重复插入id=2的记录），对索引记录区间(2,3)添加间隙锁（lock_mode X locks gap before rec）

也就是说，（测试用例有些许不恰当）如果说放大到10~20~30测试，id直到29之间的记录插入都会被阻塞，而插入id=30因为不在间隙锁范围，不会被阻塞。

1.4、表有显式主键和索引

  1. 无where条件，如1.2.1描述

  2. 有where条件，其实，无论是主键，还是唯一键索引，只要能准确定位的，都是表上IX，主键或唯一键添加记录锁（X，REC_NOT_GAP），并且只锁住条件记录

2、READ-COMMITTED 读已提交

root@test 23:57 > show global variables like "tx_isolation";

+---------------+----------------+

| Variable_name | Value          |

+---------------+----------------+

| tx_isolation  | READ-COMMITTED |

+---------------+----------------+
2.1、表无显式主键和索引
root@test 23:57 > show create table t_people;

+----------+-------------------------------------------------------------------------------------------------------------------------------+

| Table    | Create Table                                                                                                                  |

+----------+-------------------------------------------------------------------------------------------------------------------------------+

| t_people | CREATE TABLE `t_people` (

  `id` int(11) DEFAULT NULL,

  `name` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+----------+-------------------------------------------------------------------------------------------------------------------------------+

root@test 23:58 > select * from t_people;

+------+-------+

| id   | name  |

+------+-------+

|    1 | jack  |

|    2 | kuzma |

|    3 | linda |

+------+-------+

如下，表t_people上加 IX意向排它锁，表中所有行的隐藏主键上（行格式里第三个就是隐藏主键，在表中没有显示主键的时候自动生成用于组织表数据）加 X记录锁，只锁定记录本身，非范围间隙锁。

如果带上where条件呢？

root@test 00:44 > begin;

root@test 00:45 > select * from t_people where id = 2 for update;

+------+-------+

| id   | name  |

+------+-------+

|    2 | kuzma |

+------+-------+

root@test 00:45 > show engine innodb status;

......

---TRANSACTION 582053, ACTIVE 4 sec

2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 12521, OS thread handle 123145487269888, query id 94339 localhost root

TABLE LOCK table `test`.`t_people` trx id 582053 lock mode IX

RECORD LOCKS space id 166 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_people` trx id 582053 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 6; hex 000000000908; asc       ;;

 1: len 6; hex 00000008e19e; asc       ;;

 2: len 7; hex d2000001580110; asc     X  ;;

 3: len 4; hex 80000002; asc     ;;

 4: len 5; hex 6b757a6d61; asc kuzma;;

 ......

 # 只是在 id = 2 的记录上加了X记录锁
2.2、表有显式主键无索引

  1. 不带where条件，如上的，表中所有行上加 X记录锁，不锁定范围

  2. where条件是主键字段时，对表加 IX锁，对主键添加记录锁(X, REC_NOTGAP)，where了主键，主键已经保证唯一，非范围锁，锁加在具体的记录上

root@test 00:49 > show engine innodb  status;

---TRANSACTION 582071, ACTIVE 7 sec

2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 12521, OS thread handle 123145487269888, query id 94353 localhost root

TABLE LOCK table `test`.`t_people` trx id 582071 lock mode IX

RECORD LOCKS space id 167 page no 3 n bits 72 index PRIMARY of table `test`.`t_people` trx id 582071 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000002; asc     ;;

 1: len 6; hex 00000008e19e; asc       ;;

 2: len 7; hex d2000001580110; asc     X  ;;

 3: len 5; hex 6b757a6d61; asc kuzma;;

  1. where条件包含主键字段和非主键字段，和上面是一样的，因为主键已经定位了加锁。

2.3、表无显示主键有索引

  1. 不带where条件，同样的，表中所有行上加 X记录锁，不锁定范围

  2. where条件是普通索引字段，唯一索引亦如是

root@test 20:24 > show create table t_student;

+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table     | Create Table                                                                                                                                         |

+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------+

| t_student | CREATE TABLE `t_student` (

  `id` int(11) NOT NULL,

  `name` varchar(10) DEFAULT NULL,

  KEY `ix_name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------+

root@test 20:25 > begin;

root@test 20:27 > select * from t_student where name = 'kuzma' for update;

+----+-------+

| id | name  |

+----+-------+

|  2 | kuzma |

+----+-------+

root@test 20:27 > show engine innodb status;

---TRANSACTION 582093, ACTIVE 57 sec

3 lock struct(s), heap size 1136, 2 row lock(s)

MySQL thread id 12525, OS thread handle 123145486712832, query id 94388 localhost root

TABLE LOCK table `test`.`t_student` trx id 582093 lock mode IX

RECORD LOCKS space id 168 page no 4 n bits 72 index ix_name of table `test`.`t_student` trx id 582093 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: len 5; hex 6b757a6d61; asc kuzma;;

 1: len 6; hex 00000000090b; asc       ;;

RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582093 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 6; hex 00000000090b; asc       ;;

 1: len 6; hex 00000008e1c8; asc       ;;

 2: len 7; hex f4000001e4011f; asc        ;;

 3: len 4; hex 80000002; asc     ;;

 4: len 5; hex 6b757a6d61; asc kuzma;;

如上，当where条件上是普通索引字段时，加锁情况如下：

　　① 表上加 IX，意向排它锁

　　② name='kuzma' 对应的索引上添加 X 记录锁

　　③ GEN_CLUST_INDEX，对应的（自动生成的）聚集索引上添加 X 记录锁

2.4、表有显式主键有索引

  1. 不带where条件，所有行记录添加记录锁，并没有间隙范围锁

  2. where条件是普通索引或主键索引，如前面的，先是表上加 IX 意向排它锁，然后在对应的普通索引上添加 X 记录锁（如果是主键则无需），最后在对应的聚集索引（主键）上添加 X 记录锁

三、锁模式说明及8.0的data_locks表

3.1、锁模式LOCK_MODE

show engine innodb status中见到的 lock_mode，如下整理说明：

锁模式 说明
IX   意向排它锁
X   Next-Key Lock锁定记录和记录之前的间隙(X)
S   Next-Key Lock锁定记录和记录之前的间隙(S)
X, REC_NOT_GAP   只锁定记录本身(X)
S, REC_NOT_GAP   只锁定记录本身(S)
X, GAP   间隙锁，不锁定记录本身(X)
S, GAP   间隙锁，不锁定记录本身(S)
X, GAP, INSERT_INTENTION   插入意向锁，间隙范围，排它

3.2、8.0的data_locks表

在8.0之前，查看锁信息可以通过设置innodb_status_output_locks=ON，然后在 show engine innodb status的输出里，看到详细的锁信息打印，如前面展示。

8.0之后，performance_schema.data_locks，新增的，记录表加锁情况，可将之前的innodb status锁信息打印更好的以表格的形式记录展示。

最新文章

  1. 【MySQL】 查询某个数据库有多少张数据表
  2. ls /usr/linkapp 没反应
  3. uploadify参数
  4. Vijos 1816统计数字（计数排序）
  5. JavaScript——exec和match
  6. NABCD竞争性需求分析
  7. HDU 3078 Network
  8. Android中的时间日期选择器
  9. Calling a Web API From a .NET Client (C#)
  10. php 过滤html标签的函数
  11. Python--day14（迭代器）
  12. swipper的一个小坑
  13. List排序Collections.sort 重写compare
  14. 【Python】微博自动抢红包
  15. favicon.ico
  16. Java基础-面向接口编程-JDBC详解
  17. 结构（struct）
  18. TortoiseGit上传项目代码到github方法（超简单）
  19. vivo面试经验4（linux基本操作，最基本，必须得会！！）
  20. BZOJ3720 Gty的妹子树 【树分块】

热门文章

  1. C语言获取字符年月日时分秒毫秒
  2. nim_duilib（5）之option
  3. 【九度OJ】题目1183：守形数 解题报告
  4. 【LeetCode】905. Sort Array By Parity 解题报告（Python）
  5. 【LeetCode】876. Middle of the Linked List 解题报告（Python）
  6. 1269 - Consecutive Sum
  7. 【错误】NetBeans2007：Cannot find nbproject/build-impl.xml
  8. 第二十一个知识点：CRT算法如何提高RSA的性能?
  9. Accelerating Deep Learning by Focusing on the Biggest Losers
  10. WEB文档在线预览解决方案