All Products
Search

This Product

    :How do I view the lock information of a MySQL database

    Document Center

    :How do I view the lock information of a MySQL database

    Last Updated:Mar 02, 2021

    Overview

    This topic describes how to view the locks of MySQL databases.

    Procedure

    Alibaba Cloud reminds you that:

    • Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
    • You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
    • If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.

    The following are SQL statements for querying lock information for MySQL engines of different versions:

    Version 5.6 or 5.7

    SELECT 
      r.trx_id waiting_trx_id, 
      r.trx_mysql_thread_id waiting_thread, 
      left(r.trx_query, 20) waiting_query, 
      concat(
        concat(lw.lock_type, ' '), 
        lw.lock_mode
      ) waiting_for_lock, 
      b.trx_id blocking_trx_id, 
      b.trx_mysql_thread_id blocking_thread, 
      left(b.trx_query, 20) blocking_query, 
      concat(
        concat(lb.lock_type, ' '), 
        lb.lock_mode
      ) blocking_lock 
    FROM 
      information_schema.innodb_lock_waits w 
      INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id 
      INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id 
      INNER JOIN information_schema.innodb_locks lw ON lw.lock_trx_id = r.trx_id 
       INNER JOIN information_schema.innodb_locks lb ON lb.lock_trx_id = b.trx_id;

    Note:

    • waiting_trx_id: the ID of the waiting transaction.
    • waiting_thread: the thread ID in MySQL, that is, the result displayed show processlist.
    • waiting_query: a statement that waits for a lock.
    • blocking_trx_id: the ID of the transaction waiting for the lock is being blocked.

    Version 8.0

    SELECT 
      r.trx_id waiting_trx_id, 
      r.trx_mysql_thread_id waiting_thread, 
      r.trx_query waiting_query, 
      b.trx_id blocking_trx_id, 
      b.trx_mysql_thread_id blocking_thread, 
      b.trx_query blocking_query 
    FROM 
      PERFORMANCE_SCHEMA.data_lock_waits w 
      INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id 
       INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

    Version 8.0 advanced query lock information

    This method can be used to detect a large amount of data. If there are many row locks, a large set of results are returned. Therefore, use this method with caution.

    SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, LEFT(r.trx_query, 20) AS waiting_query
     , concat(concat(lw.lock_type, ' '), lw.lock_mode) AS waiting_for_lock
     , b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, LEFT(b.trx_query, 20) AS blocking_query
     , concat(concat(lb.lock_type, ' '), lb.lock_mode) AS blocking_lock
    FROM performance_schema.data_lock_waits w
     INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
     INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
     INNER JOIN performance_schema.data_locks lw ON lw.ENGINE_TRANSACTION_ID = r.trx_id
      INNER JOIN performance_schema.data_locks lb ON lb.ENGINE_TRANSACTION_ID = b.trx_id;

    References

    Application scope

    • ApsaraDB RDS for MySQL