Detailliertes Beispiel für innodb_autoinc_lock_mode in MySQL-MySQL-Tutorial-php.cn

Der folgende Editor bietet Ihnen eine kurze Diskussion über die Ausdrucksform und die Referenzmethode zur Wertauswahl von innodb_autoinc_lock_mode. Der Herausgeber findet es ziemlich gut, deshalb werde ich es jetzt mit Ihnen teilen und es allen als Referenz geben. Folgen wir dem Editor und werfen wir einen Blick darauf.

Voraussetzung: Percona-Version 5.6, Transaktionsisolationsstufe ist 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)

Nach dem Login kopieren

Bedingung 1 innodb_autoinc_lock_mode ist auf 0 gesetzt

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

Nach dem Login kopieren

Überprüfen Sie zu diesem Zeitpunkt, ob Sitzung3 auf die automatische Inkrementsperre wartet und sich im Einstellungsstatus der automatischen Inkrementsperre befindet

Sitzung2

FEHLER 1205 (HY000): Wartezeit für Sperre überschritten; versuchen Sie, die Transaktion neu zu starten

Zu diesem Zeitpunkt beträgt die Wartezeit für Sperre bei Sitzung3Beenden

Sitzung3

Wenn Sie sich Sitzung3 zu diesem Zeitpunkt ansehen, können Sie sehen, dass die Einfügung abgeschlossen ist.

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值的申请完全是串行顺序的。

Nach dem Login kopieren

Fazit: Wenn innodb_autoinc_lock_mode 0 ist, ist es das, was der Beamte traditionell nennt

Die selbsterhöhende Sperre ist eine Tabellensperre und muss warten, bis die aktuelle SQL-Ausführung abgeschlossen oder zurückgesetzt wird, bevor sie freigegeben wird. Auf diese Weise ist es unter Bedingungen hoher Parallelität denkbar, dass die Konkurrenz um sich selbst erhöht wird. Die Zunahme der Sperren ist relativ groß.

Bedingung 2 innodb_autoinc_lock_mode ist auf 1 gesetzt

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执行完成

Nach dem Login kopieren

Fazit : Wenn innodb_autoinc_lock_mode 1 ist, handelt es sich um die offizielle fortlaufende

-Ebene. Wenn es sich zu diesem Zeitpunkt um eine einzelne Einfügungs-SQL handelt, kann die Sperre sofort abgerufen und sofort freigegeben werden, ohne auf die zu warten Die aktuelle SQL-Ausführung ist abgeschlossen (es sei denn, eine Sitzung hat in anderen Transaktionen eine automatische Inkrementierungssperre erhalten). Wenn es sich bei SQL um eine Batch-Insert-SQL handelt, z. B. Einfügen in ... Auswählen ..., Laden von Daten, Ersetzen ... Auswählen ..., handelt es sich außerdem immer noch um eine Sperre auf Tabellenebene, die als degenerierend verstanden werden kann dazu, auf die Ausführung des aktuellen SQL warten zu müssen. Erst dann freigegeben.

Wenn der Wert 1 ist, handelt es sich um eine relativ leichte Sperre, die sich nicht auf die Replikation auswirkt. Der einzige Fehler besteht darin, dass der generierte Selbstinkrementwert nicht unbedingt vollständig kontinuierlich ist (aber ich persönlich denke). Dies ist oft nicht sehr wichtig und es besteht keine Notwendigkeit, die Anzahl der Zeilen basierend auf dem automatisch inkrementierten ID-Wert zu zählen.)

Bedingung 3 innodb_autoinc_lock_mode ist auf 2 gesetzt

Erste Schlussfolgerung: Wenn innodb_autoinc_lock_mode auf 2 gesetzt ist, können alle Einfügetypen von SQL die Sperre sofort erhalten und freigeben, was am effizientesten ist. Es entsteht jedoch ein neues Problem: Wenn binlog_format eine Anweisung ist, kann die Replikation zu diesem Zeitpunkt keine Sicherheit garantieren, da in diesem Fall auch Batch-Einfügungen wie insert..select..-Anweisungen sofort ausgeführt werden können . Nachdem eine große Anzahl von sich selbst erhöhenden ID-Werten abgerufen wurde, ist es nicht erforderlich, die gesamte Tabelle zu sperren. Der Slave führt zwangsläufig zu Verwirrung bei der SQL-Wiedergabe. Führen wir einen Test durch, um zu überprüfen, ob das Kopieren nicht sicher ist.

