Monitoring MySQL data locks, or the tip of the iceberg

This story is about recent (*) performance improvements implemented in MySQL, related to monitoring of data locks.

(*) Originally written in Feb 2025.

Refresher

What is a data lock?

When a user session connects to the MySQL database, it executes SQL queries.

The query runs; it uses relational tables, possibly many (joins), either reading from or writing to them.

In each table, a query may access a few rows, or many.

We all know that. This is what a database is for: the application behind the user session just executes SQL against the database, and everything just works, there is nothing complicated about it.

Right?

No. This is all an illusion.

With transactions, isolation levels, and concurrent access, the main job of a database server is to maintain the illusion, for a session, that it executes alone without any side effects to worry about, while in reality, sessions and queries from different applications all touch the same data, simultaneously.

The database server has to balance concurrency, to achieve maximum performance, with correctness, to preserve data integrity.

To achieve this, the database keeps track of what records were used by a given session, and for what reason.

This is what a “data lock” is.

MySQL exposes the performance_schema.data_locks table, so internal locks can be observed.

Doc: https://dev.mysql.com/doc/refman/9.1/en/performance-schema-data-locks-table.html

What is a data lock wait?

When two applications both need access to the same records, in an incompatible way (for example, both want to write), this will create a lock conflict.

To mitigate this conflict, MySQL will let one query execute, while blocking another, until the contended lock is released.

This is what a “data lock wait” represents: a dependency between two queries on a given data.

MySQL exposes the performance_schema.data_lock_waits table, so contentions can be observed.

Doc: https://dev.mysql.com/doc/refman/9.1/en/performance-schema-data-lock-waits-table.html

What is sys.innodb_lock_waits?

Knowing that lock ‘X’ is blocked by lock ‘Y’, reflected by table performance_schema.data_lock_waits, is not sufficient to understand contentions, because it does not say where the contention is coming from, only what it is at a very low level.

What is needed is to translate this dependency between two locks, to a dependency between two queries.

MySQL exposes the sys.innodb_lock_waits view for this.

The view is a join between:

• table information_schema.innodb_trx (for the blocked transaction)
• table performance_schema.data_locks (for the blocked lock)
• table performance_schema.data_lock_waits (for the lock dependency)
• table performance_schema.data_locks (for the blocking lock)
• table information_schema.innodb_trx (for the blocking transaction)

Doc: https://dev.mysql.com/doc/refman/9.1/en/sys-innodb-lock-waits.html

Why monitor this?

In development, a query that has a big lock footprint can go unnoticed, when different applications are tested in isolation, and not under load.

Contentions with other applications will only happen in production, which is why system administrators want to monitor contention overall.

Monitoring application-level locks between SQL queries in production is the only way to identify bottlenecks, to get them fixed, and improve performance over time.

As a result, some monitoring tools query the sys.innodb_lock_waits view very often, creating more pressure on the MySQL server itself.

Regressions reported with MySQL 8.0

Performance regression related to monitoring

When MySQL 8.0 adoption increased, some users reported that monitoring view sys.innodb_lock_waits took longer in 8.0 compared to 5.7.

Users also reported that executing a SELECT on view sys.innodb_lock_waits could negatively affect performance on the system.

MySQL deadlock under heavy load

Much later, by the time MySQL 5.7 reached end of life, many more deployments upgraded from 5.7 to 8.0.

By this time, some users reported that MySQL can sometime crash under heavy load, after reporting a deadlock in InnoDB.

These crashes could not be reproduced or even explained; the only thing known was that it can happen, and when it happened, a critical mutex in InnoDB was involved.

The reason why this particular mutex was suddenly a problem, and only under heavy load, has been very elusive.

Before diving into investigations (and fixes) related to these two issues, a small detour into MySQL history is needed to understand the landscape.

MySQL history

The code involved with the sys.innodb_lock_waits view is ancient.

2009: Creation of INFORMATION_SCHEMA.INNODB_LOCK_WAITS

