MariaDB ALTER TABLE Waits for the Open Transaction

The deployment looked quiet: an application request finished, its database connection returned to the pool, and a migration started. MariaDB then showed Waiting for table metadata lock while the ALTER TABLE made no visible progress. The missing fact is often outside the waiting statement. A transaction that touched the table can keep its metadata locked until COMMIT or ROLLBACK, even when its last query has finished.

Do not treat the oldest-looking session as guilty or restart the database to clear the queue. Freeze the waiter, table and transaction owner first. Release only work whose application owner has confirmed whether it should commit or roll back, then rerun the DDL with a bounded acquisition window. Recovery is proved by one completed schema change, healthy application reads/writes and caught-up replicas—not by the disappearance of a process-list row.

Reconstruct the three-session incident

Metadata locking protects object definitions while statements and transactions use tables. MariaDB’s current metadata-locking documentation states that a transaction holds related table metadata until the transaction ends; rolling back only to a savepoint does not release those locks. A later DDL statement that needs an incompatible lock joins a queue.

Three actors can therefore create one outage pattern:

At capture time, the visible ALTER is the waiter, not automatically the root cause. Later requests can pile up behind an exclusive DDL request, so canceling only the newest application queries treats the queue’s symptoms. A WordPress or PHP request may surface the pileup as a gateway timeout; use the separate WordPress 504 evidence workflow when the web tier is the reported symptom, but keep database lock ownership as the decision source here.

Metadata locks are not InnoDB row locks

Row locks protect records accessed by transactions. Metadata locks protect table definitions and related objects. MariaDB uses lock_wait_timeout for metadata-lock acquisition; InnoDB row waits use a different timeout and evidence surface. If the engine reports a lock-order cycle between transactions, use MySQL deadlock and whole-transaction retry evidence instead of tuning a DDL wait.

One dangerous default makes this distinction urgent. MariaDB documents lock_wait_timeout with a default of 31,536,000 seconds—one year. An unattended migration can appear hung for far longer than a deployment system’s own deadline unless the statement or session sets a smaller boundary.

Capture waiter, holder and object before intervening

Begin with a process snapshot from a privileged diagnostic account. MariaDB’s SHOW PROCESSLIST reference defines the connection/thread fields used here. Do not publish real SQL literals, tenant identifiers or client addresses in a ticket.

SHOW FULL PROCESSLIST;

Record the capture time, server identity, connection ID, user, host, database, command, elapsed time, state and full statement. Waiting for table metadata lock identifies the waiter; it does not name the holder. A Sleep row can still own an open transaction, while a long-running query against another table may be irrelevant.

Confirm that Performance Schema can see metadata locks

MariaDB exposes performance_schema.metadata_locks from version 10.5.2. The official table reference also says Performance Schema and the metadata instrument must be enabled. Check the environment before interpreting an empty result:

SELECT VERSION() AS mariadb_version;
SHOW VARIABLES LIKE 'performance_schema';
SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME LIKE 'wait/lock/metadata%';

An empty metadata_locks result does not prove that no conflict exists when the server is older, Performance Schema is disabled, or instrumentation was not active. Enabling persistent instrumentation is a configuration change; schedule it through normal change control rather than improvising during the incident. On older MariaDB versions, the optional metadata_lock_info plugin provides another documented surface, but installing a plugin mid-incident changes the server and needs its own approval.

Join the pending request to candidate holders

On an instrumented server, read the table and thread mapping together. The following read-only query focuses on table-level pending requests and the granted locks on the same object:

SELECT
  w.OBJECT_SCHEMA,
  w.OBJECT_NAME,
  w.LOCK_TYPE AS waiting_lock,
  wt.PROCESSLIST_ID AS waiting_connection,
  wt.PROCESSLIST_TIME AS waiting_seconds,
  wt.PROCESSLIST_INFO AS waiting_sql,
  b.LOCK_TYPE AS granted_lock,
  b.LOCK_DURATION AS granted_duration,
  bt.PROCESSLIST_ID AS holder_connection,
  bt.PROCESSLIST_TIME AS holder_seconds,
  bt.PROCESSLIST_COMMAND AS holder_command,
  bt.PROCESSLIST_INFO AS holder_sql
FROM performance_schema.metadata_locks AS w
JOIN performance_schema.threads AS wt
  ON wt.THREAD_ID = w.OWNER_THREAD_ID
JOIN performance_schema.metadata_locks AS b
  ON b.OBJECT_TYPE = w.OBJECT_TYPE
 AND b.OBJECT_SCHEMA <=> w.OBJECT_SCHEMA
 AND b.OBJECT_NAME <=> w.OBJECT_NAME
 AND b.LOCK_STATUS = 'GRANTED'
JOIN performance_schema.threads AS bt
  ON bt.THREAD_ID = b.OWNER_THREAD_ID
WHERE w.OBJECT_TYPE = 'TABLE'
  AND w.LOCK_STATUS = 'PENDING'
