Usage of auto_increment in mysql

Auto increment
You can use the following command to query the auto-increment step of mysql,

Mysql> show variables like 'auto _ inc % ';
+ -------------------------- + ------- +
| Variable_name | Value |
+ -------------------------- + ------- +
| Auto_increment_increment | 1 |
| Auto_increment_offset | 1 |
+ -------------------------- + ------- +

Here, auto_increment_increment is the step of auto-increment, value is 1 indicates + 1 each time, auto_increment_offset indicates the auto-increment offset, that is, the auto-increment starts, and value is 1 indicates increasing from 1.

The InnoDB auto-incrementing primary key is obtained through its own auto-incrementing counter, which is completed through the table lock mechanism.
The table lock is released only after the insertion is complete, that is, after the transaction is completed.

To solve the problem of auto-increment primary key lock tables, innodb_autoinc_lock_mode is introduced, which is achieved through the growth mechanism of lightweight mutex.

Mysql> show variables like 'innodb _ autoinc_lock_mode ';
+ -------------------------- + ------- +
| Variable_name | Value |
+ -------------------------- + ------- +
| Innodb_autoinc_lock_mode | 1 |
+ -------------------------- + ------- +
Innodb_autoinc_lock_mode has three values:

0. Table lock

1. The default value is mutex. It will "pre-apply" redundant values and may be discontinuous.
2. The auto-increment value is discontinuous and the performance is good.
When innodb_autoinc_lock_mode = 1, the redundant id (handler. cc: compute_next_insert_id). After the insert operation is complete, these reserved IDs are especially left blank, specifically, the current maximum id after the pre-application is written back to the table.

Recently, it was found that a data table is not continuous due to frequent insert on duplicate key update. The specific reason is that the data will be pre-allocated with an id, but if the insertion fails to perform the update operation, this id is discarded, and the value is skipped in the next insert operation.

Application

When synchronizing data between the mysql master and the Master (two machines synchronize data with each other), you need to set

Auto_increment_increment = 2
Auto_increment_offset = 1 and 2

In this way, we can avoid conflicts between the values of the auto-increment fields when the two servers perform updates at the same time.