Primary key repeat duplicate entry ' xxx ' for key ' PRIMARY ' when MySQL primary key is set to Auto_increment, concurrency performance test occurs

Primary key repeat duplicate entry ' xxx ' for key ' PRIMARY ' when MySQL primary key is set to Auto_increment, concurrency performance test occurs

Workaround:

Add settings to the [mysqld] fragment in My.cnf innodb_autoinc_lock_mode=0

Also pay attention to increasing the number of active links in JDBC, such as setting jdbc.maxactive=300, because setting innodb_autoinc_lock_mode=0 may result in more links.

Note that this method only needs to be set during the concurrency performance test, because this method requires full table locking when inserting records, poor performance, usually only use the default settings innodb_autoinc_lock_mode=1 in the production environment , the official MySQL documentation is described:

Http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html

The Innodb_autoinc_lock_mode property has the following meanings:

1, Innodb_autoinc_lock_mode = 0 ("traditional" lock mode)
This approach is the same as mysql5.1.22 before, this way is characterized by "table-level locking", the concurrency of poor

2, Innodb_ Autoinc_lock_mode = 1 ("Consecutive" lock mode)
This is the default mode in the new version, recommended, concurrency is relatively high, characterized by "consecutive", This guarantees that the newly inserted auto_increment ID in the same INSERT statement is contiguous.
This mode:
"simple inserts": Directly through the parse statement, get the number to insert, and then assign enough Auto_increment ID at once, will only lock the whole process of allocation.
Bulk inserts: Because the number of insertions cannot be determined, the same table-level lock is used as in the previous pattern.
Mixed-mode Inserts: Parses the statement directly, obtains the worst-case required number of insertions, and allocates enough auto_increment IDs at once, only locks the entire assigned process. It is important to note that in this way, too many IDs are allocated, resulting in a "waste". For example INSERT into T1 (C1,C2) VALUES (1, ' a '), (null, ' B '), (5, ' C '), (null, ' d '), 5 IDs are allocated one time, regardless of whether the user has specified part of the Id;insert ... The on DUPLICATE key update is allocated one time, regardless of whether the update operation will be performed because of DUPLICATE KEY in the future insert process.
Note: When the master MySQL version <5.1.22,slave MySQL version >=5.1.22, slave needs to set the Innodb_autoinc_lock_mode to 0 because the default innodb_ Autoinc_lock_mode is 1, for insert ... On DUPLICATE KEY update and insert into T1 (C1,C2) VALUES (1, ' a '), (null, ' B '), (5, ' C '), (null, ' d '), the execution result is different, the actual environment will generally use the insert ... O N DUPLICATE KEY UPDATE.

3, Innodb_autoinc_lock_mode = 2 ("Interleaved" lock mode)
This mode is to assign one, without locking the table, only locks the allocation ID of the process, and Innodb_autoinc_lock_mode = 1 is the difference is that it does not pre-allocate more than one, this way the most concurrency. However, there is a problem in replication when Binlog_format is statement-based (SBR statement-based replication) because it is to be assigned one, so that when concurrent execution, "Bulk Inserts "when assigning to other inserts at the same time, there will be a master-slave inconsistency (different from the library execution result and the main library execution result) because Binlog only records the start Insert ID.

Reference Documentation:

Http://biancheng.dnbcw.info/mysql/356243.html

Primary key repeat duplicate entry ' xxx ' for key ' PRIMARY ' when MySQL primary key is set to Auto_increment, concurrency performance test occurs