I am having a table in which we have some 20 million entries inserted(blind insertion without any constraints) per day. We have two foreign keys and one of it is a reference id to a table with some 10 million entries.
I am planning to delete all the data in this table older than a month, because this data is not needed anymore. But the problem is that with the huge number of insertions happening, if i start deleting, the table will be locked and insertions will be blocked.
I wanted to know if we can use partitioning on the table based on month. This way, i was hoping that when i try deleting all the data older than 2 months, this data should be in a different partition and insertions should be happening in a different partition, and the delete lock will not be blocking the read lock.
Please tell me if this is possible. I am fairly new to using DB, so please let me know if there is something wrong with my thought.
From the MySQL documentation
For InnoDB and BDB tables, MySQL uses table locking only if you explicitly lock the table with LOCK TABLES. For these storage engines, avoid using LOCK TABLES at all, because InnoDB uses automatic row-level locking and BDB uses page-level locking to ensure transaction isolation.
I'm not sure you even have an issue. Have you tested this and seen locking issues, or are you just theorizing about them right now?