Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the concept of mysql lock and conversation". In daily operation, I believe that many people have doubts about the concept of mysql lock and conversation. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the concept of mysql lock and conversation". Next, please follow the editor to study!

1. View the table that is being locked

Show OPEN TABLES where In_use > 0

In_use: how many threads are in use

Name_locked: whether it is locked

2. Query which threads are running.

The most important thing in this command is the state column. Mysql lists the following states

Checking table

Checking the datasheet (this is automatic).

Closing tables

The modified data in the table is being flushed to disk and the table that has been used up is being closed. This is a quick operation, and if not, you should make sure that the disk space is full or that the disk is under a heavy load.

Connect Out

The replication slave server is connecting to the master server.

Copying to tmp table on disk

Because the temporary result set is larger than tmp_table_size, temporary tables are being converted from memory storage to disk storage to save memory.

Creating tmp table

Creating a temporary table to hold some of the query results.

Deleting from main table

The server is performing the first part of the multi-table deletion, and the first table has just been deleted.

Deleting from reference tables

The server is performing the second part of the multi-table deletion and is deleting records for other tables.

Flushing tables

Executing FLUSH TABLES, waiting for another thread to close the data table.

Killed

If a kill request is sent to a thread, the thread will check the kill flag bit and abandon the next kill request. MySQL checks the kill flag bit in each main loop, but in some cases the thread may take a short time to die. If the thread is locked by another thread, the kill request takes effect as soon as the lock is released.

Locked

Locked by other queries.

Sending data

The record of the SELECT query is 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 very fast unless disturbed by other factors. For example, a data table cannot be opened by another thread until the execution of an ALTER TABLE or LOCK TABLE statement is complete. Trying to open a table.

Removing duplicates

An SELECT DISTINCT query is being executed, but MySQL cannot optimize those duplicate records in the previous phase. Therefore, MySQL needs to remove the duplicate records again and then send the results to the client.

Reopen table

A lock on a table is acquired, but the lock cannot be acquired until the table structure has been modified. The lock has been released, the datasheet has been closed, and an attempt is being made to reopen the datasheet.

Repair by sorting

The repair directive is sorting to create an index.

Repair with keycache

The repair instruction is using the index cache to create new indexes one by one. It will be slower than Repair by sorting.

Searching rows for update

We are talking about finding qualified records for updating. It must be done before UPDATE modifies the relevant records.

Sleeping

Waiting for the client to send a new request.

System lock

Waiting to acquire an external system lock. If you are not currently 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 get a lock table 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 informed that the data table structure has been modified and needs to be reopened to get the new structure. Then, in order to reopen the table, you must wait until all other threads close the table. This notification occurs 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 a new request.

Most states correspond to fast operations, and as long as one thread stays in the same state for a few seconds, there may be a problem that needs to be checked.

There are other states not listed above, but most of them are only useful to see if there are any errors on the server.

Add: look at the transaction being locked SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; View the transaction waiting for lock SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; so far, the study of "the concept of mysql lock and session" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.