master session1
mysql> show variables like &#39;%binlog_for%&#39;;
+---------------+-----------+
| 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 &#39;Duplicate entry &#39;1376236&#39; for key &#39;PRIMARY&#39;&#39; on query. Default database: &#39;test&#39;. Query: &#39;insert into test_autoinc_lock(a) select * from test_auto&#39;
         Skip_Counter: 0
     Exec_Master_Log_Pos: 75822971

Nach dem Login kopieren

Es ist nicht schwierig, die Ursache des Problems zu finden, wenn wir das Binlog der Hauptbibliothek analysieren, bevor die erste Batch-Einfügung ausgeführt wird, die zweite einfach Zu diesem Zeitpunkt haben wir eine Sperre mit einem automatisch inkrementierten ID-Wert von 1376236 erhalten. Zu diesem Zeitpunkt gibt es beim Schreiben in der Hauptbibliothek kein Problem, es wird jedoch in der Slave-Bibliothek reflektiert, da es sich um eine Anweisung handelt -basierte Replikation führt zwangsläufig zu einem Primärschlüsselkonflikt.

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

Nach dem Login kopieren

Zusammenfassung:

1 Beim Kopieren der Innodb-Zeile können Sie innodb_autoinc_lock_mode auf 2 setzen Zu diesem Zeitpunkt kann die Tabelle in allen Einfügungssituationen die maximale Parallelität erreichen

2 Beim Kopieren der innodb-Anweisung können Sie innodb_autoinc_lock_mode auf 1 setzen, um die Sicherheit der Replikation zu gewährleisten und die maximale Parallelität einer einfachen Einfügung zu erhalten Anweisung

3 Im Fall der Myisam-Engine ist das Festlegen des Parameters innodb_autoinc_lock_mode ungültig (Testen weggelassen)

4 In der Tat, unabhängig davon, welche Art von automatischer Inkrementierungs-ID-Sperre eine Sperre auf Tabellenebene ist Was der Fragesteller erwähnt hat, ist die automatische Inkrementierung unter der Innodb-Engine. Wenn der ID-Wert als Primärschlüssel verwendet wird, kann die Einfügegeschwindigkeit im Vergleich zu UUID oder benutzerdefiniertem Primärschlüssel verbessert werden, da Innodb ein Primärschlüsselcluster Index und der Zugriff auf den tatsächlichen Primärschlüsselwert muss in der Reihenfolge des Primärschlüssels erfolgen. Dann erfolgt die automatische Erhöhung der ID selbst in aufsteigender Reihenfolge. Wenn also Daten einfügt, ist dies nicht erforderlich Führen Sie zusätzliche Sortiervorgänge durch und reduzieren Sie außerdem die Anzahl der Indexseitenaufteilungen, wodurch die Einfügegeschwindigkeit erheblich erhöht wird (es sei denn, andere Lösungen können ebenfalls garantieren, dass der Primärschlüssel vollständig automatisch inkrementiert wird)

[Verwandte Empfehlungen]

Kostenloses MySQL-Video-Tutorial

Neue Beispiele für Benutzerberechtigungen hinzufügen

3 >Detaillierte Beispiele zum Ändern von Passwörtern und Zugriffsbeschränkungen in MySQL

Verwenden Sie reguläre Ausdrücke zum Ersetzen in der Datenbank. Detaillierte Erläuterung von Inhaltsbeispielen

Detaillierte Erläuterung von Beispielen für die PHP-Speicherung von Bildern in MySQL

Das obige ist der detaillierte Inhalt vonDetailliertes Beispiel für innodb_autoinc_lock_mode in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!