InnoDB table lock problem _ auto_increment

InnoDB is most relish in its implementation of advanced features such as row locks. In contrast, MyISAM Table locks are somewhat retarded. However, many people ignore this point. Sometimes InnoDB acts as a table lock in mysql5.0: for example, when there is an auto_increment field in the table, innoDB stores a counter in the memory to record the auto_increment value. When a new row of data is inserted, InnoDB locks the counter with a table lock until the insertion ends. There is no problem if data is inserted in one row, but if a large number of concurrent inserts are discarded, the table lock will cause SQL blocking, which not only affects the efficiency, in addition, max_connections may crash instantly.

In MySQL, as long as the auto_increment field is used, the InnoDB table lock problem cannot be avoided. If you have to deal with a large number of concurrent inserts, you can either discard the auto_increment, instead, you can use a custom primary key, but you must select it with caution. Otherwise, you may fall into another trap. For example, some people will avoid the auto_increment table lock problem by using the uuid primary key, but this may cause the application to fall into a more serious Io bottleneck problem, for the reason, refer to my previous article:

Another way is to use mysql5.1. In this version, a new configuration option is displayed: innodb_autoinc_lock_mode, which is used to adjust the lock policy when auto_increment is used, currently, there are three options:

Innodb_autoinc_lock_mode = 0 ("traditional" lock mode)
Innodb_autoinc_lock_mode = 1 ("consecutive" lock mode)

Innodb_autoinc_lock_mode = 2 ("interleaved" lock mode)

CakePHP itself has a uuid implementation, so I have been trying to use UUID as the primary key for a long time. Although MySQL is my most commonly used database, I like the uuid primary key better than the auto_increment_int primary key, on the one hand, because the uuid database is unrelated, on the other hand, when you want to distribute programs on multiple servers, UUID operations are simpler.

However, MySQL does not yet support native UUID. When used with the InnoDB table type, some problems may occur:

First, InnoDB performs physical sorting on the primary key.This is good news for auto_increment_int, because the last primary key is always at the end. However, for UUID, this is a bad message, because UUID is disorganized and the position of the primary key inserted each time is uncertain. It may be at the beginning or in the middle, during Physical sorting of primary keys,It will inevitably cause a large number of Io operations to affect efficiency.

Fortunately,The string at the beginning of the uuid algorithm of CakePHP is timestamp-based.Therefore, this problem does not exist for CakePHP UUID alone. If it is another UUID algorithm, you must carefully consider this problem.

Second, because other indexes need to be associated with the primary key, when the primary key is UUID, it will occupy more space than Int, searching in a large space is certainly time-consuming than searching in a small space.

There are not many solutions to this problem. The common method is that the primary key still uses auto_increment_int, and a uuid is added for the unique index. What is the foreign key association? UUID is also used, that is to say, auto_increment_int is only a formal primary key, while UUID is the actual primary key. In this way, the int primary key does not waste much space, and you can continue to use UUID.

Another problem is that UUID is used in MySQL. Generally, char (36) is used to declare fields. If the column encoding is complex encoding such as GBK/utf8, it will drag down the efficiency of the primary key, at this time, it is better to convert the field encoding to simple encoding like ASCII/Latin1.

######################################## ########

The official document has provided a good description, so I will not mention it. Note that mysql5.1 does not have a stable, so use it with caution.

Cause of auto-increment ID discontinuous in innodb_autoinc_lock_mode

I. Problem Reproduction

Two columns in the file/tmp/data. SQL, each column has a number of 1;

 

Input

Create Table 'T '( 'Id' int (10) unsigned not null auto_increment, 'K' int (10) unsigned not null default '0 ', Primary Key ('id ') ) Engine = InnoDB auto_increment = 1 default charset = utf8; Load data infile '/tmp/data. SQL' into Table T (k ); Show create table t;

 

Result:

Create Table 'T '( 'Id' int (10) unsigned not null auto_increment, 'K' int (10) unsigned not null default '0 ', Primary Key ('id ') ) Engine = InnoDB auto_increment = 4 default charset = utf8

 

Ii. Cause Analysis

We know that after 5.1.22, InnoDB introduced the innodb_autoinc_lock_mode parameter to solve the problem of auto-increment primary key lock table. When the value is 0,You need to lock the table each time you apply for an auto-incrementing primary key.

The default value of this parameter is 1. When this value is set, the redundant ID (handler. cc: compute_next_insert_id). After the insert operation is complete, these reserved IDs are especially left blank. The action is to write the current maximum ID after the pre-application to the table (dict0dict. c: dict_table_autoinc_update_if_greater ).

 

Iii. Simple computing Reserved

Note that the reserved policy is "a few more requests if not enough". In actual execution, it is a step-by-step application. The number of applications is determined by the number of data records inserted n. When auto_increment_offset = 1, the number of pre-applications is the N-1.

Therefore, you will find that when there is only one row in data. SQL, you will not see this phenomenon and will not apply for it in advance.

When there are two rows (such as the example at the beginning of the article), you need. The number of multiple applications is 1, so the auto-increment value after execution is 4 (1 + 2 + 1 ).

What if there are three rows in data. SQL? Because the ID of the third row has been reserved for the second row, the auto-increment value of the result is still 4.

And so on, you can analyze it on your own.

Actual insert row	Auto-increment ID added value
2, 3	3
4, 5, 6, 7	7
8 ~ 15	15

 

######################################## ########################

########################################

Before mysql5.1.22, MySQL's "insert-like" Statement (package insert, insert... Select, replace, replace... Select, and load data) locks the table using an auto-Inc lock during the execution of the entire statement until the end of the entire statement (rather than the end of the transaction ). Therefore, insert... Select, insert... Values (...), Values (...) When loading data or other operations that take a long time, the entire table is locked, and other statements such as "insert-like" and "Update" are blocked, we recommend that you use a program to divide these statements into multiple statements and insert them one by one to reduce the lock table time for a single time. rongjie's superdump is good.

