Bug #104367 Query all the locks in performance.schema.data_locks cause mysqld oom

• Submitted: 20 Jul 2021 13:14
• Modified: 21 Jul 2021 1:45
• Reporter: zkong kong
• Status: Can't repeat
• Category: MySQL Server: Performance Schema
• Severity: S3 (Non-critical)
• Version: 8.0.25
• OS: Linux
• CPU Architecture: Any

[20 Jul 2021 13:14] zkong kong

Description:
Select all the locks in performance_schema.data_locks when the locks is many and eats up too much memory then the server is killed by os.

How to repeat:
1. Use sysbench to create a 10000000 rows table;
2. create a big transaction
   xa start '1';
   update sbtest1 set c='68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783442' where id<=10000000;
   xa end '1';
   xa prepare '1';
3 In another session query the data locks table get all the locks
  select * from performance_schema.data_locks;

Then view the mysqld memory increase quickly.

I suppose that if the locks not so many but many queries execute concurrently it also use much memory? not test yet.

[20 Jul 2021 13:41] MySQL Verification Team

Hi Mr. kong,

Thank you for your bug report.

We have just performed a test that you have vaguely described on a machine with 1 Tb of virtual memory.

We have not been able to repeat your OOM situation.

Hence, why do you think that this is a bug ????/

[20 Jul 2021 13:50] MySQL Verification Team

The OOM situation is most probably happening on the client side.

All you have to do is to use mysql_use_result() instead of mysql_store_result(). If you are using MySQL CLI, that means the following option:

--quick

That is it ......

[21 Jul 2021 1:45] zkong kong

Hi:
I confirm the query will cause the mysqld process use more 10G byte physical memory, it is not make sense.