Metadata Locks, Row Locks, Gap/Next-Key Locking, and Online Change Interactions

Learning outcomes

An “online” ALTER TABLE can still sit waiting for minutes while a quiet application session holds a transaction open. An UPDATE of a range can block inserts into values that do not yet exist. A missing index can turn a logically narrow predicate into a much wider lock footprint because InnoDB locks index records/ranges it scans. These incidents look mysterious only when all locks are treated as one thing.

MariaDB has different lock families for different correctness problems. Metadata locks (MDL) protect object definitions while statements/transactions use them. InnoDB record locks protect index records. Gap locks protect spaces between index records, and next-key locks combine a record with the preceding gap. Their lifetime and scope depend on transaction boundaries, isolation level, indexes and SQL shape. Diagnosis therefore starts by identifying which lock family is blocking progress.

1. Metadata locks protect definitions until the transaction is done

When a transaction uses a table, MariaDB holds metadata protection so another session cannot change that table definition underneath the active statement/transaction. MariaDB documentation states that metadata locks can last until transaction end, and rolling back to a savepoint does not release them. Therefore a read-only-looking transaction can delay DDL if it touches the target table and remains open.

USE servicehub_tx_lab;START TRANSACTION;SELECT work_order_id,status FROM work_orders WHERE work_order_id=1001;-- Leave the transaction open.

ALTER TABLE work_orders NOWAIT ADD COLUMN mdl_demo INT NULL;

Using NOWAIT makes this a safe lab because Session B fails immediately instead of waiting for the very large default metadata lock timeout. Without NOWAIT, the ALTER can queue until Session A COMMIT/ROLLBACK or lock_wait_timeout expires. This is a metadata wait, not an InnoDB row-lock timeout controlled by innodb_lock_wait_timeout.

ROLLBACK;

2. Diagnose the waiting session before killing anything

When an ALTER is waiting, first identify the waiting statement and the old transaction that owns the conflicting metadata lock. SHOW FULL PROCESSLIST can reveal a state such as waiting for table metadata lock. INFORMATION_SCHEMA.INNODB_TRX shows active InnoDB transactions and their start times. A long-running transaction with no currently busy statement is often more important than the visibly waiting DDL.

SHOW FULL PROCESSLIST;SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id,       trx_rows_locked, trx_rows_modifiedFROM information_schema.innodb_trxORDER BY trx_started;

Do not immediately KILL the oldest connection. Determine the business operation, whether it has uncommitted work, and whether the application can safely retry. Killing a transaction causes rollback work and can create an incident larger than the original DDL wait.

3. Optional metadata-lock instrumentation

On MariaDB 10.5.2 and later, performance_schema.metadata_locks can list granted and pending metadata locks when Performance Schema and the metadata lock instrument are enabled. Performance Schema itself is documented as disabled by default, so a course lab should not quietly depend on it. Enable it in a disposable instance or production only through an explicit observability design.

SHOW VARIABLES LIKE 'performance_schema';-- If it is ON, enable metadata instrumentation at runtime:UPDATE performance_schema.setup_instrumentsSET enabled='YES', timed='YES'WHERE name LIKE 'wait/lock/metadata%';SELECT OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,       LOCK_TYPE,LOCK_DURATION,LOCK_STATUS,OWNER_THREAD_IDFROM performance_schema.metadata_locksWHERE OBJECT_SCHEMA='servicehub_tx_lab';

The alternative metadata_lock_info plugin exposes INFORMATION_SCHEMA.METADATA_LOCK_INFO, but the plugin is distributed separately from its installation state. Keep that path optional and label INSTALL PLUGIN privileges/security implications rather than changing a production server just to satisfy a tutorial.

4. Record locks live on indexes, not on an abstract row object

InnoDB’s locking model is index-oriented. A point lookup using a unique index can lock the exact matching index record. A range predicate can lock multiple records and, under REPEATABLE READ, gaps between them so another transaction cannot insert a new row into the protected range. A next-key lock combines a record lock with the gap preceding that record.

Under READ COMMITTED, MariaDB documentation describes gap locking as substantially reduced, with important exceptions such as foreign-key and duplicate-key checks. That can improve concurrency but changes phantom/range protection. Verify target-version behavior before using isolation changes as a tuning shortcut.

5. Indexed predicates change the lock footprint

Suppose a dispatcher locks all queued high-priority work. The composite index (status, priority, work_order_id) lets InnoDB navigate a relatively narrow key range. If that index is removed and the engine must scan a much broader access path, more index records/ranges can participate in locking. “Add an index to make the SELECT faster” is incomplete; for locking DML, access-path design can change concurrency behavior too.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION;SELECT work_order_id,status,priorityFROM work_ordersWHERE status='open' AND priority BETWEEN 1 AND 2ORDER BY status,priority,work_order_idFOR UPDATE;-- Keep open only long enough to observe another session.

INSERT INTO work_orders(work_order_id,customer_id,technician_id,status,priority,estimated_cost,version_no,opened_at)VALUES (1010,1,NULL,'open',1,50.00,1,NOW());

Whether and exactly where Session B waits depends on the index access path, isolation level and key values. Use EXPLAIN before the locking test and lock instrumentation during it. Do not turn a single observed lock into an eternal claim about every plan/version.

6. Observe row lock waits

For active InnoDB waits, MariaDB exposes transaction and lock tables such as INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS. MariaDB’s sys.innodb_lock_waits view, available from 10.6, summarizes waiter/blocker relationships for human diagnosis. These views are more useful than staring at a blocked client because they connect the waiting statement to the transaction holding the lock.

SELECT * FROM sys.innodb_lock_waits\GSELECT trx_id,trx_state,trx_started,trx_mysql_thread_id,       trx_requested_lock_id,trx_rows_locked,trx_queryFROM information_schema.innodb_trx\GSELECT * FROM information_schema.innodb_locks\GSELECT * FROM information_schema.innodb_lock_waits\G

Some rows appear only while a wait exists, and privileges can restrict visibility. Lock IDs are diagnostic identifiers whose format is not an application API. Capture them during the incident, but do not store parsing logic that depends on their internal encoding.

7. Online DDL is not “no locks ever”

Modern MariaDB supports many online ALTER TABLE paths and improved LOCK=NONE behavior, but online DDL still needs metadata coordination at phases of the operation. A long transaction can therefore delay the start or completion of an otherwise online change. Production migration planning needs a lock budget, transaction-age monitoring, target-version algorithm verification and a rollback/abort boundary.

Before a large ALTER, inspect long transactions, replica/Galera state, disk headroom and the exact algorithm/lock mode supported by that version. A green staging run with no concurrent long transaction does not prove zero blocking in production.

8. Lab checklist, failure repair and knowledge check

1. Open Session A, START TRANSACTION and SELECT from work_orders.
2. From Session B, run ALTER TABLE ... NOWAIT and record the metadata-lock failure.
3. Use SHOW FULL PROCESSLIST and INNODB_TRX to identify the old transaction.
4. If Performance Schema is already enabled in your disposable lab, inspect metadata_locks; otherwise keep this optional.
5. Create a row-lock wait with SELECT ... FOR UPDATE and inspect sys.innodb_lock_waits/INNODB_TRX.
6. Compare a unique point lock with a range lock; record EXPLAIN access paths.
7. ROLLBACK every open lab transaction and clean up any inserted row.

9. Summary and bridge

Metadata locks protect definitions; InnoDB record/gap/next-key locks protect indexed data and ranges. Transaction lifetime, isolation and access path determine what remains protected and for how long. Diagnosing a wait therefore means identifying lock family, waiting transaction, blocker, SQL, age and safe release action—not simply increasing a timeout.

The final lesson creates the most instructive concurrency failure: a deadlock. You will force two sessions into cyclic lock acquisition, inspect the detected cycle, distinguish it from a timeout, and build a bounded retry strategy that replays the entire transaction safely rather than hiding the symptom.