Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-78580

Add locking to quiz statistics calculation to prevent database deadlocks

    XMLWordPrintable

Details

    • MySQL
    • MOODLE_401_STABLE
    • MOODLE_401_STABLE, MOODLE_402_STABLE
    • MDL-78580_401_STABLE
    • MDL-78580_402_STABLE
    • MDL-78580_master
    • Hide

      The test_quiz_statistics_report unit test covers the basic locking an timeout behaviour.

      Synthetic tests

      To test how the behaves to the user, you can use the attached test_stats_lock.php CLI script to simulate a lock:

      Set up

      • Create a course with a quiz.
      • Navigate to the quiz.
      • create a question within it (any type is fine)
      • Make a note of the quiz cmid (id parameter in the URL).
      • Place test_stats_lock.php at the root of your Moodle codebase

      Test 1 - Question bank

      1. In a terminal, run `php test_stats_lock.php -q=cmid` with the cmid you noted in the set up.
      2. Wait until you see the message "Holding for 120 seconds or until Ctrl+C is pressed."
      3. Go to the "Question bank" tab.
        1. The page should load immediately without waiting for the lock.
      4. Wait for the script to timeout or press Ctrl+C in the terminal to release the lock.

      Test 2 - Statistics page lock

      1. Go to the "Results" tab and select "Statistics" in the tertiary navigation.
      2. In a terminal, run `php test_stats_lock.php -q=cmid` with the cmid you noted in the set up.
      3. Wait until you see the message "Holding for 120 seconds or until Ctrl+C is pressed."
      4. Set "Calculate statistics from" to "all attempts" and press "Show report"
        1. You should see a progress bar with the message "Waiting for task in progress. Please wait or try again later."
      5. Press Ctrl+C in the terminal to release the lock.
        1. The "Please wait" message should disappear and the page should continue to load.

      Test 3 - Statistics page timeout

      1. Go to Site Administration > Plugins > Activity modules > Quiz > Statistics report.
      2. Set "Statistics calculation lock timeout" to a low value like 10 seconds.
      3. Navigate to the quiz go to the "Results" tab and select "Statistics" in the tertiary navigation.
      4. In a terminal, run `php test_stats_lock.php -q=cmid` with the cmid you noted in the set up.
      5. Wait until you see the message "Holding for 120 seconds or until Ctrl+C is pressed."
      6. Set "Calculate statistics from" to "all attempts" and press "Show report"
      7. Wait for the timeout set in the admin settings to expire.
        1. You should see the error message, "Could not complete the statistics calculation. There may be a long-running calculation in progress. Please try again later."
        2. If debugging is enabled, you should also see a debugging message.
      8. Go to Site Administration > Plugins > Activity modules > Quiz > Statistics report and reset the timeout.

      Real-world tests

      If you have a large enough quiz enough attempts that the statistics calculation takes a significant length of time, you can do a more realistic test. If you don't have one you can set one up with the following steps, which resulted in a 30 second calculation process on my machine, although it takes a long time to generate the data.

      Set up

      • Go to Site Administration > Development > Make test course.
      • Create an L-size course (this takes a while).
      • Restore the attached 10-question-quiz.mbz to the course and make a note of the cmid.
      • Place the attached test_big_stats_calc.php in the root of your moodle codebase, and run `php test_big_stats_calc.php --quizcmid=cmid --attempts=10000`. This will generate 10000 attempts at the quiz. This takes a long time.
      • You may want to create a snapshot of your database at this point so you can roll back to re-run the tests. You can also re-attempt the quiz as any student before each test scenario to trigger the recalculation.

      Test 1 - Question bank

      1. If needed, reset the database or re-attempt the quiz.
      2. Navigate to the quiz
      3. Go to the "Question bank" tab.
        1. The page should load immediately, and show an empty or N/A result in the "Needs checking?", "Facility index" and "Discriminative efficiency" statistics columns.
      1. In the terminal run `php admin/cli/adhoc_task.php "--execute=\quiz_statistics\task\recalculate"`
      2. Wait until you see the message for your quiz, "'...re-calculating statistics for xxx attempts".
      3. Reload the Question bank page.
        1. The page should load immediately, and show an empty or N/A result in the statistics columns.
      4. Wait for the scheduled task to complete.
      5. Reload the Question bank page.
        1. The page should load immediately, and show 0.00% in the "Facility index" column. If you've used a real quiz with actual answers in the attempts, you may see some real statistics here instead.

      Test 2 - Statistics page lock

      1. If needed, reset the database or re-attempt the quiz.
      2. Navigate to the quiz
      3. Go to the "Results" tab.
      4. In the terminal run `php admin/cli/adhoc_task.php "--execute=\quiz_statistics\task\recalculate"`
      5. Wait until you see the message for your quiz, "'...re-calculating statistics for xxx attempts".
      6. Select "Statistics" in the tertiary navigation
        1. You should see a progress bar with the message "Waiting for task in progress. Please wait or try again later."
      7. Wait for the scheduled task to complete
        1. The "Please wait" message should disappear and the page should continue to load.

      Test 3 - Statistics page timeout

      1. If needed, reset the database or re-attempt the quiz.
      2. Go to Site Administration > Plugins > Activity modules > Quiz > Statistics report.
      3. Set "Statistics calculation lock timeout" to a low value like 10 seconds.
      4. Navigate to the quiz go to the "Results" tab.
      5. In the terminal run `php admin/cli/adhoc_task.php "--execute=\quiz_statistics\task\recalculate"`
      6. Wait until you see the message for your quiz, "'...re-calculating statistics for xxx attempts".
      7. Select "Statistics" in the tertiary navigation.
      8. Wait for the timeout set in the admin settings to expire.
        1. You should see the error message, "Could not complete the statistics calculation. There may be a long-running calculation in progress. Please try again later."
        2. If debugging is enabled, you should also see a debugging message.
      9. Go to Site Administration > Plugins > Activity modules > Quiz > Statistics report and reset the timeout.
      Show
      The test_quiz_statistics_report unit test covers the basic locking an timeout behaviour. Synthetic tests To test how the behaves to the user, you can use the attached test_stats_lock.php CLI script to simulate a lock: Set up Create a course with a quiz. Navigate to the quiz. create a question within it (any type is fine) Make a note of the quiz cmid (id parameter in the URL). Place test_stats_lock.php at the root of your Moodle codebase Test 1 - Question bank In a terminal, run `php test_stats_lock.php -q= cmid ` with the cmid you noted in the set up. Wait until you see the message "Holding for 120 seconds or until Ctrl+C is pressed." Go to the "Question bank" tab. The page should load immediately without waiting for the lock. Wait for the script to timeout or press Ctrl+C in the terminal to release the lock. Test 2 - Statistics page lock Go to the "Results" tab and select "Statistics" in the tertiary navigation. In a terminal, run `php test_stats_lock.php -q= cmid ` with the cmid you noted in the set up. Wait until you see the message "Holding for 120 seconds or until Ctrl+C is pressed." Set "Calculate statistics from" to "all attempts" and press "Show report" You should see a progress bar with the message "Waiting for task in progress. Please wait or try again later." Press Ctrl+C in the terminal to release the lock. The "Please wait" message should disappear and the page should continue to load. Test 3 - Statistics page timeout Go to Site Administration > Plugins > Activity modules > Quiz > Statistics report. Set "Statistics calculation lock timeout" to a low value like 10 seconds. Navigate to the quiz go to the "Results" tab and select "Statistics" in the tertiary navigation. In a terminal, run `php test_stats_lock.php -q= cmid ` with the cmid you noted in the set up. Wait until you see the message "Holding for 120 seconds or until Ctrl+C is pressed." Set "Calculate statistics from" to "all attempts" and press "Show report" Wait for the timeout set in the admin settings to expire. You should see the error message, "Could not complete the statistics calculation. There may be a long-running calculation in progress. Please try again later." If debugging is enabled, you should also see a debugging message. Go to Site Administration > Plugins > Activity modules > Quiz > Statistics report and reset the timeout. Real-world tests If you have a large enough quiz enough attempts that the statistics calculation takes a significant length of time, you can do a more realistic test. If you don't have one you can set one up with the following steps, which resulted in a 30 second calculation process on my machine, although it takes a long time to generate the data. Set up Go to Site Administration > Development > Make test course. Create an L-size course (this takes a while). Restore the attached 10-question-quiz.mbz to the course and make a note of the cmid. Place the attached test_big_stats_calc.php in the root of your moodle codebase, and run `php test_big_stats_calc.php --quizcmid= cmid --attempts=10000`. This will generate 10000 attempts at the quiz. This takes a long time. You may want to create a snapshot of your database at this point so you can roll back to re-run the tests. You can also re-attempt the quiz as any student before each test scenario to trigger the recalculation. Test 1 - Question bank If needed, reset the database or re-attempt the quiz. Navigate to the quiz Go to the "Question bank" tab. The page should load immediately, and show an empty or N/A result in the "Needs checking?", "Facility index" and "Discriminative efficiency" statistics columns. In the terminal run `php admin/cli/adhoc_task.php "--execute=\quiz_statistics\task\recalculate"` Wait until you see the message for your quiz, "'...re-calculating statistics for xxx attempts". Reload the Question bank page. The page should load immediately, and show an empty or N/A result in the statistics columns. Wait for the scheduled task to complete. Reload the Question bank page. The page should load immediately, and show 0.00% in the "Facility index" column. If you've used a real quiz with actual answers in the attempts, you may see some real statistics here instead. Test 2 - Statistics page lock If needed, reset the database or re-attempt the quiz. Navigate to the quiz Go to the "Results" tab. In the terminal run `php admin/cli/adhoc_task.php "--execute=\quiz_statistics\task\recalculate"` Wait until you see the message for your quiz, "'...re-calculating statistics for xxx attempts". Select "Statistics" in the tertiary navigation You should see a progress bar with the message "Waiting for task in progress. Please wait or try again later." Wait for the scheduled task to complete The "Please wait" message should disappear and the page should continue to load. Test 3 - Statistics page timeout If needed, reset the database or re-attempt the quiz. Go to Site Administration > Plugins > Activity modules > Quiz > Statistics report. Set "Statistics calculation lock timeout" to a low value like 10 seconds. Navigate to the quiz go to the "Results" tab. In the terminal run `php admin/cli/adhoc_task.php "--execute=\quiz_statistics\task\recalculate"` Wait until you see the message for your quiz, "'...re-calculating statistics for xxx attempts". Select "Statistics" in the tertiary navigation. Wait for the timeout set in the admin settings to expire. You should see the error message, "Could not complete the statistics calculation. There may be a long-running calculation in progress. Please try again later." If debugging is enabled, you should also see a debugging message. Go to Site Administration > Plugins > Activity modules > Quiz > Statistics report and reset the timeout.

    Description

      After the Ugrade to 4.1.4+ from last monday we had DEADLOCKs on our database concerning the tables
      mdl_question_response_count
      and mdl_question_response_analysis.

      I think it is caused when the task quiz_statistics\task\recalculate and loading the question bank of a course (with 1200 questions!) occur at the same time.

      We use MariaDB 10.6.14.

      Follow-up to https://tracker.moodle.org/browse/MDL-75576

      Attachments

        1. 10-question-quiz.mbz
          6 kB
        2. deadlock_moodle_debug.txt
          2 kB
        3. deadlock.txt
          5 kB
        4. image-2023-08-31-16-31-47-159.png
          image-2023-08-31-16-31-47-159.png
          56 kB
        5. result_1.png
          result_1.png
          375 kB
        6. result_2.png
          result_2.png
          109 kB
        7. result_3.png
          result_3.png
          160 kB
        8. result_4.png
          result_4.png
          379 kB
        9. screenshot-1.png
          screenshot-1.png
          7 kB
        10. test_big_stats_calc.php
          4 kB
        11. test_stats_lock_42.php
          4 kB
        12. test_stats_lock.php
          4 kB

        Issue Links

          Activity

            People

              marxjohnson Mark Johnson
              melanie.treitinger@ruhr-uni-bochum.de Melanie Treitinger
              Simon Thornett Simon Thornett
              Andrew Lyons Andrew Lyons
              Huong Nguyen Huong Nguyen
              Votes:
              51 Vote for this issue
              Watchers:
              65 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 2 hours
                  1d 2h

                  Clockify

                    Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.