MariaDB Auto-Increment Locks Will Bite You at Scale

I spent a frustrating afternoon last year chasing insert latency spikes on a MariaDB instance that had no obvious slow queries, no missing indexes, and a perfectly reasonable schema. The culprit was innodb_autoinc_lock_mode = 0, the default on some older MariaDB builds, combined with a bulk-insert pattern that nobody had thought twice about. Changing one integer in my.cnf dropped p99 insert latency by about 60%.

If you're running any kind of write-heavy workload - order ingestion, event logging, IoT telemetry, queued jobs - this is worth understanding before you hit the wall.

What Auto-Increment Locking Is Actually About

Every AUTO_INCREMENT column needs to hand out monotonically increasing integers without duplicates or gaps across concurrent connections. The naive solution is a table-level lock that holds for the duration of every insert. That's safe. It's also a serialization point.

InnoDB gives you three modes, controlled by innodb_autoinc_lock_mode:

• Mode 0 (traditional): A table-level AUTO-INC lock held for the entire insert statement. Every insert, including single-row inserts, acquires and holds this lock until the statement completes. Safe for binary log replication. Terrible for concurrency.
• Mode 1 (consecutive): The default in many MariaDB 10.x versions. Single-row inserts use a lightweight mutex, not a table lock. Bulk inserts (INSERT ... SELECT, LOAD DATA, multi-row INSERT) still take the table lock. You get most of the win for OLTP workloads.
• Mode 2 (interleaved): No table-level lock at all. A lightweight mutex allocates IDs in batches. Fastest possible throughput. IDs are not guaranteed to be consecutive across concurrent statements. Requires binlog_format = ROW if you're using binary log replication.

Mode 1 feels fine until your application starts doing bulk inserts under concurrent load. Then you're back to table-level locking for the duration of each bulk statement, and single-row inserts queue up behind it.

Checking What You're Running

First, just look:

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
SHOW VARIABLES LIKE 'binlog_format';

If you're on mode 0 or mode 1 and doing any bulk inserts on a high-traffic table, you should at least understand the tradeoff. If you're seeing mysterious insert latency under load with no slow query log hits (because the lock wait isn't always long enough to cross your threshold), this is the first thing I'd check.

A Concrete Pattern That Burns You

Here's a typical Laravel job that processes a batch of incoming records - something I've written variations of a hundred times:

<?php

namespace App\Jobs;

use App\Models\SensorReading;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\DB;

class StoreSensorBatch implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public function __construct(
        private readonly array $readings
    ) {}

    public function handle(): void
    {
        // This looks innocent. Under mode 0 or mode 1, this single statement
        // holds an AUTO-INC table lock until all rows are inserted.
        SensorReading::insert(
            collect($this->readings)->map(fn($r) => [
                'device_id'  => $r['device_id'],
                'value'      => $r['value'],
                'recorded_at' => $r['recorded_at'],
                'created_at' => now(),
                'updated_at' => now(),
            ])->all()
        );
    }
}

That SensorReading::insert() call compiles to a single INSERT INTO ... VALUES (...), (...), (...) with however many rows are in the batch. Under mode 0 or 1, that's a table lock for the duration. If you have 20 queue workers all running this simultaneously with batches of 500 rows each, they serialize. You'll see queue depth grow, insert latency climb, and nothing obvious in SHOW PROCESSLIST except a lot of "waiting for auto-increment lock".

The fix in the database config:

# /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
innodb_autoinc_lock_mode = 2
binlog_format            = ROW

Mode 2 requires ROW-based binary logging. If you're on statement-based replication and you switch to mode 2, you'll get non-deterministic replication because the replica can't replay statement-based logs when IDs are allocated non-consecutively across concurrent statements. Set binlog_format = ROW first, verify replication is healthy, then flip the auto-increment mode.

If you're not using binary log replication at all - single-node installs, Galera clusters with their own sync mechanism - this is simpler. Just set mode 2.

The Gotchas I've Walked Into

Gap anxiety. With mode 2, your IDs will have gaps. Concurrent bulk inserts interleave their allocated ranges. If you have any business logic that infers meaning from consecutive IDs - "the next order number after this one should be this" - you need to stop doing that. IDs are opaque handles, not sequence numbers. I've seen billing code in the wild that reconstructed order sequences from auto-increment IDs. That's already wrong; mode 2 just makes it obviously wrong.

