Lock contention in MySQL

Time:2021-5-9

Lock is a mechanism for computer to coordinate multiple processes or threads to access a resource concurrently!
No matter MyISAM or InnoDB, lock contention will occur. In theAll kinds of locks in MySQL
In this article, we have talked about how to view table lock and row lock contention

=======================================================================

1. Table locking

SHOW  STATUS  LIKE  'table%';

By checking the table_ locks_ Waited and table_ locks_ Immediate state variable to analyze table locking
explain:
Table_ locks_ Immediate: the number of lock requests that can obtain the table level lock immediately
Table_ locks_ Waited: the number of lock requests that need to wait because the table level lock cannot be obtained immediately
analysis:
If table_ locks_ If the value of waited is high and there is a performance problem, it indicates that there is a serious table level lock contention. At this time, we need to further check the application to determine the problem. We should first optimize the query, and then split the table or copy the table. If the contention of table lock is very serious, we need to consider changing the engine to InnoDB. In addition, we need to open the slow query of SQL to see which SQL queries are slow, and do the targeted SQL optimization.

================================================================================

2. Row level lock

SHOW STATUS LIKE ‘Innodb_row_lock%’;

Lock contention in MySQL

explain:
Innodb_ row_ lock_ current_ Waits: the current number of locks waiting
Innodb_ row_ lock_ Time: the total locking time since the system was started, unit: Ms.
Innodb_ row_ lock_ time_ AVG: average locking time, unit: Ms.
Innodb_ row_ lock_ time_ Max: maximum locking time, unit: Ms.
Innodb_ row_ lock_ Waits: the total number of times the system has been locked since the system was started.

analysis:
For InnoDB_ row_ lock_ Waits and InnoDB_ row_ lock_ time_ The value of AVG is relatively high, which indicates that there may be lock contention. For InnoDB type tables, you can set InnoDB monitors to further observe the tables and data rows with lock contention, and analyze the reasons for lock contention, as follows:
Note: the statement show create table can be used_ name; View table_ The engine used by name (find engine = XXX in the query output, where XXX is the engine used);
Set InnoDB monitor first

CREATE  TABLE  innodb_monitor(a  INT)  ENGINE=INNODB;

see

SHOW ENGINE INNODB STATUS;

explain:
1. The output contains detailed information about the current lock waiting, including table name, lock type, lock record, etc. After the monitor is turned on, by default, the monitored content will be recorded in the log every 15 seconds. If the monitor is opened for a long time, the. Err file will become very huge. Therefore, after confirming the cause of the problem, we should remember to delete the drop table InnoDB_ monitor;) To turn off the monitor.
2. The output result is the average value per second based on a period of data sampling. The time here is taken from the time interval between the system startup and the current time or the time interval between the last output and the current time
3. Find the contents of transactions to view the deadlock contention of transactions
For more details about show engine InnoDB status in mysql, please refer to the following link address:
https://www.cnblogs.com/olinux/p/5497176.h…

==================================================================

3. Command to view the current or currently locked or waiting transaction:

To view the current transaction:

SELECT` `* ``FROM` `information_schema.innodb_trx \G

To view the currently locked transactions:

SELECT` `* ``FROM` `INFORMATION_SCHEMA.INNODB_LOCKS;

To view the transactions currently waiting for a lock:

SELECT` `* ``FROM` `INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

Through the above three sentences of code, you can know the current executing transaction, the locked transaction and the waiting transaction
If the lock contention is fierce and the value is large, it is difficult to accurately locate the table with serious lock contention by viewing the current transaction in normal business

This work adoptsCC agreementReprint must indicate the author and the link of this article

Hu Jun