MySQL Bugs: #73463: Out of memory error when concurrently write locking tables with triggers

Bug #73463	Out of memory error when concurrently write locking tables with triggers
Submitted:	4 Aug 2014 11:37	Modified:	5 Aug 2014 19:39
Reporter:	István Rozsnyai	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.5.36, 5.6.21	OS:	Linux (Centos 5)
Assigned to:		CPU Architecture:	Any
Tags:	oom out_of_memory lock trigger procedure, regresison

Description:
Concurrent write locks on tables that has triggers updating a base table using a procedure (base table also has trigger updating itself through same procedure) causes Out of memory error: "Out of memory (Needed 669448 bytes)".
Before OOM happens all mysql processes waiting for table metadata lock and some is in "Opening table" state. While queries are waiting mysql server process memory usage growth infinitely until system goes out of memory and lock queries die with the Out of memory errors.
Server must have at >4GB memory. I couldn't reproduce on a virtualized machines with less then 2 GB of memory.

Running the script with mysql server version 5.6.17 mysql server also eats the memory but the queries dies because mysql server restarts itself.

How to repeat:
I attached an SQL db dump and a perl script that executes locks concurrently on this db that reproduces the out of memory error.

Suggested fix:
Secure these locks with a named lock prevent Out of memory to happen. Set the $secureWithNamedLock = 1 to run this scenario.

Test DB sql

Attachment: OOM.sql (text/x-sql), 4.81 KiB.

perl script to reproduce

Attachment: mysql_OOM.pl (application/x-perl, text), 2.17 KiB.

Thank you for the report.

Verified as described.

Not repeatable on 5.1 which does not support metadata locks.