mysql の innodb_autoinc_lock_mode の詳細な例

次のエディタでは、innodb_autoinc_lock_mode の式形式と値選択の参照方法について簡単に説明します。編集者はこれがとても良いと思ったので、参考として共有します。エディターをフォローして見てみましょう

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

mysql> show create table test_autoinc_lock\G
*************************** 1. row ***************************
    Table: test_autoinc_lock
Create Table: CREATE TABLE `test_autoinc_lock` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)
mysql> select * from test_autoinc_lock;
+----+------+
| id | a  |
+----+------+
| 1 |  1 |
| 12 |  2 |
| 2 |  3 |
| 3 |  5 |
| 4 |  7 |
| 5 |  7 |
| 6 |  9 |
| 7 |  10 |
+----+------+
8 rows in set (0.00 sec)

条件 1 innodb_autoinc_lock_mode が 0 に設定されています

session1
 begin;delete from test_autoinc_lock where a>7;//这时未提交
session2
mysql> insert into test_autoinc_lock(a) values(100);//gap锁的存在，这时处于锁等待
session3
mysql> insert into test_autoinc_lock(a) values(2);//这时同样处于等待状态，理论上这个不是gap锁的锁定范围，那么它是在等什么呢
session4
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
          trx_id: 2317
         trx_state: LOCK WAIT
        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_thread_id: 9
         trx_query: insert into test_autoinc_lock(a) values(2)
    trx_operation_state: setting auto-inc lock
     trx_tables_in_use: 1
     trx_tables_locked: 1
     trx_lock_structs: 1
   trx_lock_memory_bytes: 360
      trx_rows_locked: 0
     trx_rows_modified: 0
  trx_concurrency_tickets: 0
    trx_isolation_level: REPEATABLE READ
     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

このとき、session3 が待機していることを確認します自動インクリメント ロックの場合、自動インクリメント ロック状態の設定中です

session2

エラー 1205 (HY000): ロック待機タイムアウトを超過しました

この時点で、セッション 3 ロック待機タイムアウト終了

session3

this session3を見ると挿入が完了していることがわかります。

mysql> select * from test_autoinc_lock;
+----+------+
| id | a  |
+----+------+
| 1 |  1 |
| 12 |  2 |
| 13 |  2 |
| 2 |  3 |
| 3 |  5 |
| 4 |  7 |
| 5 |  7 |
| 6 |  9 |
| 7 |  10 |
+----+------+
9 rows in set (0.00 sec)//注意看这时的最大自增值是13，也就是之前自增最大值上+1，也就是说session2后来释放了预计生成的自增id，将13留给了session3，自增id值的申请完全是串行顺序的。

結論: innodb_autoinc_lock_mode が 0 (公式の traditional

レベル) の場合、自動インクリメント ロックはテーブル ロック レベルであり、現在の SQL 実行が完了するかロールバックされるまで待機する必要があります。解放されるため、同時実行性が高い状況では、自己増加するロックの競合が比較的大きくなることが考えられます。

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

session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> delete from test_autoinc_lock where a>7;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test_autoinc_lock;
+----+------+
| id | a  |
+----+------+
| 1 |  1 |
| 12 |  2 |
| 13 |  2 |
| 2 |  3 |
| 3 |  5 |
| 4 |  7 |
| 5 |  7 |
| 6 |  9 |
| 7 |  10 |
+----+------+
9 rows in set (0.00 sec)//注意看这时的最大自增值是13


session2
mysql> insert into test_autoinc_lock(a) values(100);//同样gap锁的存在，这时处于锁等待
session3
mysql> insert into test_autoinc_lock(a) values(5);
Query OK, 1 row affected (0.00 sec)


mysql> select * from 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 rows in set (0.00 sec)//session3直接完成了，并且注意观察插入的自增id值是15，也就是跳过了预计分配给session2的14，可以看到自增id值立马就分配给了session3，而不必等session2执行完成

結論: innodb_autoinc_lock_mode が 1 の場合、単一の挿入 SQL がロックはすぐに取得されますそして、現在の SQL 実行が完了するのを待たずにすぐに解放されます (セッションが他のトランザクションで自動インクリメント ロックを取得していない限り)。さらに、SQL がバッチ挿入 SQL (insert into...select...、データのロード、replace ..select... など) である場合でも、依然としてテーブルレベルのロックであり、縮退していると理解できます。現在の SQL が実行されるまで待つ必要があります。 値が 1 の場合、比較的軽量なロックであり、レプリケーションには影響しないと考えられます。唯一の欠点は、生成される自己増加値が必ずしも完全に連続的であるとは限らないことです (ただし、個人的にはそうでないことが多いと思います)。非常に重要です。自動インクリメントされる ID 値に基づいて行数をカウントする必要はありません)

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

