WL#5879: PERFORMANCE SCHEMA, MDL lock instrumentation

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

Expose:
- which session owns which MDL lock
- which session is waiting for which MDL lock
in performance schema tables.

This feature is critical to understand metadata lock dependencies between 
sessions.

In production, DBA not only need to know *if* a session is waiting on a metadata
lock, but also *which other* session is currently holding the metadata lock for
the same resource.

User Documentation
==================

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html
http://dev.mysql.com/doc/refman/5.7/en/performance-schema-lock-tables.html
TABLE performance_schema.metadata_locks
---------------------------------------

New table.

This table exposes metadata locks:
- requested
- granted
for the entire server.

Columns:

  `OBJECT_TYPE` varchar(64) NOT NULL,
  `OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
  `OBJECT_NAME` varchar(64) DEFAULT NULL,
  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
  `LOCK_TYPE` varchar(32) NOT NULL,
  `LOCK_DURATION` varchar(32) NOT NULL,
  `LOCK_STATUS` varchar(32) NOT NULL,
  `SOURCE` varchar(64) NOT NULL,
  `OWNER_THREAD_ID` bigint(20) unsigned NOT NULL,
  `OWNER_EVENT_ID` bigint(20) unsigned NOT NULL

Operations:

Only SELECT is supported, this is a read only table.

Semantic for each column:

OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME:
The Metadata object locked.

The OBJECT_TYPE column is the type of lock used in the MDL subsystem.
Current values are:
- GLOBAL
- SCHEMA
- TABLE
- FUNCTION
- PROCEDURE
- TRIGGER
- EVENT
- COMMIT

OBJECT_INSTANCE_BEGIN:
The object address in memory.

LOCK_TYPE:
The lock type from the MDL subsystem.
Currently, lock types are:
- INTENTION_EXCLUSIVE
- SHARED
- SHARED_HIGH_PRIO
- SHARED_READ
- SHARED_WRITE
- SHARED_UPGRADABLE
- SHARED_NO_WRITE
- SHARED_NO_READ_WRITE
- EXCLUSIVE

LOCK_DURATION:
The lock duration from the MDL subsystem.
Currently, lock duration are:
- STATEMENT
- TRANSACTION
- EXPLICIT

LOCK_STATUS:
The lock status from the MDL subsystem.
Currently, lock status are:
- PENDING
- GRANTED
- VICTIM
- TIMEOUT
- KILLED

SOURCE:
The source file name and line number from where the metadata lock request
originated.

OWNER_THREAD_ID:
The thread requesting a metadata lock

OWNER_EVENT_ID:
The event requesting a metadata lock.

TABLE performance_schema.table_handles
--------------------------------------

New table.

This table exposes the different table locks currently in effect,
for each table handle opened in the system.

Columns:

  `OBJECT_TYPE` varchar(64) NOT NULL,
  `OBJECT_SCHEMA` varchar(64) NOT NULL,
  `OBJECT_NAME` varchar(64) NOT NULL,
  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
  `OWNER_THREAD_ID` bigint(20) unsigned DEFAULT NULL,
  `OWNER_EVENT_ID` bigint(20) unsigned DEFAULT NULL
  `INTERNAL_LOCK` varchar(64) DEFAULT NULL,
  `EXTERNAL_LOCK` varchar(64) DEFAULT NULL

Operations:

Only SELECT is supported, this is a read only table.

Semantic for each column:

OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME:
The table opened by a table handle.

OBJECT_INSTANCE_BEGIN:
The table handle address in memory.

OWNER_THREAD_ID:
The thread owning a table handle.

OWNER_EVENT_ID:
The event which caused the table handle to be opened.

INTERNAL_LOCK:
The table lock used at the SQL level.
Currently, internal locks are:
- READ
- READ WITH SHARED LOCKS
- READ HIGH PRIORITY
- READ NO INSERT
- WRITE ALLOW WRITE
- WRITE CONCURRENT INSERT
- WRITE LOW PRIORITY
- WRITE

EXTERNAL_LOCK:
The table lock used at the storage engine level.
Currently, external locks are:
- READ EXTERNAL
- WRITE EXTERNAL

Note that some storage engines (innodb) might downgrade locks.
What is reported here is what was recorded by the table lock instrumentation.

TABLE performance_schema.setup_instruments
------------------------------------------

New data.
A new instrument is defined, named "wait/lock/metadata/sql/mdl".

TABLE performance_schema.events_waits_current
---------------------------------------------

New wait events are recorded for metadata locks.

TABLE performance_schema.events_waits_history
---------------------------------------------

Same as events_waits_current.

TABLE performance_schema.events_waits_history_long
--------------------------------------------------

Same as events_waits_current.

System variable
---------------

New variable,
performance_schema_max_metadata_locks

This variable is global, read only.
It indicates how many metadata locks can be tracked by the performance schema.

Status variable
---------------

New status variable,
Performance_schema_metadata_lock_lost

It indicates how many metadata locks could not be recorded, if any.

REQUIREMENTS
============

Install
-------

Func-Req (1): A fresh MySQL installation of CURRENT-VERSION must create the
following tables:
- 1.1, TABLE performance_schema.metadata_locks
- 1.2, TABLE performance_schema.table_handles

Upgrade
-------

Func-Req (2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create
the following tables:
- 2.1, TABLE performance_schema.metadata_locks
- 2.2, TABLE performance_schema.table_handles

Server options and variables
----------------------------

Func-Req (3): The following server variables are defined:
- 3.1, global variable performance_schema_max_metadata_locks
- 3.2, global status Performance_schema_metadata_lock_lost

Behavior
--------

Func-Req (4): The content of table metadata_locks is as follows:
- 4.1, When a metadata lock is requested and obtained immediately,
a row with STATUS = GRANTED is inserted.
- 4.2, When a metadata lock is requested and not obtained immediately,
a row with status = PENDING is inserted.
- 4.3, When a metadata lock previously requested is granted,
the corresponding row is updated with status = GRANTED
- 4.4, When a metadata lock is released, the corresponding row is deleted.