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 Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:8.0.25 OS:Linux
Assigned to: CPU Architecture:Any

[20 Jul 2021 13:14] zkong kong
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:


That is it ......
[21 Jul 2021 1:45] zkong kong
I confirm the query will cause the mysqld process use more 10G byte physical memory, it is not make sense.