Renaming a MySQL Table Atomically with RENAME TABLE

MySQL does not run DDL inside transactions, so the PostgreSQL trick of renaming a table and creating a compatibility view in one BEGIN ... COMMIT does not exist. What MySQL has instead is a single statement that renames several tables at once, atomically: RENAME TABLE a TO b, c TO a. No other session ever sees a moment in which a is missing. That statement is the foundation of every MySQL table swap — it is how gh-ost and pt-online-schema-change cut over, and how you can replace a table with a rebuilt copy or rename a table while parking a view under the old name almost simultaneously. Its weakness is the metadata lock it needs: queued behind a long transaction, it freezes the table for everyone. This guide covers atomic renames and swaps, how to keep them from queueing, and how to bridge old names. It belongs to Renaming and Splitting Tables Online.

An Atomic Table Swap Sequence between the migration session, MySQL and an application session. The migration issues RENAME TABLE orders TO orders_old, orders_new TO orders. MySQL acquires exclusive metadata locks on all three names, performs both renames, and releases the locks. An application query arriving during the swap waits milliseconds and then reads the new table. An Atomic Table Swap Migration MySQL App session RENAME TABLE orders TO orders_old, orders_new TO orders exclusive MDL on all names SELECT … FROM orders (waits ms) both renames applied, locks released rows from the new orders
Both renames happen under one set of metadata locks; queries see either the old table or the new one, never neither.

Symptom / Error Signatures

A rename or swap that queues shows the familiar metadata-lock pile-up:

| 4021 | mig | Query | 38 | Waiting for table metadata lock | RENAME TABLE orders TO orders_old, orders_new TO orders |
| 4033 | app | Query | 37 | Waiting for table metadata lock | SELECT * FROM orders WHERE id = 9912                     |

A non-atomic sequence — two separate RENAME TABLE statements, or DROP then RENAME — produces brief windows of failure:

ERROR 1146 (42S02): Table 'shop.orders' doesn't exist

And with a short lock_wait_timeout, a blocked rename fails fast instead of queueing: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

Root Cause Analysis

RENAME TABLE acquires exclusive metadata locks on every source and target name in the statement, then performs the renames in order, then releases the locks. Because all names are locked for the duration, no other statement can observe an intermediate state, which is what makes a multi-table rename atomic. ALTER TABLE ... RENAME also works for a single table but cannot be combined with other renames.

The exclusive metadata lock must wait for every transaction that has touched any of the tables to finish, and while it waits, new statements on those tables queue behind it — the pathology explained in diagnosing “Waiting for table metadata lock”. The default lock_wait_timeout of one year means an unguarded rename will wait essentially forever.

Operation Atomic? Risk
RENAME TABLE a TO b yes (single rename) old name disappears for running code
RENAME TABLE a TO a_old, a_new TO a yes (swap) MDL queue if a long transaction holds a
two separate RENAME TABLE statements no window where a does not exist
DROP TABLE a; RENAME TABLE a_new TO a no window plus data loss if the second fails
RENAME TABLE a TO b then CREATE VIEW a AS SELECT * FROM b two statements milliseconds without a between them
Swap Done Right vs Wrong Two panels. Wrong: two statements, RENAME orders TO orders_old then RENAME orders_new TO orders, with a gap in which queries fail with table doesn't exist. Right: one statement renaming both, with lock_wait_timeout set so it fails fast rather than queueing behind a long transaction. Swap Done Right vs Wrong Two statements RENAME TABLE orders TO orders_old (gap: ERROR 1146 for queries) RENAME TABLE orders_new TO orders visible outage One statement + timeout SET SESSION lock_wait_timeout = 3 RENAME TABLE orders TO orders_old, orders_new TO orders retry on 1205 atomic, bounded wait
One statement makes the swap atomic; a short lock_wait_timeout keeps it from freezing the table while it waits.

Immediate Mitigation

1. If a rename is queueing now, kill it. It has not done anything yet, and killing it releases the queue.

