Last active
October 14, 2017 02:55
-
-
Save twainy/9923b5edecf5a0abf4aed846368f70af to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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;; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment