Bug#4118: multi-table UPDATE takes WRITE lock on read table

  Ensures that WRITE lock is not obtained on all tables referenced.


mysql-test/r/lock_multi.result:
  Bug#4118
    New test for multi-update locking
mysql-test/r/multi_update.result:
  Bug#4118
    Fix test
mysql-test/t/lock_multi.test:
  Bug#4118
    New test for multi-update locking
mysql-test/t/multi_update.test:
  Bug#4118
    Fix test
sql/sql_parse.cc:
  Bug#4118
    Split multi-update to its own case statement in sql_parse.cc
sql/sql_update.cc:
  Bug#4118
    Overview of locking checking:    
      1. Open and acquire READ lock
      2. Check to see which tables need WRITE lock
      3. Unlock tables and relock
sql/sql_yacc.yy:
  Bug#4118
    Split multi-update to its own case statement in sql_parse.cc

3 files changed, 117 insertions, 28 deletions

diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index e95c52f1e48..894fa355262 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1927,21 +1927,26 @@ mysql_execute_command(void)
       send_error(&thd->net,ER_WRONG_VALUE_COUNT);
       DBUG_VOID_RETURN;
     }
-    if (select_lex->table_list.elements == 1)
-    {
-      if (check_one_table_access(thd, UPDATE_ACL, tables, 0))
-	goto error; /* purecov: inspected */
+    if (check_one_table_access(thd, UPDATE_ACL, tables, 0))
+      goto error; /* purecov: inspected */
 
 
-      res= mysql_update(thd,tables,
-			select_lex->item_list,
-			lex->value_list,
-			select_lex->where,
-			(ORDER *) select_lex->order_list.first,
-			select_lex->select_limit,
-			lex->duplicates);
+    res= mysql_update(thd,tables,
+		      select_lex->item_list,
+		      lex->value_list,
+		      select_lex->where,
+		      (ORDER *) select_lex->order_list.first,
+		      select_lex->select_limit,
+		      lex->duplicates);
+    break;
+  case SQLCOM_MULTI_UPDATE:
+    if (check_db_used(thd,tables))
+      goto error;
+    if (select_lex->item_list.elements != lex->value_list.elements)
+    {
+      send_error(&thd->net,ER_WRONG_VALUE_COUNT);
+      DBUG_VOID_RETURN;
     }
-    else
     {
       const char *msg= 0;
       TABLE_LIST *table;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index d51c81ee127..a17742df03b 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -401,25 +401,101 @@ int mysql_multi_update(THD *thd,
   int res;
   multi_update *result;
   TABLE_LIST *tl;
+  const bool locked= !(thd->locked_tables);
   DBUG_ENTER("mysql_multi_update");
 
-  if ((res=open_and_lock_tables(thd,table_list)))
-    DBUG_RETURN(res);
+  for (;;)
+  {
+    table_map update_map= 0;
+    int tnr= 0;
+    
+    if ((res= open_tables(thd, table_list)))
+      DBUG_RETURN(res);
 
-  thd->select_limit=HA_POS_ERROR;
+    /*
+      Only need to call lock_tables if (thd->locked_tables == NULL)
+    */
+    if (locked && ((res= lock_tables(thd, table_list))))
+      DBUG_RETURN(res);
 
-  /*
-    Ensure that we have update privilege for all tables and columns in the
-    SET part
-  */
-  for (tl= table_list ; tl ; tl=tl->next)
-  {
-    TABLE *table= tl->table;
-    table->grant.want_privilege= (UPDATE_ACL & ~table->grant.privilege);
-  }
+    thd->select_limit=HA_POS_ERROR;
 
-  if (setup_fields(thd, table_list, *fields, 1, 0, 0))
-    DBUG_RETURN(-1);
+    /*
+      Ensure that we have update privilege for all tables and columns in the
+      SET part
+      While we are here, initialize the table->map field.
+    */
+    for (tl= table_list ; tl ; tl=tl->next)
+    {
+      TABLE *table= tl->table;
+      table->grant.want_privilege= (UPDATE_ACL & ~table->grant.privilege);
+      table->map= (table_map) 1 << (tnr++);
+    }
+
+    if (!setup_fields(thd, table_list, *fields, 1, 0, 0))
+    {
+      List_iterator_fast<Item> field_it(*fields);
+      Item_field *item;
+
+      while ((item= (Item_field *) field_it++)) 
+	update_map|= item->used_tables();
+
+      DBUG_PRINT("info",("update_map=0x%08x", update_map));
+    }
+    else
+      DBUG_RETURN(-1);
+
+    /*
+      Unlock the tables in preparation for relocking
+    */
+    if (locked) 
+    {      
+      pthread_mutex_lock(&LOCK_open);
+      mysql_unlock_tables(thd, thd->lock); 
+      thd->lock= 0;
+      pthread_mutex_unlock(&LOCK_open);
+    }
+
+    /*
+      Set the table locking strategy according to the update map
+    */
+    for (tl= table_list ; tl ; tl=tl->next)
+    {
+      TABLE *table= tl->table;
+      if (update_map & table->map) 
+      {
+	DBUG_PRINT("info",("setting table `%s` for update", tl->alias));
+	tl->lock_type= thd->lex.lock_option;
+	tl->updating= 1;
+      }
+      else
+      {
+	DBUG_PRINT("info",("setting table `%s` for read-only", tl->alias));
+	tl->lock_type= TL_READ;
+	tl->updating= 0;
+      }
+      if (locked)
+	tl->table->reginfo.lock_type= tl->lock_type;
+    }
+
+    /*
+      Relock the tables
+    */
+    if (!(res=lock_tables(thd,table_list)))
+      break;
+      
+    if (!locked)
+      DBUG_RETURN(res);
+
+    List_iterator_fast<Item> field_it(*fields);
+    Item_field *item;
+
+    while ((item= (Item_field *) field_it++)) 
+    /*  item->cleanup();		XXX Use this instead in MySQL 4.1+ */
+      item->field= item->result_field= 0;
+
+    close_thread_tables(thd);
+  }
 
   /*
     Count tables and setup timestamp handling
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 6b073db2e36..7b72c73a915 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2751,10 +2751,18 @@ update:
           lex->select->order_list.next= (byte**) &lex->select->order_list.first;
         }
         opt_low_priority opt_ignore join_table_list
-	SET update_list where_clause opt_order_clause delete_limit_clause
+	SET update_list 
 	{
-	  set_lock_for_tables($3);
+	  if (Lex->select->table_list.elements > 1)
+	  {
+	    LEX *lex=Lex;
+            lex->sql_command= SQLCOM_MULTI_UPDATE;
+	    lex->lock_option= $3;
+	  }
+	  else
+	    set_lock_for_tables($3);
 	}
+	where_clause opt_order_clause delete_limit_clause {}
 	;
 
 update_list: