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:
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

[31 Oct 2007 8:52] Mikiya Okuno
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;

*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`)
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.


while [ $n -lt 10 ]
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`

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.
[31 Oct 2007 8:56] MySQL Verification Team
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.

[29 Nov 2007 7:21] Sveta Smirnova
test case

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

[29 Nov 2007 7:52] Sveta Smirnova
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".
[29 Nov 2007 8:07] Sveta Smirnova
See also bug #32839