Innodb and auto-growth auto_increment

Limitations of Innodb and Myisam storage engines on auto_crement:

The auto-increment column in Innodb must be the index and the first column in the index. Otherwise, the following error will be reported: there can be only one column and it must be defined as a key; myISAM does not have this restriction.

MyISAM adopts the table lock design, so you do not need to consider concurrency issues for self-growth;

How auto_increment works in Innodb:

A table containing the auto_increment column contains an auto-increment counter in the Innodb digital dictionary, which only exists in the memory rather than on the disk;

Value of initializing auto_increment:

After the server is started and inserted into the table, innodb obtains the mutex lock equivalent to: select max (ai_col) from t for update; the value retrieved by the statement is incremented by one, and assigned to columns and auto-growth counters. After the automatic growth counter is initialized, if a specified column value is inserted and the value is greater than the current counter value, the counter is set to the specified column value. If no value is specified, InnoDB adds one to the counter and assigns a new value to the column.

Reasons for non-continuous auto-growth:

When you use a table lock to insert auto_increment values, in order to provide the insertion performance, the lock is released after the auto-increment column is inserted, not after the transaction is completed. Therefore, the auto-incrementing column is not continuous, and transaction rollback is usually performed.

When a value is inserted using mutex, concurrency may result in value continuity;

Auto-growth and lock:

Before Version 5.1.22, a special table lock mechanism-auto-inc Locking was adopted, and the lock was not released after a transaction was completed, instead, the SQL statement is released immediately after the auto-increment value is inserted. This only improves the efficiency of Concurrent Insertion to a certain extent, but for insert... insertion of large data volumes in the select statement will affect the insertion performance;

In Versions later than 5.1.22, innodb increases with lightweight mutex. innodb allocates the auto-increment value to the number of rows inserted in the statement in advance, the subsequent statements can be executed without waiting for the previous statement that has not been executed. because the number of inserted rows is known, the auto-increment values are allocated through mutex in advance );

Example: before an insert Statement 1 is executed, the table's AUTO_INCREMENT = 1, and the number of inserted rows of Statement 1 is known to be 3, innodb pre-allocates three auto-increment values to this statement before executing the actual insert operation of Statement 1. When there is a new insert Statement 2 to be executed, the read AUTO_INCREMENT is 4, in this way, although Statement 1 may not be fully executed, Statement 2 can be directly executed without waiting for Statement 2.

NOTE: If an auto-increment value is explicitly set in a statement, multiple values are inserted at the same time, and the auto-increment feature may cause primary key conflicts. For example: if the current auto-increment value is 4, inserting the following statement will cause a primary key conflict: insert into t1 (c1, c2) VALUES (1, 'A'), (NULL, 'B'), (5, 'C'), (NULL, 'D'); because the growth of all auto-increment values is determined by the maximum auto-increment values of the previous statement;

Lock design parameters: innodb_autoinc_lock_mode parameter value 0 1 (default) 2

0: indicates that the auto-inco table lock mechanism is not recommended.) The advantage is that the auto-increment value can be ensured continuously without rollback, but the concurrency efficiency is slow;

1: Use mutex) to accumulate counters in the memory. For bulk insert, the table lock mechanism is still used. If auto-inc is used to generate a self-increasing value, at this time, the "simple inserts" operation still uses auto-inc to generate auto-increment values. Advantage: Even if binlog replication is in the sbr mode, data continuity can be ensured;

"Bulk insert" refers to the statement that is not sure about the number of rows to be inserted before insertion, such as insert... select; replace... select; load data)

"Simple insert" refers to the statement that can determine the number of inserted rows before insertion, such as insert; replace; does not include statements such as insert... on dupliate key update)

2: Use mutex to obtain auto-increment values for all statements. Advantage: High concurrency efficiency. disadvantage: auto-increment continuity may occur. It is best to copy statements in RBR mode, maintain master-slave data consistency;

This article from the "Technical Achievement dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1299792