MySQL Bugs: #64892: Session-level low_priority_updates does not work for INSERT

Bug #64892	Session-level low_priority_updates does not work for INSERT
Submitted:	6 Apr 2012 17:53	Modified:	13 Aug 2012 16:22
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The manual (http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_low-priority-upda...) says that LOW_PRIORITY_UPDATES can be set on a session level, to change the priority in one thread. However, it does not work for INSERT (while works for UPDATE and DELETE).

The test case provided in 'how to repeat' section does the following (for each of INSERT, UPDATE, DELETE):

- set concurrent_insert globally to 0, just so it does not affect further flow;
- create a MyISAM table with 2 rows;
- in a new connection and start a long enough SELECT;
- in another connection, set low_priority_updates=ON for the session, start INSERT (UPDATE, DELETE);
- in a 3rd connection, start one more SELECT;
- check that that the results of the 2nd SELECT do not reflect the changes performed by INSERT (UPDATE, DELETE).

Results show that UPDATE and DELETE are executed with the low priority, as expected, while INSERT is not.

The test case as it's given checks setting low_priority_updates=ON, but it can be converted into the opposite by changing the values in SET commands (2 places). Results are the same -- it works correctly with UPDATE and DELETE, but not INSERT.

How to repeat:
# This test case checks whether setting
# session-level low_priority_updates to ON works
# when the global value is OFF.
# To check the other way round, modify 
# SET GLOBAL LOW_PRIOORITY_UPDATES 
# and SET LOW_PRIORITY_UPDATES below. 
# The comments about expectations are already inline.

--enable_connect_log

# This can be replaced
# by the command-line option
SET GLOBAL LOW_PRIORITY_UPDATES = 0;

# Just so that concurrent inserts don't interfere 
# anyhow with the test case
SET GLOBAL concurrent_insert = 0;

CREATE TABLE t1 (i INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2);

let $show_statement = SHOW PROCESSLIST;
let $field = State;

--connect (con0,localhost,root,,)
--connect (con1,localhost,root,,)
# This should make the commands 
# to wait for *both* SELECTs to be finished
SET LOW_PRIORITY_UPDATES = 1;
--connect (con2,localhost,root,,)

#
# Checking INSERT
#

--connection con0
send SELECT i+SLEEP(1) FROM t1;

--connection con1

let $condition = = 'User sleep';
let $wait_timeout = 2;
--source include/wait_show_condition.inc

--send
INSERT INTO t1 VALUES (3);

--connection con2

# In 5.1 it's 'Locked', 
# In 5.5 and 5.6 'Waiting for table level lock'
let $condition = LIKE '%lock%';
let $wait_timeout = 2;
--sleep 0.5
--source include/wait_show_condition.inc

--echo # Should return 2 rows with low_priority_updates=1
--echo # and 3 rows with low_priority_updates=0
SELECT i+SLEEP(1) FROM t1;

--connection con0
--disable_result_log
--reap
--enable_result_log

--connection con1
--reap
DELETE FROM t1 WHERE i=3;

#
# Checking UPDATE
#

--connection con0
send SELECT i+SLEEP(1) FROM t1;

--connection con1

let $condition = = 'User sleep';
let $wait_timeout = 2;
--source include/wait_show_condition.inc

--send
UPDATE t1 SET i=10 WHERE i=1;

--connection con2

let $condition = LIKE '%lock%';
let $wait_timeout = 2;
--source include/wait_show_condition.inc

--echo # Should return 1, 2 with low_priority_updates=1
--echo # and 10, 2 with low_priority_updates=0
SELECT i+SLEEP(1) FROM t1;

--connection con0
--disable_result_log
--reap
--enable_result_log

--connection con1
--reap
UPDATE t1 SET i=1 WHERE i=10;

#
# Checking DELETE
#

--connection con0
send SELECT i+SLEEP(1) FROM t1;

--connection con1

let $condition = = 'User sleep';
let $wait_timeout = 2;
--source include/wait_show_condition.inc

--send
DELETE FROM t1 LIMIT 1;

--connection con2

let $condition = LIKE '%lock%';
let $wait_timeout = 2;
--source include/wait_show_condition.inc

--echo # Should return 2 rows with low_priority_updates=1
--echo # and 1 row with low_priority_updates=0
SELECT i+SLEEP(1) FROM t1;

--connection con0
--disable_result_log
--reap
--enable_result_log

DROP TABLE t1;
# Restore global values here!

# End of test case

In fact, although in the test case the table is MyISAM, it's reproducible with the MEMORY engine too (I didn't try MERGE), so I'm changing the category, for now to 'Locking' -- please adjust if needed.

Thank you for the problem report. Verified with 5.1.61 on Mac OS X:

...
+SET GLOBAL LOW_PRIORITY_UPDATES = 0;
+SET GLOBAL concurrent_insert = 0;
+CREATE TABLE t1 (i INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+connect  con0,localhost,root,,;
+connect  con1,localhost,root,,;
+SET LOW_PRIORITY_UPDATES = 1;
+connect  con2,localhost,root,,;
+connection con0;
+SELECT i+SLEEP(1) FROM t1;
+connection con1;
+INSERT INTO t1 VALUES (3);
+connection con2;
+# Should return 2 rows with low_priority_updates=1
+# and 3 rows with low_priority_updates=0
+SELECT i+SLEEP(1) FROM t1;
+i+SLEEP(1)
+1
+2
+3
+connection con0;
+connection con1;
+DELETE FROM t1 WHERE i=3;
+connection con0;
+SELECT i+SLEEP(1) FROM t1;
+connection con1;
+UPDATE t1 SET i=10 WHERE i=1;
+connection con2;
+# Should return 1, 2 with low_priority_updates=1
+# and 10, 2 with low_priority_updates=0
+SELECT i+SLEEP(1) FROM t1;
+i+SLEEP(1)
+1
+2
+connection con0;
+connection con1;
+UPDATE t1 SET i=1 WHERE i=10;
+connection con0;
+SELECT i+SLEEP(1) FROM t1;
+connection con1;
+DELETE FROM t1 LIMIT 1;
+connection con2;
+# Should return 2 rows with low_priority_updates=1
+# and 1 row with low_priority_updates=0
+SELECT i+SLEEP(1) FROM t1;
+i+SLEEP(1)
+1
+2
+connection con0;
+DROP TABLE t1;
...

Noted in 5.7.0 changelog.

Enabling the session value of low_priority_updates had no effect for
INSERT statements.