Not the answer you need?
Register and ask your own question!

look table write takes over 10 minutes

jiewujiewu EntrantCurrent User Role Beginner
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!
Jie
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.