MySQL locking priorities

Question

If there is an item that has a read lock on a table for a whole minute, then other items can still read that same table uninterrupted.

So I can have this timeline

A makes a read lock for a minute B, C, D, E ... and many more still read from that table without a problem

But say that T asks for a write lock. T has to wait.. It seems that U V W are all waiting for T,, even though U V W are only asking for read locks and T has not actually aquired it's lock yet.

Is it possible to disable this First In First Out sort of prioritizing and to give read locks higher priority than write locks?

Edit: Perhaps there is some sort of low priority switch that I can put on my update commands? I would like for it to be synchronous (so a thread can wait till it is done, even though it is low priority).

Answer 1

But say that T asks for a write lock. T has to wait.. It seems that U V W are all waiting for T,, even though U V W are only asking for read locks and T has not actually aquired it's lock yet

Write locks are assigned higher priority than read locks.

This means that T will only have to wait for A to complete, and U, V and W will be queued after B, C, D, and E.

Actually, read and write locks are held in the separate queues, the latter being of higher priority.

In your case, the request will be executed in the following order:

A
T
B C D U V W

, everything after T being executed concurrently.

From the docs:

Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the lock is made available to the requests in the write lock queue and then to the requests in the read lock queue. This ensures that updates to a table are not “starved” even if there is heavy SELECT activity for the table. However, if you have many updates for a table, SELECT statements wait until there are no more updates.

If you want the writing transaction to wait until there are no pending reads, you should explicitly issue

LOCK TABLES mytable LOW_PRIORITY WRITE

prior to the INSERT, and do not forget to do

UNLOCK TABLES

afterwards.

Answer 2

I found a way to make updates low priority without sending them to a separate thread.

Change one of

UPDATE bob ...
INSERT INTO bob ...
DELETE FROM bob;
LOCK TABLE bob WRITE;

to

UPDATE LOW_PRIORITY bob ...
INSERT LOW_PRIORITY INTO bob ...
DELETE LOW_PRIORITY FROM bob;
LOCK TABLE bob LOW_PRIORITY WRITE;