まず結論について話しましょう: innodb_autoinc_lock_mode が 2 に設定されている場合、SQL のすべての挿入タイプは即座にロックを取得して解放することができ、現時点では効率が最も高くなります。しかし、新たな問題が発生します。binlog_format がステートメントの場合、insert..select.. ステートメントなどのバッチ挿入もこの場合、大量の自己増加する ID 値はテーブル全体をロックする必要はありません。スレーブは、この SQL を再生するときに必然的に混乱を引き起こします。コピーが安全ではないことを確認するテストを行ってみましょう。

master session1
mysql> show variables like '%binlog_for%';
+---------------+-----------+
| Variable_name | Value   |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> insert into test_autoinc_lock(a) select * from test_auto;
Query OK, 8388608 rows affected, 1 warning (29.85 sec)
Records: 8388608 Duplicates: 0 Warnings: 1


master session2（注意session2在session1执行完成之前执行）
mysql> insert into test_autoinc_lock(a) values(2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_autoinc_lock where a=2;
+---------+------+
| id   | a  |
+---------+------+
| 1376236 |  2 |
+---------+------+
1 row in set (0.00 sec)


slave session1（这时可看到1376236主键冲突）
mysql> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 10.9.73.139
         Master_User: ucloudbackup
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mysql-bin.000006
     Read_Master_Log_Pos: 75823243
        Relay_Log_File: mysql-relay.000002
        Relay_Log_Pos: 541
    Relay_Master_Log_File: mysql-bin.000006
       Slave_IO_Running: Yes
      Slave_SQL_Running: No
       Replicate_Do_DB: 
     Replicate_Ignore_DB: 
      Replicate_Do_Table: 
    Replicate_Ignore_Table: 
   Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
          Last_Errno: 1062
          Last_Error: Error 'Duplicate entry '1376236' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test_autoinc_lock(a) select * from test_auto'
         Skip_Counter: 0
     Exec_Master_Log_Pos: 75822971

メイン ライブラリのバイナリ ログを分析することで、問題の原因を見つけるのは難しくありません。最初のバッチ挿入が実行される前に、2 番目の単純な挿入で自動インクリメントされる ID 値 1376236 のロックが取得されました。 . この時、メインライブラリに書き込むのは問題ありませんが、スレーブライブラリに反映させると、ステートメントベースのレプリケーションなので、必然的に主キーの競合が発生します。

SET INSERT_ID=1376236/*!*/;
#161031 21:44:31 server id 168380811 end_log_pos 75822940 CRC32 0x65797f1c   Query  thread_id=20  exec_time=0   error_code=0
use `test`/*!*/;
SET TIMESTAMP=1477921471/*!*/;
insert into test_autoinc_lock(a) values(2)
/*!*/;
# at 75822940
#161031 21:44:31 server id 168380811 end_log_pos 75822971 CRC32 0xbb91449d   Xid = 274
COMMIT/*!*/;
# at 75822971
#161031 21:44:26 server id 168380811 end_log_pos 75823050 CRC32 0xa297b57b   Query  thread_id=57  exec_time=30  error_code=0
SET TIMESTAMP=1477921466/*!*/;
BEGIN
/*!*/;
# at 75823050
# at 75823082
#161031 21:44:26 server id 168380811 end_log_pos 75823082 CRC32 0xa5aa31a1   Intvar
SET INSERT_ID=1/*!*/;
#161031 21:44:26 server id 168380811 end_log_pos 75823212 CRC32 0x470282ba   Query  thread_id=57  exec_time=30  error_code=0
SET TIMESTAMP=1477921466/*!*/;
insert into test_autoinc_lock(a) select * from test_auto

概要:

1 innodb 行をコピーするとき、innodb_autoinc_lock_mode を 2 に設定できます。この時点で、テーブルはすべての挿入状況で最大の同時実行性を得ることができます2 innodb ステートメントをコピーするとき、 innodb_autoinc_lock_mode は 1 に設定できます。これにより、単純な挿入ステートメントの同時実行性を最大限に高めながら、レプリケーションのセキュリティが確保されます。3 myisam エンジンの場合、テーブルの自動インクリメント ID ロックの種類に関係なく、レベルロック、innodb_autoinc_lock_mode パラメータの設定は無効です (テスト省略)

4 実際、質問者が述べたように、自動インクリメントされた ID 値が innodb エンジンの下で主キーとして使用される場合、挿入速度は、 uuid またはカスタマイズされた主キー。innodb は主キーの集約

インデックス

であるため、実際には主キーの値は主キーの順序でアクセスする必要があるため、データを挿入するときに自動増加する ID 自体は昇順になります。最下層では追加の並べ替え操作を実行する必要がなく、インデックス ページの分割数も減るため、挿入の数が大幅に増加します (他のソリューションで主キーが完全に自動インクリメントされる場合を除く)。

【関連おすすめ】

1. Mysqlの無料ビデオチュートリアル2.

MySQLでの新しいユーザー権限の追加の詳細な例

3. MySQLのパスワードとアクセス制限の変更例の詳細な説明

4. データベースの内容を正規表現で置換する例を詳しく解説

5. PHPがmysqlに画像を格納する例を詳しく解説

以上がmysql の innodb_autoinc_lock_mode の詳細な例の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。