@Peter Brawley (●'◡'●)
where i have learned:
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html
-------------------------------------------------------------------------------
Hello sir,Now I'm confused about different insertion methods in different innodb_autoinc_lock_mode.
The following terms are used in describing innodb_autoinc_lock_mode settings:
1.“INSERT-like” statements;
2.“Simple inserts”;
3.“Bulk inserts”
4.“Mixed-mode inserts”
And there are three possible settings for the innodb_autoinc_lock_mode variable:
1.innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
2.innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
3.innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
I want to know ,if i use "Mixed-mode insert" ,for example:
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
in “traditional” lock mode(mode=0),it will hold a AUTO-INC until the end of the statement.But in mode=1 or mode=2,this statement won't get the AUTO_INC and it will never be locked.
I want to know if I understand this correctly?
-------------------------------------------------------------------------------
And my second question,about Bulk inserts
My table is follows,only one column with PK and AI:
create table t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)engine=innodb;
-------------------------------------------------------------------------------
innodb_autoinc_lock_mode = 1 :
mysql> select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 select null from t1;/* 1st Bulk Insert */
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t1 select null from t1;/* 2nd Bulk Insert */
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t1 select null from t1;/* 3rd Bulk Insert */
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t1 select null from t1;/* 4th Bulk Insert */
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
| 7 |
| 8 |
| 9 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
+----+
16 rows in set (0.00 sec)
-------------------------------------------------------------------------------
innodb_autoinc_lock_mode = 2 :
mysql> select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.00 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 select null from t1;/* 1st Bulk Insert */
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t1 select null from t1;/* 2nd Bulk Insert */
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t1 select null from t1;/* 3rd Bulk Insert */
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t1 select null from t1;/* 4th Bulk Insert */
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
| 7 |
| 8 |
| 9 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
+----+
16 rows in set (0.00 sec)
-------------------------------------------------------------------------------
I got the SAME result between 1 and 2,and I know that the auto-increment values are allocated one at a time(twice every allocated than before)
MySQL Official:
"In this lock mode, auto-increment values are guaranteed to be unique and monotonically increasing across all concurrently executing “INSERT-like” statements. However, because multiple statements can be generating numbers at the same time (that is, allocation of numbers is interleaved across statements), the values generated for the rows inserted by any given statement may not be consecutive."
My ability is not enough to read MySQL source code,So I guess if we use mode=1 and Bulk-insert,there will be a Auto-Inc in this statement.But if we use mode=2 and Bulk-insert ,like what office said,allocation of numbers is interleaved across statements.
Can you give me a example or teach me how to prove that if this statement is locked and row number(id) is interleaved across statements.