Variable's Day Out #7: innodb_autoinc_lock_mode

Properties:

Applicable To	InnoDB
Introduced In	5.1.22
Server Startup Option	--innodb-autoinc-lock-mode=<value>
Scope	Global
Dynamic	No
Possible Values	enum(0,1,2) Interpretation: Value Meaning 0 Traditional 1 Consecutive 2 Interleaved
Default Value	1 (consecutive)
Categories	Scalability, Performance

Description:

This variable was introduced in 5.1.22 as a result of the [Bug 16979] and comes very handy when stuck with auto_increment scalability issue, also mentioned in my previous post. So what do traditional, consecutive and interleaved mean?

Traditional is "traditional", this takes back InnoDB to pre-innodb_autoinc_lock_mode and a table level AUTO-INC lock is obtained and held until the statement is done. This ensures consecutive auto-increment values by a single statement. Remember, this lock is scoped for a statement and not transaction and hence is not equivalent to serializing transactions as someone raised a question to me recently.

Consecutive, the default lock mode, works in context switching method. For inserts where the number of rows is not known (bulk inserts), ie INSERT ... SELECT, REPLACE ... SELECT, LOAD DATA, it takes a table level AUTO-INC lock. Otherwise for inserts where the number of rows is known in advance (simple inserts), it uses a light weight mutex during the allocation of auto-increment values. The mutex is of course checked for only if no other transaction holds the AUTO-INC lock. However for inserts where user provides auto-increment values for some rows (mixed mode inserts), InnoDB tends to allocate more values and lose them.

Interleaved mode just ensures uniqueness for each generated auto-incremented value. This mode never takes an AUTO-INC lock and multiple statements can keep generating values simultaneously.

How to use?

My overall recommendation is not to change this variable and keep it to default. And if you are having mixed mode insert statements that contradict the usage, better look into them. Otherwise, following are the constraints on usage.

• Use interleaved only when your tables don't have auto-increment columns. Also if you don't know/don't care if they have, then you have more issues to resolve. :)
• "mixed mode inserts" can lead to losing values with consecutive mode.
• It's not safe to use statement based or mixed replication with interleaved mode.
• Traditional mode has scalability issues, but is safe when used with mixed mode inserts.

Read more:

• MySQL manual entry on innodb_autoinc_lock_mode
• Configurable InnoDB Auto-Increment Locking
• [Bug 16979]

Hope you enjoyed reading this.