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

create_contexts_task causing long course_module table lock

    XMLWordPrintable

Details

    • MOODLE_27_STABLE, MOODLE_28_STABLE
    • MOODLE_27_STABLE, MOODLE_28_STABLE
    • MDL-48807-master
    • 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.
    • Team '; drop tables Sprint 2, Team ';drop tables Sprint 3
    • 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

            lameze Simey Lameze
            skylarkelty Skylar Kelty
            Ankit Agarwal Ankit Agarwal
            Andrew Lyons Andrew Lyons
            Mark Nelson Mark Nelson
            Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              9/Mar/15