We have an application that uses mysql database backend. there is a table that we replace all the data daily and our procedure is:
- “load data infile” to load the data into tmp_table
- lock table tmp_table write, prod_table write;
- alter table prod_table rename to prod_table_old;
- alter table tmp_table rename to prod_table;
- unlock tables;
This is a real-time system and prod_table is constantly being used for select queries. Step 2 sometimes take over 10 minutes to come back. During this 10 minutes, the application just times out. My understanding is that 5.5 introduced table metadata lock. SELECT will place a metadata lock on the pro_table. So when step tries to lock the table and sees the metadata lock, it will be put into a queue. But the SELECT is simple and returned very quickly. How could the LOCK take 10 minutes?
Thanks for insight/suggestion!