9

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.

NobodyNada
  • 7,529
  • 6
  • 44
  • 51
Itay Moav -Malimovka
  • 52,579
  • 61
  • 190
  • 278

1 Answers1

12

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"

Lee
  • 10,496
  • 4
  • 37
  • 45
  • ok...how would I mark a query low priority (can u add this to the answer?) – Itay Moav -Malimovka Sep 03 '12 at 22:01
  • 1
    added, dont forget you will also need to chunk the operation into smaller bits for this to work, once the query starts it must finish in order to let other operations be performed. LOW PRIORITY will simply allow other normal/high priority queries to be performed as soon as the LOW PRIORITY query finishes and before the next LOW PRIORITY (i.e. the second of the chunked query) is executed. – Lee Sep 04 '12 at 08:37
  • 1
    @Lee The only thing I'd add is info about `low_priority_updates` option http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_low_priority_updates – Maksym Polshcha Sep 04 '12 at 08:50
  • I thought about that Maksym, however i prefer granular control over this stuff so i didn't want to complicate things, and of course using that global setting could bring other issues depending on implementation. But definitely if its suitable to the needs of the application its a viable alternative, especially if you're going to have to otherwise re-factor a large sum of queries. – Lee Sep 04 '12 at 18:21
  • I dont understand this answer, you are advancing to split the query to more simpler quieries but in the end you write some code that should do what questioner asked for but without any better description ... – Buksy Jan 12 '15 at 20:33