Description:
The option low_priority_updates means, if set to ON, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there is no pending SELECT or LOCK TABLE READ on the affected table. I start MySQL with low_priority_updates as ON and add a lock to table t1 with "LOCK TABLE t1 READ LOCAL;". When I update global low_priority_updates to OFF in another session, it still hang when inserting at table t1. Then I check the global and session property of low_priority_updates, and find the session value of low_priority_updates is still ON.
When people dynamically set a global option, usually they also want to update the option in the current session. So I suggest that setting global low_priority_updates also makes sense to the current session.
How to repeat:
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connection root;
CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE=MyISAM;
LOCK TABLE t1 READ LOCAL;
connect (root2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connection root2;
show global variables where variable_name like 'low_priority_updates';
show session variables where variable_name like 'low_priority_updates';
set global low_priority_updates = OFF;
show global variables where variable_name like 'low_priority_updates';
show session variables where variable_name like 'low_priority_updates';
# hang when inserting to t1
INSERT INTO t1 VALUES (1), (2), (3);
connection root;
SELECT * FROM t1;
UNLOCK TABLES;
drop table t1;
disconnect root;
disconnect root2;
Suggested fix:
Potential fixing is that when updating global low_priority_updates, also update the session value of low_priority_updates
sql/sys_vars.cc
static bool fix_low_prio_updates(sys_var *self, THD *thd, enum_var_type type)
{
if (type == OPT_SESSION)
{
thd->update_lock_default= (thd->variables.low_priority_updates ?
TL_WRITE_LOW_PRIORITY : TL_WRITE);
thd->insert_lock_default= (thd->variables.low_priority_updates ?
TL_WRITE_LOW_PRIORITY : TL_WRITE_CONCURRENT_INSERT);
}
else
thr_upgraded_concurrent_insert_lock=
(global_system_variables.low_priority_updates ?
TL_WRITE_LOW_PRIORITY : TL_WRITE);
+ //when updating global low_priority_updates, also updating the session value
+ thd->variables.low_priority_updates = global_system_variables.low_priority_updates;
+ thd->update_lock_default= (thd->variables.low_priority_updates ?
TL_WRITE_LOW_PRIORITY : TL_WRITE);
+ thd->insert_lock_default= (thd->variables.low_priority_updates ?
TL_WRITE_LOW_PRIORITY : TL_WRITE_CONCURRENT_INSERT);
return false;
}
Description: The option low_priority_updates means, if set to ON, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there is no pending SELECT or LOCK TABLE READ on the affected table. I start MySQL with low_priority_updates as ON and add a lock to table t1 with "LOCK TABLE t1 READ LOCAL;". When I update global low_priority_updates to OFF in another session, it still hang when inserting at table t1. Then I check the global and session property of low_priority_updates, and find the session value of low_priority_updates is still ON. When people dynamically set a global option, usually they also want to update the option in the current session. So I suggest that setting global low_priority_updates also makes sense to the current session. How to repeat: connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE=MyISAM; LOCK TABLE t1 READ LOCAL; connect (root2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root2; show global variables where variable_name like 'low_priority_updates'; show session variables where variable_name like 'low_priority_updates'; set global low_priority_updates = OFF; show global variables where variable_name like 'low_priority_updates'; show session variables where variable_name like 'low_priority_updates'; # hang when inserting to t1 INSERT INTO t1 VALUES (1), (2), (3); connection root; SELECT * FROM t1; UNLOCK TABLES; drop table t1; disconnect root; disconnect root2; Suggested fix: Potential fixing is that when updating global low_priority_updates, also update the session value of low_priority_updates sql/sys_vars.cc static bool fix_low_prio_updates(sys_var *self, THD *thd, enum_var_type type) { if (type == OPT_SESSION) { thd->update_lock_default= (thd->variables.low_priority_updates ? TL_WRITE_LOW_PRIORITY : TL_WRITE); thd->insert_lock_default= (thd->variables.low_priority_updates ? TL_WRITE_LOW_PRIORITY : TL_WRITE_CONCURRENT_INSERT); } else thr_upgraded_concurrent_insert_lock= (global_system_variables.low_priority_updates ? TL_WRITE_LOW_PRIORITY : TL_WRITE); + //when updating global low_priority_updates, also updating the session value + thd->variables.low_priority_updates = global_system_variables.low_priority_updates; + thd->update_lock_default= (thd->variables.low_priority_updates ? TL_WRITE_LOW_PRIORITY : TL_WRITE); + thd->insert_lock_default= (thd->variables.low_priority_updates ? TL_WRITE_LOW_PRIORITY : TL_WRITE_CONCURRENT_INSERT); return false; }