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

create_contexts_task causing long course_module table lock

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Run accesslib unit tests and confirm there are no failures.
      Go to Site Administration > Server > Schedule tasks
      Make sure the Create missing context is set to run on daily basis.

      Show
      Run accesslib unit tests and confirm there are no failures. Go to Site Administration > Server > Schedule tasks Make sure the Create missing context is set to run on daily basis.
    • Affected Branches:
      MOODLE_27_STABLE, MOODLE_28_STABLE
    • Fixed Branches:
      MOODLE_27_STABLE, MOODLE_28_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-48807-master
    • Sprint:
      Team '; drop tables Sprint 2, Team ';drop tables Sprint 3
    • Issue size:
      Small

      Description

      We have a fairly large course_module/context table (132434/161368 rows) on a Percona MySQL 5.5.40 server.

      The create_contexts_task runs a bunch of queries like this one:

      SELECT 70, cm.id
      FROM mdl_course_modules cm
      WHERE NOT EXISTS (
          SELECT 'x'
          FROM mdl_context cx
          WHERE cm.id = cx.instanceid AND cx.contextlevel=70
      )
      

      Which take ~0.5 seconds and by default run once a minute.
      The problem is, this query causes a table lock on mdl_course_modules which (fairly) frequently (7-8 times per day) causes a user's activity modification to fail.

      Our workaround is to do this instead:

      SELECT 70, cm.id
      FROM mdl_course_modules cm
      LEFT OUTER JOIN mdl_context cx ON cx.contextlevel=70 AND cx.instanceid=cm.id
      WHERE cx.id IS NULL
      

      But that is only slightly faster (0.3s).

      It might be better to remove the insert and bulk insert later, which removes the need for a table lock?

        Attachments

          Activity

            People

            Assignee:
            lameze Simey Lameze
            Reporter:
            skylarkelty Skylar Kelty
            Peer reviewer:
            Ankit Agarwal
            Integrator:
            Andrew Nicols
            Tester:
            Mark Nelson
            Participants:
            Component watchers:
            Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              9/Mar/15