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

Index needed for event table (modulename, instance, eventtype)

    XMLWordPrintable

    Details

    • Type: New Feature
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.5
    • Fix Version/s: None
    • Component/s: Events API
    • Labels:
      None
    • Affected Branches:
      MOODLE_35_STABLE

      Description

      When the event table has a large amount of data, user actions like editing assignment activities results in delete queries similar to the following being ran on the event table which can encounter database deadlocks.

      DELETE FROM mdl_event WHERE modulename = 'assign' AND instance = '115568' AND eventtype = 'due'
      

      ------------------------
      LATEST DETECTED DEADLOCK
      ------------------------
      2018-08-08 15:27:48 7f0cd1a6f700

          • (1) TRANSACTION:
            TRANSACTION 78892045880, ACTIVE 1 sec starting index read
            mysql tables in use 1, locked 1
            LOCK WAIT 11 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 18
            MySQL thread id 342821645, OS thread handle 0x7f0cdf861700, query id 25187061245 v2 updating
            DELETE FROM mdl_event WHERE modulename = ‘assign’ AND instance = ‘399900’ AND eventtype = ‘gradingdue’
          • (1) WAITING FOR THIS LOCK TO BE GRANTED:
            RECORD LOCKS space id 3639614 page no 10 n bits 160 index `PRIMARY` of table `southeast2`.`mdl_event` trx id 78892045880 lock_mode X locks rec but not gap waiting
          • (2) TRANSACTION:
            TRANSACTION 78892045787, ACTIVE 1 sec fetching rows, thread declared inside InnoDB 1344
            mysql tables in use 1, locked 1
            5753 lock struct(s), heap size 538152, 2 row lock(s)
            MySQL thread id 342819310, OS thread handle 0x7f0cd1a6f700, query id 25187060613 v2p updating
            DELETE FROM mdl_event WHERE modulename = ‘assign’ AND instance = ‘399889’ AND eventtype = ‘gradingdue’
          • (2) HOLDS THE LOCK(S):
            RECORD LOCKS space id 3639614 page no 10 n bits 160 index `PRIMARY` of table `southeast2`.`mdl_event` trx id 78892045787 lock_mode X locks rec but not gap
          • (2) WAITING FOR THIS LOCK TO BE GRANTED:
            RECORD LOCKS space id 3639614 page no 3774 n bits 72 index `PRIMARY` of table `southeast2`.`mdl_event` trx id 78892045787 lock_mode X locks rec but not gap waiting
          • WE ROLL BACK TRANSACTION (1)

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                jogarcia Jonathan García Gómez
                Participants:
                Component watchers:
                Jake Dallimore, Jun Pataleta, Ryan Wyllie
              • Votes:
                6 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: