MDEV-16232: Use fewer mini-transactions

SQL layer support part #2. The main idea is:
employ this optimization only when the query is using just one table.
(the scope is too limited but the goal is to get sysbench to work).

Make UPDATE, DELETE, and SELECT codepaths check that the query has just
one table before the optimization is employed.

Also: in DELETE code, the start_operations_batch/end_operations_batch
were in the "Direct DELETE" branch. I think it's a mistake as InnoDB
doesn't use Direct DELETE optimization.
Removed the calls from there and added them to the regular DELETE codepath.

7 files changed, 79 insertions, 19 deletions

diff --git a/sql/handler.cc b/sql/handler.cc
index ec701c52daa..51222146b35 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -8662,12 +8662,12 @@ Table_scope_and_contents_source_st::fix_period_fields(THD *thd,
   return false;
 }
 
-void handler::ha_start_operations_batch()
+void handler::ha_start_operations_batch() // currently useless??
 {
   table->file->start_operations_batch();
 }
 
-void handler::ha_end_operations_batch()
+void handler::ha_end_operations_batch() // currently useless??
 {
   table->file->end_operations_batch();
 }
diff --git a/sql/handler.h b/sql/handler.h
index afda200bef6..72f9b4dfde0 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -3560,8 +3560,8 @@ public:
 
   virtual void ha_start_operations_batch();
   virtual void ha_end_operations_batch();
-  virtual void start_operations_batch()=0;
-  virtual void end_operations_batch()=0;
+  virtual void start_operations_batch(){}
+  virtual void end_operations_batch(){}
   void adjust_next_insert_id_after_explicit_value(ulonglong nr);
   int update_auto_increment();
   virtual void print_error(int error, myf errflag);
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 9c4cbfc8494..96d4d226060 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -332,10 +332,12 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   bool delete_record= false;
   bool delete_while_scanning;
   bool portion_of_time_through_update;
+  bool ops_batch_started= false;
   DBUG_ENTER("mysql_delete");
 
   query_plan.index= MAX_KEY;
   query_plan.using_filesort= FALSE;
+  query_plan.using_batched_ops= can_use_operations_batch(table_list);
 
   create_explain_query(thd->lex, thd->mem_root);
   if (open_and_lock_tables(thd, table_list, TRUE, 0))
@@ -637,7 +639,6 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
       !table_list->has_period())
   {
     table->mark_columns_needed_for_delete();
-    table->file->start_operations_batch();
     if (!table->check_virtual_columns_marked_for_read())
     {
       DBUG_PRINT("info", ("Trying direct delete"));
@@ -659,7 +660,6 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
         THD_STAGE_INFO(thd, stage_updating);
         if (!(error= table->file->ha_direct_delete_rows(&deleted)))
           error= -1;
-        table->file->end_operations_batch();
         goto terminate_delete;
       }
     }
@@ -669,6 +669,11 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   if (query_plan.using_filesort)
   {
     {
+      if (query_plan.using_batched_ops)
+      {
+        table->file->start_operations_batch();
+        ops_batch_started= true;
+      }
       Filesort fsort(order, HA_POS_ERROR, true, select);
       DBUG_ASSERT(query_plan.index == MAX_KEY);
 
@@ -693,6 +698,11 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
       if (!returning)
         free_underlaid_joins(thd, select_lex);
       select= 0;
+      if (ops_batch_started)
+      {
+        table->file->end_operations_batch();
+        ops_batch_started= false;
+      }
     }
   }
 
@@ -807,6 +817,12 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   THD_STAGE_INFO(thd, stage_updating);
   fix_rownum_pointers(thd, thd->lex->current_select, &deleted);
 
+  if (query_plan.using_batched_ops)
+  {
+    table->file->start_operations_batch();
+    ops_batch_started= true;
+  }
+
   thd->get_stmt_da()->reset_current_row_for_warning(0);
   while (likely(!(error=info.read_record())) && likely(!thd->killed) &&
          likely(!thd->is_error()))
@@ -887,6 +903,13 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
     else
       break;
   }
+
+  if (ops_batch_started)
+  {
+    table->file->end_operations_batch();
+    ops_batch_started= false;
+  }
+
   thd->get_stmt_da()->reset_current_row_for_warning(1);
 
 terminate_delete:
@@ -1009,6 +1032,12 @@ send_nothing_and_leave:
   DBUG_RETURN((return_error || thd->is_error() || thd->killed) ? 1 : 0);
 
 got_error:
