1Z0-908 practice question 3 of 25

Which two tools are available to monitor the global status of InnoDB locking? (Choose two.)

Full set: 140 questions
Answer options
A: SHOW ENGINE INNODB STATUS; Correct answer
B: INFORMATION_SCHEMA.INNODB_METRICS Correct answer
C: SHOW TABLE STATUS;
D: INFORMATION_SCHEMA.STATISTICS
E: INFORMATION_SCHEMA.INNODB_TABLESTATS
F: SHOW STATUS;
Correct Answer:
A. SHOW ENGINE INNODB STATUS;; B. INFORMATION_SCHEMA.INNODB_METRICS
Explanation

To monitor the global status of InnoDB locking, administrators can use two primary tools. SHOW ENGINE INNODB STATUS provides a detailed, human-readable text report of the current state of the InnoDB storage engine. This includes the TRANSACTIONS section, which lists active transactions, the locks they hold, and any locks they are waiting for, making it invaluable for real-time diagnostics. The INFORMATIONSCHEMA.INNODBMETRICS table provides a comprehensive set of queryable performance counters. It includes numerous lock-related metrics (e.g., lockdeadlocks, lockwaits, lockrowlocktime) that allow for monitoring aggregate locking behavior and trends over time.

Why Incorrect

C. SHOW TABLE STATUS; This command provides general table metadata, such as storage engine and row count, not information about active locks.
D. INFORMATIONSCHEMA.STATISTICS This table contains metadata about table indexes, which is used by the query optimizer, not for monitoring runtime locking.
E. INFORMATIONSCHEMA.INNODBTABLESTATS This table stores persistent, table-level statistics for the query optimizer and does not reflect the current locking status.
F. SHOW STATUS; While this command shows some relevant global status variables (e.g., Innodbrowlockwaits), INFORMATIONSCHEMA.INNODBMETRICS is far more comprehensive for InnoDB-specific monitoring.

References

1. MySQL 8.0 Reference Manual, Section 15.16.2, "InnoDB Standard Monitor and Lock Monitor Output": This section details the output of SHOW ENGINE INNODB STATUS, explaining that its TRANSACTIONS section provides detailed information about transactions and their locks.

2. MySQL 8.0 Reference Manual, Section 15.17.2, "The INFORMATIONSCHEMA INNODBMETRICS Table": This document describes the INNODBMETRICS table, listing the available counters, including numerous metrics for monitoring lock activity such as lockdeadlocks, locktimeouts, and lockwaits.

3. MySQL 8.0 Reference Manual, Section 26.3.35, "The INFORMATIONSCHEMA STATISTICS Table": This source confirms that the STATISTICS table provides information about table indexes, not runtime locking.

4. MySQL 8.0 Reference Manual, Section 26.4.21, "The INFORMATIONSCHEMA INNODBTABLESTATS Table": This document clarifies that INNODBTABLESTATS is used for storing optimizer statistics, not for monitoring locks.

Scroll to Top