I will introduce how to use the data_locks table to check useful locks in MySQL 8

Hello.
I'm Mandai, the Wild team member in charge of development.

This time, we will introduce a convenient way to check the lock status in MySQL 8

When checking for deadlocks, you can easily find out by searching for SHOW ENGINE INNODB STATUS\G , but if it's not a deadlock but simply a case where the lock time is long and subsequent queries cannot be executed, then it's not a deadlock and therefore cannot be confirmed with SHOW ENGINE INNODB STATUS\G

The data_locks and data_lock_waits tables added to the performance_schema in MySQL 8 are useful in such situations

What is the data_locks table?

The data_locks table is a table that holds InnoDB lock information

Let's create a suitable table, apply a lock, and see what happens

-- Create a database CREATE DATABASE test; -- Create a table CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, PRIMARY KEY (`id`), KEY `idx_username` (`username`), UNIQUE KEY `unique_email` (`email`) );

At this point, take a look at the data.locks table

SELECT * FROM performance_schema.data_locks; Empty set (0.001 sec)

Yes, nothing happens

Next, add one piece of data to this table

INSERT INTO users (username, email) VALUES ('test', '[email protected]');

Next, we start a transaction and apply a lock using SELECT ... FOR UPDATE

-- Begin transaction BEGIN; -- Apply lock SELECT * FROM users WHERE username = 'test' FOR UPDATE;

Let's look at the data_locks table without committing

SELECT * FROM performance_schema.data_locks; +--------+---------------------------------------+-----------------------+----------+----------+--------------+-------------+----------------+-------------------+--------------+----------------------+----------+---------------+-------------+-------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +---------+---------------------------------------+----------------------+------------+---------+--------------+-------------+----------------+-------------------+--------------+----------------------+----------+--------------------------+-------------+-------------------------+ | INNODB | 139629897444568:4067:139629789500112 | 432725 | 56 | 170 | test | users | NULL | NULL | NULL | 139629789500112 | TABLE | IX | GRANTED | NULL | | INNODB | NULL | NULL | idx_username | 139629789497120 | RECORD | idx_username | 139629789497120 | RECORD | | 139629789497464 | RECORD |

It was empty earlier, but now 4 data entries have appeared. The data_locks table is used to visualize the transactions that currently have locks on the table.

There's some important information here, so let's take a look at the columns

ENGINE_TRANSACTION_ID

This is the transaction ID. This value is the Trx id , which can be found by running SHOW ENGINE INNODB STATUS\G .

Detailed information about transactions is stored in the innodb_trx table of the information_schema table, so you can get more information by searching by trx_id

OBJECT_SCHEMA

This is the schema name where the table is stored.
In environments where locking occurs frequently or where there are multiple schemas, this also needs to be checked.

OBJECT_NAME

This is the table name.
In a case I encountered, the `data_locks` table had over 150 records, and the tables were all different, so I used this column to filter the results.

INDEX_NAME

This is the name of the locked index.
"PRIMARY" appears, it means the primary key is locked. If a specific index is locked, the name of that index will be displayed.

If it is NULL, the table is locked

LOCK_TYPE

This refers to the type of lock. There are two types: TABLE and RECORD . TABLE means the entire table is locked, while RECORD means the record is locked.

LOCK_MODE

These are the lock modes. In this example, there are three: IX , X , and REC_NOT_GAP . IX is an intention exclusive lock, and if a table lock is in place, the lock is applied in this mode. X is an exclusive lock, and when displayed alone, it is a next-key lock. REC_NOT_GAP is a record lock, indicating that it is not a gap lock.

There are other types and modes of locking available; please refer to the official documentation for details

LOCK_STATUS

This indicates the lock status. If the lock has been acquired , it will display GRANTED ; if the lock has not been acquired, meaning it is waiting for another query to finish its lock, it will display WAITING

LOCK_DATA

This displays data related to locks. For INNODB, it is only displayed if LOCK_TYPE is RECORD ; for TABLE , NULL is output.

For compound indexes, multiple values ​​are displayed, separated by commas

Furthermore, "supremum pseudo-record" is displayed, it indicates a pseudo-record representing the minimum upper limit of the index.
Since it is a pseudo-record, no actual record exists, but it is a record that is artificially created because the mechanism requires a lock termination.

What is the data_lock_waits table?

If you try to lock the same record in another session while a lock is already held on the users table, a lock wait will occur. The data_lock_waits table is used to check queries that are waiting for this lock .

Let's try to reproduce this as follows.
You will need two terminals, so connect to MySQL using separate terminals.

First, from Terminal 1, execute the SELECT ... FOR UPDATE statement mentioned earlier

-- Execute in terminal 1 -- Start transaction BEGIN; -- Apply lock SELECT * FROM users WHERE username = 'test' FOR UPDATE;

-- Connect to MySQL in terminal 2 and execute the following query -- Start a transaction BEGIN; -- Apply a lock SELECT * FROM users WHERE username = 'test' FOR UPDATE;

When you run this query, the Terminal 2 screen may appear to freeze.
In the background, a lock wait is occurring, preventing the results from being returned.

First, let's check the data_locks table

