[转载] 数据库分析手记 —— InnoDB锁机制分析
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了[转载] 数据库分析手记 —— InnoDB锁机制分析，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含36904字，纯文字阅读大概需要53分钟。
内容图文
会先设置一个IS锁
SELECT ... FOR UPDATE 会先设置一个IX锁
不同的锁有不同的兼容性。四种锁的兼容矩阵如下：
|
请求模式 当前模式 |
X |
IX |
S |
IS |
|
X |
冲突 |
冲突 |
冲突 |
冲突 |
|
IX |
冲突 |
兼容 |
冲突 |
兼容 |
|
S |
冲突 |
冲突 |
兼容 |
兼容 |
|
IS |
冲突 |
兼容 |
兼容 |
兼容 |
意向锁之间没有任何冲突，S和X锁之间的关系显而易见。只有X、S锁和意向锁之间的关系比较特殊，后文详细举例分析。
2.2 锁类型
除了有锁模式概念，还有锁的类型，总体分为表级锁和行级锁。
2.2.1 表锁
lock table XXX read；对表XXX加S读锁。
lock table XXX write；对表XXX加X写锁。
意向锁就是表级锁，会跟表锁之间有冲突。
2.2.2 行锁
- 间隙锁(Gap Lock)，只锁间隙。表现为锁住一个区间（注意这里的区间都是开区间，也就是不包括边界值）。
- 记录锁(Record Lock)，只锁记录。表现为仅仅锁着单独的一行记录。
- Next-Key锁(源码中称为Ordinary Lock)，同时锁住记录和间隙。从实现的角度为record lock+gap lock，而且两种锁有可能只成功一个，所以next-key是半开半闭区间，且是下界开，上界闭。一张表中的next-key锁包括：(负无穷大，最小的第一条记录]，(记录之间]，(最大的一条记录，正无穷大)。
- 插入意图锁(Insert Intention Lock)，插入操作时使用的锁。在代码中，插入意图锁实际上是Gap锁上加了一个LOCK_INSERT_INTENTION的标记。也就是说insert语句会对插入的行加一个X记录锁，但是在插入这个行的过程之前，会设置一个Insert intention的Gap锁，叫做Insert intention锁。
看一下官方定义：
|
|
|---|
行锁在X锁上做了一些精确的细分，在代码中称作Precise Mode。这些精确的模式使的锁的粒度更细小，可以减少冲突。而且在事务级别RC或者innodb_locks_unsafe_for_binlog打开的情况下GAP锁会失效。这个很重要，后面会说到。
3. 主键索引锁分析
分析锁之前一定要确认前提条件。
|
锁分析前提条件： 隔离级别为RR： tx_isolation = REPEATABLE-READ
关闭binlog不安全写： innodb_locks_unsafe_for_binlog = OFF
|
同时打开InnoDB监控：create table innodb_lock_monitor(x int) engine=InnoDB;
（关于监控的官方文档：http://dev.MySQL.com/doc/refman/5.1/en/innodb-standard-monitor.html）
3.1 主键索引锁测试
下面的实验建立在一张简单的表A上，我们通过实例观察InnoDB锁机制。假设我们有这样一张表A：
建表语句和数据集如下：
|
| A | CREATE TABLE `A` ( `id` int(11) NOT NULL, `name` varchar(1024) DEFAULT NULL, `t` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `i_name` (`name`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | |
|
MySQL> select * from A; +----+------+ | id | name | +----+------+ | 2 | aa | | 6 | eee | | 7 | aa | | 8 | adf | | 9 | aa | | 11 | a | | 12 | bbb | +------+------+ 7 rows in set (0.00 sec) |
下面通过针对不同的where条件，观察主键索引加锁情况，注意彩色的内容，先把要测试的条件列出来：
| where条件 |
|---|
| =1 |
| <2 |
| =2 |
| <=2 |
| >2 and <6 |
| >=2 and <6 |
| >=2 and <=6 |
| =4 |
| =6 |
| >12 |
| >=12 |
| =12 |
| <=12 and >11 |
| <12 and >11 |
下面逐条分析：
|
case1：=1 |
|
MySQL> select * from A where id=1 for update; Empty set (0.00 sec) ------------ TRANSACTIONS ------------ Trx id counter 721 Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 720, ACTIVE 3 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 178 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 720 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 720lock_mode X locks gap before rec //行锁模式与类型 Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 //上锁的记录 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000714; asc ;; 2: len 7; hex 94000001960110; asc ;; 3: len 2; hex 6161; asc aa;; |
|
解析：
事务720对表A加IX锁，如前面所述，对行加X锁之前先对表加IX
对索引项加X锁，类型为gap锁
表示gap锁加在哪个索引项上，19可以先理解为索引项的物理地址，InnoDB使用Page no. +Heap no.来做行的唯一识别。我们可以将Heap no.理解为页面上的一个自增数值。每条物理记录在被创建时，都会分配一个唯一的heap no。 键值可以理解为一个逻辑值，page no. + heap no. 可以理解为物理地址。
从这里也可以看出gap锁实际是加在索引项上的，不同的索引项之间并没有其他数据结构管理gap锁。 |
一条行记录可由(space_id, page_no, heap_no)唯一标识，记录项字段包含四个部分：
0: len 4; hex 80000002; asc 聚簇值字段
1: len 6; hex 000000000714; asc 事务ID：48位整型的ID值，由最近一次修改该字段的事务决定。
2: len 7; hex 94000001960110; asc 回滚指针：包含最近一次修改该字段的undo记录，长度为7字节（1-bit“is insert”标记；7-bit回滚段ID；4字节页号；2字节undo log的页偏移）
3: len 2; hex 6161; asc aa;; 非主键字段：
|
case2：<2 |
|
select * from A where id<2 for update; |
|
---TRANSACTION 718, ACTIVE 36 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 134 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 718 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 718lock_mode X //后面未标明锁类型的是默认类型，在源码中是LOCK_ORDINARY Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000714; asc ;; 2: len 7; hex 94000001960110; asc ;; 3: len 2; hex 6161; asc aa;; |
|
解析：
在索引项2上加next-key锁，其他两处解释同上 |
|
case3：=2 |
|
MySQL> select * from A where id=2 for update; +----+------+ | id | name | +----+------+ | 2 | aa | +----+------+ 1 row in set (0.00 sec) |
|
------------ TRANSACTIONS ------------ Trx id counter 721 Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 720, ACTIVE 3 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 178 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 720 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 720lock_mode X locks rec but not gap //X模式的记录锁 Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000714; asc ;; 2: len 7; hex 94000001960110; asc ;; 3: len 2; hex 6161; asc aa;; |
|
分析：
只有一个对2这条记录（heap no 19）的记录锁，符合常识。 |
|
case4：<=2 |
|
MySQL> select * from A where id<=2 for update; +----+------+ | id | name | +----+------+ | 2 | aa | +----+------+ 1 row in set (0.00 sec) |
|
------------ TRANSACTIONS ------------ Trx id counter 71F Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 71E, ACTIVE 2 sec 2 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 168 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 71E lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 71Elock_mode X Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; //主key值6 1: len 6; hex 000000000536; asc 6;; 2: len 7; hex ae0000014f0110; asc O ;; 3: len 3; hex 656565; asc eee;; //记录中非主建字段
Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000714; asc ;; 2: len 7; hex 94000001960110; asc ;; 3: len 2; hex 6161; asc aa;; |
|
解析： 表示给heap no 14和heap no 19分别加一个X模式的next-key锁。这里heap no 19是指key为2的记录（看前面的hex值），heap no 14是指key为6的记录，也就是说<=2时不仅会加锁2，还会加锁2后面的一条记录6. 这个条件会导致后面的很多特殊的锁冲突。原因暂时没想到，应该跟索引扫描有关。
|
|
InnoDB锁系统有1个全局对象lock_sys(type lock_sys_t)，而行锁的hash table就存储在其中 struct lock_sys_t { ib_mutex_t mutex; hash_table_t* rec_hash; --行锁hash表，以(space_id, page_no)为hash key，即同一页的所有锁均在一个hash bucket上， ulint n_lock_max_wait_time; // more ... }; |
|
case5：>2 and <6 |
|
MySQL> select * from A where id>2 and id<6 for update; Empty set (0.00 sec) |
|
------------ TRANSACTIONS ------------ Trx id counter 723 Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 722, ACTIVE 3 sec 2 lock struct(s), heap size 376, 1 row lock(s) //2个锁结构，1个行锁 MySQL thread id 1, OS thread handle 0x415b9960, query id 188 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 722 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 722 lock_mode X Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 000000000536; asc 6;; 2: len 7; hex ae0000014f0110; asc O ;; 3: len 3; hex 656565; asc eee;; |
|
只在记录6上加了一个X的next-key锁 |
|
case6:：>=2 and <6 |
|
MySQL> select * from A where id>=2 and id<6 for update; +----+------+ | id | name | +----+------+ | 2 | aa | +----+------+ 1 row in set (0.00 sec) |
|
TABLE LOCK table `test`.`A` trx id 71D lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 71Dlock_mode X locks rec but not gap Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000714; asc ;; 2: len 7; hex 94000001960110; asc ;; 3: len 2; hex 6161; asc aa;;
RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 71Dlock_mode X Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 000000000536; asc 6;; 2: len 7; hex ae0000014f0110; asc O ;; 3: len 3; hex 656565; asc eee;; |
|
记录2上加了一个X的记录锁；记录6上加了一个X的next-key锁 |
|
case7: >=2 and <=6 |
|
select * from A where id>=2 and id<=6 for update; |
|
------------ TRANSACTIONS ------------ Trx id counter 729 Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 727, not started MySQL thread id 2, OS thread handle 0x41743960, query id 207 localhost root ---TRANSACTION 728, ACTIVE 2 sec 3 lock struct(s), heap size 376, 3 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 212 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 728 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 728lock_mode X locks rec but not gap Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000714; asc ;; 2: len 7; hex 94000001960110; asc ;; 3: len 2; hex 6161; asc aa;;
RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 728lock_mode X Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 000000000536; asc 6;; 2: len 7; hex ae0000014f0110; asc O ;; 3: len 3; hex 656565; asc eee;;
Record lock, heap no 18 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000007; asc ;; 1: len 6; hex 0000000005cd; asc ;; 2: len 7; hex a7000001900110; asc ;; 3: len 2; hex 6161; asc aa;; |
|
记录2加了记录锁；记录6和7加了next-key锁 |
|
case8: =4（记录不存在） |
|
MySQL> select * from A where id=4 for update; Empty set (0.01 sec) |
|
------------ TRANSACTIONS ------------ Trx id counter 720 Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 71F, ACTIVE 3 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 172 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 71F lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 71Flock_mode X locks gap before rec Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 000000000536; asc 6;; 2: len 7; hex ae0000014f0110; asc O ;; 3: len 3; hex 656565; asc eee;;
|
|
对一个不存在的记录加锁，锁住间隙，所以在记录6上加了一个X模式的gap锁 |
|
case9: =6 |
|
MySQL> select * from A where id=6 for update; +----+------+ | id | name | +----+------+ | 6 | eee | +----+------+ 1 row in set (0.00 sec) |
|
------------ TRANSACTIONS ------------ Trx id counter 722 Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 71A, not started MySQL thread id 2, OS thread handle 0x41743960, query id 163 localhost root ---TRANSACTION 721, ACTIVE 2 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 182 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 721 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 721 lock_mode X locks rec but not gap Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 000000000536; asc 6;; 2: len 7; hex ae0000014f0110; asc O ;; 3: len 3; hex 656565; asc eee;; |
|
只加一个记录锁 |
对于记录的末尾会不会有什么不同吗？我们继续验证一下：
|
case10：>12 |
|
MySQL> select * from A where id>12 for update; Empty set (0.00 sec) |
|
------------ TRANSACTIONS ------------ Trx id counter 72A Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 727, not started MySQL thread id 2, OS thread handle 0x41743960, query id 207 localhost root ---TRANSACTION 729, ACTIVE 19 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 217 localhost root show engine InnoDB status Trx read view will not see trx with id >= 72A, sees < 72A TABLE LOCK table `test`.`A` trx id 729 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 729 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; |
|
不太一样，对无穷大supremum加了一个X的next-key锁 |
|
case11：>=12 |
|
MySQL> select * from A where id>=12 for update; +----+------+ | id | name | +----+------+ | 12 | bbb | +----+------+ 1 row in set (0.00 sec) |
|
------------ TRANSACTIONS ------------ Trx id counter 72A Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 727, not started MySQL thread id 2, OS thread handle 0x41743960, query id 207 localhost root ---TRANSACTION 729, ACTIVE 62 sec 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 219 localhost root show engine InnoDB status Trx read view will not see trx with id >= 72A, sees < 72A TABLE LOCK table `test`.`A` trx id 729 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 729 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;
RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 729lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000000c; asc ;; 1: len 6; hex 000000000542; asc B;; 2: len 7; hex b900000156011c; asc V ;; 3: len 3; hex 626262; asc bbb;; |
|
除了等于的记录12加记录所，还要给sup加next-key |
|
case12: =12 |
|
MySQL> select * from A where id=12 for update; +----+------+ | id | name | +----+------+ | 12 | bbb | +----+------+ 1 row in set (0.01 sec) |
|
------------ TRANSACTIONS ------------ Trx id counter 78E Purge done for trx‘s n:o < 78D undo n:o < 0 History list length 63 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 789, not started MySQL thread id 10, OS thread handle 0x415b9960, query id 510 localhost root ---TRANSACTION 78D, ACTIVE 17 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 522 localhost root show engine InnoDB status Trx read view will not see trx with id >= 78E, sees < 78E TABLE LOCK table `test`.`A` trx id 78D lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 78D lock_mode X locks rec but not gap /*后面值的操作由于我做了表内容改动，所以heap no变了，但不影响结论*/ Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000000c; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef000001750158; asc u X;; 3: len 3; hex 626262; asc bbb;; |
|
不多说 |
|
case13: <=12 and >11 |
|
MySQL> select * from A where id<=12 and id>11 for update; +----+------+ | id | name | +----+------+ | 12 | bbb | +----+------+ 1 row in set (0.00 sec) |
|
------------ TRANSACTIONS ------------ Trx id counter 78F Purge done for trx‘s n:o < 78D undo n:o < 0 History list length 63 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 789, not started MySQL thread id 10, OS thread handle 0x415b9960, query id 510 localhost root ---TRANSACTION 78E, ACTIVE 9 sec 2 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 527 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 78E lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 78E lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000000c; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef000001750158; asc u X;; 3: len 3; hex 626262; asc bbb;; |
|
sup和记录12加X的next-key锁 |
|
case14: <12 and >11 |
|
MySQL> select * from A where id<12 and id>11 for update; Empty set (0.00 sec) |
|
------------ TRANSACTIONS ------------ Trx id counter 790 Purge done for trx‘s n:o < 78D undo n:o < 0 History list length 63 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 789, not started MySQL thread id 10, OS thread handle 0x415b9960, query id 510 localhost root ---TRANSACTION 78F, ACTIVE 4 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 531 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 78F lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 78F lock_mode X Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000000c; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef000001750158; asc u X;; 3: len 3; hex 626262; asc bbb;; |
|
|
从上面的case实验可以得出一张表，表示不同条件下分别在哪些索引项上，加何种锁：
左边一列是where条件，G表是GAP锁加在哪个索引项上，N是next-key锁，R是记录锁。有了这个表，就可以知道在不同条件的事务并发下，哪些会产生锁等待。
比如，如果有下面两个并发事务发生：
|
t1 |
t2 |
|
select * from A where id<2 for update; |
select * from A where id=2 for update; |
t1事务需要<2的，t2事务需要=2的，表面上两个条件没有重合之处，但是由于他们都是在索引key=2上加锁，所以就会产生冲突：
|
------------ TRANSACTIONS ------------ Trx id counter 71A Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 719, ACTIVE 13 sec starting index read MySQL tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2, OS thread handle 0x41743960, query id 133 localhost root statistics select * from A where id=2 for update ------- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 719 lock_mode X locks rec but not gap waiting Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000714; asc ;; 2: len 7; hex 94000001960110; asc ;; 3: len 2; hex 6161; asc aa;;
------------------ TABLE LOCK table `test`.`A` trx id 719 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 719 lock_mode X locks rec but not gap waiting Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000714; asc ;; 2: len 7; hex 94000001960110; asc ;; 3: len 2; hex 6161; asc aa;;
---TRANSACTION 718, ACTIVE 36 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 134 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 718 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 718 lock_mode X Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000714; asc ;; 2: len 7; hex 94000001960110; asc ;; 3: len 2; hex 6161; asc aa;; |
|
解析： 先执行了TRANSACTION 718，锁住heap no 19 PHYSICAL RECORD，后执行的TRANSACTION 719就会发生lock_mode X locks rec but not gap waiting。注意观察上面锁等待的信息 |
从上表还可知，同样的事情还会发生在下面类似的例子：
|
t1 |
t2 |
|
select * from A where id<=2 for update; |
select * from A where id>2 and id<6 for update; |
3.2 锁类型的精确模式
那么对于下面这种呢：
|
t1 |
t2 |
|
select * from A where id<2 for update; |
select * from A where id=1 for update; |
按照表来说两个事务都会对key=2加锁，而且都是for update的X锁，应该会有冲突，我们看下结果。
|
------------ TRANSACTIONS ------------ Trx id counter 71A Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 719, ACTIVE 216 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2, OS thread handle 0x41743960, query id 136 localhost root TABLE LOCK table `test`.`A` trx id 719 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 719 lock_mode X locks gap before rec Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000714; asc ;; 2: len 7; hex 94000001960110; asc ;; 3: len 2; hex 6161; asc aa;;
---TRANSACTION 718, ACTIVE 239 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 1, OS thread handle 0x415b9960, query id 137 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 718 lock mode IX RECORD LOCKS space id 0 page no 306 n bits 88 index `PRIMARY` of table `test`.`A` trx id 718 lock_mode X Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000714; asc ;; 2: len 7; hex 94000001960110; asc ;; 3: len 2; hex 6161; asc aa;; |
|
结果毫无冲突，两个X锁都成功了，而且都锁住heap no 19，为什么呢？ |
为了解释上面的case就要引入一种行锁的精确模式，我先贴一下源码中对于4中锁类型的注释：
|
#define LOCK_S 4 /* shared */ #define LOCK_X 5 /* exclusive */ ... /* Waiting lock flag */ #define LOCK_WAIT 256 /* this wait bit should be so high that it can be ORed to the lock mode and type; when this bit is set, it means that the lock has not yet been granted, it is just waiting for its turn in the wait queue */ ... /* Precise modes */ #define LOCK_ORDINARY 0 /* this flag denotes an ordinary next-key lock in contrast to LOCK_GAP or LOCK_REC_NOT_GAP */ #define LOCK_GAP 512 /* this gap bit should be so high that it can be ORed to the other flags; when this bit is set, it means that the lock holds only on the gap before the record; for instance, an x-lock on the gap does not give permission to modify the record on which the bit is set; locks of this type are created when records are removed from the index chain of records */ #define LOCK_REC_NOT_GAP 1024 /* this bit means that the lock is only on the index record and does NOT block inserts to the gap before the index record; this is used in the case when we retrieve a record with a unique key, and is also used in locking plain SELECTs (not part of UPDATE or DELETE) when the user has set the READ COMMITTED isolation level */ #define LOCK_INSERT_INTENTION 2048 /* this bit is set when we place a waiting gap type record lock request in order to let an insert of an index record to wait until there are no conflicting locks by other transactions on the gap; note that this flag remains set when the waiting lock is granted, or if the lock is inherited to a neighboring record */ |
精确模式就是从源码中导出的。大家都知到S锁和X锁的兼容关系，但这只是锁的模式，上面说的InnoDB行锁有四种类型：G(gap锁)、R(记录锁)、N(next-key锁)、I(插入意向锁)。那么对于不同类型的锁在X模式下有怎样的兼容关系呢？（S模式下没有什么冲突，不用解释）
有人从源码发掘出一个行锁兼容矩阵，这个在官方文档中并没有。
|
兼容性 |
G |
I |
R |
N |
当前持有的X锁类型 |
|
G |
+ |
+ |
+ |
+ |
要加的X锁类型 |
|
I |
- |
+ |
+ |
- |
|
|
R |
+ |
+ |
- |
- |
|
|
N |
+ |
+ |
- |
- |
+ 代表兼容， -代表不兼容。S锁和S锁是完全兼容的，因此在判别兼容性时不需要对比精确模式，精确模式的检测，用在S、X和X、X之间。从这个精确模式可以看出，<2是N锁，=1是G锁，这两种锁匙完全兼容的，所以即使都是X锁也没有冲突，而=2是R锁，N和R是不兼容的，所以<2和=2冲突。同时大家要注意这个矩阵不是对称的，这点在I锁的兼容性上，大家可以通过类似实验验证。其实上表中的N锁应该分解成G+R锁来看会好理解一些。
这种新的兼容性是为了带来更好的事务并发性，但也会带来一些其他问题呢？比如下面的例子：
|
t1 |
t2 |
|
MySQL> select * from A where id>2 and id<6 for update; Empty set (0.00 sec) |
MySQL> select * from A where id=4 for update; Empty set (0.00 sec) |
|
两个事务都加锁成功： ------------ TRANSACTIONS ------------ Trx id counter 792 Purge done for trx‘s n:o < 78D undo n:o < 0 History list length 63 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 791, ACTIVE 58 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 539 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 791 lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 791 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef00000175011c; asc u ;; 3: len 3; hex 656565; asc eee;;
---TRANSACTION 790, ACTIVE 95 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 10, OS thread handle 0x415b9960, query id 537 localhost root TABLE LOCK table `test`.`A` trx id 790 lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 790 lock_mode X Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef00000175011c; asc u ;; 3: len 3; hex 656565; asc eee;; |
|
|
MySQL> insert into A values(3,‘abc‘); |
|
|
出现锁等待： ------------ TRANSACTIONS ------------ Trx id counter 792 Purge done for trx‘s n:o < 78D undo n:o < 0 History list length 63 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 791, ACTIVE 226 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 543 localhost root show engine InnoDB status TABLE LOCK table `test`.`A` trx id 791 lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 791 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef00000175011c; asc u ;; 3: len 3; hex 656565; asc eee;;
---TRANSACTION 790, ACTIVE 263 sec inserting MySQL tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 10, OS thread handle 0x415b9960, query id 542 localhost root update insert into A values(3,‘abc‘) ------- TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 790 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef00000175011c; asc u ;; 3: len 3; hex 656565; asc eee;;
------------------ TABLE LOCK table `test`.`A` trx id 790 lock mode IX RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 790 lock_mode X Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef00000175011c; asc u ;; 3: len 3; hex 656565; asc eee;;
RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 790 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef00000175011c; asc u ;; 3: len 3; hex 656565; asc eee;;
原因很明显，I锁和G锁不兼容，需要等待，你虽然通过条件>2 and <6加了for update锁，但是并没有真正锁住区间，这时insert 3时会先加I锁，于是要等待t2的G锁冲突了 |
|
|
|
这时如果t2认为锁住了4记录，然后执行 MySQL> insert into A values(4,‘abc‘); 会怎样呢？ 直接死锁了。 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
|
|
很明显，因为t1等待t2的G锁，t2等待t1的N锁。 下面是死锁日志： ------------------------ LATEST DETECTED DEADLOCK ------------------------ 141216 14:54:55 *** (1) TRANSACTION: TRANSACTION 790, ACTIVE 556 sec inserting MySQL tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s) MySQL thread id 10, OS thread handle 0x415b9960, query id 544 localhost root update insert into A values(3,‘abc‘) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 790lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef00000175011c; asc u ;; 3: len 3; hex 656565; asc eee;;
*** (2) TRANSACTION: TRANSACTION 791, ACTIVE 519 sec inserting MySQL tables in use 1, locked 1 3 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 11, OS thread handle 0x41743960, query id 545 localhost root update insert into A values(4,‘abc‘) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 791 lock_mode X locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef00000175011c; asc u ;; 3: len 3; hex 656565; asc eee;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 420 n bits 80 index `PRIMARY` of table `test`.`A` trx id 791 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 00000000077f; asc ;; 2: len 7; hex ef00000175011c; asc u ;; 3: len 3; hex 656565; asc eee;;
*** WE ROLL BACK TRANSACTION (2) |
|
同样的原因还会有下面这种死锁的例子：
|
t1 |
t2 |
|
select * from A where id=3 for update; insert into A values(3,‘abc‘); |
select * from A where id=4 for update; insert into A values(4,‘def‘); |
“InnoDB locks all index records found by the WHERE clause with an exclusive lock and the gaps between them with a shared gap lock.” 网上会有上面这种说法，其实是因为X的gap锁之间是兼容的，其底层实现可能是通过S锁的方式实现的。
精确模式在InnoDB中非常重要，从中也可以观察到一些特性：
- GAP锁基本上跟所有锁都兼容
- Next-key锁和Record锁之间都冲突
- 持有Insert锁的记录可以兼容所有锁，但是Insert锁却不能加到GAP和Next-key锁上。
想想这些特性为什么。
4. 无索引锁
对于无索引的表，由于无法利用索引，因此会对所有记录加Next-key锁，可以观察一下实验结果：
|
建一个无索引的表B： | B | CREATE TABLE `B` ( `id` int(11) NOT NULL, `name` varchar(1024) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | MySQL> select * from B; +----+------+ | id | name | +----+------+ | 3 | dd | | 4 | t | | 5 | dd | | 4 | t | +----+------+ 4 rows in set (0.00 sec) |
|
执行下面语句： MySQL> select * from A where id=2 for update; +----+------+ | id | name | +----+------+ | 2 | aa | +----+------+ 1 row in set (0.00 sec) 由于没有索引，会锁全部索引项。而且全都是N锁。（4条记录和一个无穷大） |
|
------------ TRANSACTIONS ------------ Trx id counter 72F Purge done for trx‘s n:o < 703 undo n:o < 0 History list length 43 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 72C, not started MySQL thread id 1, OS thread handle 0x415b9960, query id 248 localhost root show engine InnoDB status ---TRANSACTION 72E, ACTIVE 3 sec 2 lock struct(s), heap size 376, 5 row lock(s) MySQL thread id 2, OS thread handle 0x41743960, query id 247 localhost root TABLE LOCK table `test`.`B` trx id 72E lock mode IX RECORD LOCKS space id 1 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`B` trx id 72E lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000203; asc ;; //InnoDB自动生成的6字节ID（索引相关文章有介绍） 1: len 6; hex 0000000005be; asc ;; 2: len 7; hex 1a0000018d0110; asc ;; 3: len 4; hex 80000003; asc ;; //记录字段 4: len 2; hex 6464; asc dd;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 0000000005c5; asc ;; 2: len 7; hex 200000018e0110; asc ;; 3: len 4; hex 80000004; asc ;; 4: len 1; hex 74; asc t;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000205; asc ;; 1: len 6; hex 0000000005be; asc ;; 2: len 7; hex 1a0000018d0154; asc T;; 3: len 4; hex 80000005; asc ;; 4: len 2; hex 6464; asc dd;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000206; asc ;; 1: len 6; hex 0000000005c5; asc ;; 2: len 7; hex 200000018e0130; asc 0;; 3: len 4; hex 80000004; asc ;; 4: len 1; hex 74; asc t;; |
还有执行计划中没有使用到索引的也是类似上面这种情形，可以自行验证。
5. 辅助索引锁
下面再观察一下辅助索引的情况：
|
---------+ | transfer | CREATE TABLE `transfer` ( `id` int(11) NOT NULL AUTO_INCREMENT, `trans_id` int(11) NOT NULL, `name` varchar(256) NOT NULL, PRIMARY KEY (`id`), KEY `trans_id` (`trans_id`), KEY `name` (`name`(255)) ) ENGINE=InnoDB AUTO_INCREMENT=1 本文系统来源：http://www.cnblogs.com/zhengran/p/4611616.html |
内容总结
以上是互联网集市为您收集整理的[转载] 数据库分析手记 —— InnoDB锁机制分析全部内容，希望文章能够帮你解决[转载] 数据库分析手记 —— InnoDB锁机制分析所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。