Given a table X and a view Y (that has the same structure as X) is there a way to rename X to Z and Y to X atomically so that no query will ever see nothing named X? Renaming X and creating the view would also be valid.
The point would be to, as part of a schema migration plan, replace the old tables with views that simulate the old version from the new version so both sets of client code can run at the same time.
MySQL is my choice but info on others would be useful as well.
This is trivial in postgres and impossible in mysql.
mysql exempts schema modifications from transactions. I have a postgres background, so that's an obvious thing to do (begin a transaction, do some stuff, commit or rollback -- postgres isn't picky about what "do some stuff" is).
You can find a few bugs on this if you look through mysql's bug base.
MySQL, like Oracle, regards most DDL statements (CREATE TABLE, ALTER TABLE etc) as causing an "implicit commit". You can't protect other connections from seeing your DDL changes using the transaction model. (I believe Postgres is unusual in this regard).
You might be able to lock the tables, using LOCK TABLES, but I suspect you will lose the lock as soon as you rename a table! LOCK DATABASE might work, but may well require all the other clients to disconnect altogether before your lock request is granted.
This, of course, will cause the other clients to block while you rename the tables, which is probably an inexpensive operation.