Why does adding a column to the orders table stay stuck at waiting for table metadata lock: table size or a long-running transaction?

Whether adding a column directly to the orders table will block online writes does not depend on how many rows the table appears to have. It depends on whether this change only updates the data dictionary or requires a full table rebuild, and whether long-running transactions are present at execution time. Based on typical 2026 delivery experience, when the column is added at the end, the type is simple, and conditions allow metadata-only changes, it usually completes in milliseconds to seconds. Once it becomes a full table rebuild, a table with a few million rows commonly takes from over ten minutes to one or two hours. What actually causes overall API timeouts is often not the rebuild being slow itself, but metadata lock queuing that blocks all later requests.

1. When waiting for table metadata lock appears, first determine whether the ALTER is slow or blocked

An ALTER statement must acquire an exclusive metadata lock on the table before it can start. At execution time, if a long-running transaction, an uncommitted query, or a long-idle connection still holds the old table definition, the ALTER has to queue. While it is queued, new ordinary queries arriving later queue behind the ALTER, so reads and writes on the table slow down together and it looks as if the whole site is stuck. During on-call, what you usually see is API timeout alerts, but the real starting point is an uncommitted transaction somewhere else.

So when you see this message, do not first increase the lock wait timeout. That parameter only decides how long to wait before giving up; it does not change the fact that requests are queued. First open the session list and sort by execution time, check for long-running transactions, connections that have been idle for a long time, and running reports or backup jobs. Clear them, and then decide when to execute. The time spent on this step is usually more valuable than moving the change to the early morning.

• Rebuilding the table itself consumes IO: other workloads on the same instance slow down, not just this table.
• Lock waiting spills over: one long-running transaction blocks the table, and all later requests accessing the table pile up in the queue.
• Replica replay is slower: the primary finishes in one go, while replicas replay serially, so lag is stretched.
• Disk headroom: during the rebuild, old and new copies of the data coexist, so temporary space must be reserved in advance.

Among these, lock-wait spillover is the item that most needs prevention; the other three only mean slower performance. Slowness can be waited out, but a blocked queue can only be avoided by clearing blockers in advance.

2. Does this column addition change metadata or require a rebuild, and how do you tell?

The official database documentation has an online DDL support matrix, but it gives capability boundaries, not the specific cost of this change. In project work, we usually go through four checks before adding a column: the first three determine whether a rebuild will happen, and the fourth determines whether a rebuild will turn into an incident.

1. Check the algorithm: whether it can take the metadata-only path is limited by version, row format, whether the table is compressed, and other conditions; if the conditions are not met, it falls back to a rebuild.
2. Check the column position and type: an ordinary column added at the end that allows NULL or has a default value is cheap; adding it in the middle, changing the type, or changing the character set basically means a rebuild.
3. Check table specifications: row count, average row length, and the number of secondary indexes together determine duration; the more indexes, the slower the data copy.
4. Check traffic and long-running transactions: whether report connections and batch jobs are running, and whether current write volume can withstand jitter during this period.

Item four is the easiest to skip. Many online incidents are not caused by a slow ALTER but by an ALTER running into a long-running transaction that was not cleared; the former is only slow, while the latter escalates into site-wide queuing. So clearing sessions five minutes before execution has higher priority than choosing the time slot.

Testing on staging with comparable data is more reliable than guessing from documentation

Clone the production table structure to staging, import data of a similar scale, and run it once to see actual duration and disk growth. This is a common and worthwhile practice. If data volume differs by an order of magnitude, duration may differ several times; but as long as the test jumps from seconds to minutes, it means this change is not metadata-only and later arrangements should follow a rebuild.

3. Cost and duration of three paths, compared by experience range

After confirming that a rebuild is needed, there are usually three paths: direct ALTER, online schema change tools, and shadow tables with dual writes. They are not a new-versus-old replacement relationship; they are layered by table size and acceptable interruption risk. Choosing the wrong direction wastes more time than tuning the wrong parameter.

• Direct ALTER: within a few hundred thousand rows and with suitable conditions, it commonly takes seconds to tens of seconds; when a few million rows trigger a rebuild, the typical range is over ten minutes to one or two hours; extra disk and extra components are close to zero; the risk is exposing locks and latency directly to the business.
• Online schema change tools: copy data while chasing incremental changes, then perform a brief switch at the final stage; they need extra disk, roughly the volume of data plus indexes with headroom, and require a usable primary key or unique key on the table; for a few million to tens of millions of rows, the typical range is tens of minutes to several hours; the risk falls on the brief lock during the one- or two-second switch, and on triggers and intermediate tables left behind if the tool is interrupted.
• Shadow table with dual writes: the application writes to both old and new tables, compares and verifies, and then switches reads; the cycle is commonly counted in days to weeks, suitable for very large tables or scenarios with higher consistency requirements; the cost is changing code, writing comparison scripts, and tolerating double writes for a period.

