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

1 files changed, 90 insertions, 14 deletions

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