CODEWITHSUNDEEP
mysqlsqldatabase
natenjo
natenjo

Reputation: 51

MySQL execute 2 queries to the same table simultaneously

New values are added to a MySQL Database all day long. There are over 8 million rows in this table. Because there are a lot of old values, I want to clean these up without stopping the Java Program Inserting the values.

I am sending this query to the MySQL:

DELETE FROM `tablename`
WHERE `from` <= (date_add(now(), interval -20 DAY))

but what happens is that the Java Program stops adding new values to the table. It there any way I can handle this without editing the Java Program? I would be willing to run the query as a cronjob once a day.

I have also tried working with the parameter LOW_PRIORITY, but it doesn't make any difference.

Upvotes: 4

Views: 1315

Answers (2)

hd1
hd1

Reputation: 34657

I'd imagine you need to change your storage engine. Do this using ALTER TABLE tbl ENGINE = innodb, but do be aware of the differences between MyISAM and Innodb.

Upvotes: 0

mvp
mvp

Reputation: 116187

It seems that you are using old MyISAM db engine.

Unfortunately MyISAM uses table-level locking. When a row is inserted, updated or deleted, all other changes to that table are held up until that request has been completed. In your case long running DELETE blocks any INSERTs into that table.

You can improve it by changing your table engine to InnoDB like this:

ALTER TABLE mytable ENGINE = innodb;

InnoDB engine is fully transactional and does not lock whole table during INSERT, UPDATE or DELETE, so your problem should go away.

Upvotes: 2

Related Questions