Description:
The generation of autoincr is controled by two thread variables: auto_increment_offset and auto_increment_increment. Generally, If the two variables fixed, all new generated autoincr values got the same modulo number(autoincr % auto_increment_increment + 1 = auto_increment_offset)
However, when set auto_increment_offset > auto_increment_increment, the generated autoincr values may have different modulo. And in a multi-primary MGR cluster, the behaviour may lead to duplicate key conflict.
How to repeat:
##########################################################
# connect a mysql server of 8.0.21
# check the generated autoincr values
##########################################################
## generate autoincr values for [offset, increment] = [11, 7]
mysql> set auto_increment_increment=7;
Query OK, 0 rows affected (0.00 sec)
mysql> set auto_increment_offset=11;
Query OK, 0 rows affected (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table t1 (c1 int auto_increment primary key, c2 int) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 2 | 1 |
| 9 | 2 |
| 11 | 3 |
| 18 | 4 |
| 46 | 1 |
| 53 | 2 |
| 60 | 3 |
| 67 | 4 |
| 74 | 1 |
| 81 | 2 |
| 88 | 3 |
| 95 | 4 |
+----+------+
12 rows in set (0.00 sec)
mysql> truncate table t1;
Query OK, 0 rows affected (0.05 sec)
## generate autoincr values for [offset, increment] = [69, 7]
mysql> set auto_increment_offset=69;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 4 | 1 |
| 11 | 2 |
| 18 | 3 |
| 25 | 4 |
| 32 | 1 |
| 39 | 2 |
| 46 | 3 |
| 53 | 4 |
| 60 | 1 |
| 67 | 2 |
| 69 | 3 |
| 76 | 4 |
+----+------+
12 rows in set (0.00 sec)
## "offset % increment" is not same, but there has duplicated value such as: [11,18,60,67]
mysql> select 11%7;
+------+
| 11%7 |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
mysql> select 69%7;
+------+
| 69%7 |
+------+
| 6 |
+------+
1 row in set (0.00 sec)
##########################################################
# connect a multi-primary MGR cluster of 8.0.21
# check the lock conflict on MGR
##########################################################
1. Build a MGR with three nodes, and set 'auto_increment_offset=69' on the first node, set 'auto_increment_offset=11' on the second node
## first node
mysql> show variables like 'auto_increment_%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 7 |
| auto_increment_offset | 69 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
## second node
mysql> show variables like 'auto_increment_%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 7 |
| auto_increment_offset | 11 |
+--------------------------+-------+
2 rows in set (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
2. begin trasaction and insert records (there have duplicated records)
## first node
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 4 | 1 |
| 11 | 2 |
| 18 | 3 |
| 25 | 4 |
| 32 | 1 |
| 39 | 2 |
| 46 | 3 |
| 53 | 4 |
| 60 | 1 |
| 67 | 2 |
| 69 | 3 |
| 76 | 4 |
+----+------+
12 rows in set (0.00 sec)
## second node
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t1(c2) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 2 | 1 |
| 9 | 2 |
| 11 | 3 |
| 18 | 4 |
| 46 | 1 |
| 53 | 2 |
| 60 | 3 |
| 67 | 4 |
| 74 | 1 |
| 81 | 2 |
| 88 | 3 |
| 95 | 4 |
+----+------+
12 rows in set (0.00 sec)
3. commit transaction
## first node
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
## second node
mysql> commit;
ERROR 1180 (HY000): Got error 149 - 'Lock deadlock; Retry transaction' during COMMIT
Suggested fix:
If the auto_increment_offset and auto_increment_increment fixed, all generated autoincr values have the save modulo.
