INSERT SELECT randomly blocks in Copy to temp table

Hello,

we have a INSERT SELECT statement that started to block randomly about a week ago. Normally the statement executes in less than 5 seconds, but when it blocks, it does not return in more than 3 hours (longer we did not wait).

The statement blocks randomly, that means sometimes it runs fine and then a minute later it may block.

The statement looks as follows:

insert into findispyear(fyperi,fytyp,fywert,fyMenge,fyupdt,fylgrx,fymat,fywareh)
select f1peri as Peri,'22. + WE aus Vorperioden' as Art,round(sum(atbwmg*atbwwt),4) as WEWert,
sum(atbwmg) as fyMenge,
date_format(current_date, '%Y%m%d')as fyupdt,
f1lgrx as LgRx,f1mat as fymat,atzo01 as fywareh
from werh01
join werd01 on wezo01=wercid
join atad01 on weatid=atpkey
join w_findispmon01 on f1whse=atzo01 and f1zo05=atzo05
where weendperi=f1peri
and atbwdt <(f1peri*100)+1
group by art,Peri,f1lgrx,f1mat,atzo01

Some tests showed that it is most likely that the table atad01 is involved in this issue: The atad01 gets quite a lot of inserts. As long as these inserts are running (or even some inserts are pending when the statement above starts), then it is more likely for the statement to block. When we stop the inserts into atad01, then no blocking could be observed.

Therefore here the table definition of atad01 and an example of the insert statements we are using there:

