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

Question

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?

Answer 1

No, you can't DELETE without locking the rows examined.

But you can minimize the number of rows examined by creating an index on the timestamp column you are searching.

This will also create gap locks against the potential rows you might try to insert at the end of the table, to ensure new rows don't affect the DELETE.

In InnoDB, ordinary write locks like those created by DELETE don't block reads. Concurrent transactions can still read the rows—even the rows you're deleting.

Ordinary write locks don't lock the whole table. Well, it locks the table with an intention lock which just prevents other table locks, like those required by ALTER TABLE or DROP TABLE. In other words, you can't ALTER/DROP a table while it has any reads or writes in progress.

You might like my presentation: InnoDB Locking Explained with Stick Figures.

Answer 2

Maybe i am wrong, but on https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html i have read that it makes row lock, not table lock.

Anyway you can try

DELETE ... FROM ... WHERE ... LIMIT x

And execute as many times as needed. Between executions other queries can enter and minimize impact. Of course, make this job on low load hours.

Answer 3

(NOW() * 1000) - (7 * 24 * 60 * 60 * 1000) does not look like a valid time. It is 20170519568613000, which looks like a mixture of DATETIME and some kind of milliseconds. Perhaps you wanted UNIX_TIMESTAMP() * 1000 - (7 * 24 * 60 * 60 * 1000) = 1494742589000.

How many rows are you expecting to delete? If it is a large number, then consider partitioning, or deleting in chunks