How to give priority to certain requests?

Question

In some cases, when several back-end processes start at the same time (queue management is something else, I can solve it this way, but this is not a question here), I get General error: 1205 Lock wait timeout exceeded; try restarting transaction ROLLING BACK General error: 1205 Lock wait timeout exceeded; try restarting transaction ROLLING BACK

A process with a lower priority is one that locks the table due to the fact that it started a few minutes before the priority one.

How to give priority to a request for an already running process?

Hope this was clear enough.

Answer 1

Once a request has started execution, it cannot be suspended / interrupted. The only exception to this is at the database administration level, where you could essentially make the request stop (think of it as how to kill the current process on Windows if you want). However, you do not want to do this, so forget about it.

Your best option is to use the LOW PRIORITY chunked operation. Basically, this means that if a query in LOW PRIORITY takes too long to complete, think about how you could split it so that it is faster without creating lost data or illegal data in the database.

A very simple use case is an insert into which 10,000 new lines are inserted. By “breaking” the insert so that it performs the insert several times with smaller data sets (i.e. 500 at a time), each of them will complete faster and, therefore, will allow you to perform any operations other than LOW PRIORITY more timely.

how

Setting something as low priority is as simple as adding LOW_PRIORITY to the 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"