Exemple détaillé de innodb_autoinc_lock_mode dans MySQL

L'éditeur suivant vous proposera une brève discussion sur la forme d'expression et la méthode de référence de sélection de valeur de innodb_autoinc_lock_mode. L'éditeur pense que c'est plutôt bien, alors je vais le partager avec vous maintenant et le donner comme référence. Suivons l'éditeur et jetons un coup d'œil

Prérequis : percona version 5.6, le niveau d'isolation des transactions est 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)

La condition 1 innodb_autoinc_lock_mode est définie sur 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

À ce stade, la session3 attend le verrouillage par incrémentation automatique et est dans l'état de verrouillage par incrémentation automatique

session2

ERREUR 1205 (HY000) : Délai d'attente du verrouillage dépassé ; essayez de redémarrer la transaction

À ce moment, le délai d'attente du verrouillage de la session3Quitter

session3

En regardant la session3 à ce moment, vous pouvez voir que l'insertion est terminée.

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

Conclusion : Lorsque innodb_autoinc_lock_mode vaut 0, c'est ce que le responsable appelle traditionnel

Au niveau, le verrou auto-croissant est un niveau de verrouillage de table, et il doit attendre que l'exécution SQL en cours soit terminée ou annulée avant d'être libérée. Dans ce cas, sous une concurrence élevée, il est concevable qu'il y ait une concurrence pour l'auto-augmentation. les écluses sont relativement grandes.

Condition 2 innodb_autoinc_lock_mode est définie sur 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执行完成

Conclusion : Lorsque innodb_autoinc_lock_mode vaut 1, c'est le niveau

consécutif officiel A ce moment, s'il s'agit d'une seule insertion SQL, le verrou peut être obtenu immédiatement et libéré immédiatement sans attendre le. SQL actuel. L'exécution est terminée (sauf si une session a acquis un verrou à incrémentation automatique dans d'autres transactions). De plus, lorsque le SQL est un SQL d'insertion par lots, tel que insérer dans...select..., charger des données, remplacer ..select..., il s'agit toujours d'un verrou au niveau de la table, qui peut être compris comme dégénératif. en devant attendre que le SQL actuel soit exécuté seulement ensuite publié.

On peut considérer que lorsque la valeur est 1, il s'agit d'un verrou relativement léger et n'affectera pas la réplication. Le seul défaut est que la valeur d'auto-incrémentation générée n'est pas forcément complètement continue (mais je pense personnellement. c'est souvent ce n'est pas très important, et il n'est pas nécessaire de compter le nombre de lignes en fonction de la valeur de l'identifiant auto-incrémenté)

Condition 3 innodb_autoinc_lock_mode est défini sur 2

Première conclusion : lorsque innodb_autoinc_lock_mode est défini sur 2, tous les types d'insertion de SQL peuvent immédiatement obtenir le verrou et le libérer, ce qui est le plus efficace. Mais un nouveau problème sera introduit : lorsque binlog_format est une instruction, la réplication à ce moment ne peut pas garantir la sécurité, car les insertions par lots, telles que les instructions insert..select.., peuvent également être exécutées immédiatement dans ce cas. .Après avoir obtenu un grand nombre de valeurs d'ID auto-croissantes, il n'est pas nécessaire de verrouiller la table entière. L'esclave provoquera inévitablement une confusion lors de la lecture du SQL. Faisons un test pour vérifier que la copie n'est pas sûre.

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

Il n'est pas difficile de trouver la cause du problème lorsque l'on analyse le binlog de la bibliothèque principale Avant que la première insertion batch ne soit exécutée, la deuxième simple. insert est exécuté. À ce stade, nous avons obtenu un verrou avec une valeur d'ID auto-incrémentée de 1376236. À ce stade, il n'y a aucun problème d'écriture dans la bibliothèque principale, mais lorsqu'il est reflété dans la bibliothèque esclave, car il s'agit d'une instruction. réplication basée sur les clés primaires, un conflit de clé primaire se produira inévitablement.

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

Résumé :

1 Lors de la copie de la ligne innodb, vous pouvez définir innodb_autoinc_lock_mode sur 2 , à ce stade, la table peut obtenir la concurrence maximale dans toutes les situations d'insertion

2 Lors de la copie de l'instruction innodb, vous pouvez définir innodb_autoinc_lock_mode sur 1 pour garantir la sécurité de la réplication et obtenir la concurrence maximale d'une simple insertion

3 Dans le cas du moteur myisam, quel que soit le type de verrouillage d'identifiant à incrémentation automatique qui constitue un verrouillage au niveau de la table, la définition du paramètre innodb_autoinc_lock_mode n'est pas valide (tests omis)

4 En fait , ce que l'interrogateur a mentionné est l'incrémentation automatique sous le moteur innodb. Lorsque la valeur id est utilisée comme clé primaire, la vitesse d'insertion peut être améliorée par rapport à l'uuid ou à la clé primaire personnalisée, car innodb est un cluster de clé primaire index , et la valeur réelle de la clé primaire doit être accessible dans l'ordre de la clé primaire. Ensuite, l'ID à augmentation automatique lui-même est par ordre croissant, donc lorsque insère des données , la couche inférieure n'a pas besoin de le faire. effectuer des opérations de tri supplémentaires, et cela réduit également le nombre de fractionnements de pages d'index, augmentant ainsi considérablement la vitesse d'insertion (à moins que d'autres solutions puissent également garantir que la clé primaire est complètement auto-incrémentée)

[Recommandations associées]

1. Tutoriel vidéo gratuit MySQL

2 Ajouter un nouveau dans MySQL Exemples détaillés d'autorisations utilisateur

3. >Exemples détaillés de modification des mots de passe et des restrictions d'accès dans MySQL

Utiliser des expressions régulières à remplacer dans la base de données Explication détaillée des exemples de contenu

Explication détaillée d'exemples de stockage d'images php dans MySQL

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!