mysql query locked table

Mysql, as a commonly used database management system, plays a vital role in today's Internet applications. However, for the management and maintenance of Mysql, improving its performance and stability is also a very important aspect. In the application process of Mysql, it is also common to encounter locked tables. So, how to query the locked table in this case? Below, we will introduce in detail the method of Mysql querying the locked table.

1. View the existing lock status of Mysql

Enter the following command in the Mysql client:

show engine innodb status;

The function of this command is to view the current lock status of Mysql Lock status, this status information is returned to the user in the form of the InnoDB storage engine. Through this command, we can observe a lot of information, including the version of the InnoDB storage engine, the number of locks currently acquired, the transaction ID of the locked transaction, etc.

2. View locked transaction information

There is a trx_rollback_to_savepoint parameter in the Mysql storage engine, which can be used to view all currently locked transaction information. Enter the following command in the Mysql client:

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   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;

The function of this command is to view the specific information of the locked table and the corresponding blocking transaction ID. In the output results, waiting_trx_id represents the blocked transaction ID, waiting_thread represents the process number corresponding to the transaction, waiting_query represents the SQL statement executed by the transaction, blocking_trx_id represents the transaction ID blocking the transaction, and blocking_thread represents the process number corresponding to the transaction. , blocking_query represents the SQL statement executed by the transaction.

3. Query the locked table information

Mysql storage engine has an innodb_locks table, which can be used to view the information of all currently locked tables. Enter the following command in the Mysql client:

SELECT concat('`', t1.`TABLE_SCHEMA`, '`.`', t1.`TABLE_NAME`, '`') AS `table`,
       t2.`ENGINE`,
       t2.`TRX_ID`,
       t2.`LOCK_MODE`,
       t2.`LOCK_TYPE`,
       t2.`LOCK_DATA`
FROM   `information_schema`.`TABLES` t1,
       `information_schema`.`INNODB_LOCKS` t2
WHERE  ( ( t1.`TABLE_SCHEMA` = DATABASE() )
         AND ( concat('`', t1.`TABLE_SCHEMA`, '`.`', t1.`TABLE_NAME`, '`') = t2.`LOCK_DATA` ) );

The function of this command is to view the currently locked table, where 'table' represents the name of the currently locked table, and 'ENGINE' represents the database used by the table. Storage engine. At the same time, 'TRX_ID' represents the transaction ID that locked the table, 'LOCK_MODE' represents the lock mode, 'LOCK_TYPE' represents the lock type, and 'LOCK_DATA' represents the locked data.

Summary:

There are many ways to query locked tables in Mysql. Through the above methods, you can view the currently locked tables and corresponding transaction information in detail, which is helpful for management. Members understand the performance bottlenecks of Mysql and solve the problems from the root cause. However, when querying the locked table information, you should try to avoid forcibly killing the process through kill -9 and other operations, otherwise it may bring unpredictable results. Therefore, in the actual application process, it is necessary to treat the situation of Mysql locked tables with caution, and adopt corresponding solutions and strategies based on the specific business and system architecture to improve the operating efficiency and stability of Mysql.

The above is the detailed content of mysql query locked table. For more information, please follow other related articles on the PHP Chinese website!