Perform Online DDL in MariaDB with Minimal Locking

Desired Outcome

Execute an ALTER TABLE change (e.g., add a column) without blocking concurrent DML, verify that lock impact is negligible, and know how to recover if the operation stalls.

Prerequisites

• MariaDB server version 10.0 or newer (Online DDL introduced in 10.0).
• User account with the ALTER privilege on the target table and the PROCESS privilege to view lock information.
• Enough free disk space for a temporary copy of the table (roughly the size of the table plus indexes).
• Access to the mysql client or any compatible SQL interface.

Procedure

1. Check server version

   SELECT VERSION();

   Confirm the version string starts with 10. or higher.

2. Identify the table and column to add

   Replace mydb.users and new_flag with your actual schema.

3. Run the Online DDL with the INSTANT algorithm (if supported)

   The INSTANT algorithm modifies only the table metadata and avoids copying data.

   ALTER TABLE mydb.users ADD COLUMN new_flag TINYINT(1) NOT NULL DEFAULT 0 ALGORITHM=INSTANT;

   If the column definition cannot be satisfied with INSTANT (e.g., adding a NOT NULL column without a default), MariaDB will automatically fall back to INPLACE or COPY. You can explicitly request a method to see what will happen:

   ALTER TABLE mydb.users ADD COLUMN new_flag TINYINT(1) NOT NULL DEFAULT 0 ALGORITHM=INPLACE, LOCK=NONE;

4. Monitor lock and performance impact

   While the statement runs, open another session and check:

   SHOW ENGINE INNODB STATUS\G

   Look for the ---TRANSACTION section; there should be no long waits labeled Waiting for table lock.

   You can also query the performance schema for current locks:

   SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE
   FROM performance_schema.data_locks
   WHERE OBJECT_SCHEMA = 'mydb' AND OBJECT_NAME = 'users';
   

   Expect few or no rows, or only short‑lived RECORD_NOT_GAP locks.

5. Verify the change completed

   After the statement returns, confirm the column exists:

   SHOW COLUMNS FROM mydb.users LIKE 'new_flag';

   Check UPDATE_TIME in information_schema.tables to see when the table was last modified.

Expected Checks

• The ALTER TABLE statement returns quickly (usually within a few seconds) for INSTANT or INPLACE operations.
• SHOW ENGINE INNODB STATUS shows no pending lock waits for the table.
• Querying performance_schema.data_locks returns minimal or no lock rows for the table during the operation.
• The table’s UPDATE_TIME reflects the time of the change, confirming the metadata swap.

Recovery Options

If the operation stalls before the metadata swap

Long‑running transactions can block the final swap phase. You can:

1. Identify blocking transactions with:

SELECT trx_id, trx_state, trx_started
FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id IN (SELECT blocking_thread FROM sys.innodb_lock_waits);

2. Kill the blocking session (requires SUPER privilege):

KILL <thread_id>;

3. The Online DDL will then resume and complete the swap automatically.

If you need to cancel the operation entirely

You can issue a KILL on the thread running the ALTER TABLE. Depending on the phase:

• Before the temporary table is created, the server rolls back the statement with no lasting effects.
• After a temporary copy exists but before the swap, MariaDB automatically drops the temporary table when the statement is killed.
• After the swap has occurred, the change is already committed; to revert you must issue another ALTER TABLE (e.g., drop the column) or restore from a backup.

Disk‑space exhaustion

If the server runs out of space while creating the temporary copy, the operation fails with an error like The table is full. In that case:

1. Free space or increase the tmpdir capacity.
2. Retry the ALTER TABLE.

No manual cleanup is required; any partial temporary table is removed automatically on failure.