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

前提条件: Percona 5.6 バージョン、トランザクション分離レベルは RR

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 innodb_autoinc_lock_modeが0に設定されている

セッション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

このとき、セッション 3 が自動インクリメントロックを待機しており、自動インクリメントロック設定状態になっていることを確認します。

セッション2

エラー 1205 (HY000): ロック待機タイムアウトを超えました。トランザクションを再起動してください。

この時点で、セッション3はロックされ、タイムアウトが終了するのを待ちます。

セッション3

ここでセッション3を見ると、挿入が完了したことがわかります。

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 値の適用は完全にシリアルです。

結論: innodb_autoinc_lock_modeが0の場合、正式には伝統的と呼ばれる

レベルでは、自動インクリメントロックはテーブルロックレベルであり、現在の SQL が実行されるかロールバックされるまで待機してから解放されます。このように、同時実行性が高い場合、自動インクリメントロックの競合が比較的大きくなると考えられます。

条件2 innodb_autoinc_lock_modeが1に設定されている

セッション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 に割り当てられていることがわかります。

結論：innodb_autoinc_lock_modeが1の場合、正式には連続ロックと呼ばれます。

このレベルでは、単一の挿入 SQL であれば、現在の SQL の実行を待たずに、すぐにロックを取得および解放できます (セッションが他のトランザクションで既に自動インクリメントロックを取得している場合を除く)。さらに、SQL が insert into ...select ...、load data、replace ..select.. などのバッチ挿入 SQL である場合、それは依然としてテーブルレベルのロックであり、解放される前に現在の SQL が実行されるまで待機する必要があるという状態に悪化すると考えられます。

値が 1 の場合は比較的軽いロックであり、レプリケーションに影響しないと考えられます。唯一の欠点は、生成された自動インクリメント値が完全に連続していない可能性があることです (ただし、個人的には、これはあまり重要ではない場合が多く、自動インクリメント ID 値に基づいて行数をカウントする必要はないと考えています)。

条件3: innodb_autoinc_lock_modeが2に設定されている

まず結論を述べます。innodb_autoinc_lock_mode を 2 に設定すると、すべての挿入型 SQL ステートメントが即座にロックを取得および解放できるため、最も効率的です。ただし、新しい問題が発生します。binlog_format ステートメントの場合、レプリケーションの安全性は保証されません。これは、insert ..select... ステートメントなどのバッチ挿入でも、テーブル全体をロックせずに大量の自動増分 ID 値を即座に取得できるためです。この SQL を再生すると、スレーブは必然的に混乱します。レプリケーションが安全ではないことを確認するためのテストを実行しましょう。

マスターセッション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

マスターデータベースのバイナリログを分析すると、問題の原因を簡単に見つけることができます。最初のバッチ挿入が完了していないときに、2番目の単純な挿入で自動インクリメントID値1376236のロックを取得しました。この時点ではマスターデータベースへの書き込みに問題はありませんが、スレーブデータベースに反映されると、ステートメントベースのレプリケーションであるため、主キーの競合が発生します。

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 から * を選択します。

要約:

1 Innodb 行をコピーするときに、innodb_autoinc_lock_mode を 2 に設定すると、すべての挿入状況でテーブルの同時実行性が最大化されます。

2 innodb ステートメントをレプリケートする場合、innodb_autoinc_lock_mode を 1 に設定して、単純な挿入ステートメントの同時実行性を最大限に高めながら、レプリケーションのセキュリティを確保できます。

3 MyISAMエンジンの場合、どのような自動インクリメントIDロックであってもテーブルレベルロックの場合、innodb_autoinc_lock_modeパラメータの設定は無効です（テスト省略）

4 実際、質問者は、InnoDB エンジンで自動増分 ID 値を主キーとして使用する場合、InnoDB は主キークラスター化インデックスであり、実際の主キー値は主キーの順序でアクセスする必要があるため、UUID やカスタム主キーに比べて挿入速度が向上すると述べています。自動増分 ID 自体は昇順であるため、データを挿入するときに、基礎レイヤーで追加のソート操作を実行する必要がなく、インデックスページの分割数が減り、挿入速度が大幅に向上します (他のソリューションでも主キーが完全に自動増分されることを保証できる場合を除く)。

innodb_autoinc_lock_mode の表現と値の選択参照方法についての上記の簡単な説明は、エディターが皆さんと共有する内容のすべてです。参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。

<<: ES6実装クラスのプライベート変数の書き方をいくつか詳しく説明します

>>: 開発環境にUbuntu 16をインストール後の初期設定