WL#9275: DEPRECATE INFORMATION_SCHEMA.INNODB_LOCKS IN 5.7
Affects: Server-5.7 — Status: Complete
Deprecate the following tables in 5.7 - INFORMATION_SCHEMA.INNODB_LOCKS - INFORMATION_SCHEMA.INNODB_LOCK_WAITS These tables will be removed in 5.8/8.0 by: PERFORMANCE_SCHEMA, ROW LOCKS and replaced by equivalent performance schema tables. Rationale for the change ======================== Currently, the INFORMATION_SCHEMA.INNODB_LOCKS and INFORMATION_SCHEMA.INNODB_LOCK_WAITS are: - specific to innodb - report only some, not all, rows - exposed in the information schema, where the data is runtime PERFORMANCE_SCHEMA, ROW LOCKS - defines new performance schema tables to expose the same kind of data, with a structure that can accommodate any engine. This is important to users like enterprise monitoring. In the long term, this is also important for performance schema adoption (think instrumenting third party locks in a way that enterprise monitor can consume) - reports more rows, which is critical for enterprise monitoring / the DBA - exposes runtime data in the performance_schema Impossibility to maintain the old table as is ============================================= Keeping the tables: - INFORMATION_SCHEMA.INNODB_LOCKS - INFORMATION_SCHEMA.INNODB_LOCK_WAITS to report more data is impossible, because: - only innodb data can fit in these tables - reporting more (all locks, not just locks waited on) rows will create massive performance issues. The technical underlying problem here is that these tables are INFORMATION_SCHEMA PLUGINS, which have a very crude interface: the entire table **MUST** be materialized first, before executing any query on it. This prevents any feature development to extend INFORMATION_SCHEMA.INNODB_LOCKS. In 5.8, performance schema tables can expose some rows at a time, and indexes are currently implemented in 5.8 to improve access patterns, to address these information schema limitations.
R1: SELECT on table INFORMATION_SCHEMA.INNODB_LOCKS raises a deprecation warning. R2: SELECT on table INFORMATION_SCHEMA.INNODB_LOCK_WAITS raises a deprecation warning.
SELECT from the following tables: - INFORMATION_SCHEMA.INNODB_LOCKS - INFORMATION_SCHEMA.INNODB_LOCK_WAITS will raise a deprecation warning.
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.