Bug #29936 Stored Procedure DML ignores low_priority_updates setting
Submitted: 20 Jul 2007 14:24 Modified: 31 Aug 2007 1:38
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0.48-BK OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: regression

[20 Jul 2007 14:24] Konstantin Osipov 
Description:
This is a follow up for the patch for Bug#26162 "Trigger DML ignores low_priority_updates
setting"

The patch for the bug is incomplete.

See 'How to repeat' for a test case.

How to repeat:
--disable_warnings
drop table if exists t1;
drop procedure if exists p1;
--enable_warnings

create table t1 (id varchar(15));
create procedure p1() update t1 set id="updated value" where id="old value";

# load the procedure into sp cache and execute once
call p1();

insert into t1 (id) values ("old value");

connect (rl_holder,    localhost, root,,);
connect (rl_acquirer, localhost, root,,);
connect (rl_contender, localhost, root,,);

connection rl_holder;
select get_lock('b26162',120);

connection rl_acquirer;
--send select 'rl_acquirer', id from t1 where get_lock('b26162',120);
# we must wait till this select opens and locks the tables.
sleep 1;

connection default;
set session low_priority_updates=on;

--send call p1();
#--send update t1 set id="updated value" where id="old value";
sleep 1;

connection rl_contender;
--send select 'rl_contender', id from t1;

connection rl_holder;
select release_lock('b26162');
connection rl_acquirer;
--reap
connection rl_contender;
--reap
connection default;
--reap

disconnect rl_holder;
disconnect rl_acquirer;
drop procedure p1;
drop table t1;
set session low_priority_updates=default;

This test case produces the following output:
-------------------------------------------------------
*** r/foo.result	Fri Jul 20 15:26:39 2007
--- r/foo.reject	Fri Jul 20 17:21:47 2007
***************
*** 0 ****
--- 1,23 ----
+ drop table if exists t1;
+ drop procedure if exists p1;
+ create table t1 (id varchar(15));
+ create procedure p1() update t1 set id="updated value" where id="old value";
+ call p1();
+ insert into t1 (id) values ("old value");
+ select get_lock('b26162',120);
+ get_lock('b26162',120)
+ 1
+ select 'rl_acquirer', id from t1 where get_lock('b26162',120);;
+ set session low_priority_updates=on;
+ call p1();;
+ select 'rl_contender', id from t1;;
+ select release_lock('b26162');
+ release_lock('b26162')
+ 1
+ rl_acquirer	id
+ rl_acquirer	old value
+ rl_contender	id
+ rl_contender	updated value
+ drop procedure p1;
+ drop table t1;
+ set session low_priority_updates=default;
-------------------------------------------------------

As you can see from the output, rl_contender sees the updated value - that
means rl_contender ran after call p1(), despite low_priority_updates setting.

Here is the output of this test case if the call is replaced with a plain
insert:
-------------------------------------------------------
*** r/foo.result	Fri Jul 20 15:26:39 2007
--- r/foo.reject	Fri Jul 20 17:23:03 2007
***************
*** 0 ****
--- 1,23 ----
+ drop table if exists t1;
+ drop procedure if exists p1;
+ create table t1 (id varchar(15));
+ create procedure p1() update t1 set id="updated value" where id="old value";
+ call p1();
+ insert into t1 (id) values ("old value");
+ select get_lock('b26162',120);
+ get_lock('b26162',120)
+ 1
+ select 'rl_acquirer', id from t1 where get_lock('b26162',120);;
+ set session low_priority_updates=on;
+ update t1 set id="updated value" where id="old value";;
+ select 'rl_contender', id from t1;;
+ select release_lock('b26162');
+ release_lock('b26162')
+ 1
+ rl_acquirer	id
+ rl_acquirer	old value
+ rl_contender	id
+ rl_contender	old value
+ drop procedure p1;
+ drop table t1;
+ set session low_priority_updates=default;
-------------------------------------------------------

And here is the output of the test case when low_priority_updates setting
is commented out:
-------------------------------------------------------
*** r/foo.result	Fri Jul 20 15:26:39 2007
--- r/foo.reject	Fri Jul 20 17:23:51 2007
***************
*** 0 ****
--- 1,22 ----
+ drop table if exists t1;
+ drop procedure if exists p1;
+ create table t1 (id varchar(15));
+ create procedure p1() update t1 set id="updated value" where id="old value";
+ call p1();
+ insert into t1 (id) values ("old value");
+ select get_lock('b26162',120);
+ get_lock('b26162',120)
+ 1
+ select 'rl_acquirer', id from t1 where get_lock('b26162',120);;
+ update t1 set id="updated value" where id="old value";;
+ select 'rl_contender', id from t1;;
+ select release_lock('b26162');
+ release_lock('b26162')
+ 1
+ rl_acquirer	id
+ rl_acquirer	old value
+ rl_contender	id
+ rl_contender	updated value
+ drop procedure p1;
+ drop table t1;
+ set session low_priority_updates=default;
-------------------------------------------------------

