Bug #98617
closedDeadlock Error on Cache-Rootline Tags in Typo3 DatabaseBackend.php
0%
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();
}
}
Updated by Christian Kuhn about 2 years 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
Updated by Sybille Peters about 1 year ago · Edited
Can you check if you problem has now been fixed? If yes, I think we should close.
- related issue: https://forge.typo3.org/issues/90518
- patch (merged): https://review.typo3.org/c/Packages/TYPO3.CMS/+/78916
The patch was applied to current main branch (v13) and v12. There is not yet a release for this.
Updated by Riccardo De Contardi 8 months ago
- Status changed from New to Closed
- Target version deleted (
next-patchlevel)
I close this issue as solved on TYPO3 12 and 13; sorry but it will not be backported to version 11; as Stefan Bürk wrote on his comment on https://review.typo3.org/c/Packages/TYPO3.CMS/+/78916/8#message-4d3df1bbe62d63a573839123904ae1fae51e77e3
I will remove the 11.5 backport. The impact is a little risky. It's possible to use composer patches to backport it manually if needed.
If you think that this is the wrong decision please open a new issue with a reference to this one
Thank you