MySQL Auto_increment's Pit

Background:

The InnoDB engine uses the B_TREE structure to hold table data, which requires a unique key to represent each row of records (such as a two-level index record reference).

The logic for handling primary keys in the InnoDB table definition is:

1. If the table defines a primary key, use the primary key to uniquely locate a record

2. If no primary key is defined, InnoDB generates a globally unique rowid to locate a record

The origin of Auto_increment:

1.Innodb It is highly recommended to customize a primary key in the design table because ROWID is globally unique, so if there are many tables that do not have a primary key defined, contention is generated on the build rowID.

      /* Dictionary system struct */      struct dict_sys_struct{      Mutex_tmutex;      row_id_trow_id;      ......      }

ROW_ID is protected by a mutex and is written to the file header of the data dictionary each time the checkpoint is made.

2. When the user has customized the primary key, because most of the actual application deployment of distributed, so the primary key value generation, in a centralized way, more easily to achieve uniqueness, so auto_increment is very suitable.

Auto_increment also brings two benefits:

1. The value of the auto_increment is table-level and does not generate contention at the DB level

2. Due to the sequential nature of the auto_increment, it reduces the likelihood of random reads, guaranteeing a buffer hit of the page being written. (Admittedly, the concurrency of the write is large enough to generate contention for hotspot blocks)

Auto_increment caused by the bug:

Environment: MySQL 5.6.16 Version, Binlog_format=row

Case Recurrence:

     CREATE TABLE TEST.KKK (c int (one) default NULL, ID int (one) not NULL auto_increment, D int (one) default NULL, PRIMARY KEY (i d), unique key D (d))     engine=innodb default charset=latin1;     INSERT into TEST.KKK values (5, 27,4);     Replace into TEST.KKK (c, id, D) VALUES (6, 4);     Commit

     Show CREATE TABLE:     Main Library: auto_increment=36     Library: auto_increment=28

When the primary and standby switch is made, the primary key conflict is caused, and the slave recovery exception.

Similarly, the Insert on duplication UPDATE statement has the same problem.

Aliyun RDS Branch Bug fix

　　the cause of the problem: InnoDB for Auto_increment, when the statement is insert, it is incremented, and the Update,delete statement is not updated.

When the Replace statement is executed in the main library:

1. Follow the INSERT statement to find the UK conflict.

2. Evolve into an UPDATE statement for updates.

So in the main library, although insert failed, but the auto_increment also incremented up. But to the repository, the row format produces only an update row event,

The repository does not know that the main library is a replace statement, and the insert fails, so the auto_increment is not incremented in the standby repository.

　　FIX: in standby, the auto_increment increment for update may have side effects, that is, auto_increment waste, but no primary key conflicts.

Those years experienced the Auto_increment pit:

1. Instance restart, primary key conflict:

The Autoinc value in memory that is initialized by using the Select MAX (ID) from table after the system restarts. So, if you design a business table that has a delete operation, then once your instance crash, it may re-use the ID value you used before. If you need to keep a logical backup of this table, you may encounter a primary key conflict problem.

2. Load file blocking:

In setting up the innodb_autoinc_lock_mode=1, MySQL maintains the ID continuity of a single statement statement, and when the number of inserts is not determined, the entire execution of the statement

Hold Lock_auto_inc,/* Locks the auto-inc counter of a table in an exclusive mode */

This lock is table-level and uses mutex mode.

Therefore, on busy tables, if you are importing data, be careful to block normal business writes, and concurrent writes will also block at this time.

MySQL auto_increment Pits