Bug #32839 Inconsistent behavior of LOW_PRIORITY in 5.0 and 5.1

• Submitted: 29 Nov 2007 8:07
• Modified: 4 Dec 2007 14:47
• Reporter: Sveta Smirnova
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1 BK
• OS: Any (Linux)
• Assigned to: Davi Arnaut
• CPU Architecture: Any

[29 Nov 2007 8:07] Sveta Smirnova

Description:
Working on bug #31977 I found behaviour of LOW_PRIORITY has changed in MySQL 5.1

If MySQL 5.0 and erarlier versions follow user manual exactly:

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.

But MySQL 5.1 waits for tables locked for *writing* as well.

How to repeat:
CREATE TABLE `t2` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into t2 values(null, 0);

insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;
insert into t2 select null, b from t2;

#case 1: low
send update t2 set b=1;

sleep 1;

connect (addconroot, localhost, root,,);
connection addconroot;

send update low_priority t2 set b=2;

sleep 1;

connect (addconroot1, localhost, root,,);
connection addconroot1;
select * from t2 limit 3;

You will get:

MySQL 5.0:

update t2 set b=1;
update low_priority t2 set b=2;
select * from t2 limit 3;
a       b
1       2
2       2
3       2

MySQL 5.1:

update t2 set b=1;
update low_priority t2 set b=2;
select * from t2 limit 3;
a       b
1       1
2       1
3       1

Suggested fix:
Fix 5.1 implementation or fix user manual to reflect this change.

[4 Dec 2007 14:47] Davi Arnaut

It seems this is merely a timing issue, somehow on your machine the select thread got schedule first
and hit the server before the update, probably due to "cosmic entropy" :-)

5.1-runtime:

update t2 set b=1;
update low_priority t2 set b=2;
select * from t2 limit 3;
a	b
1	2
2	2
3	2

[4 Dec 2007 14:53] Davi Arnaut

Expanding the comment: the update properly tries to get a low priority write lock and succeeds, the select has not yet hit the server. If the sleep is commented, the select gets to run first.

[4 Dec 2007 19:37] Sveta Smirnova

In main tree bug is not repeatable as well.