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

TOI (ALTER) hangs on a parent table if SR transaction is in progress on a child table

    XMLWordPrintable

Details

    Description

      This MTR snippet causes node_1 to hang:

      --connection node_1
      CREATE TABLE parent (id INT AUTO_INCREMENT PRIMARY KEY, v INT) ENGINE=InnoDB;
      INSERT INTO parent VALUES (1, 1),(2, 2),(3, 3);
       
      CREATE TABLE child (id INT AUTO_INCREMENT PRIMARY KEY, parent_id INT, CONSTRAINT parent_fk
          FOREIGN KEY (parent_id) REFERENCES parent (id)) ENGINE=InnoDB;
       
      --connection node_2
      # Start SR transaction and make it lock both parent and child tales.
      SET SESSION wsrep_trx_fragment_size = 1;
      START TRANSACTION;
      INSERT INTO child (parent_id) VALUES (1),(2),(3);
       
      --connection node_1
      # Sync wait for SR transaction to replicate and apply fragments, thus
      # locking parent table as well.
      SET SESSION wsrep_sync_wait = 15;
      SELECT COUNT(*) FROM child;
      # Now run TOI on the parent, which BF-aborts the SR-transaction in progress.
      ALTER TABLE parent AUTO_INCREMENT = 100;
      

      with the log message:

      2024-08-29 13:57:16 11 [Note] InnoDB: WSREP: BF lock wait long for trx:0x32 query: ALTER TABLE parent AUTO_INCREMENT = 100
      

      Attachments

        Issue Links

          Activity

            People

              sysprg Julius Goryavsky
              denis.protivensky Denis Protivensky
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.