mysql-list-all-locks

About

mysql-list-all-locks tool retrieves active database locks by joining performance_schema.data_locks with information_schema.innodb_trx, providing a comprehensive view of blocked threads, transaction states, and the specific queries causing contention.

mysql-list-all-locks outputs a detailed view of data locks including lock type, lock mode, lock status, transaction state, current operation and query for all threads running on specified object or all objects in a database. The output is a JSON formatted array of top 10 data locks ordered by longest running transaction time.

Compatible Sources

Requirements

• performance_schema should be turned ON for this tool to work.

Parameters

This tool takes 3 optional input parameters:

• table_schema (optional): The target database for active locks. If not specified the results will be displayed for all databases.
• table_name (optional): Name of the table to be checked. Check all tables visible to the current user if not specified.
• limit (optional): Max rows to return, default 10.
• connected_schema (optional): The database user is connected to, the value is set from env variable CLOUD_SQL_MYSQL_DATABASE or MYSQL_DATABASE.

Example

kind: tools
name: list_all_locks
type: mysql-list-all-locks
source: my-mysql-instance
description: list all active locks including lock type, lock mode, locked object, lock status, transaction state, query and process id for all objects or specified objects within a designated database or across all databases as requested.

Output Format

The response is a json array with the following fields:

[
  {
  "thread_id": "The internal MySQL server thread identifier associated with the lock",
  "process_id": "MySQL Process ID",
  "db": "The database schema where the locked object is located",
  "table_name": "The name of the specific table affected by the lock",
  "lock_type": "The target of the lock, such as a table or an individual record",
  "lock_mode": "The specific permission level of the lock (e.g., Shared or Exclusive)",
  "lock_status": "Whether the lock has been successfully granted or is currently waiting",
  "transaction_state": "The current lifecycle phase of the transaction",
  "current_operation": "The specific internal task the transaction is currently performing",
  "query": "The trimmed text of the SQL statement"
  }
]

Reference