Oct 10, 2018

How to reduce MySQL InnoDB Lock by innodb_autoinc_lock_mode

There are three possible settings for the innodb_autoinc_lock_mode configuration parameter. The settings are 0, 1, or 2, for traditional,consecutive, or interleaved lock mode, respectively.
  •  innodb_autoinc_lock_mode = 0 (traditional lock mode)
    The traditional lock mode provides the same behavior that existed before the innodb_autoinc_lock_mode configuration parameter was introduced in MySQL 5.1. The traditional lock mode option is provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics.
  •  innodb_autoinc_lock_mode = 1 (consecutive lock mode)
    This is the default lock mode. In this mode, bulk inserts use the special AUTO-INC table-level lock and hold it until the end of the statement. 
  •  innodb_autoinc_lock_mode = 2 (interleaved lock mode)
    In this lock mode, no INSERT-like statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.
So after you change innodb_autoinc_lock_mode to 2 and also change Binary Log Format to either ROW or MIXED, your MySQL are in the the fastest and most scalable lock mode.