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, 2024, Oracle Corporation and/or its affiliates. All rights reserved.