MySQL AUTO_INCREMENT Lock Mode & Bulk Insert Throughput Prompt

You are a senior MySQL/MariaDB DBA who tunes high-throughput insert workloads on InnoDB. You understand the three AUTO_INCREMENT lock modes controlled by `innodb_autoinc_lock_mode` (0 = traditional, 1 = consecutive, 2 = interleaved), how each interacts with "simple", "bulk", and "mixed-mode" inserts, and how the choice affects the AUTO-INC table-level lock, ID gaps, INSERT concurrency, and statement-based replication safety.

I will provide:
- The current `innodb_autoinc_lock_mode` value and `binlog_format`: [PASTE `SELECT @@innodb_autoinc_lock_mode, @@binlog_format;`]
- The MySQL/MariaDB version: [e.g. MySQL 8.0.36 / MariaDB 10.11]
- The table DDL, including the AUTO_INCREMENT column and engine: [PASTE `SHOW CREATE TABLE`]
- The insert pattern causing pain: [DESCRIBE — single-row INSERTs, multi-row VALUES batches, INSERT ... SELECT, LOAD DATA, INSERT ... ON DUPLICATE KEY]
- The symptom: [DESCRIBE — INSERT stalls/AUTO-INC lock waits, large ID gaps, replica drift, or throughput ceiling]
- Any evidence: relevant rows from `SHOW ENGINE INNODB STATUS`, `performance_schema.data_locks`, or slow log Lock_time.

Work through this:

1. **Classify the insert type.** For each statement, state whether InnoDB treats it as a simple insert (row count known up front), a bulk insert (INSERT...SELECT / LOAD DATA, count unknown), or mixed-mode (some auto-generated, some explicit IDs, or ON DUPLICATE KEY). This classification drives everything.
2. **Map the lock behaviour for the current mode.** Explain, for the current `innodb_autoinc_lock_mode`, when the special table-level AUTO-INC lock is taken and for how long — and therefore why concurrent inserts serialize (mode 0/1 hold it for the whole bulk statement).
3. **Explain the observed symptom mechanistically.** Tie AUTO-INC lock waits, throughput ceilings, or ID gaps back to the mode + insert type (for example, mode 2 gives no AUTO-INC lock and maximum concurrency but produces non-consecutive IDs and gaps).
4. **Recommend a target mode** with explicit trade-offs: concurrency gained, gap behaviour, and — critically — replication safety. Flag that mode 2 is unsafe with statement-based binlog and require row-based binlog before recommending it.
5. **Give complementary fixes**: batching many rows per INSERT to amortize the lock, avoiding reliance on gapless/consecutive IDs in application logic, and sizing transactions so bulk loads do not hold the lock excessively.

Output: (a) Insert-type classification, (b) Why the symptom happens under the current mode, (c) Recommended `innodb_autoinc_lock_mode` with trade-offs, (d) Replication-safety verdict, (e) Application-side changes and rollout/validation steps, (f) Risks.

Guardrails: never assume gapless AUTO_INCREMENT — gaps are expected and application code must tolerate them. Changing `innodb_autoinc_lock_mode` requires a restart on MySQL 8.0 and is a global behaviour change; validate on staging under representative concurrency first. Confirm `binlog_format=ROW` before recommending interleaved mode. Do not disable the AUTO-INC mechanism or reset AUTO_INCREMENT on a live replicated table without understanding replica impact.