Novice here. I have a huge data that is generated every day and are stored in day wise partition. However, I have a cron that automatically deletes the old day (30 days ago). The query for deletion is as follows:
ALTER TABLE my_table DROP PARTITION p20210206;
The doc of mysql Here says when we drop a partition of innodb tables then the other partition does not get locked. However, when I perform any query during this process of partition deletion, they get locked.
I hit a small insert query and this is the status in FULL PROCESS LIST
id: 32031012 User: ab Host: myhost db: myDB Command: Query Time: 1 State: Waiting for table metadata lock Info: INSERT INTO my_table VALUES (9, 1, NULL, NULL, 'SUCCESS', '2020-02-05 09:46:13.356', '2020-02-05 09:47:36.869', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 20210307)
Can someone, please explain me why are the other partition getting locked and how can I overcome this problem.
Also, if the truncation query fails due to any reason, will the lock continue or will it be freed?