Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21175

Remove dict_table_t::n_foreign_key_checks_running from InnoDB

    XMLWordPrintable

Details

    Description

      InnoDB should clean n_foreign_key_checks usage and assume that SQL is already holding
      appropriate MDL. But an Insert into the child table doesn't seem to acquire MDL on the parent. When vice-versa is happening that there is MDL on the child when insert is happening
      for the parent table.

      The following test case could repeat the issue:

      --source include/have_innodb.inc
      create table t1(f1 int not null, primary key(f1))engine=innodb;
      create table t2(f1 int not null primary key,
                      f2 int not null, foreign key(f2) references t1(f1))engine=innodb;
      set DEBUG_SYNC='ib_after_row_insert_step SIGNAL alter WAIT_FOR dml';
      --send insert into t1 values(1)
      connect(con1,localhost,root,,,);
      set DEBUG_SYNC="now WAIT_FOR alter";
      alter table t2 force;
      set DEBUG_SYNC="now SIGNAL dml";
      connection default;
      reap;
      drop table t2, t1;
      disconnect con1;
      

      In above test case, ALTER TABLE t2 FORCE waits for MDL to open the table.

      Another test case:

      --source include/have_innodb.inc
      create table t1(f1 int not null, primary key(f1))engine=innodb;
      create table t2(f1 int not null primary key,
                      f2 int not null, foreign key(f2) references t1(f1))engine=innodb;
      set DEBUG_SYNC='ib_after_row_insert_step SIGNAL alter WAIT_FOR dml';
      --send insert into t2 values(1, 2)
      connect(con1,localhost,root,,,);
      set DEBUG_SYNC="now WAIT_FOR alter";
      alter table t1 force;
      set DEBUG_SYNC="now SIGNAL dml";
      connection default;
      reap;
      drop table t2, t1;
      disconnect con1;
      

      In this case, alter waits at commit_inplace_alter_table(). That too for innodb table lock.

      #6  0x00005555565df591 in lock_wait_suspend_thread (thr=0x7fffb8030768)
          at /home/thiru/mariarepo/10.5/10.5-work/storage/innobase/lock/lock0wait.cc:351
      #7  0x00005555566bb378 in row_mysql_handle_errors (new_err=0x7ffff01c3844,
          trx=0x7ffff0cc3268, thr=0x7fffb8030768, savept=0x0)
          at /home/thiru/mariarepo/10.5/10.5-work/storage/innobase/row/row0mysql.cc:740
      #8  0x00005555565cd2f7 in lock_table_for_trx (table=0x7fffc41317e8,
          trx=0x7ffff0cc3268, mode=LOCK_X)
          at /home/thiru/mariarepo/10.5/10.5-work/storage/innobase/lock/lock0lock.cc:4070
      #9  0x00005555566ad12d in row_merge_lock_table (trx=0x7ffff0cc3268,
          table=0x7fffc41317e8, mode=LOCK_X)
          at /home/thiru/mariarepo/10.5/10.5-work/storage/innobase/row/row0merge.cc:3698
      #10 0x0000555556568d42 in ha_innobase::commit_inplace_alter_table (this=
          0x7fffb8020c08, altered_table=0x7ffff01c4fb0,
          ha_alter_info=0x7ffff01c4f20, commit=true)
          at /home/thiru/mariarepo/10.5/10.5-work/storage/innobase/handler/handler0alter.cc:10699
      #11 0x000055555611bf0a in handler::ha_commit_inplace_alter_table (
          this=0x7fffb8020c08, altered_table=0x7ffff01c4fb0,
          ha_alter_info=0x7ffff01c4f20, commit=true
      

      First case is acquiring MDL on child table (un-necessary locking)
      Second case should do MDL on parent table.

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              thiru Thirunarayanan Balathandayuthapani
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.