MySQL ALTER TABLE Locks for Hours – Fix It Fast

1. You're Not Using Online DDL (InnoDB Only)

This is the #1 reason your ALTER TABLE locks for hours. By default, MySQL 5.6 and older versions use a blocking method for most ALTER operations. Even MySQL 8.0 can fall back to a table copy if you don't specify the right algorithm. If you're adding a column, changing a data type, or rebuilding an index, MySQL might copy the whole table to a temp one, lock it with a write lock, and block all reads and writes until it's done. On a table with 50 million rows and 100GB of data, that can take 3–4 hours. I've seen this happen on a production e-commerce site running MySQL 5.7 – the checkout page went down for an afternoon because someone ran ALTER TABLE orders ADD COLUMN discount DECIMAL(5,2); without thinking about locks.

The fix: Use online DDL with InnoDB. For MySQL 5.6+, you can add ALGORITHM=INPLACE and LOCK=NONE to your ALTER statement. This allows concurrent reads and writes while the operation runs in the background. Here's an example:

ALTER TABLE orders
ADD COLUMN discount DECIMAL(5,2) AFTER total,
ALGORITHM=INPLACE, LOCK=NONE;

Not all operations support LOCK=NONE. Adding a column? Yes. Changing a column's data type from INT to BIGINT? Yes. Dropping a primary key? No – that still requires a table copy and blocks writes. For operations that can't use LOCK=NONE, use LOCK=SHARED – it allows reads but not writes. That's better than nothing.

Also, check your MySQL version. MySQL 8.0 improved online DDL a lot. If you're on 5.7 or older, consider upgrading. The performance difference is huge – like 10x faster for some ALTERs.

2. The Table is Too Big and You're Rebuilding an Index

If your ALTER TABLE is rebuilding a primary key or adding a unique index on a large table, you're in for a long wait. Index rebuilds on big tables (over 10GB) can take hours because MySQL has to scan the entire table, sort the rows, and build the new index structure. Even with online DDL, the operation can still lock the table for a short time at the beginning and end to commit the change.

Real-world example: I worked with a financial system that had a 200GB transaction table. Adding a unique index on the transaction_id column took 45 minutes. During that time, all writes were blocked because the index had to be validated for uniqueness. The team had to schedule it during maintenance windows. Painful.

The fix: First, check if you really need that index. If yes, use a two-step approach. Create the index in the background with ALGORITHM=INPLACE, LOCK=NONE (if supported). Then add the unique constraint separately. But – here's the catch – unique indexes always require a write lock at the end to check for duplicates. There's no way around it. So plan for a brief lock (seconds to minutes) at the tail end.

Another trick: Use pt-online-schema-change from Percona Toolkit. This tool creates a shadow table, copies data in chunks with triggers, and swaps the table at the end without a big lock. It's saved me many times. Download it from Percona's site. The command looks like:

pt-online-schema-change --alter "ADD INDEX idx_transaction_id (transaction_id)" D=mydb,t=transactions --execute

But be careful – this tool creates triggers and can add overhead. Test on a staging server first.

3. Long-Running Queries Are Blocking the ALTER

Sometimes the ALTER itself isn't slow – it's waiting for other queries to finish. MySQL has a lock queue. If a long-running SELECT or UPDATE is holding a read lock on the table, your ALTER (which needs a write lock) will sit and wait. This can look like the ALTER is stuck for hours when really it's just queued behind a 3-hour reporting query.

I've debugged this scenario twice in the past month. In one case, a data analyst ran a SELECT * FROM orders WHERE status='pending' that scanned 20 million rows and took 2 hours. That blocked the nightly ALTER job. The logs showed the ALTER was waiting for a metadata lock.

The fix: Check if your ALTER is waiting. Run SHOW PROCESSLIST; or SELECT * FROM performance_schema.metadata_locks; (MySQL 8.0+). Look for a thread with state "Waiting for table metadata lock". That's your ALTER. Find the query that's blocking it – usually a long-running SELECT or a transaction that hasn't committed.

Then kill the blocking query (if safe) with KILL <thread_id>;. Or cancel the ALTER with ALTER TABLE ... ALTER NOWAIT; if you want it to fail fast instead of waiting. But that's a band-aid.

For a permanent fix, set a lock wait timeout in your MySQL config:

lock_wait_timeout = 600

This makes ALTER wait only 10 minutes before giving up. Also, ask your team to avoid long-running queries during maintenance windows. Use pt-query-digest to find slow queries and optimize them.

Quick-Reference Summary Table

Remember, ALTER TABLE doesn't have to ruin your day. Start with online DDL. Use pt-online-schema-change for heavy changes. And keep an eye on blocking queries. Your production system will thank you.