In actual development, we always have such a requirement that the data in the table should be kept unique, so we often query such data first, skip if there is, and then insert if there is no, and then add a unique index to the column that can represent the unique data record for the bottom. Although some students basically do so, but also a little bit of other SAO operation 😄😄😄.

# 😄😄😄 if there is a duplicate record, it will ignore the error and return the number of affected rows0
INSERT IGNORE INTO tableName VALUES(a)Copy the code

Some students think that in the case of high concurrency, there is a concurrency problem if the query before insert, I directly shield the error during the repeat insert, do not catch or throw an exception, directly use the return of insert to affect the number of rows to determine whether the insert is successful. This may seem like a good idea, but there are a lot of problems.

Semantically incorrect

You define a unique index in the table, insert duplicate records directly to shield the error, this causes the exception to be swallowed, resulting in functionality with duplicate data bugs cannot be located through the exception log.

The data consistency between the primary and secondary MySQL servers is damaged

Before we talk about how this IGNORE causes inconsistency between primary and secondary data in MySQL, let’s talk about a table level lock in MySQL that is rarely heard of as a developer: auto-inc lock.

In the process of using MySQL, we can add AUTO_INCREMENT attribute for the table of a column, the record is inserted in the column can not specified, the MySQL for the column automatically assigned an increasing value. MySQL > alter table AUTO_INCREMENT increment assigns AUTO_INCREMENT to AUTO_INCREMENT columns

  • With an AUTO_INC lock, a table-level AUTO_INC lock is added when an insert statement is executed, and incrementing values are assigned to each record to be inserted. At the end of the statement, the AUTO_INC lock is released. So while a transaction holds the AUTO_INC lock, other insert statements block to ensure that the increments assigned by a statement are continuous.

  • Use a lightweight lock that is acquired when the increment value is generated for the INSERT statement, and then released after the increment value is generated for the INSERT statement, rather than waiting for the entire INSERT statement to complete.

If our insert statement determines how many records to insert before execution, for example

INSERT INTO test(a) VALUES ('1'), ('b'); 
Copy the code

In this case, we can determine that two records are to be inserted before the execution, so the lightweight lock method is generally used to assign values to AUTO_INCREMENT columns, which can avoid locking the table and improve the insert performance.

Mysql provides a system variable named innodb_atuoINC_lock_mode that controls which of the two methods Mysql uses to assign AUTO_INCREMENT columns.

  • When innodb_atuoINC_lock_mode is 0, the AUTO_INC lock is used.
  • When innodb_atuoINC_lock_mode is set to 1, the two methods are mixed, with lightweight locks used when insert records are certain and AUTO_INC locks used when uncertain.
  • When innodb_atuoINC_lock_mode is 2, lightweight locks are used.

Innodb_autoinc_lock_mode =0 If innodb_autoINC_LOCK_mode =0, the AUTO_INCREMENT value is incremented only after a successful insert but not after a failed insert. When innodb_autoINC_LOCK_mode =1, the value of AUTO_INCREMENT is incremented regardless of whether innodb_autoINC_LOCK_mode is inserted successfully. When innodb_atuoINC_LOCK_mode is set to 2, the values of AUTO_INCREMENT columns in different objects may be crossed, which is unsafe in master-slave replication scenarios. [1]

MySQL is in master-slave mode, isolation level is set to Read Committed, binlog is set to ROW, innodb_atuoINC_lock_mode is set to 1, That is, the value of AUTO_INCREMENT is incremented whether the insert succeeds or not. Mysql > create a new table for master mysql > create a new table for master mysql > create a new table for master mysql > create a new table for master mysql

SQL > alter table structure and autoincrement in slave3307

Run the show master status command to check the master binglog information.

Execute the three SQL statements in the following figure on the master library to see how the master works against Salve.

Alter table AUTO_INCREMENT alter table AUTO_INCREMENT alter table AUTO_INCREMENT alter table AUTO_INCREMENT alter table AUTO_INCREMENT

Master binglog:

If you use ignore, AUTO_INCREMENT is increased even though the record to be inserted is not inserted because of a unique key conflict, and the binlog does not contain the INSERT ignore statement log. The primary key of the table in the slave database is inconsistent with that in the master database.

From this we can guess at the implementation mechanism for IGNORE:

1, try to insert new row into table;

2. If the insert is successful, the normal number of affected rows is returned; If the unique key conflicts (error), the error is ignored and the number of affected rows is returned as 0;

There are three types of InnoDB table insert statements:

  • Simple insert

The number of inserted rows can be predetermined by the statement. This includes single-line, multi-line INSERT and REPLACE statements without subqueries, as well as the INSERT statement… ON DUPLICATE KEY UPDATE.

  • Bulk inserts

The number of inserted rows cannot be predetermined by the statement. Including the INSERT… SELECT and REPLACE… SELECT > and LOAD DATA statements. InnoDB allocates only one AUTO_INCREMENT value for each row processed.

  • Mixed-mode inserts(Mixed mode inserts)

In a simple insert statement, some rows have a value specified for the increment column, while others do not.

For example: mixed mode insert

# id column as increment columnINSERT INTO t1 (id,c1,c2) VALUES (1.1.'a'), (NULL.NULL.'b'), (10.5.'c'), (NULL.NULL.'d');Copy the code

In a real production environment, the business needs to throw an exception instead of swallowing it when a unique key conflict occurs. In addition, as we can see from the above execution procedure, the AUTO_INCREMENT field value is incremented when the desired record is not inserted due to a unique key conflict. Since the insert statement was not executed successfully, no execution record is recorded in the binlog, which means that the AUTO_INCREMENT value of the secondary database is not incremented, that is, the AUTO_INCREMENT value of the primary database is inconsistent with that of the secondary database. If the AUTO_INCREMENT value of the secondary database falls behind that of the primary database due to the failure of the primary database, the AUTO_INCREMENT value of the secondary database will fail to be inserted into the primary database due to the conflict of the AUTO_INCREMENT primary key. This is definitely something you didn’t think of before using INSERT IGNORE, so it’s recommended not to use it in real development! 😄 😄 😄

This article was first published on Sam’s personal blog

  • See how MySQL works