Database migration tool hangs on large table fix

Quick answer

Set --lock-wait-timeout=10 and --chunk-size=1000 in your migration command. If that doesn't work, increase innodb_lock_wait_timeout on the server side.

Why this happens

I've seen this pattern at least 50 times in the last two years. You're running a migration tool — could be Flyway, Liquibase, SQL Server Migration Assistant, or just a raw script — and it hits a table with 10 million rows or more. The tool tries to ALTER TABLE or add a column or rebuild an index. It locks the whole table. If another query holds a lock, or if the operation takes longer than the default timeout, the migration just sits there. Forever. Sometimes it looks like it's hung, sometimes it crashes with a generic timeout error like Lock wait timeout exceeded; try restarting transaction.

The real issue: most migration tools are designed for small-to-medium databases. They don't handle big tables well out of the box. The default chunk size is 10,000 rows, which is fine for a 500k row table, but on 50 million rows that's 5,000 chunks, each taking seconds. The default lock wait timeout is 50 seconds in MySQL. One chunk takes 60 seconds, and bam — deadlock.

I fixed this same problem last month for a client running PostgreSQL 14 with a 200GB orders table. Took 15 minutes once I knew the trick.

Fix steps (do these in order)

1. Check current locks. Before anything else, run this in your database:

   SHOW FULL PROCESSLIST;

   In MySQL, look for rows where State is Locked or Waiting for table metadata lock. Kill any old sessions holding locks with KILL <id>. In PostgreSQL use SELECT * FROM pg_stat_activity WHERE state = 'active'.
2. Reduce chunk size. Most tools let you set this. For Liquibase add --chunk-size=1000. For Flyway use flyway.chunkSize=1000. For raw MySQL use pt-online-schema-change --chunk-size=1000. Smaller chunks mean fewer rows per transaction, less lock time.
3. Increase lock wait timeout. Set it per session:

   SET SESSION innodb_lock_wait_timeout = 300;

   That gives 5 minutes instead of 50 seconds. For PostgreSQL use SET lock_timeout = '10min'.
4. Run the migration during low traffic. Schedule it for 2 AM Sunday when nobody's using the system. I know, obvious, but I can't count how many people run it during peak hours and blame the tool.

If the main fix doesn't work

Try these in order:

• Use online schema change tools. pt-online-schema-change (Percona Toolkit) or gh-ost for MySQL. These create a shadow table and copy data in chunks, then swap. No long locks. For PostgreSQL use pg_repack or pg_reorg.
• Increase server memory. Not the first thing to try, but if your innodb_buffer_pool_size is 1GB and your table is 200GB, you're swapping. Bump it to 80% of available RAM. On PostgreSQL increase shared_buffers to 25% of RAM.
• Skip the migration entirely. Yeah, I said it. If the change is adding a nullable column with a default, you can sometimes do it in two steps: add the column without default, then update rows in batches, then add the default. Use ALTER TABLE ... ALTER COLUMN ... SET DEFAULT after the data's in place.

Prevention tips

• Test on a copy of the big table. Before running on production, copy the table to a test server and run the migration there. Time it. Adjust chunk size until each chunk takes under 5 seconds.
• Monitor migrations with a simple script. I use a bash loop that shows progress:

  while true; do clear; echo "Chunks so far:"; mysql -e "SELECT COUNT(*) FROM my_big_table WHERE new_column IS NOT NULL;"; sleep 10; done

• Always set a lock timeout. Even if you think it won't hang. Set it to 10 minutes. You'll thank me later.
• Use version control for your migration scripts. If something breaks, you can roll back to the last working version. I use Git and tag each migration.

That's it. The fix is almost always chunk size or lock timeout. Don't waste time reinstalling the tool or blaming the database. Adjust those two knobs and move on.