Symfony version(s) affected

5.4.11

Description

I believe this is not a completely new issue. See #41541 for an older issue that reported the same problem.

We are using Symfony Messenger on top of a MariaDB 10.5.13 table, using only one queue that regularly holds several dozen to a couple hundred messages with the same available_at date, and around 20 consumers/workers that listen on this queue in parallel.

We thus have a high level of parallelization when the available_at time comes (which is once per hour).

This always results in a couple of "An exception occurred while executing 'DELETE FROM symfony_messages WHERE delivered_at = ?' with params ["9999-12-31"]: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction" exceptions.

The solution for the previously reported problem was to not set indices on table fields queue_name and available_at - see #42345.

However, this was later taken back by #45888, which instead uses a "soft-delete" approach to avoid deadlocks.

Alas, I have to report that deadlocks still occur when multiple consumers consume the same queue in parallel.

How to reproduce

The reproducer from #41541 should do the job.

Possible Solution

No response

Additional Context

No response