From: magnus.blaudd
Date: June 10 2011 10:26am
Subject: bzr commit into mysql-trunk branch (magnus.blaudd:3187) WL#5906
List-Archive: http://lists.mysql.com/commits/139055
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============7659034292476263080=="

--===============7659034292476263080==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#At file:///data0/magnus/mysql/trunk-wl5906-rbwr/ based on revid:jon.hauglid@stripped

 3187 magnus.blaudd@stripped	2011-06-10
      WL#5906 read before write removal (RBWR)
       - Make it possible for storage engines to optimize away
         read of record before UPDATE/DELETE in order to save
         roundtrips and allow for batching.

    modified:
      sql/handler.h
      sql/sql_delete.cc
      sql/sql_update.cc
=== modified file 'sql/handler.h'
--- a/sql/handler.h	2011-06-01 09:11:28 +0000
+++ b/sql/handler.h	2011-06-10 10:24:29 +0000
@@ -1796,6 +1796,28 @@ public:
   virtual int extra_opt(enum ha_extra_function operation, ulong cache_size)
   { return extra(operation); }
 
+  /*
+    Informs the handler if this handler support read removal
+    (could use table_flags, but patch is smaller this way)
+   */
+  virtual bool read_before_write_removal_supported(void) const
+  { return false; }
+
+  /*
+    Informs handler that it is possible to optimise away the real read
+    operation from the handler for the current table and instead
+    use a generated read to optimise simple UPDATE and DELETEs.
+  */
+  virtual bool read_before_write_removal_possible(void)
+  { return false; }
+
+  /*
+    Return the number of rows the handler has written while using
+    read before write removal
+   */
+  virtual ha_rows read_before_write_removal_rows_written(void) const
+  { DBUG_ASSERT(0); return (ha_rows) 0; }
+
   /**
     In an UPDATE or DELETE, if the row under the cursor was locked by another
     transaction, and the engine used an optimistic read of the last

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2011-05-26 15:20:09 +0000
+++ b/sql/sql_delete.cc	2011-06-10 10:24:29 +0000
@@ -59,6 +59,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   bool          const_cond_result;
   ha_rows	deleted= 0;
   bool          reverse= FALSE;
+  bool          read_removal= false;
   bool          skip_record;
   ORDER *order= (ORDER *) ((order_list && order_list->elements) ?
                            order_list->first : NULL);
@@ -293,6 +294,31 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   else
     will_batch= !table->file->start_bulk_delete();
 
+  /*
+    Read removal is possible if the selected quick read
+    method is using full unique index
+  */
+  if (select && select->quick &&
+      will_batch &&
+      !using_limit &&
+      table->file->read_before_write_removal_supported())
+  {
+    const uint idx = select->quick->index;
+    DBUG_PRINT("rbwr", ("checking index: %d", idx));
+    const KEY *key= table->key_info + idx;
+    if ((key->flags & HA_NOSAME) == HA_NOSAME)
+    {
+      DBUG_PRINT("rbwr", ("index is unique"));
+      bitmap_clear_all(&table->tmp_set);
+      table->mark_columns_used_by_index_no_reset(idx, &table->tmp_set);
+      if (bitmap_cmp(&table->tmp_set, table->read_set))
+      {
+        DBUG_PRINT("rbwr", ("using whole index, rbwr possible"));
+        read_removal=
+          table->file->read_before_write_removal_possible();
+      }
+    }
+  }
 
   table->mark_columns_needed_for_delete();
 
@@ -355,6 +381,13 @@ bool mysql_delete(THD *thd, TABLE_LIST *
       table->file->print_error(loc_error,MYF(0));
     error=1;
   }
+  if (read_removal)
+  {
+    /* Only handler knows how many records really was written */
+    DBUG_PRINT("rbwr", ("old deleted: %ld", (long)deleted));
+    deleted= table->file->read_before_write_removal_rows_written();
+    DBUG_PRINT("rbwr", ("really deleted: %ld", (long)deleted));
+  }
   THD_STAGE_INFO(thd, stage_end);
   end_read_record(&info);
   if (options & OPTION_QUICK)

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2011-06-09 08:58:41 +0000
+++ b/sql/sql_update.cc	2011-06-10 10:24:29 +0000
@@ -158,6 +158,37 @@ static bool check_fields(THD *thd, List<
 }
 
 
+/*
+  Check if all expressions in list are constant expressions
+
+  SYNOPSIS
+    check_constant_expressions()
+    values                       List of expressions
+
+  RETURN
+    TRUE                         Only constant expressions
+    FALSE                        At least one non-constant expression
+*/
+
+static bool check_constant_expressions(List<Item> &values)
+{
+  Item *value;
+  List_iterator_fast<Item> v(values);
+  DBUG_ENTER("check_constant_expressions");
+
+  while ((value= v++))
+  {
+    if (!value->const_item())
+    {
+      DBUG_PRINT("exit", ("expression is not constant"));
+      DBUG_RETURN(FALSE);
+    }
+  }
+  DBUG_PRINT("exit", ("expression is constant"));
+  DBUG_RETURN(TRUE);
+}
+
+
 /**
   Re-read record if more columns are needed for error message.
 
@@ -423,6 +454,36 @@ int mysql_update(THD *thd,
     DBUG_RETURN(0);
   }
 
+  /*
+    Read removal is possible if the selected quick read
+    method is using full unique index
+
+    NOTE! table->read_set currently holds the columns which are
+    used for the WHERE clause(this info is most likely already
+    available in select->quick, but where?)
+  */
+  bool read_removal= false;
+  if (select && select->quick &&
+      !ignore &&
+      !using_limit &&
+      table->file->read_before_write_removal_supported())
+  {
+    const uint idx= select->quick->index;
+    DBUG_PRINT("rbwr", ("checking index: %d", idx));
+    const KEY *key= table->key_info + idx;
+    if ((key->flags & HA_NOSAME) == HA_NOSAME)
+    {
+      DBUG_PRINT("rbwr", ("index is unique"));
+      bitmap_clear_all(&table->tmp_set);
+      table->mark_columns_used_by_index_no_reset(idx, &table->tmp_set);
+      if (bitmap_cmp(&table->tmp_set, table->read_set))
+      {
+        DBUG_PRINT("rbwr", ("using full index, rbwr possible"));
+        read_removal= true;
+      }
+    }
+  }
+
   /* If running in safe sql mode, don't allow updates without keys */
   if (table->quick_keys.is_clear_all())
   {
@@ -595,6 +656,10 @@ int mysql_update(THD *thd,
     }
     if (table->key_read)
       table->restore_column_maps_after_mark_index();
+
+    /* Rows are already read -> not possible to remove */
+    DBUG_PRINT("rbwr", ("rows are already read, turning off rbwr"));
+    read_removal= false;
   }
 
   if (ignore)
@@ -634,6 +699,14 @@ int mysql_update(THD *thd,
   else
     will_batch= !table->file->start_bulk_update();
 
+  if (read_removal &&
+      will_batch &&
+      check_constant_expressions(values))
+  {
+    assert(select && select->quick);
+    read_removal= table->file->read_before_write_removal_possible();
+  }
+
   /*
     Assure that we can use position()
     if we need to create an error message.
@@ -840,6 +913,20 @@ int mysql_update(THD *thd,
     table->file->end_bulk_update();
   table->file->try_semi_consistent_read(0);
 
+  if (read_removal)
+  {
+    /* Only handler knows how many records really was written */
+    DBUG_PRINT("rbwr", ("adjusting updated: %ld, found: %ld",
+                        (long)updated, (long)found));
+
+    updated= table->file->read_before_write_removal_rows_written();
+    if (!records_are_comparable(table))
+      found= updated;
+
+    DBUG_PRINT("rbwr", ("really updated: %ld, found: %ld",
+                        (long)updated, (long)found));
+  }
+
   if (!transactional_table && updated > 0)
     thd->transaction.stmt.mark_modified_non_trans_table();
 