CREATE TABLE `atad01` (
`ATBWID` INT(11) NULL DEFAULT NULL,
`ATZO01` INT(11) NULL DEFAULT NULL,
`ATZO02` INT(11) NULL DEFAULT NULL,
`ATZO03` INT(11) NULL DEFAULT NULL,
`ATZO04` INT(11) NULL DEFAULT NULL,
`ATZO05` INT(11) NULL DEFAULT NULL,
`ATZO06` INT(11) NULL DEFAULT NULL,
`ATZO07` INT(11) NULL DEFAULT NULL,
`ATZO08` INT(11) NULL DEFAULT NULL,
`ATZO09` INT(11) NULL DEFAULT NULL,
`ATZO10` INT(11) NULL DEFAULT NULL,
`ATBART` VARCHAR(10) NULL DEFAULT NULL,
`ATBLGN` VARCHAR(64) NULL DEFAULT NULL,
`ATBPOS` INT(11) NULL DEFAULT NULL,
`ATCHGN` VARCHAR(10) NULL DEFAULT NULL,
`ATKDLI` INT(11) NULL DEFAULT NULL,
`ATBNCH` VARCHAR(10) NULL DEFAULT NULL,
`ATPBVB` INT(11) NULL DEFAULT NULL,
`ATBWMG` INT(11) NULL DEFAULT NULL,
`ATBWWT` DECIMAL(13,4) NULL DEFAULT NULL,
`ATBWDT` INT(11) NULL DEFAULT NULL,
`ATBWZT` INT(11) NULL DEFAULT NULL,
`ATCMT1` VARCHAR(77) NULL DEFAULT NULL,
`ATUPDT` INT(11) NULL DEFAULT NULL,
`ATUPZT` INT(11) NULL DEFAULT NULL,
`ATUPUS` VARCHAR(50) NULL DEFAULT NULL,
`ATUPBS` VARCHAR(50) NULL DEFAULT NULL,
`ATPKEY` INT(11) NOT NULL AUTO_INCREMENT,
`ATSRCE` VARCHAR(10) NULL DEFAULT NULL,
`ATBUID` INT(11) NULL DEFAULT NULL,
`ATSTOCKEVALUATIONTYPE` VARCHAR(25) NULL DEFAULT NULL,
`ATIGNORELOCK` BIT(1) NULL DEFAULT NULL,
`ATPBVBLGR` INT(11) NULL DEFAULT NULL,
`ATCMTUSER` VARCHAR(1024) NULL DEFAULT NULL,
`ATCMTERROR` VARCHAR(1024) NULL DEFAULT NULL,
`ATWTVB` DECIMAL(13,4) NULL DEFAULT NULL,
`ATCONSHIST` VARCHAR(10) NULL DEFAULT NULL,
`ATZOTECHID` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`ATPKEY`),
INDEX `Art_ARTicle` (`ATBART`, `ATZO05`),
INDEX `iATBWID_atad01` (`ATBWID`),
INDEX `iDocid` (`ATBLGN`, `ATZO03`, `ATZO04`),
INDEX `iLens` (`ATZO03`, `ATZO04`),
INDEX `iWDBWID` (`ATZO08`),
INDEX `iAS_KEY` (`ATBLGN`, `ATBPOS`),
INDEX `izo06` (`ATZO06`),
INDEX `iLocation` (`ATZO01`, `ATZO02`),
INDEX `iArticle` (`ATZO05`),
INDEX `atbwdt` (`ATBWDT`),
INDEX `iATZO07_atad01` (`ATZO07`),
INDEX `iATZOTECHID_atad01` (`ATZOTECHID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=17721126

INSERT INTO `atad01` (`ATBWID`, `ATZO01`, `ATZO02`, `ATZO03`, `ATZO04`, `ATZO05`, `ATZO06`, `ATZO07`, `ATZO08`, `ATZO09`, `ATZO10`, `ATBART`, `ATBLGN`, `ATBPOS`, `ATCHGN`, `ATKDLI`, `ATBNCH`, `ATPBVB`, `ATBWMG`, `ATBWWT`, `ATBWDT`, `ATBWZT`, `ATCMT1`, `ATUPDT`, `ATUPZT`, `ATUPUS`, `ATUPBS`, `ATSRCE`, `ATBUID`, `ATSTOCKEVALUATIONTYPE`, `ATIGNORELOCK`, `ATPBVBLGR`, `ATCMTUSER`, `ATCMTERROR`, `ATWTVB`, `ATCONSHIST`, `ATZOTECHID`) VALUES (9, 1, NULL, NULL, NULL, 46432, 24, NULL, NULL, 529, NULL, 'STD', 'PICK529', 1, NULL, 0, 'LENS', 12, -1, 0.8100, 20120619, 110337, NULL, 20120619, 110337, 'user', 'OV-VM', 'BULK', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

At the moment we are still in the stage where we try to understand, what is blocking. So at the moment we would be glad for hints, what to check, when the statement hangs, to get more details about what is going on.

As so often: We were not able to reproduce this behavior on our test server, it happens on the live server only.

As mentioned above, the problem started last week. Before that, the statement never blocked before, and the table structures, database version, statements running on those tables did not change for many weeks. Just the size of atad01 is constantly growing (currently around 7GB and 16 million lines.

We double checked the free disk space for table space and temp files, both show many GB free space.

After stopping such a statement, it shows up in the slow query log. Here is an example of such an entry, where the statement took more than 300 seconds before it was stopped, but the lock time is close to 0.

1. Time: 190910 11:30:38
2. User@Host: OWStudio[OWStudio] @ [192.168.184.6]
3. Thread_id: 3381816 Schema: optovision QC_hit: No
4. Query_time: 308.844872 Lock_time: 0.000713 Rows_sent: 0 Rows_examined: 0
5. Rows_affected: 0
   SET timestamp=1568107838;
6. WE-PeriodenAbgrenzung
   insert into findispyear(fyperi,fytyp,fywert,fyMenge,fyupdt,fylgrx,fymat,fywareh)
   select f1peri as Peri,'22. + WE aus Vorperioden' as Art,round(sum(atbwmg*atbwwt),4) as WEWert,
   sum(atbwmg) as fyMenge,
   date_format(current_date, '%Y%m%d')as fyupdt,
   f1lgrx as LgRx,f1mat as fymat,atzo01 as fywareh
   from werh01
   join werd01 on wezo01=wercid
   join atad01 on weatid=atpkey
   join w_findispmon01 on f1whse=atzo01 and f1zo05=atzo05
   where weendperi=f1peri
   and atbwdt <(f1peri*100)+1
   group by art,Peri,f1lgrx,f1mat,atzo01

Any suggestions, how to continue?

Best regards,

Thomas Mischke