Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-5576

Server stalls because ALTER TABLE on partitioned table holds dict mutex

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.25-28, 8.0.15-5
    • Fix Version/s: 5.7.27-30
    • Component/s: None

      Description

      TRUNCATE PARTITION blocks completely unrelated queries.

      This was originally reported at https://bugs.mysql.com/bug.php?id=94610 However I have simpler test case for MTR.

      How to Repeat.

      Modify code:

      1. sveta@delly:~/src/percona-server$ git diff
        diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc
        index 45c0e629fd9..be2d878690b 100644
        --- a/storage/innobase/row/row0mysql.cc
        +++ b/storage/innobase/row/row0mysql.cc
        @@ -3494,6 +3494,8 @@ row_mysql_lock_data_dictionary_func(
                trx->dict_operation_lock_mode = RW_X_LATCH;
         
                mutex_enter(&dict_sys->mutex);
        +
        +       DBUG_EXECUTE_IF("bug94610", {my_sleep(60000000); fprintf(stderr, "bug94610\n");});
         }
        

      Run test case:

      $ cat ~/src/tests/t/bug94610.test
      --source include/have_innodb.inc
      --source include/have_debug.inc
      --source include/have_debug_sync.inc
      create table t1(f1 int) engine=innodb;
      insert into t1 values(1);
      insert into t1 values(2);
      CREATE TABLE `t2` (
        `id` bigint(20) NOT NULL,
        `p` json NOT NULL,
        `m` tinyint(2) NOT NULL,
        `ho` char(64) COLLATE utf8_bin GENERATED ALWAYS AS (json_unquote(json_extract(`p`,'$.ho'))) VIRTUAL,
        `hr` char(64) COLLATE utf8_bin GENERATED ALWAYS AS (json_unquote(json_extract(`p`,'$.hr'))) VIRTUAL,
        PRIMARY KEY (`id`,`m`),
        KEY `originator_idx` (`ho`(16)),
        KEY `recipient_idx` (`hr`(16))
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
      PARTITION BY RANGE (`m`)
      (PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
       PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
       PARTITION p3 VALUES LESS THAN (4) ENGINE = InnoDB,
       PARTITION p4 VALUES LESS THAN (5) ENGINE = InnoDB,
       PARTITION p5 VALUES LESS THAN (6) ENGINE = InnoDB,
       PARTITION p6 VALUES LESS THAN (7) ENGINE = InnoDB,
       PARTITION p7 VALUES LESS THAN (8) ENGINE = InnoDB,
       PARTITION p8 VALUES LESS THAN (9) ENGINE = InnoDB,
       PARTITION p9 VALUES LESS THAN (10) ENGINE = InnoDB,
       PARTITION p10 VALUES LESS THAN (11) ENGINE = InnoDB,
       PARTITION p11 VALUES LESS THAN (12) ENGINE = InnoDB,
       PARTITION p12 VALUES LESS THAN (13) ENGINE = InnoDB);
      set debug="d,bug94610";
      --send alter table t2 truncate partition p3
      --connect(con1,localhost,root,,test)
      --connection con1
      --sleep 5
      select now();
      insert into t1 values(3);
      select now();
      --connection default
      --reap
      set debug='';
      drop table t1, t2;
      

      Notice that INSERT command took 55 seconds (should return immediately).

        Smart Checklist

          Attachments

          1. s1.st
            40 kB
          2. s2.st
            38 kB
          3. s3.st
            44 kB
          4. s4.st
            38 kB

            Issue Links

              Activity

                People

                • Assignee:
                  satya.bodapati Satya Bodapati
                  Reporter:
                  sveta.smirnova Sveta Smirnova
                • Votes:
                  1 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - Not Specified
                    Not Specified
                    Logged:
                    Time Spent - 1 week, 4 days, 6 hours, 16 minutes
                    1w 4d 6h 16m