--===============7659034292476263080==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/magnus.blaudd@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: magnus.blaudd@stripped\
#   nav9ppjwmf59u6xw
# target_branch: file:///data0/magnus/mysql/trunk-wl5906-rbwr/
# testament_sha1: 6f0676f7719c4d2d3dc9f6df9144a4775d2706da
# timestamp: 2011-06-10 12:26:12 +0200
# base_revision_id: jon.hauglid@stripped\
#   u5030fex1hg5kpvz
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWUgUVjIABat/gFUQDgB7f///
/+/eoL////pgDA67Dm+vecJeUK83Gdlw9zKAAVUlKqnbD6YSimk1PKHqNqeKeo8kHhTNI0ZAAAAA
0BoBKTSZMEJk0yptNQ0ek0AANAyAAAAaBKQyDVJ+kaE9UGgGgAADIAAAAeiBIiJpNCT9RpM0m0T0
k2E0AjIzRDEDExPU00ekGVTQaaAeoAGgAAAAAAAAABIoIAIAEYU9NFP0E1R6aj2qDIaDygGgAdQQ
sKiIIqfyE1GJWHwNT0U3HsYVwViLhw4j5x37dPt9TCcw3EY8RLXpu6vXeSMbPevjBfPw5Q3CvIr1
ChtoaFFGhoYKbeqNwPvMtXn7+xgZE/ZXLVswSTyzONg5nE2EHYhCEeW6D5+c2h0EMWwpXFSfdabV
mPONGg+ftgJJSIQbr7LPQNKgwQxpB5AKHz6d97hOSFwHN0kITLVg07tQlElc6Pgf53THjqjyL+1v
pttobFhu/NG1XGdUmpnC+1SvD3ovug7MVtSaNVGUpkkpSKMsy3JmMeNra5juE7I/R3CG98NV/PJx
9uN9STTsVpHZtF2iyrwGUlNqyqa2xhC9OYOTsshdwStK+un4nFHlpFVLInELghSBCyGFQTsKnW8D
HE9WseNtmIlNI/QScepGda3ldHWyKWSEihpMsTRr3ddqqzwMYFcichmHnwMQtqnwWXcWK7MYyR+X
OUXCoKX5QLY3RqYzjLM+0aSZtvO1mMl0pMW0JXtUVFQNcEgKku5yKLfqvZEN2onupXQj1iV2KEFh
zhtGCgNBII9BDSbGDSTAZ7uuB2QGNNCkGHxZ8hoaQ0cB9CFn15jdMS/zXWhnne0POMPxGibyluqN
ls/XkNd37MxEKM/K4qJFdt5OJ6z9R+KTLYTgm/WyCrCUCtwZL3dRfnQe2pCw1XmI0liX4wIkjEjV
3B4G8A1KWhLYWkArE0l7G75QNvgSokhXfXZiqQ6q7Ps22RSMUkdQwB/iQZkbuagt9GSpeK+XtovS
G0tim1MNae1HfFKqfQhbYmAZKAEV8MyIbNQxZl1MvU1zkJK955keskibst1RZQlyr/TeUyZSv+W0
xYVstujG3pWpITF9jEZRiuAPgu+Xd1oBbbU60pOMiNj7tu5BhcwvPIhNJMXG3S8kMTf8q7blpxiY
FE2CmZlmNNT90wSBeNU3KhHnUS7VtfaWRY5WtbbqMoBnpeaRVkk6VrI1lwiGlxs89Rr1EsoQKNiI
VWFhRzxOmWwMDkL0yIkueVQLGE8RUBvNcK4mtM1LxyYA8ol2aUbCcJ2MUSeKi7tdBDUNddQdpG2D
rKFTZMTQEM0MnyImCPKnCy5yY72gvtvB6Q3giVrq15PNGS3Ce2GWYhmQnnEmatZ2nmtEslcTSMxx
IIVsiJS47oaYy0S120GV26s7B5lGFtZiRKk0mYg7ZZonylqUS6/RhA071x5UpWYbImTO0Ec1ZZIS
qUNrQdgdXuMLXUwSlrNScQtOdliLAvXDcNsUas1412sEIERBVkbrcqkWMsFxyzUEeYomVZC3QSN4
jrUI7jOKdudqDueGfU6EN4wCsIdzoFhXWdrX+M3DKQhjEQ0MaMINJ3g0ecatuRkO6RF9+zDY3oVE
oLGn7V4+ZsEFbWbEvifoHV8jpDovW2mNuiA8/lx/0a9XmTGHtInQ8OD0vA2AqywuYyxn7CybBs/n
zIBcOGiCWmL+iA2s/h+XaQQsn30A/AglvkJ+6mcognDPC8ZsbiCURcCiPpIMTTG99yCXtnBamytV
HnCgECxGcLXFOQmDSXrPf3TjSP4D2mthRd8LU7kqF4AggX7HiLLBBRB7DN7fyLlctgWApFcXoGuv
eizXp6D2FD0Q4zwlfERP967rs1QQgXtQCYgHpZxMTz3jsWCcaIWr9fsw+iZW0B3/Pd8CuFANfNFK
XNaL5rlsESQVi42GqkLoIJn0KvfThOT3FL5LBilfE0pFIPIIt1YmC8sTgbzmfMhl7jVyRM1l0KQ5
uksmqEGmk+o/QfgPokX8U2JtoRWjkyvwOxJXH3BtaRX3kahARsitlRcstlDzpMSjCMSvJO61Xnmo
QrT2jMyS1wyW5uUsYaISYr5QGo0MlmhAaYkzh09aQoGQOLehIocxmRoNcE1Kd4pKYUR/xK2VdDao
T4KJz8t+l9SyxFRSiExlSTmkYpFiCQwU955oI6OCRpX8DSbNWFTBbrAjERedcnWo1uNG2AF6pyO6
HHD2IJyTSj8NQhNaB8+ah2hl5VXliESyDQKYmCK+w7y3aSWfGjwLS0adCzq/oaXi4RxDAU0EUHFq
DR8hpIzMChYHoSSkEkjDnUItGlE4lfPOaszt2LDMe4F6G71yRenXAypHQDSXaAwATFPZzkLgmilG
LVbTKs/YZd8uy8jObXG0ytIdCRaBAit7FcLUmKfiZWMO8MUtArLgXOxkFy4iekykSnYtPvIiPQ4k
8iMnPENxQsmQNy+5eYuitVxEuvW8JhMEz0ieRE976m4gqOZSAa0zqBOG4M2pTEuJ6rSeJxDzTDwA
u9Wl04kOlIbCAjR/6wXpr71BRoRXFm0iYHFCgi3rIRHdHaBu5rNyS2h1jgtY1b6hyIoKA/1Vfwx4
psFwNR5F4VD0XBZuFA4DDvNB6boEqgpNlkcjJ7TX9XK2ipgHDJaHerbhiH5ERZ/tUSZ8SpIzGoIC
ntHeq8+UMTt3CICMrsGHgdtq67D7ztMTxzisNJmLlZrvOjBzg2oKAQP6rwC5HNziExQVUIGr9K3k
jP+Bl7HlFFt60JhLIaAYmgpQEFBqBlvSKDclg1EDCB3BRoHKsd6Z7okEsN4WEANpbCyrVRnFCvKV
bAGe44wa1mzArDecljp8civsw8R/h4CIB3PgqbVzWTQAsC/KK2wGGKwGYnFdOBdBKhRNGqJamorG
ZWEZQyFRNRAJiIqXUR6xoJE1N2FyPUuAw4Wc54oqlfEuDUwYwC/FMwgNg0oNFlSRFSOw1hpRWuJ5
l9galejjM2qhpBeFT+8GivL7hRAuAONF+t2WIYn1QDStRrNVAciKpiNqhlooRXMBX5D2lUMWMKDQ
2g0hAWD5QTQ96CIe/E81vMDMIopIIqS4L2ESRNWJFvTJerlQUYrFBnVg5TYGHWw1i2uQmRjKSCmU
G2dBcUbAW+arXJI75ooY0ycIIJhxaLhsbH5L4U+tImq4VTKNHkxg4GmgHs5wbRJtpFFv+CAyz9Dp
YgKzOtyZxR8lR0rwuyxgwrKiu9pvtVlbirlXnK7BY1wXSKMSHFLURXFMJqy/2ZkjJmsWXLGum0Zr
Q12qYHPXVnrGBFcHBVExFMEoKwTAkshgsWU/Wdda+DbbvTY2YadgiCRpDGLqaKqrUqBusvpgIy9F
wIFqSak5yQe70PSdVfLrcOgqXlWBkFTkLGEpQSLPu/ZC8wxjX0E7ki8Li2DGVxQStJXKggMab+ND
NiUKxdrBYOk3nVV53bpPcsZaLw7xXIpnQlQuDnOuKKkoK8J9x571uUBGhW3pHoGm1lYw2EwmBaUZ
6QxqXeSFQFHBfYRXl8DUjIjeXJE6MYVm4l3MfkW+h1UxKcGRIQSQnekNZSQqBNNwaYaGMDVsBQzD
FChWEoCBWXDk0eJ00iKUlI2IGmqxHEfIyHNjBoGmj1aR4ppdhvETyVrm1E2Eop0kxmAzYTNZyluI
xn7rffEpvrFDDObYta0nhlNgQOV/4u5IpwoSCQKKxkA=


--===============7659034292476263080==--