+  if (ops_batch_started)
+  {
+    table->file->end_operations_batch();
+    ops_batch_started= false;
+  }
+
   return_error= 1;
   goto send_nothing_and_leave;
 }
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 4f2e775a7fc..d5f4676204b 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3003,6 +3003,8 @@ public:
   key_map possible_keys;
   bool using_filesort;
   bool using_io_buffer;
+
+  bool using_batched_ops;
   
   /* Set this plan to be a plan to do nothing because of impossible WHERE */
   void set_impossible_where() { impossible_where= true; }
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 44cc1ce7d35..e4f9660f649 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -21183,14 +21183,22 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)
   if (pfs_batch_update)
     join_tab->table->file->start_psi_batch_mode();
 
-#if 0
+
   bool ops_batch_started= false;
-  if (join_tab->next_select == end_send)
+  /*
+    A crude check to employ the optimization only if this is a one-table
+    select.
+    Check the list of open tables. It must have one table open (2), but just
+    one table (3).
+  */
+  if (join_tab->next_select == end_send &&
+      join->thd->open_tables &&  //  (2)
+      !join->thd->open_tables->next) // (3)
   {
     join_tab->table->file->start_operations_batch();
     ops_batch_started= true;
   }
-#endif
+
 
   if (rc != NESTED_LOOP_NO_MORE_ROWS)
   {
@@ -21239,10 +21247,8 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)
     rc= evaluate_join_record(join, join_tab, error);
   }
 
-#if 0
   if (ops_batch_started)
     join_tab->table->file->end_operations_batch();
-#endif
 
   if (rc == NESTED_LOOP_NO_MORE_ROWS &&
       join_tab->last_inner && !join_tab->found)
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 34d689cf698..260f31c5822 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -2483,4 +2483,6 @@ void propagate_new_equalities(THD *thd, Item *cond,
                               bool *is_simplifiable_cond);
 
 bool dbug_user_var_equals_str(THD *thd, const char *name, const char *value);
+bool can_use_operations_batch(TABLE_LIST *query_tables);
+
 #endif /* SQL_SELECT_INCLUDED */
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 80e377c247c..89f875d430e 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -339,6 +339,11 @@ int cut_fields_for_portion_of_time(THD *thd, TABLE *table,
   return res;
 }
 
+
+bool can_use_operations_batch(TABLE_LIST *query_tables) 
+{
+  return (query_tables->next_global==NULL);
+}
 /*
   Process usual UPDATE
 
@@ -459,6 +464,7 @@ int mysql_update(THD *thd,
     DBUG_RETURN(1);
   }
   
+  query_plan.using_batched_ops= can_use_operations_batch(table_list);
   /* Calculate "table->covering_keys" based on the WHERE */
   table->covering_keys= table->s->keys_in_use;
   table->opt_range_keys.clear_all();
@@ -796,8 +802,11 @@ int mysql_update(THD *thd,
 
       note: We avoid sorting if we sort on the used index
     */
-    table->file->start_operations_batch();
-    ops_batch_started= true;
+    if (query_plan.using_batched_ops)
+    {
+      table->file->start_operations_batch();
+      ops_batch_started= true;
+    }
     if (query_plan.using_filesort)
     {
       /*
@@ -948,8 +957,11 @@ int mysql_update(THD *thd,
       table->file->ha_end_keyread();
       table->column_bitmaps_set(save_read_set, save_write_set);
     }
-    table->file->end_operations_batch();
-    ops_batch_started= false;
+    if (ops_batch_started)
+    {
+      table->file->end_operations_batch();
+      ops_batch_started= false;
+    }
   }
 
 update_begin:
@@ -1016,8 +1028,13 @@ update_begin:
   THD_STAGE_INFO(thd, stage_updating);
   fix_rownum_pointers(thd, thd->lex->current_select, &updated_or_same);
   thd->get_stmt_da()->reset_current_row_for_warning(1);
-  table->file->start_operations_batch();
-  ops_batch_started= true;
+  
+  if (query_plan.using_batched_ops)
+  {
+    table->file->start_operations_batch();
+    ops_batch_started= true;
+  }
+
   while (!(error=info.read_record()) && !thd->killed)
   {
     explain->tracker.on_record_read();
@@ -1231,8 +1248,12 @@ error:
       break;
     }
   }
-  table->file->end_operations_batch();
-  ops_batch_started= false;
+  
+  if (ops_batch_started)
+  {
+    table->file->end_operations_batch();
+    ops_batch_started= false;
+  }
 
   ANALYZE_STOP_TRACKING(thd, &explain->command_tracker);
   table->auto_increment_field_not_null= FALSE;