MySQL Forums
Forum List  »  Replication

About AUTO-INC In different mode (1,2 or 3)
Posted by: five small
Date: April 21, 2021 05:38AM

@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.

Options: ReplyQuote


Subject
Views
Written By
Posted
About AUTO-INC In different mode (1,2 or 3)
840
April 21, 2021 05:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.