bzr commit into mysql-5.1-bugteam branch (davi:2728) Bug#40536

[Davi Arnaut]

# At a local mysql-5.1-bugteam repository of davi

 2728 Davi Arnaut	2009-01-06
      Bug#40536: SELECT is blocked by INSERT DELAYED waiting on
                 upgrading lock, even with low_pri
      
      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 add a new global variable which specifies
      whether a delayed insert thread should take high or low priority
      locks. The name of the variable is low_priority_delayed_updates.
modified:
  include/thr_lock.h
  mysql-test/r/delayed.result
  mysql-test/t/delayed.test
  mysys/thr_lock.c
  sql/mysqld.cc
  sql/set_var.cc
  sql/sql_class.h
  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/mysqld.cc
    Off by default.
  sql/set_var.cc
    Add the global low_priority_delayed_updates variable.
  sql/sql_class.h
    Add the global low_priority_delayed_updates variable.
  sql/sql_insert.cc
    Take a low priority write lock if low_priority_delayed_updates is
    turned on.
=== modified file 'include/thr_lock.h'
--- a/include/thr_lock.h	2008-11-10 20:21:49 +0000
+++ b/include/thr_lock.h	2009-01-06 12:25:10 +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-01-06 12:25:10 +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 = @@low_priority_delayed_updates;
+set global low_priority_delayed_updates = 1;
+select @@low_priority_delayed_updates;
+@@low_priority_delayed_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_delayed_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-01-06 12:25:10 +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 = @@low_priority_delayed_updates;
+set global low_priority_delayed_updates = 1;
+select @@low_priority_delayed_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_delayed_updates = @old_delayed_updates;
+
 --echo End of 5.1 tests

=== modified file 'mysys/thr_lock.c'
--- a/mysys/thr_lock.c	2008-08-25 13:57:34 +0000
+++ b/mysys/thr_lock.c	2009-01-06 12:25:10 +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/mysqld.cc'
--- a/sql/mysqld.cc	2009-01-05 16:10:20 +0000
+++ b/sql/mysqld.cc	2009-01-06 12:25:10 +0000
@@ -7596,6 +7596,7 @@ static int mysql_init_variables(void)
   max_system_variables.max_join_size=   (ulonglong) HA_POS_ERROR;
   global_system_variables.old_passwords= 0;
   global_system_variables.old_alter_table= 0;
+  global_system_variables.low_priority_delayed_updates= 0;
   global_system_variables.binlog_format= BINLOG_FORMAT_UNSPEC;
   /*
     Default behavior for 4.1 and 5.0 is to treat NULL values as unequal

=== modified file 'sql/set_var.cc'
--- a/sql/set_var.cc	2009-01-05 16:10:20 +0000
+++ b/sql/set_var.cc	2009-01-06 12:25:10 +0000
@@ -338,6 +338,9 @@ static sys_var_microseconds	sys_var_long
 static sys_var_thd_bool	sys_low_priority_updates(&vars, "low_priority_updates",
 						 &SV::low_priority_updates,
 						 fix_low_priority_updates);
+static sys_var_bool_ptr
+  sys_low_priority_delayed_updates(&vars, "low_priority_delayed_updates",
+                                  
&global_system_variables.low_priority_delayed_updates);
 #ifndef TO_BE_DELETED	/* Alias for the low_priority_updates */
 static sys_var_thd_bool	sys_sql_low_priority_updates(&vars,
"sql_low_priority_updates",
 						     &SV::low_priority_updates,

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2009-01-05 16:10:20 +0000
+++ b/sql/sql_class.h	2009-01-06 12:25:10 +0000
@@ -396,6 +396,7 @@ struct system_variables
   DATE_TIME_FORMAT *datetime_format;
   DATE_TIME_FORMAT *time_format;
   my_bool sysdate_is_now;
+  my_bool low_priority_delayed_updates;
 };
 
 

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	2008-11-26 23:02:10 +0000
+++ b/sql/sql_insert.cc	2009-01-06 12:25:10 +0000
@@ -2531,6 +2531,7 @@ bool Delayed_insert::handle_inserts(void
   bool using_ignore= 0, using_opt_replace= 0,
        using_bin_log= mysql_bin_log.is_open();
   delayed_row *row;
+  thr_lock_type delayed_lock;
   DBUG_ENTER("handle_inserts");
 
   /* Allow client to insert new rows */
@@ -2538,9 +2539,11 @@ bool Delayed_insert::handle_inserts(void
 
   table->next_number_field=table->found_next_number_field;
   table->use_all_columns();
+  delayed_lock= global_system_variables.low_priority_delayed_updates ?
+                                        TL_WRITE_LOW_PRIORITY : TL_WRITE;
 
   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