Sometime in 2009, the following tables were implemented:

• table INFORMATION_SCHEMA.INNODB_LOCKS
• table INFORMATION_SCHEMA.INNODB_LOCK_WAITS
• table INFORMATION_SCHEMA.INNODB_TRX

Source:

  commit 1b9fe0bbac72d49a32863241b2b5081438b5f691
  Date:   Wed May 27 15:15:59 2009 +0530
  Adding innodb_plugin-1.0.4 as storage/innodb_plugin.

Table INFORMATION_SCHEMA.INNODB_LOCK_WAITS was present in MySQL 5.1.

2014: Creation of the sys_schema

A collection of helper views, known as the sys_schema, was implemented in 2014 in the then separate mysql-sys GitHub repository.

Source: https://github.com/mysql/mysql-sys/blob/master/views/i_s/innodb_lock_waits.sql

2016: Creation of performance_schema.data_lock_waits

In 2016, table performance_schema.data_lock_waits was created in MySQL 8.0.

Source: https://github.com/mysql/mysql-server/blob/8.0/storage/perfschema/table_data_lock_waits.cc

This is a transition from INFORMATION_SCHEMA to performance_schema, as well as a change in the table structure.

The table INFORMATION_SCHEMA.INNODB_LOCK_WAITS was deprecated in MySQL 5.7, and removed from MySQL 8.0, at the same time.

2019: Make the sys_schema mainstream in MySQL

The sys_schema, which was optional and maintained separately, was integrated into the mainstream MySQL code in 2019 (in 5.7 and 8.0).

Source: https://github.com/mysql/mysql-server/blob/5.7/scripts/sys_schema/views/i_s/innodb_lock_waits.sql

Performance regression, the analysis

As many deployments upgraded from MySQL 5.7 to 8.0, it became apparent that the impact of issues reported earlier was in fact much more severe than originally anticipated.

This prompted an effort to investigate and fix this area as a high priority.

Fixes and improvements

The fixes listed below are presented in the order that makes the most sense to understand the whole code base, now that all moving parts involved are identified.

Analysis, discussions, fixes, benchmarks, prototypes, commits, reviews, merges, and backports happened in a different order in reality.

1 – BUG#35068461 mysql crashes after detecting DEADLOCK

This bug was the “tip of the iceberg”.

For a very long time, no clear root cause was identified that could explain why innodb would “forget” to unlock a mutex, leading the server to certain doom.

No call stack or meaningful data was available to explain the context, making this very challenging to investigate.

Then, more investigation led to a major break through: the deadlock issue always occurred after the server memory consumption spiked to Out Of Memory levels, so somehow, failures to allocate memory were a trigger for the bug.

The culprit was found to be table performance_schema.data_locks, due to some (incomplete) exception handling of out of memory conditions.

What were two independent issues before:

• monitoring data locks causing performance regressions
• the server crashing on a deadlock

was now in fact the same problem, the former issue being the root cause of the latter.

2 – Bug#36012737 Dead code in sys_schema

This is a trivial low-hanging fruit, to remove dead code from the code base.

Although this dead code was never executed, its existence caused confusion during analysis, adding noise and causing distractions.

In particular, the file “i_s/innodb_lock_waits.sql” was dead code, not to be confused with file “p_s/innodb_lock_waits.sql”.

This matters because each file defines a view named sys.innodb_lock_waits, but uses different tables in the underlying join.

3 – Bug#36028618 add_trx_relevant_locks_to_cache wastes CPU and memory

The following tables were removed from MySQL 8.0:

• table INFORMATION_SCHEMA.INNODB_LOCKS
• table INFORMATION_SCHEMA.INNODB_LOCK_WAITS

However, some code in innodb was still populating a cache internally, to support these tables.

This code has been removed, as nothing was using the data collected.

This made the collection of data for table INFORMATION_SCHEMA.INNODB_TRX more efficient (it uses the same cache, now simplified).

4 – Bug#35240825 performance_schema.data_locks query increased RAM usage to OOM

