TL;DR
- innodb_autoinc_lock_mode のデフォルトはMySQL 8.0で2になった(5.7とそれ以前は1)
- innodb_autoinc_lock_mode= 2だとステートメントベースのレプリケーションではアンセーフだ 、というのはよく語られるけど
INSERT INTO .. SELECT ..やLOAD DATA ..でauto_incrementで連番を払い出すようなステートメント同士が競合すると、1と2で差が出るSELECT LAST_INSERT_ID()からの、SELECT .. WHERE id > その値みたいなクエリーを使っている時は気を付けた方が良い鴨
(0, 'one') な値を INSERT INTO .. SELECT .. で1000万件突っ込んで、オートインクリメントで払い出させる。 MySQL localhost:3306 ssl SQL > CREATE DATABASE d1;
Query OK, 1 row affected (0.0334 sec)
MySQL localhost:3306 ssl SQL > CREATE TABLE d1.t1 (num int auto_increment PRIMARY KEY, val varchar(32));
Query OK, 0 rows affected (0.1181 sec)
MySQL localhost:3306 ssl SQL > SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.0007 sec)
MySQL localhost:3306 ssl SQL > SET SESSION cte_max_recursion_depth = 50000000;
Query OK, 0 rows affected (0.0002 sec)
MySQL localhost:3306 ssl SQL > INSERT INTO d1.t1 WITH RECURSIVE a AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM a WHERE n < 10000000) SELECT 0, 'one' FROM a;
MySQL localhost:3306 ssl SQL > SELECT * FROM t1 ORDER BY num ASC LIMIT 3;
+-----+-----+
| num | val |
+-----+-----+
| 1 | one |
| 2 | one |
| 3 | one |
+-----+-----+
3 rows in set (0.0183 sec)
MySQL localhost:3306 ssl SQL > SELECT * FROM d1.t1 ORDER BY num DESC LIMIT 3;
+----------+-----+
| num | val |
+----------+-----+
| 10000000 | one |
| 9999999 | one |
| 9999998 | one |
+----------+-----+
3 rows in set (0.0004 sec)
MySQL localhost:3306 ssl SQL > SELECT COUNT(*) FROM d1.t1;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.2423 sec)
これは従来(
で、これを競合させる。
innodb_autoinc_lock_mode= 1 )の動作と変わりはない。で、これを競合させる。
MySQL localhost:3306 ssl SQL > TRUNCATE d1.t1;
Query OK, 0 rows affected (0.2704 sec)
MySQL localhost:3306 ssl SQL > INSERT INTO d1.t1 WITH RECURSIVE a AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM a WHERE n < 10000000) SELECT 0, 'one' FROM a;
MySQL localhost:3306 ssl SQL > INSERT INTO d1.t1 WITH RECURSIVE a AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM a WHERE n < 10000000) SELECT 0, 'two' FROM a; -- こっちは別のターミナル && valカラムに 'two' を割り当てる
とするとこうなる。
MySQL localhost:3306 ssl SQL > SELECT * FROM d1.t1 ORDER BY num ASC LIMIT 3;
+-----+-----+
| num | val |
+-----+-----+
| 1 | one |
| 2 | one |
| 3 | one |
+-----+-----+
3 rows in set (0.0006 sec)
MySQL localhost:3306 ssl SQL > SELECT * FROM d1.t1 ORDER BY num DESC LIMIT 3;
+----------+-----+
| num | val |
+----------+-----+
| 20026855 | two |
| 20026854 | two |
| 20026853 | two |
+----------+-----+
3 rows in set (0.0018 sec)
MySQL localhost:3306 ssl SQL > SELECT COUNT(*) FROM d1.t1;
+----------+
| COUNT(*) |
+----------+
| 20000000 |
+----------+
1 row in set (1.8136 sec)
多少の歯抜けが存在するのは良いとして（AUTO_INCREMENTはもともとそういうもの）、ちゃんと入っているように見える…が。
MySQL localhost:3306 ssl SQL > WITH a AS (SELECT num, val = LEAD(val) OVER (ORDER BY num) AS same_val_prev_row FROM d1.t1)
-> SELECT num FROM a WHERE same_val_prev_row <> 1;
+----------+
| num |
+----------+
| 16383 |
| 20478 |
| 36862 |
..
| 19791570 |
| 19857105 |
| 19895785 |
+----------+
307 rows in set (1 min 1.3050 sec)
このクエリーが何を意味しているかというと、
(1, 'one'), (2, 'one') のように直後の行と同じvalの値を持つ行は1、 (2, 'one'), (3, 'two') のように違うvalを持つ行は0が返されるような same_val_prev_row カラムを計算してそれを <> 1（つまり、直後の行とvalの値が違う) 行を抽出している。
この抽出結果を鑑みて前後の行を確認してみると
MySQL localhost:3306 ssl SQL > SELECT * FROM d1.t1 WHERE num BETWEEN 16383 - 1 AND 16383 + 1;
+-------+-----+
| num | val |
+-------+-----+
| 16382 | one |
| 16383 | one |
| 16384 | two |
+-------+-----+
3 rows in set (0.0554 sec)
MySQL localhost:3306 ssl SQL > SELECT * FROM d1.t1 WHERE num BETWEEN 19857105 - 1 AND 19857105 + 1;
+----------+-----+
| num | val |
+----------+-----+
| 19857104 | two |
| 19857105 | two |
| 19857106 | one |
+----------+-----+
3 rows in set (0.5144 sec)
確かに途中でvalの値が変わるのが発生している。これが
innodb_autoinc_lock_mode= 2 の時の動作。innodb_autoinc_lock_mode はオンラインで変更できないので、 SET PERSIST_ONLY を使って書き込んでから RESTART ステートメントで再起動。 MySQL localhost:3306 ssl SQL > TRUNCATE d1.t1;
SERR ^HQuery OK, 0 rows affected (1.3815 sec)
MySQL localhost:3306 ssl SQL > SET PERSIST_ONLY innodb_autoinc_lock_mode = 1;
Query OK, 0 rows affected (0.0201 sec)
MySQL localhost:3306 ssl SQL > RESTART;
Query OK, 0 rows affected (0.0147 sec)
で、同じことをもう一回。
MySQL localhost:3306 ssl SQL > SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.6702 sec)
MySQL localhost:3306 ssl SQL > INSERT INTO d1.t1 WITH RECURSIVE a AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM a WHERE n < 10000000) SELECT 0, 'one' FROM a;
MySQL localhost:3306 ssl SQL > INSERT INTO d1.t1 WITH RECURSIVE a AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM a WHERE n < 10000000) SELECT 0, 'two' FROM a;
MySQL localhost:3306 ssl SQL > SELECT COUNT(*) FROM d1.t1;
+----------+
| COUNT(*) |
+----------+
| 20000000 |
+----------+
1 row in set (0.4621 sec)
MySQL localhost:3306 ssl SQL > SELECT * FROM d1.t1 ORDER BY num LIMIT 3;
+-----+-----+
| num | val |
+-----+-----+
| 1 | one |
| 2 | one |
| 3 | one |
+-----+-----+
3 rows in set (0.0006 sec)
MySQL localhost:3306 ssl SQL > SELECT * FROM d1.t1 ORDER BY num DESC LIMIT 3;
+----------+-----+
| num | val |
+----------+-----+
| 20026855 | two |
| 20026854 | two |
| 20026853 | two |
+----------+-----+
3 rows in set (0.0006 sec)
MySQL localhost:3306 ssl SQL > WITH a AS (SELECT num, val = LEAD(val) OVER (ORDER BY num) AS same_val_prev_row FROM d1.t1 WHERE num) SELECT * FROM a WHERE same_val_prev_row <> 1;
+----------+
| num |
+----------+
| 10000000 |
+----------+
1 row in set (1 min 8.4337 sec)
MySQL localhost:3306 ssl SQL > SELECT * FROM d1.t1 WHERE num >= 9999999 ORDER BY num LIMIT 3;
+----------+-----+
| num | val |
+----------+-----+
| 9999999 | one |
| 10000000 | one |
| 10026856 | two |
+----------+-----+
3 rows in set (0.0005 sec)
今度は切り替わりが1回だけになった。
performance_schema.data_locks を見ると、 innodb_autoinc_lock_mode= 1 の時はAUTO_INCロックでぶつかりまくっているのに対して MySQL localhost:3306 ssl performance_schema SQL > SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.0007 sec)
MySQL localhost:3306 ssl performance_schema SQL > SELECT * FROM data_locks;
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 140684506070480:1065:140684405311704 | 2183 | 47 | 47 | d1 | t1 | NULL | NULL | NULL | 140684405311704 | TABLE | AUTO_INC | WAITING | NULL |
| INNODB | 140684506071352:1065:140682660470608 | 2182 | 57 | 5 | d1 | t1 | NULL | NULL | NULL | 140682660470608 | TABLE | AUTO_INC | GRANTED | NULL |
| INNODB | 140684506071352:1065:140684405317672 | 2182 | 57 | 5 | d1 | t1 | NULL | NULL | NULL | 140684405317672 | TABLE | IX | GRANTED | NULL |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
3 rows in set (0.0217 sec)
MySQL localhost:3306 ssl performance_schema SQL > SELECT SUM(count_star), SUM(sum_timer_wait) FROM events_waits_summary_by_instance WHERE event_name = 'wait/synch/mutex/innodb/autoinc_mutex';
+-----------------+---------------------+
| SUM(count_star) | SUM(sum_timer_wait) |
+-----------------+---------------------+
| 338 | 49899332 |
+-----------------+---------------------+
1 row in set (0.0146 sec)
innodb_autoinc_lock_mode= 2 ではAUTO_INCロックはぶつかってはいない。mutex取ってる回数は変わらなさげだけどAUTO_INCREMENTを払い出す回数は変わらないだろうからそれはそれで合っている気がする。 MySQL localhost:3306 ssl performance_schema SQL > SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.0005 sec)
MySQL localhost:3306 ssl performance_schema SQL > SELECT * FROM data_locks;
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 140413401562576:1065:140413285501144 | 2570 | 47 | 5 | d1 | t1 | NULL | NULL | NULL | 140413285501144 | TABLE | IX | GRANTED | NULL |
| INNODB | 140413401561704:1065:140413285495192 | 2569 | 48 | 7 | d1 | t1 | NULL | NULL | NULL | 140413285495192 | TABLE | IX | GRANTED | NULL |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
2 rows in set (0.0206 sec)
MySQL localhost:3306 ssl performance_schema SQL > SELECT SUM(count_star), SUM(sum_timer_wait) FROM events_waits_summary_by_instance WHERE event_name = 'wait/synch/mutex/innodb/autoinc_mutex';
+-----------------+---------------------+
| SUM(count_star) | SUM(sum_timer_wait) |
+-----------------+---------------------+
| 349 | 25780608 |
+-----------------+---------------------+
1 row in set (0.0009 sec)
という訳で、「バルクロードの先頭から最後まではAUTO_INCREMENTが一貫していなきゃイヤン！」という場合は
（
innodb_autoinc_lock_mode= 1 の時がいいかもですね。（
SELECT last_insert_id() からの SELECT .. FROM id > さっきの値 でバルクロードしたレコード(だけ)をフェッチできると期待しているような場合かな… )