[#MDEV-9025] Lock monitor shows unrelated tables locked

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)

[MDEV-9025] Lock monitor shows unrelated tables locked Created: 2015-10-27 Updated: 2015-12-25 Resolved: 2015-12-25
Status:	Closed
Project:	MariaDB Server
Component/s:	Locking
Affects Version/s:	5.5.34
Fix Version/s:	N/A

[MDEV-9025] Lock monitor shows unrelated tables locked Created: 2015-10-27 Updated: 2015-12-25 Resolved: 2015-12-25