TYPO3 Core

In a MultiDomain Typo3-Environment with a higher number of pages, with Database Caching i regularly get this error message and the system slows down enormously. The Error is triggered by normal Website Frontend Visitors.

Core: Exception handler (WEB): Uncaught TYPO3 Exception: An exception occurred while executing 'DELETE FROM `cache_rootline_tags` WHERE `identifier` = ?' with params ["9979__0_0_0"]: Deadlock found when trying to get lock; try restarting transaction | Doctrine\DBAL\Exception\DeadlockException thrown in file /home/xxxxxxxxxx/typo3_src-11.5.17/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php in line 51. Requested URL: https://mydomain.com/samplepagexxxx

I know i should use Redis or similar in such cases, however this is not possible with this customer and a minor change fixes the problem and the system runs absolutely stable.

As i found out the problem is caused by optimized queries on mysql in the file below ... they cause this deadlock and do not terminate the process. If you deactivate these (&&1==2) everything runs perfect.
I would recommend to delete them completely - or at least set an additional Flag in the Cache Config that can be used here.

typo3/sysext/core/Classes/Cache/Backend/Typo3DatabaseBackend.php Line 290 (same in line 337 and 375)

 if ($this->isConnectionMysql($connection) && 1==2) { //Deactivated by 1==2
            // Use an optimized query on mysql ... don't use on your own
            // * ansi sql does not know about multi table delete
            // * doctrine query builder does not support join on delete()
            $connection->executeQuery(
                'DELETE tags2, cache1'
                . ' FROM ' . $this->tagsTable . ' AS tags1'
                . ' JOIN ' . $this->tagsTable . ' AS tags2 ON tags1.identifier = tags2.identifier'
                . ' JOIN ' . $this->cacheTable . ' AS cache1 ON tags1.identifier = cache1.identifier'
                . ' WHERE tags1.tag IN (' . implode(',', $quotedTagList) . ')'
            );
        } else {
            $queryBuilder = $connection->createQueryBuilder();
            $result = $queryBuilder->select('identifier')
                ->from($this->tagsTable)
                ->where('tag IN (' . implode(',', $quotedTagList) . ')')
                // group by is like DISTINCT and used here to suppress possible duplicate identifiers
                ->groupBy('identifier')
                ->executeQuery();
            $cacheEntryIdentifiers = $result->fetchFirstColumn();
            $quotedIdentifiers = $queryBuilder->createNamedParameter($cacheEntryIdentifiers, Connection::PARAM_STR_ARRAY);
            $queryBuilder->delete($this->cacheTable)
                ->where($queryBuilder->expr()->in('identifier', $quotedIdentifiers))
                ->executeStatement();
            $queryBuilder->delete($this->tagsTable)
                ->where($queryBuilder->expr()->in('identifier', $quotedIdentifiers))
                ->executeStatement();
        }
    }