0

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?

2
  • 1
    However, when I perform any query during this process of partition deletion, they get locked. Test SELECT query with and without partition hints - does you'll see the lock too?
    – Akina
    Mar 25 at 11:37
  • Please provide SHOW CREATE TABLE. That INSERT seems to need to insert into the partition that you are dropping??
    – Rick James
    Mar 25 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.