(This is fairly development-oriented, after a bit of mulling, posted here. Could move it to quiz forum...)
On a heavily loaded M2.6.x running on MariaDB 5.5.x. Bouncing between innotop's view of Locks and Queries, I see that this setup gets hit by queries following this format
UPDATE mdl_question_attempts
SET maxmark = 2
WHERE
questionusageid IN (
SELECT quiza.uniqueid
FROM mdl_quiz_attempts quiza
WHERE quiza.quiz = '23440')
AND slot = '1'
these queries can take 5~10 minutes to complete. They hold on to a table-wide lock, so nobody can touch mdl_question_attempts. To make things more colorful the subselect yields an empty result set. I feel like this:
To illustrate, checking for empty results like this makes the query take less than 10ms
UPDATE mdl_question_attempts
SET maxmark = 2
WHERE
( SELECT COUNT(quiza.uniqueid)
FROM mdl_quiz_attempts quiza
WHERE quiza.quiz = '23441') > 0
AND questionusageid IN (
SELECT quiza.uniqueid
FROM mdl_quiz_attempts quiza
WHERE quiza.quiz = '23441')
AND slot = '2' ;
Clearly MariaDB is being rather stupid.
Following the code around, qubaids_for_quiz::__construct() has bits of the subselect, and quiz_update_question_instance() calls it thus:
question_engine::set_max_mark_in_attempts(new qubaids_for_quiz($quiz->id),
$slot, $grade);
so I think I found the code path that leads to that SQL. However... we see a lot these updates. Are there code paths or uses cases that run over this in a loop? All I see seems to come from the quiz edit form, which I'd say edits one quiz at a time...