As you can see, the stored procedure ignores the session setting
of low_priority_updates.

Suggested fix:
A minimal fix looks like below:
===== sql_base.cc 1.384 vs edited =====
--- 1.384/sql/sql_base.cc	2007-07-06 16:18:44 +04:00
+++ edited/sql_base.cc	2007-07-20 16:56:56 +04:00
@@ -2676,11 +2676,6 @@ int open_tables(THD *thd, TABLE_LIST **s
   {
     safe_to_ignore_table= FALSE;                // 'FALSE', as per coding style
 
-    if (tables->lock_type == TL_WRITE_DEFAULT)
-    {
-      tables->lock_type= thd->update_lock_default;
-      DBUG_ASSERT (tables->lock_type >= TL_WRITE_ALLOW_WRITE);
-    }
     /*
       Ignore placeholders for derived tables. After derived tables
       processing, link to created temporary table will be put here.
@@ -2825,7 +2820,8 @@ int open_tables(THD *thd, TABLE_LIST **s
     }
 
     if (tables->lock_type != TL_UNLOCK && ! thd->locked_tables)
-      tables->table->reginfo.lock_type=tables->lock_type;
+      tables->table->reginfo.lock_type= tables->lock_type == TL_WRITE_DEFAULT ?
+        thd->update_lock_default : tables->lock_type;
     tables->table->grant= tables->grant;
 
 process_view_routines:

But I also suggest to remove TL_WRITE_DEFAULT lock type and fix the parser
to set TABLE_LIST::implicit_lock_type variable when LOW_PRIORITY/HIGH_PRIORITY
was not provided. That would provide a better separation of the parser and the locking
subsystem.
[31 Jul 2007 3:52] Valeriy Kravchuk 
Thank you for a bug report. Verified just as described with latest 5.0.48-BK on Linux.
[16 Aug 2007 1:16] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32600

ChangeSet@1.2513, 2007-08-15 22:16:25-03:00, davi@moksha.local +1 -0
  Bug#29936 (Stored Procedure DML ignores low_priority_updates setting)
  
  This is a follow up for the patch for Bug#26162 "Trigger DML ignores low_priority_updates setting", where the stored procedure ignores the session setting
  of low_priority_updates.
  
  For every table open operation with default write (TL_WRITE_DEFAULT) lock_type, downgrade the lock type to the session setting of low_priority_updates.
[16 Aug 2007 16:13] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32649

ChangeSet@1.2514, 2007-08-16 12:51:21-03:00, davi@moksha.local +2 -0
  Bug#29936 (Stored Procedure DML ignores low_priority_updates setting)
  
  This is a follow up for the patch for Bug#26162 "Trigger DML ignores low_priority_updates setting", where the stored procedure ignores the session setting of low_priority_updates.
  
  For every table open operation with default write (TL_WRITE_DEFAULT) lock_type, downgrade the lock type to the session setting of low_priority_updates.
[16 Aug 2007 17:52] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32661

ChangeSet@1.2514, 2007-08-16 14:51:37-03:00, davi@moksha.local +2 -0
  Bug#29936 (Stored Procedure DML ignores low_priority_updates setting)
  
  This is a follow up for the patch for Bug#26162 "Trigger DML ignores low_priority_updates setting", where the stored procedure ignores the session setting of low_priority_updates.
  
  For every table open operation with default write (TL_WRITE_DEFAULT) lock_type, downgrade the lock type to the session setting of low_priority_updates.
[21 Aug 2007 23:21] Bugs System 
Pushed into 5.1.22-beta
[21 Aug 2007 23:22] Bugs System 
Pushed into 5.0.48
[27 Aug 2007 13:06] Paul DuBois 
Pushed to 5.1.23, not 5.1.22.
[31 Aug 2007 1:38] Paul DuBois 
Noted in 5.0.48, 5.1.23 changelogs.

Statements within stored procedures ignored the value of the
low_priority_updates system variable.
[5 Sep 2007 18:05] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/33748

ChangeSet@1.2602, 2007-09-05 15:03:02-03:00, davi@moksha.local +4 -0
  The test case for Bug#29936 doesn't work with the embedded version,
  the first query is not running while we are doing wait queries on
  a second connection.
[7 Sep 2007 8:09] Bugs System 
Pushed into 5.1.23-beta
[20 Feb 2008 15:03] Konstantin Osipov 
Incomplete fix for Bug#26162
[20 Feb 2008 15:08] Konstantin Osipov 
(Caused by an incomplete fix for Bug#26162)