DELETE without lock in MariaDB/MySQL?`(InnoDB)

Viewed 15770

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 and SELECTs 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) (where data is 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 like data_index (BIGINT id, INT userId, INT itemId, BIGINT timestamp), so I could use SELECT on userId and itemId, and join on the actual data. (timestamp is the unix timestamp in milliseconds)
  • 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?

3 Answers
Related