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

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

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

When checking for deadlocks, SHOW ENGINE INNODB STATUS\G is the best option, but if the lock is simply taking too long to allow subsequent queries, it is not a deadlock and 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, start a transaction and acquire a lock with 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 before, but now there are four entries.
As you can see, data_locks table is a table that visualizes the transactions that currently have locks on tables.

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

ENGINE_TRANSACTION_ID

Transaction ID.
This value is the Trx id that can be confirmed by 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

The name of the schema the table is stored in.
If you are in an environment where locking occurs frequently or where there are multiple schemas, you will need to check this as well.

OBJECT_NAME

This is the table name.
In one case I encountered, there were more than 150 records in the data_locks table, and the tables were all different, so I used this column to narrow down the results.

INDEX_NAME

The name of the locked index.
PRIMARY appears, the lock is on the primary key, and if a specific index is locked, the name of that index will be displayed.

If it is NULL, the table is locked.

LOCK_TYPE

The type of lock.
There are two types: TABLE and RECORD TABLE means that the entire table is locked, and RECORD means that a record is locked.

LOCK_MODE

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

There are other lock types and modes, so please refer to the official documentation

LOCK_STATUS

The lock status.
If the lock is acquired GRANTED is displayed. If the lock is not acquired, that is, if the query is waiting for another query to finish locking, WAITING is displayed.

LOCK_DATA

Displays data related to locks.
For INNODB, it is displayed only if LOCK_TYPE is RECORD TABLE , NULL is output.

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

Also "supremum pseudo-record" is displayed, it indicates a pseudo record of the minimum upper limit of the index.
Since it is a pseudo record, no actual record exists, but since the system requires the end of the lock, it is a pseudo record.

What is the data_lock_waits table?

If a session holds a lock on the users table and another session tries to lock the same record, a lock wait will occur.
data_lock_waits to check which queries are waiting for this lock .

Let's try reproducing it as follows.
You will need two terminals, so connect to MySQL in separate terminals.

First, in Terminal 1, execute the SELECT ... FOR UPDATE command

-- 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 screen of Terminal 2 appears to freeze.
Behind the scenes, a lockup occurs, 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)

There were only four locks before, but now there are six.
Looking at the two new locks, we can see that the LOCK_STATUS 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 the ENGINE_TRANSACTION_ID you can see that a lock wait is occurring for a query whose LOCK_STATUS is WAITING

also find out which lock is causing the lock wait from BLOCKING_ENGINE_TRANSACTION_ID and BLOCKING_ENGINE_LOCK_ID

From this information, you can identify the queries to be stopped, but it is true that it is difficult to track them down using only the schema and table name.
Therefore, there is a way to check the queries that are actually being executed, although this requires some prior setup.

Of course, if you try hard, you can track it using binary logs, but I think it would be difficult to track it using queries alone unless there are very few queries flowing, 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, so you need to enable it.
You can enable it 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.

holds
all executed queries, performance_schema_events_statements_history_long_size performance_schema_events_statements_history_long_size is 10,000, the first query will be deleted when the 10,001st query is inserted. Therefore, if you know the specific execution source (such as a batch process different from the web server), it may be better to use the events_statements_history table.

The events_statements_history table stores the number of items set in
performance_schema_events_statements_history_size performance_schema_events_statements_history_size is 10 items, which is a little insufficient, but this setting can also be changed in my.cnf (requires restarting).

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 it.