How to swap MySQL table and change auto_increment atomically?

Question

I have two tables:

CREATE TABLE table_a (
    id SERIAL
);
CREATE TABLE table_b (
    id SERIAL
);

I want to swap the tables out and set the auto_increment of the new table_a to the MAX(id)+1 of table_b. E.g.

SELECT @A:=MAX(id) FROM table_a;
SET @qry = CONCAT('ALTER TABLE table_b AUTO_INCREMENT =', @A+1);
PREPARE stmt FROM @qry;
EXECUTE stmt;
RENAME TABLE table_a TO table_b_tmp, table_b TO table_a, table_b_tmp TO table_a;

Unfortunately I can't lock the tables nor do this in a transaction as RENAME TABLE doesn't work on a locked table and ALTER TABLE implicitly commits.

The only solution I can think of to avoid overlapping ids is to set the auto_increment + 50, but as this process is happening frequently I'd rather not have loads of holes in my ids.

Any ideas?

Answer 1

As long as the server is not on a version of MS Windows and you stick with non-transactional tables, you should be able to use table locks and ALTER TABLE table_name RENAME TO new_table_name statements.

LOCK TABLES table_a WRITE, table_b WRITE;
SELECT @A:=MAX(id)+1 FROM table_a;
ALTER TABLE table_a RENAME TO table_a_tmp;
LOCK TABLES table_b WRITE, table_a_tmp WRITE;
PREPARE stmt FROM 'ALTER TABLE table_b RENAME TO table_a, AUTO_INCREMENT = ?';
EXECUTE stmt USING @A;
ALTER TABLE table_a_tmp RENAME TO table_b;
UNLOCK TABLES;

The second lock worries me. If there are any other pending sessions, will they get a chance at the tables before the lock? I could also easily be wrong about the above working. I'm basing the above on the pages below about locks, transactions and ALTER statements.

My other thought is you could create a MERGE table for inserts, updating the merge method to FIRST or LAST instead of (along with?) swapping the tables. Would that work?

• 12.1.7. ALTER TABLE Syntax
• B.1.7.1. Problems with ALTER TABLE
• 12.4.3. Statements That Cause an Implicit Commit
• 12.4.5.1. Interaction of Table Locking and Transactions