bk commit into 5.0 tree (davi:1.2508) BUG#28587

[Davi Arnaut]

Below is the list of changes that have just been committed into a local
5.0 repository of davi. When davi does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-08-30 14:53:17-03:00, davi@stripped +3 -0
  Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with
low_priority_updates
  
  The problem is that a SELECT on one thread is blocked by INSERT ... ON
  DUPLICATE KEY UPDATE on another thread even when low_priority_updates is
  activated.
  
  The solution is to possibly downgrade the lock type to the setting of
  low_priority_updates if the INSERT cannot be concurrent.

  mysql-test/r/insert_update.result@stripped, 2007-08-30 14:53:14-03:00, davi@stripped +19
-0
    Add test case result for Bug#28587.

  mysql-test/t/insert_update.test@stripped, 2007-08-30 14:53:14-03:00, davi@stripped +29
-0
    Add test case for Bug#28587.

  sql/sql_insert.cc@stripped, 2007-08-30 14:53:14-03:00, davi@stripped +1 -1
    Possibly downgrade lock type to the the setting of low_priority_updates if
    if the INSERT cannot be concurrent.

diff -Nrup a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
--- a/mysql-test/r/insert_update.result	2007-06-11 18:41:11 -03:00
+++ b/mysql-test/r/insert_update.result	2007-08-30 14:53:14 -03:00
@@ -407,3 +407,22 @@ select if( @stamp1 = @stamp2, "correct",
 if( @stamp1 = @stamp2, "correct", "wrong")
 correct
 drop table t1;
+connection: default
+set low_priority_updates=1;
+drop table if exists t1;
+create table t1 (a int, b int, unique key t1$a (a));
+lock table t1 read;
+connection: update
+set low_priority_updates=1;
+show variables like 'low_priority_updates';
+Variable_name	Value
+low_priority_updates	ON
+insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;;
+connection: select
+select * from t1;
+a	b
+connection: default
+select * from t1;
+a	b
+drop table t1;
+set low_priority_updates=default;
diff -Nrup a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
--- a/mysql-test/t/insert_update.test	2007-06-11 18:41:11 -03:00
+++ b/mysql-test/t/insert_update.test	2007-08-30 14:53:14 -03:00
@@ -306,3 +306,32 @@ insert into t1(f1) values(1) on duplicat
 select @stamp2:=f2 from t1;
 select if( @stamp1 = @stamp2, "correct", "wrong");
 drop table t1;
+
+#
+# Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with
low_priority_updates
+#
+--echo connection: default
+set low_priority_updates=1;
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int, b int, unique key t1$a (a));
+lock table t1 read;
+connect (update,localhost,root,,);
+connection update;
+--echo connection: update
+set low_priority_updates=1;
+show variables like 'low_priority_updates';
+--send insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;
+sleep 1;
+connect (select,localhost,root,,);
+--echo connection: select
+select * from t1;
+connection default;
+--echo connection: default
+select * from t1;
+connection default;
+disconnect update;
+disconnect select;
+drop table t1;
+set low_priority_updates=default;
diff -Nrup a/sql/sql_insert.cc b/sql/sql_insert.cc
--- a/sql/sql_insert.cc	2007-08-02 18:26:02 -03:00
+++ b/sql/sql_insert.cc	2007-08-30 14:53:14 -03:00
@@ -417,7 +417,7 @@ void upgrade_lock_type(THD *thd, thr_loc
   if (duplic == DUP_UPDATE ||
       duplic == DUP_REPLACE && *lock_type == TL_WRITE_CONCURRENT_INSERT)
   {
-    *lock_type= TL_WRITE;
+    *lock_type= TL_WRITE_DEFAULT;
     return;
   }