As far as I know, DELETE FROM ... WHERE issues a lock on the table.
Now, I have a huge InnoDB table in MariaDB with a size of 1TB+ that is actively being used, and having it locked until the entire database has been searched for rows to delete is no option.
Is there any way how I could delete rows matching certain criteria without locking it while the delete is happening?
Here are some more specifics for this case:
- The server is running MariaDB 10.1.22-3 from the Debian Stretch repository
- The server has 32 GB ram and
innodb_buffer_pool_size = 20G - The database has a size of 1TB+ with lots of active
INSERTs andSELECTs at all times - The entire database contains only 2 tables:
- One table for the actual data (which basically has a structure like
data (BIGINT id, LONGTEXT data)(wheredatais a big chunk of JSON. I know that this is not a perfect relational database model, but the JSON comes from a third party, it's pretty complex, and could contain structure changes from the third party at any time and without notice) - And one table for some kind of 'indexes' to satisfy
SELECTs. (Simplified example, this could have a structure likedata_index (BIGINT id, INT userId, INT itemId, BIGINT timestamp), so I could useSELECTon userId and itemId, and join on the actual data. (timestamp is the unix timestamp in milliseconds)
- One table for the actual data (which basically has a structure like
- Like I said, the data is meant to be stored for a limited time only. So basically now I want to create a cronjob that runs once per day to delete rows that are older than 7 days.
To fulfill the task, I would naturally come up with this simple query:
DELETE `data`, `data_index`
FROM `data_index`
LEFT JOIN `data` ON `data`.`id` = `data_index`.`id`
WHERE `timestamp` > (NOW() * 1000) - (7 * 24 * 60 * 60 * 1000)
But this would probably lock the tables for a pretty long time. How could I accomplish the same task without locking the tables, so the database remains functional for other SELECT and INSERT queries?