Replication format discovery. I inherited a MariaDB 10.4 setup where binlog_format was MIXED. Switching to mode 2 without changing that first caused silent replication divergence on exactly the tables with concurrent bulk inserts. MIXED mode falls back to statement logging when it thinks statement logging is safe, and its heuristics don't account for non-consecutive ID allocation. Set ROW explicitly and verify with SHOW BINARY LOGS and mysqlbinlog spot checks.

SHOW ENGINE INNODB STATUS is your friend. When I'm chasing contention, I dump this and grep for auto_increment:

mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G" \
  | grep -A5 -i 'auto.increment\|LOCK WAIT'

You'll see waiting threads and the lock holder. It's ugly output but it's honest.

MariaDB vs MySQL defaults differ. MariaDB 10.6+ changed the default to mode 2. MySQL 8.0 changed it to mode 2 back in 2018. If you're on MariaDB 10.3 or 10.4 - which a lot of managed hosting still runs - you're on mode 1 by default. Don't assume the version you're running matches what you read in the MySQL 8 docs.

Galera and mode 2. If you're running Galera Cluster (Percona XtraDB Cluster, or MariaDB Galera), the recommended setting is actually mode 2 anyway, because Galera can't guarantee statement-level replication ordering across nodes. The Galera documentation says this explicitly, but I've found plenty of Galera installs still sitting on mode 1 from whoever provisioned them years ago.

Observing the Difference

Before you change anything in production, reproduce it in staging with concurrent load. I usually reach for a quick shell loop:

# Simulate 20 concurrent workers each doing a 500-row bulk insert
for i in $(seq 1 20); do
  mysql -u app -ppassword mydb -e \
    "INSERT INTO sensor_readings (device_id, value, recorded_at)
     SELECT FLOOR(RAND()*1000), RAND()*100, NOW()
     FROM information_schema.columns c1
     CROSS JOIN information_schema.columns c2
     LIMIT 500;" &
done
wait

Then watch SHOW PROCESSLIST in another terminal. With mode 1 you'll see the waiting threads pile up. With mode 2 they scatter. The difference is not subtle once you're looking at it.

For Laravel applications specifically, I also lean on the query log during load tests:

DB::listen(function ($query) {
    if ($query->time > 100) { // log anything over 100ms
        Log::warning('slow query', [
            'sql'      => $query->sql,
            'bindings' => $query->bindings,
            'time_ms'  => $query->time,
        ]);
    }
});

Bulk inserts waiting on the auto-increment lock show up here as slow even though the SQL itself is fine.

When I'd Reach for Mode 2 (and When I Wouldn't)

I reach for mode 2 any time I have concurrent bulk inserts on a table that sees real write throughput - queue workers, batch ETL, event ingestion, anything where more than one process is inserting rows simultaneously. The performance difference at scale is not marginal.

I stay on mode 1 when I'm working with a client whose replication setup I don't fully control and can't test, or when I'm inheriting a system and the risk of touching my.cnf outweighs the benefit. Mode 1 is safe. It's not fast, but it's safe.

I'd never run mode 0 on a new project. The only reason mode 0 exists is backward compatibility with very old replication setups. If someone asks you to run mode 0, ask them why, and then ask them when they last audited their replication.

One thing I won't do: use UUIDs as primary keys to sidestep this entirely. Yes, UUIDs eliminate the auto-increment contention. They also fragment your clustered index, bloat your foreign keys, and make EXPLAIN output harder to read. The tradeoff isn't worth it for most of what I build. If you're distributing writes across shards, maybe. For a single MariaDB instance or a Galera cluster, fix the lock mode.

Bottom Line

This is one of those settings that's invisible at low volume and suddenly very visible at high volume, which makes it exactly the kind of thing that bites you when a client's business is growing and their database is struggling to keep up. Check your innodb_autoinc_lock_mode, check your binlog_format, and if you're doing any concurrent bulk inserts at all, seriously consider mode 2. The gap in IDs is not a bug. The insert contention is.