WL#7464: InnoDB: provide a way to do non-locking reads
Affects: Server-8.0
—
Status: Complete
New Data Dictionary allows INFORMATION_SCHEMA queries to get their data
directly from DD tables (see WL#6599 for more information). The main point
is that I_S tables will be represented as views, thus a query from I_S
table will be a query from a view.
I_S queries can be executed under different isolation levels that is set by
user. Hence it is possible that I_S queries getting blocked by a parallel
DDL operation. This will be a huge compatibility issue.
The proposed behavior is to request InnoDB to do lockless read operation
from the underlying DD-tables.
In order to do that, there should be a way to request non-locking read on
per handler (in terms of the Handler API) instance. This WL is about
providing such a way.
NF1: No user visible changes.
MySQL server should convey InnoDB to do lock-less reads for some DD table
handles. Note that I_S queries directly read DD tables and do not use DD API's.
So, the server should have some way to identify all the DD table handles
that are part of I_S query.
Once the handler that belong to DD table in a I_S query is identified,
MySQL server can use following way to tell InnoDB to do lock-less reads.
1) Add new enum,
enum ha_extra_function {
...
HA_EXTRA_SKIP_SERIALIZABLE_DD_VIEW
};
2) Call handler::extra(HA_EXTRA_SKIP_SERIALIZABLE_DD_VIEW) for all the DD table
handlers.
3) Within ha_innobase::extra() mark prebuilt->skip_serializable_dd_view = true
The blocking of I_S query happens only in Serializable transaction isolation
level. With other transaction isolation levels, it is a consistent read.
How serializable transaction isolation level is achieved in InnoDB:
(comment in source)
/* To get serializable execution, we let InnoDB
conceptually add 'LOCK IN SHARE MODE' to all SELECTs
which otherwise would have been consistent reads...
.. */
i.e. We use prebuilt->select_lock_type = LOCK_S
So, we add an exception to the above with
extra(HA_EXTRA_SKIP_SERIALIZABLE_DD_VIEW)and the select lock type
remains as LOCK_NONE.
After the select is done, we reset the flag using ha_innobase::reset()
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.