MySQL locking priorities

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).

Answers


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.


Need Your Help

find pair of numbers in array that add to given sum

arrays performance algorithm processing-efficiency

Question: Given an unsorted array of positive integers, is it possible to find a pair of integers from that array that sum up to a given sum?

Instantiate an object with only some of its members

php object

I'm working in an PHP application, and I'd like to be able to instantiate an object, but I need it to get instantiated only with SOME of it's properties, not all of them. Eg.