From: Date: February 3 2009 6:16pm Subject: bzr commit into mysql-5.1-bugteam branch (davi:2768) Bug#40536 List-Archive: http://lists.mysql.com/commits/65035 X-Bug: 40536 Message-Id: <20090203171636.5F913EC195@skynet.ctb.virtua.com.br> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit # At a local mysql-5.1-bugteam repository of davi 2768 Davi Arnaut 2009-02-03 Bug#40536: SELECT is blocked by INSERT DELAYED waiting on upgrading lock, even with low_priority_updates The problem is that there is no mechanism to control whether a delayed insert takes a high or low priority lock on a table. The solution is to modify the delayed insert thread ("handler") to take into account the global value of low_priority_updates when taking table locks. The value of low_priority_updates is retrieved when the insert delayed thread is created and will remain the same for the duration of the thread. modified: include/thr_lock.h mysql-test/r/delayed.result mysql-test/t/delayed.test mysys/thr_lock.c sql/sql_insert.cc per-file messages: include/thr_lock.h Update prototype. mysql-test/r/delayed.result Add test case result for Bug#40536 mysql-test/t/delayed.test Add test case for Bug#40536 mysys/thr_lock.c Add function parameter which specifies the write lock type. sql/sql_insert.cc Take a low priority write lock if global value of low_priority_updates was ON when the thread was created. === modified file 'include/thr_lock.h' --- a/include/thr_lock.h 2008-11-10 20:21:49 +0000 +++ b/include/thr_lock.h 2009-02-03 17:16:24 +0000 @@ -159,7 +159,8 @@ void thr_multi_unlock(THR_LOCK_DATA **da void thr_abort_locks(THR_LOCK *lock, my_bool upgrade_lock); my_bool thr_abort_locks_for_thread(THR_LOCK *lock, my_thread_id thread); void thr_print_locks(void); /* For debugging */ -my_bool thr_upgrade_write_delay_lock(THR_LOCK_DATA *data); +my_bool thr_upgrade_write_delay_lock(THR_LOCK_DATA *data, + enum thr_lock_type new_lock_type); void thr_downgrade_write_lock(THR_LOCK_DATA *data, enum thr_lock_type new_lock_type); my_bool thr_reschedule_write_lock(THR_LOCK_DATA *data); === modified file 'mysql-test/r/delayed.result' --- a/mysql-test/r/delayed.result 2007-12-13 12:10:57 +0000 +++ b/mysql-test/r/delayed.result 2009-02-03 17:16:24 +0000 @@ -284,4 +284,30 @@ ERROR 22007: Incorrect date value: '0000 INSERT DELAYED INTO t2 VALUES (0,'2007-00-00'); ERROR 22007: Incorrect date value: '2007-00-00' for column 'f1' at row 1 DROP TABLE t1,t2; +set @old_delayed_updates = @@global.low_priority_updates; +set global low_priority_updates = 1; +select @@global.low_priority_updates; +@@global.low_priority_updates +1 +drop table if exists t1; +create table t1 (a int, b int); +insert into t1 values (1,1); +lock table t1 read; +connection: update +insert delayed into t1 values (2,2);; +connection: select +select * from t1; +a b +1 1 +connection: default +select * from t1; +a b +1 1 +unlock tables; +select * from t1; +a b +1 1 +2 2 +drop table t1; +set global low_priority_updates = @old_delayed_updates; End of 5.1 tests === modified file 'mysql-test/t/delayed.test' --- a/mysql-test/t/delayed.test 2007-11-29 10:11:36 +0000 +++ b/mysql-test/t/delayed.test 2009-02-03 17:16:24 +0000 @@ -285,4 +285,47 @@ INSERT DELAYED INTO t2 VALUES (0,'0000-0 INSERT DELAYED INTO t2 VALUES (0,'2007-00-00'); DROP TABLE t1,t2; +# +# Bug#40536: SELECT is blocked by INSERT DELAYED waiting on upgrading lock, +# even with low_priority_updates +# + +set @old_delayed_updates = @@global.low_priority_updates; +set global low_priority_updates = 1; +select @@global.low_priority_updates; + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int, b int); +insert into t1 values (1,1); +lock table t1 read; +connect (update,localhost,root,,); +connection update; +--echo connection: update +--send insert delayed into t1 values (2,2); +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where command = "Delayed insert" and state = "upgrading lock"; +--source include/wait_condition.inc +connect (select,localhost,root,,); +--echo connection: select +select * from t1; +connection default; +--echo connection: default +select * from t1; +connection default; +disconnect update; +disconnect select; +unlock tables; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where command = "Delayed insert" and state = "Waiting for INSERT"; +--source include/wait_condition.inc +select * from t1; +drop table t1; + +set global low_priority_updates = @old_delayed_updates; + --echo End of 5.1 tests === modified file 'mysys/thr_lock.c' --- a/mysys/thr_lock.c 2009-01-15 18:11:25 +0000 +++ b/mysys/thr_lock.c 2009-02-03 17:16:24 +0000 @@ -1359,7 +1359,8 @@ void thr_downgrade_write_lock(THR_LOCK_D /* Upgrade a WRITE_DELAY lock to a WRITE_LOCK */ -my_bool thr_upgrade_write_delay_lock(THR_LOCK_DATA *data) +my_bool thr_upgrade_write_delay_lock(THR_LOCK_DATA *data, + enum thr_lock_type new_lock_type) { THR_LOCK *lock=data->lock; DBUG_ENTER("thr_upgrade_write_delay_lock"); @@ -1372,7 +1373,7 @@ my_bool thr_upgrade_write_delay_lock(THR } check_locks(lock,"before upgrading lock",0); /* TODO: Upgrade to TL_WRITE_CONCURRENT_INSERT in some cases */ - data->type=TL_WRITE; /* Upgrade lock */ + data->type= new_lock_type; /* Upgrade lock */ /* Check if someone has given us the lock */ if (!data->cond) @@ -1411,6 +1412,7 @@ my_bool thr_upgrade_write_delay_lock(THR my_bool thr_reschedule_write_lock(THR_LOCK_DATA *data) { THR_LOCK *lock=data->lock; + enum thr_lock_type write_lock_type; DBUG_ENTER("thr_reschedule_write_lock"); pthread_mutex_lock(&lock->mutex); @@ -1420,6 +1422,7 @@ my_bool thr_reschedule_write_lock(THR_LO DBUG_RETURN(0); } + write_lock_type= data->type; data->type=TL_WRITE_DELAYED; if (lock->update_status) (*lock->update_status)(data->status_param); @@ -1438,7 +1441,7 @@ my_bool thr_reschedule_write_lock(THR_LO free_all_read_locks(lock,0); pthread_mutex_unlock(&lock->mutex); - DBUG_RETURN(thr_upgrade_write_delay_lock(data)); + DBUG_RETURN(thr_upgrade_write_delay_lock(data, write_lock_type)); } === modified file 'sql/sql_insert.cc' --- a/sql/sql_insert.cc 2009-01-08 19:03:56 +0000 +++ b/sql/sql_insert.cc 2009-02-03 17:16:24 +0000 @@ -1690,6 +1690,7 @@ public: class Delayed_insert :public ilink { uint locks_in_memory; + thr_lock_type delayed_lock; public: THD thd; TABLE *table; @@ -1731,6 +1732,8 @@ public: pthread_cond_init(&cond_client,NULL); VOID(pthread_mutex_lock(&LOCK_thread_count)); delayed_insert_threads++; + delayed_lock= global_system_variables.low_priority_updates ? + TL_WRITE_LOW_PRIORITY : TL_WRITE; VOID(pthread_mutex_unlock(&LOCK_thread_count)); } ~Delayed_insert() @@ -2540,7 +2543,7 @@ bool Delayed_insert::handle_inserts(void table->use_all_columns(); thd_proc_info(&thd, "upgrading lock"); - if (thr_upgrade_write_delay_lock(*thd.lock->locks)) + if (thr_upgrade_write_delay_lock(*thd.lock->locks, delayed_lock)) { /* This can happen if thread is killed either by a shutdown