Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
5.5.34
-
CentOS6 on x86-64 VM
Description
We are seeing the lock monitor report very odd locks. An UPDATE foo ... WHERE (subselect FROM bar) locks tables which are not related.
---TRANSACTION 3A7482E15, ACTIVE 3149 sec fetching rows
|
mysql tables in use 2, locked 1
|
19438 lock struct(s), heap size 1915320, 12147 row lock(s), undo log entries 11649
|
MySQL thread id 3184382, OS thread handle 0x7f1eed108700, query id 161830093 localhost moodle Updating
|
UPDATE mdl_grade_grades
|
SET aggregationstatus = 'unknown',
|
aggregationweight = 0
|
WHERE userid = '112108'
|
AND itemid IN (SELECT id
|
FROM mdl_grade_items
|
WHERE categoryid = '53225')
|
Trx read view will not see trx with id >= 3A76F69A4, sees < 3A7540CBB
|
TABLE LOCK table `moodle_prod`.`mdl_course_modules` trx id 3A7482E15 lock mode IX
|
TABLE LOCK table `moodle_prod`.`mdl_course` trx id 3A7482E15 lock mode IX
|
RECORD LOCKS space id 2493 page no 2223 n bits 120 index `PRIMARY` of table `moodle_prod`.`mdl_course` trx id 3A7482E15 lock_mode X locks rec but not gap
|
TABLE LOCK table `moodle_prod`.`mdl_context` trx id 3A7482E15 lock mode IX
|
RECORD LOCKS space id 2491 page no 21390 n bits 112 index `PRIMARY` of table `moodle_prod`.`mdl_context` trx id 3A7482E15 lock_mode X locks rec but not gap
|
TABLE LOCK table `moodle_prod`.`mdl_assign` trx id 3A7482E15 lock mode IX
|
TABLE LOCK table `moodle_prod`.`mdl_assign_plugin_config` trx id 3A7482E15 lock mode IX
|
TABLE LOCK table `moodle_prod`.`mdl_event` trx id 3A7482E15 lock mode IX
|
TABLE LOCK table `moodle_prod`.`mdl_grade_items` trx id 3A7482E15 lock mode IX
|
TABLE LOCK table `moodle_prod`.`mdl_grade_items_history` trx id 3A7482E15 lock mode IX
|
Tables are not related...
MariaDB [moodle_prod]> describe mdl_grade_grades;
|
+-------------------+---------------+------+-----+-----------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------------------+---------------+------+-----+-----------+----------------+
|
| id | bigint(10) | NO | PRI | NULL | auto_increment |
|
| itemid | bigint(10) | NO | MUL | NULL | |
|
| userid | bigint(10) | NO | MUL | NULL | |
|
| rawgrade | decimal(10,5) | YES | | NULL | |
|
| rawgrademax | decimal(10,5) | NO | | 100.00000 | |
|
| rawgrademin | decimal(10,5) | NO | | 0.00000 | |
|
| rawscaleid | bigint(10) | YES | MUL | NULL | |
|
| usermodified | bigint(10) | YES | MUL | NULL | |
|
| finalgrade | decimal(10,5) | YES | | NULL | |
|
| hidden | bigint(10) | NO | | 0 | |
|
| locked | bigint(10) | NO | MUL | 0 | |
|
| locktime | bigint(10) | NO | | 0 | |
|
| exported | bigint(10) | NO | | 0 | |
|
| overridden | bigint(10) | NO | | 0 | |
|
| excluded | bigint(10) | NO | | 0 | |
|
| feedback | longtext | YES | | NULL | |
|
| feedbackformat | bigint(10) | NO | | 0 | |
|
| information | longtext | YES | | NULL | |
|
| informationformat | bigint(10) | NO | | 0 | |
|
| timecreated | bigint(10) | YES | | NULL | |
|
| timemodified | bigint(10) | YES | | NULL | |
|
| aggregationstatus | varchar(10) | NO | | unknown | |
|
| aggregationweight | decimal(10,5) | YES | | NULL | |
|
+-------------------+---------------+------+-----+-----------+----------------+
|
23 rows in set (0.01 sec)
|
MariaDB [moodle_prod]> describe mdl_grade_items;
|
+------------------+---------------+------+-----+-----------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+------------------+---------------+------+-----+-----------+----------------+
|
| id | bigint(10) | NO | PRI | NULL | auto_increment |
|
| courseid | bigint(10) | YES | MUL | NULL | |
|
| categoryid | bigint(10) | YES | MUL | NULL | |
|
| itemname | varchar(255) | YES | | NULL | |
|
| itemtype | varchar(30) | NO | MUL | | |
|
| itemmodule | varchar(30) | YES | | NULL | |
|
| iteminstance | bigint(10) | YES | | NULL | |
|
| itemnumber | bigint(10) | YES | | NULL | |
|
| iteminfo | longtext | YES | | NULL | |
|
| idnumber | varchar(255) | YES | MUL | NULL | |
|
| calculation | longtext | YES | | NULL | |
|
| gradetype | smallint(4) | NO | MUL | 1 | |
|
| grademax | decimal(10,5) | NO | | 100.00000 | |
|
| grademin | decimal(10,5) | NO | | 0.00000 | |
|
| scaleid | bigint(10) | YES | MUL | NULL | |
|
| outcomeid | bigint(10) | YES | MUL | NULL | |
|
| gradepass | decimal(10,5) | NO | | 0.00000 | |
|
| multfactor | decimal(10,5) | NO | | 1.00000 | |
|
| plusfactor | decimal(10,5) | NO | | 0.00000 | |
|
| aggregationcoef | decimal(10,5) | NO | | 0.00000 | |
|
| aggregationcoef2 | decimal(10,5) | NO | | 0.00000 | |
|
| sortorder | bigint(10) | NO | | 0 | |
|
| display | bigint(10) | NO | | 0 | |
|
| decimals | tinyint(1) | YES | | NULL | |
|
| hidden | bigint(10) | NO | | 0 | |
|
| locked | bigint(10) | NO | MUL | 0 | |
|
| locktime | bigint(10) | NO | | 0 | |
|
| needsupdate | bigint(10) | NO | | 0 | |
|
| weightoverride | tinyint(1) | NO | | 0 | |
|
| timecreated | bigint(10) | YES | | NULL | |
|
| timemodified | bigint(10) | YES | | NULL | |
|
+------------------+---------------+------+-----+-----------+----------------+
|
31 rows in set (0.00 sec)
|
MariaDB [moodle_prod]> describe mdl_course;
|
+-------------------+--------------+------+-----+---------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------------------+--------------+------+-----+---------+----------------+
|
| id | bigint(10) | NO | PRI | NULL | auto_increment |
|
| category | bigint(10) | NO | MUL | 0 | |
|
| sortorder | bigint(10) | NO | MUL | 0 | |
|
| fullname | varchar(254) | NO | | | |
|
| shortname | varchar(255) | NO | MUL | | |
|
| idnumber | varchar(100) | NO | MUL | | |
|
| summary | longtext | YES | | NULL | |
|
| summaryformat | tinyint(2) | NO | | 0 | |
|
| format | varchar(21) | NO | | topics | |
|
| showgrades | tinyint(2) | NO | | 1 | |
|
| newsitems | mediumint(5) | NO | | 1 | |
|
| startdate | bigint(10) | NO | | 0 | |
|
| marker | bigint(10) | NO | | 0 | |
|
| maxbytes | bigint(10) | NO | | 0 | |
|
| legacyfiles | smallint(4) | NO | | 0 | |
|
| showreports | smallint(4) | NO | | 0 | |
|
| visible | tinyint(1) | NO | | 1 | |
|
| visibleold | tinyint(1) | NO | | 1 | |
|
| groupmode | smallint(4) | NO | | 0 | |
|
| groupmodeforce | smallint(4) | NO | | 0 | |
|
| defaultgroupingid | bigint(10) | NO | | 0 | |
|
| lang | varchar(30) | NO | | | |
|
| theme | varchar(50) | NO | | | |
|
| timecreated | bigint(10) | NO | | 0 | |
|
| timemodified | bigint(10) | NO | | 0 | |
|
| requested | tinyint(1) | NO | | 0 | |
|
| enablecompletion | tinyint(1) | NO | | 0 | |
|
| completionnotify | tinyint(1) | NO | | 0 | |
|
| cacherev | bigint(10) | NO | | 0 | |
|
| calendartype | varchar(30) | NO | | | |
|
+-------------------+--------------+------+-----+---------+----------------+
|
30 rows in set (0.00 sec)
|
MariaDB [moodle_prod]> describe mdl_course_modules;
|
+---------------------------+--------------+------+-----+---------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+---------------------------+--------------+------+-----+---------+----------------+
|
| id | bigint(10) | NO | PRI | NULL | auto_increment |
|
| course | bigint(10) | NO | MUL | 0 | |
|
| module | bigint(10) | NO | MUL | 0 | |
|
| instance | bigint(10) | NO | MUL | 0 | |
|
| section | bigint(10) | NO | | 0 | |
|
| idnumber | varchar(100) | YES | MUL | NULL | |
|
| added | bigint(10) | NO | | 0 | |
|
| score | smallint(4) | NO | | 0 | |
|
| indent | mediumint(5) | NO | | 0 | |
|
| visible | tinyint(1) | NO | MUL | 1 | |
|
| visibleold | tinyint(1) | NO | | 1 | |
|
| groupmode | smallint(4) | NO | | 0 | |
|
| groupingid | bigint(10) | NO | MUL | 0 | |
|
| completion | tinyint(1) | NO | | 0 | |
|
| completiongradeitemnumber | bigint(10) | YES | | NULL | |
|
| completionview | tinyint(1) | NO | | 0 | |
|
| completionexpected | bigint(10) | NO | | 0 | |
|
| showdescription | tinyint(1) | NO | | 0 | |
|
| availability | longtext | YES | | NULL | |
|
+---------------------------+--------------+------+-----+---------+----------------+
|
19 rows in set (0.01 sec)
|