ORDER BY w.OBJECT_SCHEMA, w.OBJECT_NAME, holder_seconds DESC;

Interpret the join as candidate holders on the same object, not a universal compatibility proof for every lock type. For the incident, confirm that the pending row belongs to the exact migration and that the granted transaction-duration row belongs to a session which touched that table. Preserve every candidate before choosing one to close.

For open InnoDB transactions, correlate connection identity with transaction age and last statement:

SELECT
  trx_mysql_thread_id AS connection_id,
  trx_started,
  trx_state,
  trx_rows_locked,
  trx_rows_modified,
  trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

trx_query may be NULL when the connection is idle between statements. That does not make the transaction empty. The application may have uncommitted writes, locks or business state that only its owner can classify.

Choose the least disruptive release action

Ownership comes before termination. Map the holder’s database user and client host to an application instance, worker, maintenance script or administrator. Ask whether the transaction should commit, roll back or remain open. Capture its connection ID and transaction evidence again immediately before acting because pooled connections and process IDs can change.

Prefer an application-owned commit or rollback

Least disruption comes from letting the transaction owner finish through the same application path that began it. A confirmed commit preserves intended work; a confirmed rollback abandons it consistently. Restarting a whole application tier or database to release one session increases the blast radius and may recreate the same pool behavior after recovery.

Background workers deserve explicit coordination. Before a WooCommerce schema window, inspect and drain recurring work through Action Scheduler backlog evidence rather than killing an unexplained database session while the worker may retry it.

Cancel the waiter when the queue harms production

MariaDB’s KILL reference distinguishes stopping the current query from terminating its connection. KILL QUERY <id> leaves the connection available. Use the waiting migration’s connection ID only after verifying it from the fresh snapshot:

KILL QUERY 8421;

Canceling the waiter is containment, not root-cause repair. Confirm that the migration tool records failure and will not automatically submit the same DDL in a tight retry loop. Later application queries should drain once the exclusive request is gone, even while the original holder remains open.

Terminate a holder only with rollback authority

If the application owner cannot close a harmful abandoned transaction, KILL CONNECTION <id> ends the connection and rolls back its active transaction. That may discard business writes and can take time while rollback works. Never terminate a holder from age or Sleep status alone. Require the exact connection, object relationship, transaction owner, rollback impact and approval in the incident record.

After the action, verify that the old transaction disappeared and no replacement connection immediately acquired the same lock. If it returns, fix the code path or pool lifecycle before attempting DDL again.

Make the next migration refuse an unbounded queue

MariaDB’s WAIT/NOWAIT syntax can bound lock acquisition for ALTER TABLE, index operations, rename, truncate and other supported statements. Test the exact DDL on the same MariaDB family and version before production:

ALTER TABLE app.orders WAIT 10
  ADD COLUMN fulfillment_note VARCHAR(255) NULL,
  ALGORITHM=INSTANT;

WAIT 10 bounds lock acquisition; it does not guarantee safety. It cannot prove the requested algorithm is supported or choose a holder to terminate. A timeout should fail the deployment, preserve evidence and require a new preflight. Do not turn it into automatic retries that repeatedly re-enter the queue.

Before the maintenance window, run a read-only preflight for open transactions touching the target workflow, stop new background work through its owner, and verify that connection pools return sessions with no open transaction. Application frameworks should commit or roll back in a finally/deferred cleanup path; pool checkout/return telemetry should expose abandoned transaction state rather than hiding it behind idle connections.

Online DDL is another boundary, not an exemption. MariaDB’s current ALTER TABLE reference describes ALGORITHM and LOCK choices, but even LOCK=NONE does not mean “no metadata lock.” Confirm the chosen algorithm with staging evidence, estimate rollback/replication cost and keep a maintenance abort threshold.

Accept the schema change across the whole data path

After the bounded retry succeeds, compare SHOW CREATE TABLE app.orders with the approved migration and run a representative application read plus write. Check server error logs and connection-pool metrics for new exceptions, timeouts or leaked transactions. The acceptance window must be long enough to cover the workload that originally exposed the holder.

Replication completes the proof. A DDL statement can be accepted on the source while a replica is still receiving or applying it. Use MySQL replica receive-versus-apply diagnosis as a stage model, adapting commands to the deployed MariaDB version, and keep stale or failed replicas out of reads until their schema and data state agree.

Close the incident with a compact migration receipt: target server and schema, exact DDL hash, preflight time, waiter/holder IDs, transaction owner, chosen release action, WAIT/NOWAIT boundary, start/end time, resulting SHOW CREATE TABLE, application probes, replica state and rollback decision. Browse related database operations guides for separate engine-specific incidents; this receipt should remain narrowly about the metadata-lock owner and the one approved schema change.

ALTER TABLE safety is operational rather than merely syntactic: a migration window begins only after transaction ownership is quiet and observable. Online algorithms reduce disruption after acquisition; they cannot compensate for a pool that returns open transactions or a deployer willing to wait indefinitely.