innodb_autoinc_lock_mode の表現と値の選択方法についての簡単な説明

mysql> テーブル test_autoinc_lock\G の作成を表示します
************************** 1. 行 ****************************
    テーブル: test_autoinc_lock
テーブルの作成: CREATE TABLE `test_autoinc_lock` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) デフォルト NULL,
 主キー (`id`)、
 キー `idx_a` (`a`)
) エンジン=InnoDB AUTO_INCREMENT=14 デフォルト文字セット=utf8

セット内の 1 行 (0.00 秒)
mysql> test_autoinc_lock から * を選択します。
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 12 | 2 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 7 |
| 6 | 9 |
| 7 | 10 |
+----+------+
セット内の行数は 8 です (0.00 秒)

セッション1
 begin;delete from test_autoinc_lock where a>7;//session2 はこの時点では送信されません
mysql> insert into test_autoinc_lock(a) values(100); //ギャップロックが存在し、ロックはセッション3を待機しています
mysql> insert into test_autoinc_lock(a) values(2); //これも待機状態です。理論的には、これはギャップロックのロック範囲ではないので、何を待っているのでしょうか? session4
mysql> information_schema.innodb_trx\G から * を選択します
************************** 1. 行 ****************************
          トランザクションID: 2317
         trx_state: ロック待機
        trx_started: 2016-10-31 19:28:05
   trx_requested_lock_id: 2317:20
     trx_wait_started: 2016-10-31 19:28:05
        trx_weight: 1
    trx_mysql_スレッドID: 9
         trx_query: test_autoinc_lock(a) の値(2) に挿入
    trx_operation_state: 自動インクリメントロックの設定
     使用中のtrxテーブル: 1
     trx_tables_locked: 1
     trx_lock_structs: 1
   trx_lock_memory_bytes: 360
      ロックされた行数: 0
     trx_rows_modified: 0
  trx_concurrency_tickets: 0
    trx_isolation_level: 繰り返し読み取り
     trx_unique_checks: 1
  trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
     trx_is_read_only: 0
trx_autocommit_non_locking: 0

mysql> test_autoinc_lock から * を選択します。
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 12 | 2 |
| 13 | 2 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 7 |
| 6 | 9 |
| 7 | 10 |
+----+------+
セット内の 9 行 (0.00 秒) //この時点での最大自動インクリメント値は 13 であり、これは以前の最大自動インクリメント値 + 1 であることに注意してください。つまり、セッション 2 は後で予想される自動インクリメント ID を解放し、13 をセッション 3 に残しました。自動インクリメント ID 値の適用は完全にシリアルです。

セッション1
mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)


mysql> test_autoinc_lock から a>7 を削除します。
クエリは正常、2 行が影響を受けました (0.00 秒)
mysql> test_autoinc_lock から * を選択します。
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 12 | 2 |
| 13 | 2 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 7 |
| 6 | 9 |
| 7 | 10 |
+----+------+
セット内の行数は 9 行 (0.00 秒) //この時点での自動増分最大値は 13 であることに注意してください


セッション2
mysql> insert into test_autoinc_lock(a) values(100); //同じギャップロックが存在し、ロックはセッション3を待機しています
mysql> test_autoinc_lock(a) に値(5) を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)


mysql> test_autoinc_lock から * を選択します。
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 12 | 2 |
| 13 | 2 |
| 2 | 3 |
| 3 | 5 |
| 15 | 5 |
| 4 | 7 |
| 5 | 7 |
| 6 | 9 |
| 7 | 10 |
+----+------+
セット内の 10 行 (0.00 秒) //session3 は直接完了し、挿入された自動増分 ID 値が 15 であることに注意してください。これは、session2 に割り当てられるはずの 14 がスキップされることを意味します。自動増分 ID 値が session2 の実行が完了するのを待たずに、すぐに session3 に割り当てられていることがわかります。

マスターセッション1
mysql> '%binlog_for%' のような変数を表示します。
+---------------+-----------+
| 変数名 | 値 |
+---------------+-----------+
| binlog_format | ステートメント |
+---------------+-----------+
セット内の 1 行 (0.00 秒)
mysql> test_autoinc_lock(a) に挿入し、test_auto から * を選択します。
クエリは正常、8388608 行が影響を受け、1 つの警告 (29.85 秒)
レコード: 8388608 重複: 0 警告: 1


マスターセッション2（セッション2はセッション1が完了する前に実行されることに注意してください）
mysql> test_autoinc_lock(a) に値(2) を挿入します。
クエリは正常、1 行が影響を受けました (0.01 秒)
mysql> test_autoinc_lock から * を選択します。ここで、a=2;
+---------+------+
| id | a |
+---------+------+
| 1376236 | 2 |
+---------+------+
セット内の 1 行 (0.00 秒)


スレーブセッション1（この時点で1376236の主キー競合を確認できます）
mysql>スレーブステータスを表示\G
************************** 1. 行 ****************************
        Slave_IO_State: マスターがイベントを送信するのを待機中
         マスターホスト: 10.9.73.139
         マスターユーザー: ucloudbackup
         マスターポート: 3306
        接続再試行: 60
       マスターログファイル: mysql-bin.000006
     読み取りマスターログ位置: 75823243
        リレーログファイル:mysql-relay.000002
        リレーログ位置: 541
    リレーマスターログファイル: mysql-bin.000006
       スレーブIO実行中: はい
      スレーブSQL実行中: いいえ
       レプリケート_Do_DB: 
     レプリケート_無視_DB: 
      テーブルの複製: 
    無視テーブルを複製: 
   Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
          最終エラー番号: 1062
          Last_Error: クエリでエラー 'キー 'PRIMARY' の重複エントリ '1376236' が発生しました。デフォルト データベース: 'test'。クエリ: 'insert into test_autoinc_lock(a) select * from test_auto'
         スキップカウンタ: 0
     実行マスターログポジション: 75822971

INSERT_ID=1376236/*!*/ を設定します。
#161031 21:44:31 サーバー ID 168380811 end_log_pos 75822940 CRC32 0x65797f1c クエリ thread_id=20 exec_time=0 error_code=0
`test`/*!*/ を使用します。
タイムスタンプを 1477921471/*!*/ に設定します。
test_autoinc_lock(a) の値に挿入する(2)
//*!*/;
# 75822940 で
#161031 21:44:31 サーバー ID 168380811 end_log_pos 75822971 CRC32 0xbb91449d Xid = 274
専念 /*！*/;
# 75822971 で
#161031 21:44:26 サーバー ID 168380811 end_log_pos 75823050 CRC32 0xa297b57b クエリ thread_id=57 exec_time=30 error_code=0
タイムスタンプを 1477921466/*!*/ に設定します。
始める
//*!*/;
# 75823050 で
# 75823082 で
#161031 21:44:26 サーバーID 168380811 end_log_pos 75823082 CRC32 0xa5aa31a1 Intvar
INSERT_ID を 1/*!*/ に設定します。
#161031 21:44:26 サーバー ID 168380811 end_log_pos 75823212 CRC32 0x470282ba クエリ thread_id=57 exec_time=30 error_code=0
タイムスタンプを 1477921466/*!*/ に設定します。
test_autoinc_lock(a) に挿入し、test_auto から * を選択します。