MySQL temporary table hung in system lock

[Biju Philips]

We have a data warehouse load process in MySQL 8.0.39 which gets hung in system lock while creating and loading temporary table. If we stop it and rerun it finishes in under a minute, otherwise it will be hung for hours till you kill the thread. We checked all the configurations and everything seems to be ok. Anyone any idea what could be causing this, what is it waiting for? There is no other process running on the server, the server is not out of any resource.

Create Temporary Table temp_svc AS
SELECT eps.EpisodeID,
eps.BusinessUnitID,
eps.PlanGroupID,
eph.EffectiveBeginDate,
eph.EffectiveEndDate
From stg_EpisodePropertiesHistory eph
INNER JOIN stg_EpisodeServices eps
ON eps.EpisodePropertySetID = eph.EpisodePropertySetID
AND eps.BusinessUnitID = eph.BusinessUnitID
And eps.ServiceTypeID = eph.ServiceTypeID
AND eph.IsInvalid = 0
Where eps.GroupResponsibilityLevel = 'Primary';
Tried checking all the configs, indexes, stopped all other jobs, no change.

Table: stg_EpisodePropertiesHistory
PRIMARY KEY (`BI_ID`),
KEY `IX_stg_EpisodePropertiesHistory_BusinessUnitID` (`BusinessUnitID`),
KEY `IX_stg_EpisodePropertiesHistory_EpisodeID` (`EpisodeID`),
KEY `IX_stg_EpisodePropertiesHistory_EPSID_BID` (`EpisodePropertySetID`,`BusinessUnitID`,`ServiceTypeID`,`IsInValid`,`EffectiveBeginDate`,`EffectiveEndDate`)

Table: stg_EpisodeServices
PRIMARY KEY (`BI_ID`),
KEY `IX_stg_EpisodeServices_BusinessUnitID` (`BusinessUnitID`),
KEY `IX_stg_EpisodeServices_EpisodeID` (`EpisodeID`),
KEY `IX_stg_EpisodeServices_EpisodePropertySetID` (`EpisodePropertySetID`),
KEY `IX_stg_EpisodeServices_ServiceTypeID` (`ServiceTypeID`)