How to swap MySQL table and change auto_increment atomically?

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?

From stackoverflow

• 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
  outis : ALTER TABLE unlocks transactional tables or under MS Windows. It should work with MyISAM tables. That's why I asked about the table type earlier (and included the caveat about MS Windows).