WL#9248: InnoDB: Allow multiple readers and one writer on InnoDB internal tmp table

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Necessary changes inside innodb to support CTE.

Basically, this is to support multiple readers and single writer access the same
intrinsic table and still maintain the context for them separately.

The key is that these readers and writer are with the same thread, so the work
is serialized:

An example:

reader a reads 1
reader b reads 1
writer c writes 2
reader a reads 2
writer c writes 3
read b reads 2

The current intrinsic table does not have mechanism to support multiple cursor
positions. So this worklog is to make sure we put this support back (the normal
table can support multiple cursor position).

To do so, we will also need mechanism to tell readers to "reposition" if a page
splits happens. Since the tree splits could move the records, including those
already have cursors positioned on it.


This worklog would also remove the consistent read requirement on intrinsic
table. All the access are done with read dirty scheme.

This worklog also puts an optimization that use statistics row count instead of
a full table scan. Since there is only one thread working on intrinsic table,
thus statistics count should be accurate.
CTE stores its result in an internal tmp table: this must
work both with innodb intrinsic tables in-mem (for small-result CTEs) and
on-disk (for big-result).

Four problematic points, from (a), to (d), are identified below.
For recursive CTE they are all blockers.
For nonrecursive CTE only (a) applies and is a blocker.

Example: the recursive CTE algorithm has, in a single thread, TABLE objects TW
(for write)
and TR1 and TR2 (for read), all three pointing to the same physical intrinsic
table. A duplicate-eliminating secondary index may exist on the table. No
updates, no deletes are to be done
on the table. For the recursive algorithm, reads are scans: an initial
rnd_init(), then rnd_next() calls. No index_init.

Example query:
with recursive qn as (
select R0
union all
select  from qn # select1
union all
select  from qn # select2
)