Long running SELECT with UPDATE locks database-CodePudding

I have a select statement that takes a long time to run (around 5 minutes). Because of this I only run the query every hour and save the results to a metadata table. Here is the query:

UPDATE `metadata` SET `value` = (select count(`id`) from `logs`) WHERE `key` = 'logs'

But this is the issue I have been having (And correct me if I am wrong). A select statement does not lock the database, but an update statement does. Now since I am running this long ruining select query inside of the update query, it ends up locking the DB for about 5 minutes.

Is there a better way to do this to run the select statement and save it to a variable and then once that is done then running the update query? This way it wont lock the DB.

Also note I don't care about dirty data.

The database has over 300 million rows and has data being added to it constantly.

CodePudding user response：

Just to avoid the possibility that the server disconnects between the statement getting the count and the statement storing it, leaving your variable unset, beginning in mariadb 1.1 you can run multiple statements in a single request by putting them in a block:

begin not atomic
    declare `logs_count` int;
    select count(*) into `logs_count` from `logs`;
    update `metadata` set `value`=`logs_count` where `key`='logs';
end

fiddle

CodePudding user response：

I have found that setting this before the query runs seems to work and runs a whole lot faster. This should keep the DB from locking when executing the query. We then enable locking after it has completed. (Please correct me if I have done something incorrect here)

BEGIN
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE `metadata` SET `value` = (select count(`id`) from `logs`) WHERE `key` = 'logs';
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END