look table write takes over 10 minutes

  • Filter
  • Time
  • Show
Clear All
new posts

  • look table write takes over 10 minutes

    We have an application that uses mysql database backend. there is a table that we replace all the data daily and our procedure is:
    1. "load data infile" to load the data into tmp_table
    2. lock table tmp_table write, prod_table write;
    3. alter table prod_table rename to prod_table_old;
    4. alter table tmp_table rename to prod_table;
    5. 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!