Symfony version(s) affected

5.4.25

Description

We are using doctrine-messenger in our project and have noticed a DB lock on the messenger_messages if we have many failed messages due to this query: SELECT m.* FROM messenger_messages m WHERE (m.delivered_at is null OR m.delivered_at < '2023-10-24 07:36:16') AND (m.available_at <= '2023-10-24 08:36:16') AND (m.queue_name = 'default') ORDER BY available_at ASC LIMIT 1 and m.delivered_at is set to null when a message is moved to the failed queue.

How to reproduce

Messenger Config:
framework:
messenger:
# Uncomment this (and the failed transport below) to send failed messages to this transport for later handling.
failure_transport: failed
reset_on_message: true

    transports:
        # https://symfony.com/doc/current/messenger.html#transport-configuration
        async:
            dsn: 'doctrine://default'
            retry_strategy:
                max_retries: 5
                delay: 60000

        failed: 'doctrine://default?queue_name=failed'
        sync: 'sync://'

    routing:
        # Route your messages to the transports
        'App\Core\Message\AsyncMessageInterface': async

Failed data:

Query:
SELECT m.* FROM messenger_messages m WHERE (m.delivered_at is null OR m.delivered_at < '2023-10-24 07:36:16') AND (m.available_at <= '2023-10-24 08:36:16') AND (m.queue_name = 'default') ORDER BY available_at ASC LIMIT 1

Notice the query will first include all with m.delivered_at is null which will include all in the failed queue. Even if it does not always cause a DB lock on that table, it makes the query unnecessarily slow.

Possible Solution

Move the queue_name to higher in the where clause.

Additional Context

No response