CODEWITHSUNDEEP
mysqldatabasemariadb
user2015253
user2015253

Reputation: 1444

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

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:

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?

Upvotes: 10

Views: 22602

Answers (4)

Slawomir
Slawomir

Reputation: 3343

Eat the elephant one bite at a time with reduced locking time. Here is one way. Create a stored procedure, say purge_old_transactions, that works like this:

create procedure purge_old_transactions()
begin

create temporary table if not exists tmp_purge (
`transaction_id` <datatype> not null,
primary key (`transaction_id`)
)
engine=memory;
delete from tmp_purge;

set transaction isolation level read uncommitted ;

insert into tmp_purge(`transaction_id`)
select transaction_id
from <many-tables>
where <complex-criteria>
limit 1000 -- whatever chunk you can comfortably swallow while locking (later)
;

commit;


delete fatty
from fatty_table fatty
    join tmp_purge tmp
        on tmp.transaction_id = transaction_id
;


end $$
delimiter ;

Run above as often as you need to keep trimming obsolete data, if growth of data is slower than delete-chunk, you will catch up.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562368

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.

Upvotes: 6

Rick James
Rick James

Reputation: 142296

(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

Upvotes: 2

Fran Cerezo
Fran Cerezo

Reputation: 948

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.

Upvotes: 5

Related Questions