Preface
In MySQL version 5.7, the metadata used for lock waiting for troubleshooting is mainly stored in the INNODB_LOCKS and INNODB_LOCK_WAITS tables under the information_schema library. The two tables in the 8.0 version were deleted. New lock-related tables are provided in performance_schema. This article will combine these changes to introduce how to troubleshoot lock waiting problems in the MySQL version 8.0 version.
1. data_locks
The data_locks in the performance_schema library can observe locks in MySQL, and for the InnoDB engine, table locks, row locks, Gap locks, and Next-key locks can be observed. It is worth noting that the data_locks table will be recorded regardless of whether the lock handles the waiting state, so it is beneficial for users to test the locking logic of MySQL through this table.
- ENGINE: Storage engine that holds the lock.
- ENGINE_LOCK_ID: Internal format, user can ignore it.
- ENGINE_TRANSACTION_ID: Transaction ID can be associated with the trx_id field of the INFORMATION_SCHEMA INNODB_TRX table.
- THREAD_ID: The thread ID for creating a lock is generally not necessary. The session connection can be located through the transaction ID.
- EVENT_ID: Used in combination with THREAD_ID, SQL statements can be found from the events table.
- OBJECT_SCHEMA: The name of the locked database.
- OBJECT_NAME: The name of the lock table.
- PARTITION_NAME: The name of the locked partition, if not the partition table is NULL.
- SUBPARTITION_NAME: The name of the locked subpartition, if not the partition table is NULL.
- INDEX_NAME: The name of the index.
- OBJECT_INSTANCE_BEGIN: The address locked in memory.
- LOCK_TYPE: The type of lock, for InnoDB, the allowed value is RECORD row-level lock, and TABLE is for table-level lock.
- LOCK_MODE: The behavior of locks is used to mark intention locks, write locks, read locks, gap locks, and Next-key locks.
- LOCK_STATUS: The state of lock request, for the InnoDB engine, there are two states: GRANTED already held and WAITING is waiting for lock.
- LOCK_DATA: If the primary key is locked, the primary key value will be displayed. If the secondary index is locked, the secondary index value and the corresponding primary key value will be displayed.
The following SQL is streamlined and only commonly used fields are retained:
select ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
Session 1: Lock the test_semi full table.
begin; select * from test_semi for update; +----+------+------+ | a | b | c | +----+------+------+ | 10 | 1 | 123 | | 11 | 2 | 123 | | 12 | 1 | 123 | | 13 | 2 | 123 | | 14 | 1 | 123 | +----+------+------+
Session 2: From the following table, we can see that each row in the test_semi table has a write lock on the primary key and an IX intention lock is added to the test_semi table.
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|---|---|---|
| 6711 | test | test_semi | NULL | TABLE | IX | GRANTED | NULL |
| 6711 | test | test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
| 6711 | test | test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 11 |
| 6711 | test | test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 12 |
| 6711 | test | test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 13 |
| 6711 | test | test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 14 |
2. data_lock_waits
The data_lock_waits table in the performance_schema library can observe lock waiting situations and will only be recorded when a blockage occurs. If you find that there are many records in this table, it means that there are many locks waiting for the database at present.
- ENGINE: Storage engine.
- REQUESTING_ENGINE_LOCK_ID: The ID of the lock requested by the storage engine.
- REQUESTING_ENGINE_TRANSACTION_ID: The blocked transaction ID can be associated with the trx_id field of the INFORMATION_SCHEMA INNODB_TRX table.
- REQUESTING_THREAD_ID: The thread ID of the session requesting the lock.
- REQUESTING_EVENT_ID: Performance mode event that causes lock request in a session requesting lock.
- REQUESTING_OBJECT_INSTANCE_BEGIN: The address of the requested lock in memory.
- BLOCKING_ENGINE_LOCK_ID: The ID of the blocking lock can be associated with the ENGINE_LOCK_ID field of the data_locks table.
- BLOCKING_ENGINE_TRANSACTION_ID: The transaction ID holding the lock can be associated with the trx_id field of the INFORMATION_SCHEMA INNODB_TRX table.
- BLOCKING_THREAD_ID: The thread ID of the session holding the blocking lock.
- BLOCKING_EVENT_ID: Performance mode event that causes blocking locks to occur in the session holding the lock.
- BLOCKING_OBJECT_INSTANCE_BEGIN: Blocking the address locked in memory.
Based on the association between the table and the transaction table, the transaction information can be obtained:
select trx.trx_id as waiting_trx_id, trx.trx_mysql_thread_id as waiting_thread_id, trx.trx_state as waiting_trx_state, trx.trx_query as waiting_query, lk.BLOCKING_ENGINE_TRANSACTION_ID as blocking_trx_id, lk.BLOCKING_THREAD_ID as blocking_thread_id, trx.trx_wait_started as trx_wait_started, TIMESTAMPDIFF(SECOND, trx.trx_wait_started, CURRENT_TIMESTAMP) as wait_second from performance_schema.data_lock_waits as lk join information_schema.INNODB_TRX as trx on lk.REQUESTING_ENGINE_TRANSACTION_ID = trx.trx_id;
- waiting_trx_id: The blocked transaction ID.
- waiting_thread_id: The blocked thread ID.
- waiting_trx_state: The state of the blocked transaction.
- waiting_query: A statement that is blocked from transaction.
- blocking_trx_id: The transaction ID that blocks the transaction.
- blocking_thread_id: The thread ID that blocks the transaction. If the query returns many rows and most of the values are the same, it means that the blocking source is the same. You can find the session ID through this ID and kill it.
- trx_wait_started: The start time of the blocked transaction.
- wait_second: The lock is blocked for a long time, in seconds.
3. sys.innodb_lock_waits
Most tables in the sys library are views. The reason why MySQL created this library is to simplify the difficulty of using the performance_schema table. This library provides a view that can find very detailed lock blocking information.
*************************** 1. row ***************************
wait_started: 2024-08-06 15:37:36
wait_age: 00:00:11
wait_age_secs: 11
locked_table: `test`.`test_semi`
locked_table_schema: test
locked_table_name: test_semi
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 421847145074688
waiting_trx_started: 2024-08-06 15:37:36
waiting_trx_age: 00:00:11
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 818473
waiting_query: select * from test_semi for share
waiting_lock_id: 140372168364032:10:4:2:140372080646752
waiting_lock_mode: S,REC_NOT_GAP
blocking_trx_id: 6711
blocking_pid: 819104
blocking_query: NULL
blocking_lock_id: 140372168364840:10:4:2:140372080652768
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2024-08-06 14:35:20
blocking_trx_age: 01:02:27
blocking_trx_rows_locked: 5
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 819104
sql_kill_blocking_connection: KILL 819104
The result set also gives the SQL that kills the blocking session, but the sys library generally does not give users permissions on the cloud database.
4. State variables
You can learn about row lock information in the database through the following state variables:
- Innodb_row_lock_current_waits: The operand that is currently waiting for row lock.
- Innodb_row_lock_time: The total time taken to acquire a row lock, in milliseconds.
- Innodb_row_lock_time_avg: The average time it takes to acquire a row lock, in milliseconds.
- Innodb_row_lock_time_max: The maximum time it takes to acquire a row lock, in milliseconds.
Let's do an experiment:
root@mysql 14:38: [(none)]>show status like '%Innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 33165 | | Innodb_row_lock_time_avg | 16582 | | Innodb_row_lock_time_max | 28845 | | Innodb_row_lock_waits | 2 | +-------------------------------+-------+
| Session 1 | Session 2 |
|---|---|
| Begin; | |
| delete from score where id = 5; | |
| update score set number = 66 where id = 5; – Wait for line lock |
root@mysql 14:41: [test]>show status like '%Innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 1 | | Innodb_row_lock_time | 33165 | | Innodb_row_lock_time_avg | 11055 | | Innodb_row_lock_time_max | 28845 | | Innodb_row_lock_waits | 3 | +-------------------------------+-------+
At this time, you can find that Innodb_row_lock_waits and Innodb_row_lock_current_waits have both grown, and time-related variables need to be calculated before the transaction is completed.
5. State variable bug
Innodb_row_lock_current_waits According to the document description, it reflects the operand of the current database row lock, but this value sometimes becomes inaccurate. A research and development asked me that there are 2 billion row locks in the current database on the monitoring of a certain cloud. Is the current database still normal? I was shocked at that time. There were no exceptions in the session, and there were no exceptions when using the lock detection method just introduced. Finally, it was found that the monitoring collected the value of Innodb_row_lock_current_waits. Finally, it was found that the value was very inaccurate and there were bugs. So if you encounter such problems, you can ignore it first. You can check the data_lock_waits table when waiting for monitoring, but the frequency is not recommended to be too high.
Innodb_row_lock_current_waits Bug：/?id=71520
Summarize
Some lock monitoring tables in MySQL 5.7 have changed in 8.0. However, both versions of the sys library innodb_lock_waits are common. In fact, this table is a view, and the implementation methods are different in both versions and the functions are the same.
This is the article about the problem of MySQL8.0 lock waiting for troubleshooting. This is all about this article. For more related content about MySQL lock waiting for troubleshooting, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!