[MDEV-33324] insert ... select from joins hangs or result in 1206: The total number of locks exceeds the lock table size Created: 2024-01-29  Updated: 2024-01-30

Status: Open
Project: MariaDB Server
Component/s: Locking, Optimizer, Storage Engine - InnoDB
Affects Version/s: 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Critical
Reporter: Yuchen Pei Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-28804 Increased lock objects in 10.6+ leadi... Stalled
Relates
relates to MDEV-28803 ERROR 1206 (HY000): The total number ... Open
relates to MDEV-28805 SET GLOBAL innodb_buffer_pool_size=12... Confirmed

 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

 Comments   
Comment by Roel Van de Paar [ 2024-01-29 ]

Please see MDEV-28803

Comment by Yuchen Pei [ 2024-01-29 ]

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

Comment by Marko Mäkelä [ 2024-01-29 ]

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.

Comment by Yuchen Pei [ 2024-01-29 ]

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.

Generated at Thu Feb 08 10:38:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.