`innodb_autoinc_lock_mode`

CRITICAL (2 rules)INFO

Rule IDs: cc_006, cc_007, pxc_010

Overview

Purpose: Documented in the MySQL 8.4 manual as a server system variable (scope: Global). Purpose and semantics are described at the linked manual page.
Dynamic (MySQL 8.4 reference): MySQL 8.4 marks this variable as not dynamic (Dynamic = No). It must be set in my.cnf or on the command line; changing it requires a restart.
Default value: 2 (interleaved) (MySQL 8.4)
Version and product notes: MariaDB and Percona Server may use different names, defaults, or dynamic behavior; verify their documentation.
Documentation: https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode
Other vendors: MariaDB KB Percona Docs

What is checked

Rules that reference this variable, with their severity and what each rule detects:

CRITICAL cc_006: Set innodb_autoinc_lock_mode=2 for Galera/PXC. This is mandatory. Mode 2 is also the default in MySQL 8.0+ and is safe with ROW-based replication.
INFO cc_007: Upgrade to innodb_autoinc_lock_mode=2 (interleaved). Safe with binlog_format=ROW. Mode 0 is never recommended.
CRITICAL pxc_010: Set innodb_autoinc_lock_mode=2. This is mandatory for Galera cluster operation.

Tuning guidance

Recommended actions:
- Set innodb_autoinc_lock_mode=2 for Galera/PXC. This is mandatory. Mode 2 is also the default in MySQL 8.0+ and is safe with ROW-based replication.
- Upgrade to innodb_autoinc_lock_mode=2 (interleaved). Safe with binlog_format=ROW. Mode 0 is never recommended.
- Set innodb_autoinc_lock_mode=2. This is mandatory for Galera cluster operation.
Trade-offs: Thread concurrency controls balance CPU utilization against contention. Too many concurrent threads cause lock waits; too few leave CPU idle. Auto-management (concurrency=0) works well for most workloads.

Example

-- In my.cnf (requires restart): innodb_autoinc_lock_mode = 2 -- Required for Galera/PXC; safe for ROW binlog format

Always validate on a non-production instance first. Use SET PERSIST (MySQL 8.0+) for changes that should survive restarts.

Generated from the MySQL Advisor documentation build. Dynamic Yes/No reflects the excerpt aligned with Oracle MySQL 8.4 reference material consumed by this project.