Bug #2494 LOW_PRIORITY WRITE LOCK AFFECTING SUBSEQUENT WRITE LOCKS
Submitted: 23 Jan 2004 11:31 Modified: 3 Feb 2004 2:22
Reporter: Matthew Lord Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.17 OS:Windows (windows 2000)
Assigned to: Konstantin Osipov CPU Architecture:Any

[23 Jan 2004 11:31] Matthew Lord
Description:
When you issue a low priority write lock and that lock is waiting for existing read locks to be released, it causes subsequent normal write locks to queue behind the low priority one.  This effectively makes any write lock low priority until the original low priority one goes through.

So the main synopsis:
1)  A low priority write lock goes in and stays in the queue before any subsequent normal write locks allowing subsequent read locks to go through before the normal write lock is aquired.  See How to repeat:

How to repeat:
in thread 1: lock table blah read;
in thread 2: lock table blah low_priority write;
in thread 3: lock table blah write; // appears to queue behind the above write                   lock
in thread 4: lock table blah read; // this one goes through!

Suggested fix:
In the above scenario the 3rd (normal write) lock should jump next in line while the 4th (read lock) should go in behind the 3rd and the 2nd (low priority) lock should remain last in the queue.
[3 Feb 2004 2:22] Konstantin Osipov
Though we can make the particular example given in the bug report work as expected, it's not easy to implement a solution for general case of writers starvation when low priority and ordinary write locks are combined.
A sample scenario given by Monty is:

connection 0: LOCK TABLES t1 READ;
connection 1: LOCK TABLES t2 WRITE, t1 LOW_PRIORITY WRITE;
connection 2: LOCK TABLES t2 WRITE;

In this case no locks on t2 will be given until there are no readers of t1.

Monty says: The lock code was not meant for mixing different priority locks. You should always use same kind of locks on the same table. 

We don't want to fix this simple case because it is the only one which can be fixed, and 
the rule above is simplier to document and understand.
I'm filing a docs-request to have this issue covered in the documentation.