MySQL :: MySQL 5.7 Reference Manual :: 5.4.4.2 Configurable InnoDB Auto-Increment Locking - 0 views
-
Configurable
-
kuni katsuya on 08 Aug 13new and improved!(?)
-
-
table-level locks held until the end of a statement make INSERT statements using auto-increment safe for use with
-
statement-based replication
- ...24 more annotations...
-
However, those locks limit concurrency and scalability when multiple transactions are executing insert statements at the same time
-
For INSERT statements where the number of rows to be inserted is known at the beginning of processing the statement, InnoDB quickly allocates the required number of auto-increment values without taking any lock, but only if there is no concurrent session already holding the table-level AUTO-INC lock (because that other statement will be allocating auto-increment values one-by-one as it proceeds)
-
obtains auto-increment values under the control of a mutex (a light-weight lock) that is not held until the statement completes, but only for the duration of the allocation process
-
innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
-
special table-level AUTO-INC lock is obtained and held to the end of the statement
-
lock mode is provided for:
-
Backward compatibility.
-
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
-
important impact of this lock mode is significantly better scalability
-
This mode is safe for use with
-
statement-based replication
-
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
-
This is the fastest and most scalable lock mode
-
but it is
-
not safe
-
when using
-
statement-based replication
-
recovery scenarios when SQL statements are replayed from the binary log
-
Using auto-increment with replication
-
set innodb_autoinc_lock_mode to 0 or 1 and use the same value on the master and its slaves
-
Auto-increment values are not ensured to be the same on the slaves as on the master if you use innodb_autoinc_lock_mode = 2 (“interleaved”) or configurations where the master and slaves do not use the same lock mode
-
If you are using
-
row-based replication
-
all of the auto-increment lock modes are safe