MySQL common statements for viewing transactions and locks

Some common statements for viewing transactions and locks in the database

Check transaction waiting status:

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;

View more specific transaction waiting status:

SELECT
   b.trx_state,
   e.state,
   e.time,
   d.state AS block_state,
   d.time AS block_time,
   a.requesting_trx_id,
   a.requested_lock_id,
   b.trx_query,
   b.trx_mysql_thread_id,
   a.blocking_trx_id,
   a.blocking_lock_id,
   c.trx_query AS block_trx_query,
   c.trx_mysql_thread_id AS block_trx_mysql_tread_id
FROM
   information_schema.INNODB_LOCK_WAITS a
LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id
LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id
LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id
LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id
ORDER BY
   a.requesting_trx_id;

View unclosed transactions:

–MySQL 5.6

SELECT
   a.trx_id,
   a.trx_state,
   a.trx_started,
   a.trx_query,
   b.ID,
   b.USER,
   b.DB,
   b.COMMAND,
   b.TIME,
   b.STATE,
   b.INFO,
   c.PROCESSLIST_USER,
   c.PROCESSLIST_HOST,
   c.PROCESSLIST_DB,
   d.SQL_TEXT
FROM
   information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
AND b.COMMAND = 'Sleep'
LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

–MySQL 5.5

SELECT
   a.trx_id,
   a.trx_state,
   a.trx_started,
   a.trx_query,
   b.ID,
   b. USER,
   b. HOST,
   b.DB,
   b.COMMAND,
   b.TIME,
   b.STATE,
   b.INFO
FROM
   information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
WHERE
   b.COMMAND = 'Sleep';

View transactions that have not been closed for a period of time:

SELECT
    trx_id,
    trx_started,
    trx_mysql_thread_id
FROM
    INFORMATION_SCHEMA.INNODB_TRX
WHERE
    trx_started < date_sub(now(), INTERVAL 1 MINUTE)
AND trx_operation_state IS NULL
AND trx_query IS NULL;

Supplement: MySQL lock status view command

1 show processlist;

SHOW PROCESSLIST shows which threads are running. You can also use the mysqladmin processlist statement to obtain this information. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account you are using). If a thread is updating or inserting a table, the status of the process is updating or sending data.

This statement is useful if you get a "too many connections" error message and want to understand what is happening. MySQL reserves an extra connection for use by accounts with the SUPER privilege to ensure that administrators can always connect and examine the system (assuming you do not grant this privilege to all users).

Status	meaning
Checking table	Checking data tables (this is automatic).
Closing tables	The modified data in the table is being flushed to disk, and the table that has been used is being closed. This is a very quick operation, but if it is not, you should check to see if the disk is full or if the disk is under heavy load.
Connect Out	The replication slave server is connecting to the master server.
Copying to tmp table on disk	Since the temporary result set is larger than tmp_table_size, the temporary table is being converted from memory storage to disk storage to save memory.
Creating tmp table	Creating a temporary table to hold partial query results.
deleting from main table	The server is executing the first part of a multiple-table delete and has just deleted the first table.
deleting from reference tables	The server is executing the second part of a multi-table delete and is deleting records from other tables.
Flushing tables	FLUSH TABLES is being executed, waiting for other threads to close the data table.
Killed	If a kill request is sent to a thread, the thread will check the kill flag and abandon the next kill request. MySQL checks the kill flag in each main loop, but in some cases it may take a while for the thread to die. If the thread is locked by other threads, the kill request will take effect immediately when the lock is released.
Locked	Locked by another query.
Sending data	The records of the SELECT query are being processed and the results are being sent to the client.
Sorting for group	Sorting for GROUP BY.
Sorting for order	Sorting for ORDER BY.
Opening tables	This process should be quick unless interfered with by other factors. For example, the table cannot be opened by other threads until the ALTER TABLE or LOCK TABLE statement is completed. Trying to open a table.
Removing duplicates	A SELECT DISTINCT query is being executed, but MySQL was unable to optimize away duplicate records in the previous stage. Therefore, MySQL needs to remove duplicate records again and then send the results to the client.
Reopen table	A lock on a table is obtained, but the lock can only be obtained after the table structure is modified. The lock has been released, the table has been closed, and an attempt is being made to reopen the table.
Repair by sorting	Fix instructions being sorted to create index.
Repair with keycache	The repair instruction is creating new indexes one by one using the index cache. It will be slower than Repair by sorting.
Searching rows for update	The records that meet the conditions are being found for update. It must be done before the UPDATE can modify related records.
Sleeping	Waiting for new request from client.
System lock	Waiting to acquire an external system lock. If you are not running multiple mysqld servers requesting the same table at the same time, you can disable external system locks by adding the --skip-external-locking parameter.
Upgrading lock	INSERT DELAYED is trying to acquire a table lock to insert a new record.
Updating	Searching for matching records and modifying them.
User Lock	Waiting for GET_LOCK().
Waiting for tables	The thread is notified that the data table structure has been modified and needs to be reopened to obtain the new structure. Then, in order to reopen the data table, you must wait until all other threads close the table. This notification is generated in the following situations: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.
waiting for handler insert	INSERT DELAYED has processed all pending insert operations and is waiting for new requests.

Most states correspond to very fast operations. If a thread remains in the same state for several seconds, there may be a problem and it needs to be checked. There are other statuses not listed above, but most of them are only useful to see if there are any errors with the server.

2 show full processlist;

show processlist; only the first 100 items are listed. If you want to list all items, please use show full processlist;

3 show open tables;

This command can check which tables are currently open. The In_use column indicates how many threads are using a table, and Name_locked indicates whether the table name is locked, which usually occurs when the Drop or Rename command operates on this table. Therefore, this command cannot help answer our common questions: whether a certain table is currently deadlocked, who owns the lock on the table, etc.

show open tables from database;

4 show status like '%lock%'

Check the server status.

5 show engine innodb status\G;

The command before MySQL 5.1 is: show innodbstatus\G;. In MySQL 5.5, you can use the above command to view the runtime information of the innodb engine.

6 show variables like '%timeout%';

View server configuration parameters.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.

You may also be interested in:

Detailed explanation of the implementation principle of transaction isolation level in MySQL
Detailed explanation of the syntax and process of executing MySQL transactions
Description of the default transaction isolation level of mysql and oracle
MySQL transaction autocommit automatic commit operation
MySQL master-slave synchronization, implementation principle of transaction rollback
In-depth analysis of MySQL database transactions and locks
Will Update in a Mysql transaction lock the table?
In-depth understanding of PHP+MySQL distributed transactions and solutions
How does MySQL implement ACID transactions?
Why MySQL should avoid large transactions and how to solve them
Detailed explanation of the persistence implementation principle of transactions in MySQL

<<: HTML table markup tutorial (40): Dark border color attribute of the header BORDERCOLORDARK

>>: Example of implementing the skeleton screen of WeChat applet