MySQL was improved after mysql5.1.22 and the innodb_autoinc_lock_mode parameter was introduced. This parameter is used to control the MySQL lock table logic. Before this introduction, several terms were introduced to facilitate the description of innodb_autoinc_lock_mode.

1. "insert-like ":

Insert, insert... Select, replace, replace... Select, and load data, insert... Values (), values ()

2. "Simple inserts"

By analyzing the insert statement, you can determine the number of insert statements, insert, insert... Values (), values ()

3. "Bulk inserts"

By analyzing the insert statement, you cannot determine the number of insert statements, insert... Select, replace... Select, load data

4. "mixed-mode inserts"

The following two types are not sure whether auto_increment ID needs to be assigned.

Insert into T1 (C1, C2) values (1, 'A'), (null, 'B'), (5, 'C'), (null, 'd ');

Insert... On duplicate key update

1. innodb_autoinc_lock_mode = 0 ("traditional" lock mode)

This method is the same as before mysql5.1.22 and retains this mode for backward compatibility. As described earlier, this mode features "table-level locking" and poor concurrency.

2. innodb_autoinc_lock_mode = 1 ("consecutive" lock mode)

This method is the default method in the new version. It is recommended to use this method. The concurrency is relatively high and the feature is "consecutive". That is, the auto_increment ID inserted in the same insert statement must be continuous.

In this mode:

"Simple inserts": directly obtain the number of inserts through the analysis statement, and assign enough auto_increment IDs at a time to lock the entire allocation process.

"Bulk inserts": because the number of inserts cannot be determined, use the same table-Level Lock as in the previous mode.

"Mixed-mode inserts": directly analyzes the statement to obtain the number of inserts in the worst case, and then allocates enough auto_increment IDs at a time to lock the entire allocation process. It should be noted that in this way, too many IDs will be allocated, resulting in "waste". For example, insert into T1 (C1, C2) values (1, 'A'), (null, 'B'), (5, 'C'), (null, 'D'); five IDs will be allocated at a time, regardless of whether the User specifies part of the ID; insert... On duplicate key update is assigned at a time, regardless of whether duplicate will occur during future insertion.
And only perform the update operation.

Note: When the master mysql version is <5.1.22, slave MySQL version> = 5.1.22, slave needs to set 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 results are different, and the actual environment usually uses insert... On duplicate
Key Update.

3. innodb_autoinc_lock_mode = 2 ("interleaved" lock mode)

In this mode, one table is allocated without locking the table,Only lock the process of ID allocation, The difference from innodb_autoinc_lock_mode = 1 is that multiple nodes are not pre-allocated. This method has the highest concurrency.HoweverIn replication, when binlog_format is statement-based
Replication) there is a problem, because it is to allocate one, so when concurrent execution, "Bulk inserts" will be allocated to other Insert at the same time,The master is inconsistent.(The execution result of the slave database is different from that of the master database.) Because BINLOG only records the start insert ID.

Test the SBR and execute begin; insert values (), (); insert values (), (); Commit; insert values (),(); add set insert_id = 18 /*! */;.

However, row-based replication RBR does not have any problems.

In addition, the main drawback of RBR is that when the number of logs contains a large number of update Delete statements (multiple update statements and multiple Delete statements), the log will be much larger than that of SBR; we recommend that you use RBR with innodb_autoinc_lock_mode if there are not many such statements in the actual statement, in actual production, "Bulk inserts" is rarely used, so innodb_autoinc_lock_mode = 1 should be enough.

Below are some examples of the official documents, which will not be translated here.

For example, assume C1 is an auto_increment column of Table T1, and that the most recent automatically generated sequence number is 100. Consider the following "mixed-mode Insert" statement:

Insert into T1 (C1, C2) values (1, 'A'), (null, 'B'), (5, 'C'), (null, 'd ');

With innodb_autoinc_lock_mode set to 0 ("traditional"), the four new rows will be:

+ -- +

| C1 | C2 |

+ -- +

| 1 | A |

| 1, 101 | B |

| 5 | c |

| 1, 102 | d |

+ -- +

The next available auto-increment value will be 103 because the auto-increment values are allocated one at a time, not all at once at the beginning of statement execution. this result is true whether or not there are concurrently executing "insert-like"
Statements (of any type ).

With innodb_autoinc_lock_mode set to 1 ("consecutive"), the four new rows will also be:

+ -- +

| C1 | C2 |

+ -- +

| 1 | A |

| 1, 101 | B |

| 5 | c |

| 1, 102 | d |

+ -- +

However, in this case, the next available auto-increment value will be 105, not 103 because four auto-increment values are allocated at the time the statement is processed, but only two are used. this result is true whether or not there are concurrently
Executing "insert-like" statements (of any type ).

With innodb_autoinc_lock_mode set to Mode 2 ("interleaved"), the four new rows will be:

+ -- +

| C1 | C2 |

+ -- +

| 1 | A |

| X | B |

| 5 | c |

| Y | d |

+ -- +

The values of X and Y will be unique and larger than any previusly generated rows. however, the specific values of X and Y will depend on the number of auto-increment values generated by concurrently executing statements.

Finally, consider the following statement, issued when the most-recently generated sequence number was the value 4:

Insert into T1 (C1, C2) values (1, 'A'), (null, 'B'), (5, 'C'), (null, 'd ');

With any innodb_autoinc_lock_mode setting, this statement will generate a duplicate-key error 23000 (can't write; duplicate key in table) Because 5 will be allocated for the row (null, 'B') and insertion of the row (5, 'C') will fail.