Selection criteria can be compressed into one sentence: if write jitter within tens of seconds is acceptable, use direct ALTER; if not, use a tool; only when even the brief switch of a tool is unacceptable is dual writing worth it. Conversely, building a full dual-write setup for a configuration table with tens of thousands of rows just to add one column is over-engineering, and maintenance cost is higher than the risk saved.

4. Delivery scenario: a four-hour window, a three-million-row table, and a nightly report still attached

In a project in the first half of 2026, the situation was fairly typical: the client gave only a four-hour window, the orders table had around three million rows, a nightly report query ran on it, and the budget did not include purchasing a separate operations platform. The constraints were a tight schedule, no one watching the whole time, and trying not to stop the business. The approach was to test in advance on staging with comparable data, clear long-running transactions and long-idle connections through the session list before execution, temporarily stop that night's report job, change replicas first and the primary afterward, and rate-limit the tool's copy speed so it would not crowd out online writes. The result was that the rebuild ran nearly twice as long as the staging test, the one- or two-second switch caused a small number of write retries, and the report was rerun once the next day.

For this type of table, typical duration is over ten minutes to more than an hour with direct ALTER, and forty minutes to three hours with an online tool, depending on the number of indexes and instance specifications. The cost is not large, but if sessions had not been cleared that night, it would most likely have gone from slightly slower to API timeouts for the whole period, and that would not have been settled by rerunning a report once. For similar changes, Xiyue Company writes the staging test data and session cleanup checklist into the change ticket during delivery rather than relying on judgment on the spot.

5. When is it worth evaluating first, and when is it not

Cases that need the full evaluation process are medium-to-large business tables with continuous writes and reports or batch jobs sharing the same instance; even if this change happens to finish in seconds, it is worth formalizing the process, because the next one may not go so smoothly.

• No tool needed: configuration tables within tens of thousands of rows, log archive tables, or internal systems that already have a maintenance window.
• Can change directly: the column is added at the end, conditions allow metadata-only changes, and you can confirm before and after execution that there are no long-running transactions.
• Not recommended to force it: replication lag is already high, disk headroom is insufficient, or no one is watching during the execution window.

6. How to accept the change after it is done, and several commonly overlooked pitfalls

Adding a column itself is not complex; problems mostly occur in surrounding steps. The acceptance criteria are best fixed as four items: schema comparison matches, row count and checksums of key fields match, replica lag returns to baseline, and business write success rate recovers. Consider it complete only when all four match; checking only one easily misses aftereffects.

• Only the primary was changed, and a same-structure table in another environment was forgotten, so code reports that the field does not exist when reading it.
• Disk headroom was estimated by table data size, ignoring indexes and temporary files, and space ran out halfway through.
• The tool was interrupted midway, triggers and intermediate tables were not cleaned up, and subsequent writes produced duplicates.
• The default value of the new column is inconsistent with the code's null-check logic, so historical rows read as NULL rather than the expected value.
• The character set was changed along the way; what was supposed to be a metadata-only change became a full table conversion, doubling the duration.

Frequently asked questions

Adding a column takes only a few seconds, so why evaluate it separately?

Because duration is determined by the implementation algorithm, not by the number of columns. Once it becomes a table rebuild, a few million rows commonly take tens of minutes to one or two hours; evaluation is to know in advance which bucket this change falls into.

Executing during the early-morning off-peak period means I do not need to worry about it, right?

Off-peak only reduces the impact scope; it does not change the nature of locks and latency. During off-peak there may still be backups, batch jobs, and long-running report transactions, so sessions still need to be cleared before execution.

If I use an online schema change tool, does that mean there is little risk?

The tool only shortens the duration of locks. It still requires extra disk, a usable primary key, and sufficient log retention; the brief lock at the switch and cleanup after an interruption must be arranged in advance.

Should replicas be changed as well, and will that make replication lag look terrible?

Yes, they should. Usually change replicas first and then switch the primary. DDL is replayed serially on replicas, and a large table rebuild stretches lag, so confirm that lag has returned to baseline before switching.

After the table structure is changed, how do we judge that this change is qualified?

Schema comparison matches, row count and checksums of key fields match, replica lag returns to baseline, and business write success rate recovers. Finish only when all four match.

If you have a table that needs a column added, do two things first: run a test on staging with comparable data, and list the long-running transactions and idle connections to clear before execution. Within a few hundred thousand rows and with suitable conditions, a direct change is usually enough; at tens of millions of rows and when write interruption is sensitive, then consider an online tool or dual writes. There is no need to put the entire business at risk just to skip one evaluation.