We have an import job that commits data to a staging table, and then swaps the production/staging tables using the below SQL:
RENAME TABLE user_accounts TO user_accounts_temp, user_accounts_staging TO user_accounts, user_accounts_temp TO user_accounts_staging;
Aside from writing to the staging table during the import, the tables are only read by our application (no other writes). The staging/production tables are always around same size (~40k rows). All reads to the staging/production table queries are by ID. There are no FKs in the tables.
The mystery is that the time to do the RENAME takes between 0sec-8min (usually it's 0sec). But in the latter case, it blocks reads to the table causing our production site to hang.
How can I fix/understand what the problem is? My understanding of what I've read about the rename operation is that this should always be nearly instant, and this is the best practice for doing periodic imports.