How to give priority to certain queries?

Question

On certain occasions, when several back-end process happen to run at the same time (queue management is something else, I can solve it like that, but this is not the question here), I get General error: 1205 Lock wait timeout exceeded; try restarting transaction ROLLING BACK

The process which has less priority is the one that locks the table, due to the fact that it started a few minutes before the high priority one.

How do I give priority to a query over an already running process?

Hope it was clear enough.

Answer 1

Once a query has begun execution it cannot be paused/interrupted. The only exception to this is at the DB administration level where you could essentially force the query to stop (think of it as killing a running process in windows if you will). However you don't want to do that, so forget it.

Your best option would be to use a LOW PRIORITY chunked operation. Basically what that means is if the query on the LOW PRIORITY is taking too long to execute, think about ways in which you could split it up to be quicker without creating orphaned data or illegal data in the database.

A very basic use case would be imagine an insert that inserts 10,000 new rows. By "chunking" the insert so that it runs the insert multiple times with smaller data sets (i.e. 500 at a time), each one will complete more quickly, and therefore allow any non-LOW PRIORITY operations to be executed in a more timely manner.

How To

Setting something as low priority is as simple as adding in the LOW_PRIORITY flag.

INSERT LOW_PRIORITY INTO xxx(a,b,c,) VALUES()

UPDATE LOW_PRIORITY xxx SET a=b

DELETE LOW_PRIORITY FROM xxx WHERE a="value"