Project

General

Profile

Actions

Bug #98617

closed

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

Added by Christoph Runkel about 2 years ago. Updated 8 months ago.

Status:
Closed
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 longClosed2020-02-24

Actions
Actions #1

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
Actions #2

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.

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

Actions #3

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

Actions

Also available in: Atom PDF