-- MySQL 8.0 · requires CONNECTION_ADMIN · safe: the rename has not been applied
KILL QUERY 4021;

2. Find and clear the transaction it was waiting for using performance_schema.metadata_locks and information_schema.innodb_trx, then retry with a timeout.

3. Always rename with a short wait budget and retry.

-- MySQL 8.0 · migration session · requires ALTER and DROP on the old names, CREATE and INSERT on the new
-- WARNING: without the timeout, a long transaction on orders makes this freeze the table.
SET SESSION lock_wait_timeout = 3;
RENAME TABLE orders TO orders_old, orders_new TO orders;
-- ROLLBACK PATH: RENAME TABLE orders TO orders_new, orders_old TO orders;

Permanent Fix / Long-Term Pattern

Use the multi-table form for every rename that must be seamless, with lock_wait_timeout of a few seconds and a runner that retries on ERROR 1205, following setting lock_timeout and retrying DDL safely. For a pure rename that running code still depends on, create the compatibility view immediately after the rename — the gap is a few milliseconds, which in practice is within most retry budgets — or, better, stage it: first deploy code that tolerates both names (for example by querying through a view created in advance under the new name), then swap.

-- MySQL 8.0 · staged rename · the new name exists as a view first, so new code can deploy early
SET SESSION lock_wait_timeout = 3;
CREATE VIEW accounts AS SELECT * FROM user_accounts;            -- step 1: new code uses accounts
-- … deploy code using accounts; wait for rollout …
DROP VIEW accounts;
RENAME TABLE user_accounts TO accounts;                          -- step 2: brief gap for the view only
CREATE VIEW user_accounts AS SELECT * FROM accounts;             -- step 3: old name bridged
-- ROLLBACK PATH: DROP VIEW user_accounts; RENAME TABLE accounts TO user_accounts;

MySQL views over a single table are updatable, so old code can keep writing through user_accounts. Remove the bridge once performance_schema shows no statements using the old name. For full table rebuilds — the swap an online schema change tool performs — see cutting over gh-ost migrations safely, which uses the same atomic rename under the hood.

Safe Swap Procedure in MySQL Five steps. Prepare the replacement table; check innodb_trx for long transactions on the table; set a short lock_wait_timeout; run one multi-table RENAME TABLE; on error 1205, back off and retry, then keep the old table for a retention period. Safe Swap Procedure in MySQL STEP 1 Prepare new table orders_new ready STEP 2 Check long txns innodb_trx STEP 3 Short timeout lock_wait_timeout = 3 STEP 4 One RENAME TABLE atomic swap STEP 5 Retry + retain keep orders_old
The swap itself is one statement; everything around it exists to keep that statement from waiting.

Replication is the last thing to confirm. RENAME TABLE is written to the binary log as a single statement and replays atomically on replicas, but a replica with a long-running query on the table will make the replicated rename wait for its metadata lock, pausing the replica’s SQL thread and growing lag until the query finishes. Keep long analytical queries off replicas that serve latency-sensitive reads during planned swaps, or watch replica lag during the change and be ready to kill the blocking query there too.

Verification Checklist

Frequently Asked Questions

Is RENAME TABLE with several tables really atomic? Yes. MySQL locks all names involved, performs the renames, and releases the locks as one operation, so other sessions see either the state before or the state after. If any rename in the list fails, MySQL reverts the ones already done.

Can I rename a table across databases? Yes, RENAME TABLE db1.t TO db2.t moves a table between databases (schemas) on the same server, provided they are on the same file system; triggers on the table can prevent it.

Why not use ALTER TABLE ... RENAME? It works for a single table but cannot be combined with other renames in one statement, so it cannot perform an atomic swap.

Do views break when a table they reference is renamed? Views in MySQL store table names, so a view referencing the old name becomes invalid after a rename (ERROR 1356). Recreate dependent views against the new name as part of the change.

Related Articles