INSERT DUPLICATE KEY の lock まわりの挙動について

mysql> show create table fuga;                                                                                                                                                                                                               
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fuga  | CREATE TABLE `fuga` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(10) NOT NULL DEFAULT '',
  `memo` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`,`name`),
  UNIQUE KEY `memo` (`memo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from hoge.fuga;                                                                                                                                                                                                              
+----+------------+--------------------+
| id | name       | memo               |
+----+------------+--------------------+
|  1 | mama       | hello friends      |
| 10 | papa       | hello hoge         |
|  2 | me         | hello me           |
|  5 | sister     | hello mysister     |
|  6 | old sister | hello old mysister |
+----+------------+--------------------+
5 rows in set (0.00 sec)

mysql> show index from hoge.fuga;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fuga  |          0 | PRIMARY  |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| fuga  |          0 | PRIMARY  |            2 | name        | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| fuga  |          0 | memo     |            1 | memo        | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> select * from hoge.fuga USE INDEX (memo);                                                                                                                                                                                             
+----+------------+--------------------+
| id | name       | memo               |
+----+------------+--------------------+
|  1 | mama       | hello friends      |
| 10 | papa       | hello hoge         |
|  2 | me         | hello me           |
|  5 | sister     | hello mysister     |
|  6 | old sister | hello old mysister |
+----+------------+--------------------+
5 rows in set (0.00 sec)

まず unique key の duplicate が発生する場合

                                                                                                                                                                                                                                             
mysql> set GLOBAL innodb_status_output=ON;                                                                                                                                                                                                   
Query OK, 0 rows affected (0.00 sec)                                                                                                                                                                                                         
                                                                                                                                                                                                                                             
mysql> set GLOBAL innodb_status_output_locks=ON;                                                                                                                                                                                             
Query OK, 0 rows affected (0.00 sec)

# ユニークキーがduplicate keyになるinsert
mysql>  insert into hoge.fuga values (8, 'supersis', 'hello mysister') ON DUPLICATE KEY UPDATE id=values(id),name=values(name);                                                                                                              
Query OK, 2 rows affected (0.00 sec)    

mysql> show engine innodb status
.....
---TRANSACTION 23312, ACTIVE 263 sec
4 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
MySQL thread id 24, OS thread handle 0x7fe07d8e2700, query id 2181 localhost root cleaning up
TABLE LOCK table `hoge`.`fuga` trx id 23312 lock mode IX
RECORD LOCKS space id 491 page no 4 n bits 80 index `memo` of table `hoge`.`fuga` trx id 23312 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 14; hex 68656c6c6f206d79736973746572; asc hello mysister;;
 1: len 4; hex 80000005; asc     ;;
 2: len 6; hex 736973746572; asc sister;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 18; hex 68656c6c6f206f6c64206d79736973746572; asc hello old mysister;;
 1: len 4; hex 80000006; asc     ;;
 2: len 10; hex 6f6c6420736973746572; asc old sister;;

RECORD LOCKS space id 491 page no 3 n bits 80 index `PRIMARY` of table `hoge`.`fuga` trx id 23312 lock_mode X locks rec but not gap ← PRIMARY KEY は gap なしでロックしている
Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 736973746572; asc sister;;
 2: len 6; hex 000000005b10; asc     [ ;;
 3: len 7; hex 5d0000016b1b29; asc ]   k );;
 4: len 14; hex 68656c6c6f206d79736973746572; asc hello mysister;;

RECORD LOCKS space id 491 page no 4 n bits 80 index `memo` of table `hoge`.`fuga` trx id 23312 lock_mode X locks gap before rec  ← PRIMARY KEY は前のレコードとの間で gap lock をとっている
Record lock, heap no 8 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 14; hex 68656c6c6f206d79736973746572; asc hello mysister;;
 1: len 4; hex 80000008; asc     ;;
 2: len 8; hex 7375706572736973; asc supersis;;

# プライマリキーがduplicate keyになるinsert
mysql>   insert into hoge.fuga values (2, 'me', 'hello new me') ON DUPLICATE KEY UPDATE name=values(name),memo=values(memo);    
Query OK, 2 rows affected (0.00 sec)    

mysql> show engine innodb status
------------
TRANSACTIONS
------------
Trx id counter 23319
Purge done for trx's n:o < 23319 undo n:o < 0 state: running but idle
History list length 1040
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 26, OS thread handle 0x7fe07d975700, query id 2192 localhost root init
show engine innodb status
---TRANSACTION 23291, not started
MySQL thread id 19, OS thread handle 0x7fe07d944700, query id 2155 localhost root cleaning up
---TRANSACTION 23273, not started
MySQL thread id 23, OS thread handle 0x7fe07d913700, query id 2183 localhost root cleaning up
---TRANSACTION 23314, ACTIVE 46 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 0x7fe07d8e2700, query id 2190 localhost root cleaning up
TABLE LOCK table `hoge`.`fuga` trx id 23314 lock mode IX
RECORD LOCKS space id 491 page no 3 n bits 80 index `PRIMARY` of table `hoge`.`fuga` trx id 23314 lock_mode X locks rec but not gap # プライマリインデックスのみロックして gap lock 無し
Record lock, heap no 13 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 2; hex 6d65; asc me;;
 2: len 6; hex 000000005b12; asc     [ ;;
 3: len 7; hex 5e000001b9071f; asc ^      ;;
 4: len 12; hex 68656c6c6f206e6577206d65; asc hello new me;;

RECORD LOCKS space id 491 page no 4 n bits 80 index `memo` of table `hoge`.`fuga` trx id 23314 lock_mode X locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 12; hex 68656c6c6f206e6577206d65; asc hello new me;;
 1: len 4; hex 80000002; asc     ;;
 2: len 2; hex 6d65; asc me;;