Project

General

Profile

Actions

Bug #98617

open

Deadlock Error on Cache-Rootline Tags in Typo3 DatabaseBackend.php

Added by Christoph Runkel about 1 year ago. Updated 20 days ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
Caching
Target version:
Start date:
2022-10-14
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
11
PHP Version:
7.4
Tags:
Cache MYSQL Database
Complexity:
easy
Is Regression:
Sprint Focus:

Description

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();
}
}

Related issues 1 (0 open1 closed)

Related to TYPO3 Core - Bug #90518: DB problems with rootline cache: DELETE cf_cache_rootline double-JOIN with cf_cache_rootline_tags take excessively longResolved2020-02-24

Actions
Actions #1

Updated by Christian Kuhn about 1 year ago

  • Related to Bug #90518: DB problems with rootline cache: DELETE cf_cache_rootline double-JOIN with cf_cache_rootline_tags take excessively long added
Actions #2

Updated by Sybille Peters 20 days ago ยท Edited

Can you check if you problem has now been fixed? If yes, I think we should close.

The patch was applied to current main branch (v13) and v12. There is not yet a release for this.

Actions

Also available in: Atom PDF