MySQL Bugs: #66463: UPDATE LOW_PRIORITY blocks concurrent

Bug #66463	UPDATE LOW_PRIORITY blocks concurrent_inserts
Submitted:	20 Aug 2012 12:58	Modified:	21 Aug 2012 6:57
Reporter:	Haluk Akin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S4 (Feature request)
Version:	5.5.25a	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
We have a database where concurrent_inserts is set to ALWAYS.
SELECTs and INSERTs are executed at the same time just fine.
When there is an UPDATE in the queue for a MyISAM table. Table is locked as expected and all SELECT and INSERT queries wait in the line.

In our case we tried to issue "UPDATE LOW_PRIORITY" so that INSERTs and SELECTs can continue to execute at the same time and UPDATE LOW_PRIORITYs to wait for them to finish.

But what happened was, SELECTs continued to execute without any locks. However INSERTs are now locked and they wait for the UPDATE LOW_PRIORITYs to finish first.

Is this by design or a bug?

I would think UPDATE LOW_PRIORITY would push UPDATEs under the INSERTS.

How to repeat:
Run UPDATE LOW_PRIORITY on a table which gets many INSERTs and SELECTs. Database concurrent_insert is set to ALWAYS.

I'd say this is not a bug. Our manual, http://dev.mysql.com/doc/refman/5.5/en/update.html, clearly says:

"With the LOW_PRIORITY keyword, execution of the UPDATE is delayed until no other clients are *reading* from the table."

(highlight is mine)

INSERT does NOT read from the table formally (unlike SELECT), so I'd expect LOW_PRIORITY to not influence it.

Thank you. Reading the section you cited, I understand this is not a bug.

If it's okay, I'm updating this ticket as a "feature request".

If UPDATE LOW_PRIORITY could allow INSERTs and SELECTs to work concurrently when concurrent insert is enabled, this would provide more flexibility for MyISAM tables.

In this new scenario, UPDATEs can resume when all INSERTs and SELECTs are out of the queue.

IMHO this feature can make sense in some cases.