TYPO3 Core

We have the same thing from time to time as well (currently ignoring this as it happens very infrequently):

2020-01-08 05:09:49 0x7fd1c5543700
*** (1) TRANSACTION:
TRANSACTION 84974964, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 6789539, OS thread handle 140538938734336, query id 850188977 XXX YYY Sending data
DELETE tags2, cache1 FROM cf_cache_pages_tags AS tags1 JOIN cf_cache_pages_tags AS tags2 ON tags1.identifier = tags2.identifier JOIN cf_cache_pages AS cache1 ON tags1.identifier = cache1.identifier WHERE tags1.tag = 'redirects'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10979 page no 4 n bits 336 index cache_id of table `YYY`.`cf_cache_pages` trx id 84974964 lock_mode X waiting
Record lock, heap no 223 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 9; hex 726564697265637473; asc redirects;;
 1: len 4; hex 5e1555f4; asc ^ U ;;
 2: len 4; hex 000014bd; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 84974962, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
9 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 6789540, OS thread handle 140538935523072, query id 850188975 XXX YYY Sending data
DELETE tags2, cache1 FROM cf_cache_pages_tags AS tags1 JOIN cf_cache_pages_tags AS tags2 ON tags1.identifier = tags2.identifier JOIN cf_cache_pages AS cache1 ON tags1.identifier = cache1.identifier WHERE tags1.tag = 'redirects'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10979 page no 4 n bits 336 index cache_id of table `YYY`.`cf_cache_pages` trx id 84974962 lock_mode X
Record lock, heap no 223 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 9; hex 726564697265637473; asc redirects;;
 1: len 4; hex 5e1555f4; asc ^ U ;;
 2: len 4; hex 000014bd; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10982 page no 3 n bits 280 index PRIMARY of table `YYY`.`cf_cache_pages_tags` trx id 84974962 lock_mode X locks rec but not gap waiting
Record lock, heap no 191 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 000014bd; asc     ;;
 1: len 6; hex 000005107af5; asc     z ;;
 2: len 7; hex ec0000038f0110; asc        ;;
 3: len 9; hex 726564697265637473; asc redirects;;
 4: len 9; hex 726564697265637473; asc redirects;;

*** WE ROLL BACK TRANSACTION (1)

I think this is what is happening:
For some reason the redirects cache tag is being flushed when the redirect information needs to be cached again (e.g. cache has become stale or was deleted).
This flush is actually not necessary because there is always only one entire with this tag (as far as I can tell) which holds all the data necessary for routing redirects.

So when two requests hit the TYPO3 installation and the cache is not valid, both requests first flush the cache. Locking cf_cache_pages and cf_cache_pages_tags.
So simple solution would be to not flush at all but just write to the same key.

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/63729

Applied in changeset 94d6369bd3b30c4a6400f1850a1e9f1859c14b30.

problem still exists in 10.4.15