WL#9275: DEPRECATE INFORMATION_SCHEMA.INNODB_LOCKS IN 5.7

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

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.