A design choice, when implementing table performance_schema.data_locks, was to list all locks held by a transaction, unlike the previous design of table INFORMATION_SCHEMA.INNODB_LOCKS, where locks are printed only if involved in a wait.

To put it simply, a transaction holding 1 million locks in a table, where only 1 lock is causing a wait of another transaction, will be seen like this:

• 1 million rows in table performance_schema.data_locks (in MySQL 8.0)
• 1 row in table INFORMATION_SCHEMA.INNODB_LOCKS (in MySQL 5.7)

This choice is motivated by the fact that exposing all locks actually held helps to understand the actual lock footprint of a query, regardless of contention.

When such a query (that locks millions of rows) existed in the MySQL workload, monitoring the locks caused the server memory usage to spike.

The fix was to revisit the memory consumption for each row (by sharing data instead of making a copy), to better support this workload.

This involved some refactoring in the performance_schema, as well as in innodb, in the code that extracts the lock information.

5 – Bug#36337708 View sys.innodb_lock_waits should join on primary keys

With the previous bug fixed, memory consumption for one row in table performance_schema.data_locks was reduced.

To reduce memory consumption further, it also helps if only the rows actually needed are returned for this table, but this was not the case.

View sys.innodb_lock_waits is a join involving 5 tables, and the join condition in MySQL 8.0 is more complex compared to MySQL 5.7, involving a multi-part key of two columns, instead of just one column.

Fixing the join condition to use the full key allowed the optimizer to pick the proper execution plan.

6 – Bug#31763497 PERFORMANCE DEGRADATION CAUSED BY MONITORING SYS.INNODB_LOCK_WAITS IN MYSQL 8.0

With the previous bug fixed, the optimizer now performs point selects to retrieve data from table performance_schema.data_locks, instead of table scans.

This exposed a new code path in the table performance_schema.data_locks implementation, which now should support a point select by actually implementing a point select internally. For historical reasons, this code did an internal scan instead, which was not efficient.

This was fixed by changing the data access pattern to use a fetch, when extracting the underlying data from innodb.

7 – Bug#36302624 performance_schema.data_lock_waits is generated with O(N^3) complexity

To achieve better performance, the last part of the puzzle was to revise and improve how innodb extracts data, when populating table performance_schema.data_lock_waits.

The change in this area was complex, and deserves a post on its own.

With this last part fixed, the whole stack is now aligned to what it should be:

• View sys.innodb_lock_waits is joining the proper tables with the proper keys, helping the optimizer to pick the proper execution plan
• Table performance_schema.data_lock_waits exposes a primary key index actually implemented with a fetch by key, for performance
• Table performance_schema.data_lock_waits represents data using an internal cache, to reduce memory footprint.
• The Performance Schema / InnoDB interface is revised to support the above
• The internal innodb code, that reports data locks from the live server state, is revised to be more efficient.

Conclusion

Investigating performance issues in MySQL 8.0, related to monitoring of data locks, led to revisiting the whole stack involved:

• The view definitions in the sys_schema
• The table implementations in the performance_schema
• The data lock extraction from the innodb engine

With these fixes, MySQL 8.0 now allows monitoring data locks, in a live system, under load, without any known issues at the time of writing.

One way to look at this is to just consider it a regression that has been fixed: this was, after all, working in 5.7, for a feature exposed as soon as 5.1.

Another way to look at this area is also to consider how much has changed in MySQL 8.0 (not to mention 9.x), compared to MySQL 5.1:

• The workload a server can process is orders of magnitude bigger.
• The stress the monitoring query causes on the running server is also bigger, by orders of magnitude, since many more locks need to be reported.

With this work:

• The server stalls, caused by the monitoring query, are reduced.
• The memory consumption, caused by the monitoring query, is reduced.
• The overall server stability is improved.
• Monitoring SQL-level lock contention in production is more robust against a heavy server workload.

Further reading

InnoDB data locks part 1, part 2 and part 3.

Thank you for using MySQL!