SELECT * FROM performance_schema.data_locks; +--------+---------------------------------------+-----------------------+----------+----------+--------------+-------------+----------------+-------------------+--------------+----------------------+----------+---------------+-------------+-------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +---------+---------------------------------------+----------------------+------------+---------+--------------+-------------+----------------+-------------------+--------------+----------------------+----------+--------------------------+-------------+-------------------------+ | INNODB | 139629897446184:4067:139629789512192 | 432729 | 60 | 89 | test | users | NULL | NULL | NULL | 139629789512192 | TABLE | IX | GRANTED | NULL | | INNODB | NULL | idx_username | 139629789509280 | RECORD | X | WAITING | 'test', 1 | | INNODB | TABLE | IX | GRANTED | NULL | | INNODB | 139629897444568:2805:6:1:139629789497120 | 432727 | 56 | 262 | test | users | NULL | NULL | idx_username | 139629789497120 | RECORD | GRANTED | supremum pseudo-record | | INNODB | 139629897444568:2805:6:2:139629789497120 | 432727 | 56 | 262 | test | users | NULL | NULL | idx_username | 139629789497120 | RECORD | GRANTED | 'test', 1 | | INNODB | 139629897444568:2805:4:2:139629789497464 | 432727 | 56 | 262 | test | users | NULL | NULL | PRIMARY | 139629789497464 | RECORD | X,REC_NOT_GAP | GRANTED | 1 | +---------+---------------------------------------+----------------------+------------+----------+--------------------------+-------------+----------------+-------------------+--------------+----------------------+----------+--------------------------+-------------+-------------------------+ 6 rows in set (0.001 sec)

The number of locks, which was only 4 a moment ago, has increased to 6. Looking at the two additional locks, we can see that the LOCK_STATUS of one of them is WAITING

This is the lock waiting state

Next, let's check the data_lock_waits table

SELECT * FROM performance_schema.data_lock_waits; +--------+---------------------------------------+---------------------------------+----------------------+--------------------+------------------------------------------------+---------------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+ | ENGINE | REQUESTING_ENGINE_LOCK_ID | REQUESTING_ENGINE_TRANSACTION_ID | REQUESTING_THREAD_ID | REQUESTING_EVENT_ID | REQUESTING_OBJECT_INSTANCE_BEGIN | BLOCKING_ENGINE_LOCK_ID | BLOCKING_ENGINE_TRANSACTION_ID | BLOCKING_THREAD_ID | BLOCKING_EVENT_ID | BLOCKING_OBJECT_INSTANCE_BEGIN | +---------+---------------------------------------+--------------------+----------------------+----------------------+---------------------------------+------------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+ | INNODB | 139629897446184:2805:6:2:139629789509280 | 432729 | 60 | 89 | 139629789509280 | 139629897444568:2805:6:2:139629789497120 | 432727 | 56 | 262 | 139629789497120|

Here too, only one piece of data was found

By comparing the REQUESTING_ENGINE_TRANSACTION_ID value with the ENGINE_TRANSACTION_ID in the data_locks table, we can see that a lock wait is occurring for queries where LOCK_STATUS is WAITING

Additionally,the BLOCKING_ENGINE_TRANSACTION_ID and BLOCKING_ENGINE_LOCK_ID indicate which lock is causing the lock wait.

Based on this information, we can identify the queries that need to be stopped, but it's true that tracking them down using only schemas and table names is difficult.
Therefore, although it requires prior configuration, there is a way to check the queries that are actually running.

Of course, if you try hard you can track it using binary logs, but unless there are very few queries flowing, it would be difficult to track it using queries alone, so it would be effective to investigate using the events_statements_history_long table, which will be explained in the next section

Enable the events_statements_history_long table

By default, writing to the events_statements_history_long table is disabled and needs to be enabled.
Enabling this can be done online by simply executing the following SQL:

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

Once you have done this, subsequent queries will be logged in the events_statements_history_long table, but if you want to keep the setting permanently, add the following setting to my.cnf as well:

[mysqld] performance-schema-consumer-events-statements-history-long=ON

The number of items saved per thread is the number of items equal to the query value below

show variables like 'performance_schema_events_statements_history_long_size'; +----------------------------------------------------+-------+ | Variable_name | Value | +----------------------------------------------------------+-------+ | performance_schema_events_statements_history_long_size | 10000 | +----------------------------------------------------+-------+ 1 row in set (0.002 sec)

This value is read-only, so if you want to change it you need to add the following setting to my.cnf and restart:

[mysqld] performance_schema_events_statements_history_long_size=20000

The data in the events_statements_history_long table is stored in memory, so if the value is too large, it may cause memory congestion

The `events_statements_history_long` table stores the entirety of executed queries up to `performance_schema_events_statements_history_long_size` . If `performance_schema_events_statements_history_long_size` is 10,000 records, when the 10,001st record is inserted, the 1st record is deleted. Therefore, if you know the specific source of the execution (e.g., a batch process different from the web server), it might be better to use the `events_statements_history` table.

The events_statements_history table stores the number of entries set by performance_schema_events_statements_history_size for each thread. The default automatic size setting for performance_schema_events_statements_history_size is 10 entries, which is a bit insufficient, but this can also be changed in my.cnf (restart required) .

The events_statements_history table is enabled by default, so you should be able to use it without any configuration

summary

This time, we introduced how to check for lock types that MySQL has not determined to be deadlocks

As a side effect of enabling the events_statements_history_long table, it also contains various execution results such as error details and memory usage in addition to queries, so it is a good idea to check it regularly

That's all