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

insert ... select from joins hangs or result in 1206: The total number of locks exceeds the lock table size

Details

    Description

      Splitted from MDEV-28804.

      Run the following test with --innodb_buffer_pool_size=5m, in a build of CMAKE_BUILD_TYPE=RelWithDebInfo and WITH_DBUG_TRACE:BOOL=OFF.

      10.5 a7ee3bc58bc23ad7ac5ced54dc745d0e65bee524

      --source include/have_innodb.inc
      CREATE TABLE t1 (d DOUBLE) engine=innodb;
      INSERT INTO t1 VALUES (0x0061),(0x0041),(0x00E0),(0x00C0),(0x1EA3),(0x1EA2),(0x00E3),(0x00C3),(0x00E1),(0x00C1),(0x1EA1),(0x1EA0);
      INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6;
       
      drop table t1;
      

      It either hangs or results in 1206: The total number of locks exceeds the lock table size.

      See marko's comment for some initial analysis and hint:
      https://jira.mariadb.org/browse/MDEV-28804?focusedCommentId=278885&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-278885

      Attachments

        Issue Links

          Activity

            Please see MDEV-28803

            Roel Roel Van de Paar added a comment - Please see MDEV-28803
            ycp Yuchen Pei added a comment - - edited

            marko, what do you think is the relation between this bug and MDEV-28803, as Roel mentioned above?

            I notice that one difference is that MDEV-28803 is for 10.6+, but this one is at least 10.4, 10.5 but potentially also other versions (pending testing).

            And of course, this one results in hang or 1206, but MDEV-28803 only results in 1206

            ycp Yuchen Pei added a comment - - edited marko , what do you think is the relation between this bug and MDEV-28803 , as Roel mentioned above? I notice that one difference is that MDEV-28803 is for 10.6+, but this one is at least 10.4, 10.5 but potentially also other versions (pending testing). And of course, this one results in hang or 1206, but MDEV-28803 only results in 1206

            I think that MDEV-28803 and MDEV-28804 had already filed for the same root cause, which is that an excessive number of explicit record lock objects is being created during INSERT…SELECT when the target table also occurs in a Cartesian product of the source. I posted some analysis of this in MDEV-28804.

            The main difference between MDEV-28803 and this one seems to be explained by MDEV-28805.

            marko Marko Mäkelä added a comment - I think that MDEV-28803 and MDEV-28804 had already filed for the same root cause, which is that an excessive number of explicit record lock objects is being created during INSERT…SELECT when the target table also occurs in a Cartesian product of the source. I posted some analysis of this in MDEV-28804 . The main difference between MDEV-28803 and this one seems to be explained by MDEV-28805 .
            ycp Yuchen Pei added a comment -

            Got it marko. Now that we have a reproducible case for MDEV-28804 I've blocked this one with that one. Will work on that one first.

            ycp Yuchen Pei added a comment - Got it marko . Now that we have a reproducible case for MDEV-28804 I've blocked this one with that one. Will work on that one first.

            People

              ycp Yuchen Pei
              ycp Yuchen Pei
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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