WL#7907: Runtime: Use non-locking reads for DD tables under I_S view.
Affects: Server-8.0
—
Status: Complete
Abbreviations: -------------- DD = Data Dictionary IS = INFORMATION_SCHEMA database. 'system view' = Some of IS tables are implemented as a view, when compare to the old implementation where they were all implemented as a temporary tables. These new views under IS are called as 'system view'. The overall direction of this WL is to keep the behavior of IS queries same as with MySQL server without new DD. In MySQL server without new DD, each IS table is internally a temporary table. As temporary tables need not be locked, the transaction isolation level do not affect their access. Overall, read's from IS tables never wait. With new DD some of IS tables like IS.TABLES, IS.VIEWS IS.COLUMNS and etc are implemented as MySQL system view on DD tables. Both the MDL locking and SE isolation level gets applied on DD tables. This might cause IS queries to wait for some other operations to finish. E.g., If the user is using transaction isolation level that is higher than 'repeatable read', then the read on DD table would block. This WL aims to do non-locking reads on DD table when they are used under a IS system view, which would match the behavior in MySQL server without DD.
The queries that use IS system view's would not be blocked while reading DD tables, irrespective of isolation level that is currently active. i.e., if IS queries use SERIALIZABLE isolation level, we should silently ignore it and continue doing non-locking reads on DD tables used under IS system views. NF1: No user visible changes.
I-1) If a IS query is used under 'LOCK IN SHARE MODE' or 'FOR UPDATE', then the query would terminate with a error. I-2) Utilize interface provided by WL#7464 to request InnoDB to do non-locking reads.
New DD has ability to identify system views uniquely. WL#6379 specifies mysql.tables.type, which has type 'SYSTEM VIEW' to uniquely identify a system view. The function, bool dd_create_view(THD *thd, TABLE_LIST *view, const char *schema_name, const char *view_name); takes care of checking if given view is a system view and then invoke dd::Schema::create_system_view() to create a system view, which sets mysql.tables.type to 'SYSTEM VIEW'. MySQL server will be able to identify if a TABLE_LIST belong to a system view. A new variable 'TABLE_LIST->is_system_view' is added for this purpose. This flag is set while opening a view, which is based on mysql.tables.type. MySQL server would identify specific TABLE_LIST that is referenced by a system view. Mostly these TABLE_LIST refer to DD tables only. MySQL server will then invoke following API (provided by WL#7464) on these identified TABLE_LIST. This API conveys innodb to to non-locking reads irrespective of isolation level active currently. Handler::extra(HA_EXTRA_SKIP_SERIALIZABLE_DD_VIEW); The overall procedure looks like following. When MySQL server open tables, at the end we do following for each table, a) Check if TABLE_LIST belongs to a DD table that is referred by a system view. b) Check if statement is using 'LOCK IN SHARED MODE' or 'FOR UPDATE'. If yes, emit a error. c) Invoke Handler::extra(HA_EXTRA_SKIP_SERIALIZABLE_DD_VIEW);
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.