MySQL Bugs: #31977: HIGH_PRIORITY and LOW_PRIORITY modifier don't look working properly.

Bug #31977	HIGH_PRIORITY and LOW_PRIORITY modifier don't look working properly.
Submitted:	31 Oct 2007 8:52	Modified:	29 Nov 2007 7:53
Reporter:	Mikiya Okuno	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	optimizer scheduler priority myisam

Description:
HIGH_PRIORITY and LOW_PRIORITY modifier don't look working properly. Read requests with HIGH_PRIORITY modifier weren't processed prior to others. Write requests with LOW_PRIORITY modifier were processed before other requests were processed.

How to repeat:
Here's sample. The table `t2` is created as MyISAM;

mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=12992244 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Then table `t2` populates 1M rows.

mysql> SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
|  1000000 | 
+----------+
1 row in set (0.00 sec)

And here's a sample script to issue concurrent read and write requests with priority modifies.

#!/bin/bash

n=0
while [ $n -lt 10 ]
do
l=`expr 12000000 + $n`
m=`expr 12100000 + $n`
echo "UPDATE LOW_PRIORITY t2 SET b=$n WHERE a BETWEEN $l and $m" | mysql -t mytest &
m=`expr 11992244 + $n`
echo "UPDATE LOW_PRIORITY t2 SET b=$l WHERE a=$m" | mysql -t mytest &
echo "SELECT * FROM t2 WHERE a=$m" | mysql -t mytest &
m=`expr 12000000 + $n`
echo "SELECT HIGH_PRIORITY * FROM t2 WHERE a=$m" | mysql -t mytest &
n=`expr $n + 1`
done

Then we can find that read and write requests are locked, and updating processes are processed first. Please see the attachment, which describes SHOW FULL PROCESSLIST output.

Eventually, read requests with HIGH_PRIORITY are processed before write requests. But you can see a wrong case within a several trial.

Suggested fix:
Read requests with HIGH_PRIORITY should be processed prior to other read and write requests. Write requests with LOW_PRIORITY should be processed after any other read and write requests.

SHOW FULL PROCESSLIST output shows that write requests are processed before read requests with HIGH_PRIORITY modifies.

Attachment: show_full_processlist1.txt (text/plain), 15.76 KiB.

test case

Attachment: bug31977.test (application/octet-stream, text), 1.39 KiB.

According to http://dev.mysql.com/doc/refman/5.0/en/select.html:

HIGH_PRIORITY gives the SELECT higher priority than a statement that updates a table. You should use this only for queries that are very fast and must be done at once. A SELECT HIGH_PRIORITY query that is issued while the table is locked for *reading* runs even if there is an update statement waiting for the table to be free. 

and to http://dev.mysql.com/doc/refman/5.0/en/insert.html:

If you use the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other clients are *reading* from the table. This includes other clients that began reading while existing clients are reading, and while the INSERT LOW_PRIORITY statement is waiting.

In provided test table is locking for *writing*. So I close this report as "Not a Bug".

See also bug #32839