SQL Layer support for: Use fewer InnoDB mini-transactions

in MDEV-16232, InnoDB is implementing an optimization to cut down on latch acquisitions/releases and other kinds of overhead when the query is about to perform multiple operations on the [index] records on the same page.

In the Storage engine API, this translates into the following calls:

  h->start_operations_batch(); // (1)

  BATCH_OF_OPERATIONS;          // (2)

  h->end_operations_batch(); // (3)

InnoDB will hold internal page latches (and something else?) between calls (1) and (2),
so the SQL layer must guarantee the following properties of the code that may execute in BATCH_OF_OPERATIONS:

* Access only one InnoDB table (further "The Table").

** Also, do not access other kinds of tables, except maybe temporary (work) tables.

* Access The Table using only one index

** But non-index-only scan on the secondary index is allowed

* * It is allowed (even intended) the we read row(s) and then 

   modify or delete them during one BATCH_OF_OPERATIONS.

Applicability

The mini-transaction calls can be put around all loops where we call 'read next' repeatedly for just one table.

The places in the select/filesort code where this happens:

• When using only one table InnoDB table (it's ok mixing InnoDB tables and other tables).
  **There can be any number of const tables as these are only called once before accessing any other tables.
• The last table in the join tabs.
• * This is calling read-next repeatedly until there are no more matching rows.
• The last table just before a table with a join cache
  • This is calling read-next repeatedly until there are no more matching rows. All read rows are put into the join cache (until join cache is full, in which case we have to end the mini transaction).
• When scanning a table in filesort
• In multi_range_read around each range
• In index_merge when finding rowid's for one table

Batching cannot be used if one of the following holds:

• We use index_merge .
• We use Multi-Range-Read

• The WHERE condition accesses other tables (that is, uses subqueries)
• For UPDATE/DELETEs: the modified table has triggers (which could potentially access other tables)
• For UPDATE/DELETEs: there are foreign keys that depend on this table.

Where to put the batching calls

Step #1

Single-Table UPDATE and DELETE that satisfy the Applicability criteria should execute the whole query in one batch.
The same goes for single-table SELECT: It can start a batch when select starts and end it when it ends.

This means we will add just two

if (table does batching)

{ ... }

onto the main execution path.

The prototype patch

The prototype patch is in this branch: 10.9-MDEV-16232, this commit:

commit 92665b862b479f6582e3a15247bfc812e59e7e34

Author: Sergei Petrunia <sergey@mariadb.com>

Date:   Wed Jun 8 15:43:06 2022 +0300

    MDEV-16232: Use fewer mini-transactions

    SQL layer support part #2. The main idea is:

It implements the check for UPDATE and DELETE. It doesn't have the applicability check (it has some hack). Or mysql-test coverage. Because of that it can cause crashes:

https://jira.mariadb.org/browse/MDEV-16232?focusedCommentId=242090&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-242090

The prototype patch has some hack to handle single-table selects.

Step #2

A multi-table select may use batches for the last table in the join. Access to the last table must satisfy the Applicability criteria.

Can we avoid calling "if (table does batching)" for every row examined for any table?
It's tempting to use special functions in READ_RECORD. This will allow to avoid any overhead. (This is for starting the batch, what about ending it? Especially if the SELECT in question is a subquery with LIMIT?)

Note: the prototype patch doesn't handle this at all.

Visibility

Need some way to see if the optimization is applied.

This is probably a too fine detail to show in EXPLAIN output.
Let's have a counter instead. It will count the number batches (that is, number of start_operations_batch() calls).

We should also write about the decision whether batching is to be used into the optimizer trace.

User Control

It's a good idea to be able to switch the optimization on and off.
The switch should be at the SQL layer.

Error handling

• Do we need to call end_operations_batch() in case of an error condition? (A: Yes)