Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

[Messenger] Remove indices in messenger table on MySQL to prevent deadlocks while removing messages when running multiple consumers#42345

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Conversation

@jeroennoten
Copy link
Contributor

@jeroennotenjeroennoten commentedAug 2, 2021•
edited
Loading

SELECT ... FOR UPDATE locks rows but also relevant indices. Since locking rows and indices is not one atomic operation,
this might cause deadlocks when running multiple workers. Removing indices on queue_name and available_at
resolves this problem.

QA
Branch?4.4
Bug fix?yes
New feature?no
Deprecations?no
TicketsFix#41541#39041
LicenseMIT

Using Doctrine transport with multiple consumers occasionally results in MySQL deadlocks while removing a message from the messages database table.

This can be reproduced consistently by setting up a defaultasync queue with the Doctrine transport and creating an emptyTestMessage andTestMessageHandler. Create a command that dispatches 10000 of these messages in a for loop en start 4 message consumers. After a while, several consumers report a deadlock:

In Connection.php line 227:                                                                                                                     [Symfony\Component\Messenger\Exception\TransportException]                                                         An exception occurred while executing 'DELETE FROM messenger_messages WHERE id = ?' with params ["32903"]:                                                                                                                            SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction                                                                                                                     Exception trace:  at /var/www/vendor/symfony/messenger/Transport/Doctrine/Connection.php:227 Symfony\Component\Messenger\Transport\Doctrine\Connection->ack() at /var/www/vendor/symfony/messenger/Transport/Doctrine/DoctrineReceiver.php:79 Symfony\Component\Messenger\Transport\Doctrine\DoctrineReceiver->ack() at /var/www/vendor/symfony/messenger/Transport/Doctrine/DoctrineTransport.php:50 Symfony\Component\Messenger\Transport\Doctrine\DoctrineTransport->ack() at /var/www/vendor/symfony/messenger/Worker.php:150 Symfony\Component\Messenger\Worker->handleMessage() at /var/www/vendor/symfony/messenger/Worker.php:81 Symfony\Component\Messenger\Worker->run() at /var/www/vendor/symfony/messenger/Command/ConsumeMessagesCommand.php:202 Symfony\Component\Messenger\Command\ConsumeMessagesCommand->execute() at /var/www/vendor/symfony/console/Command/Command.php:255 Symfony\Component\Console\Command\Command->run() at /var/www/vendor/symfony/console/Application.php:1027 Symfony\Component\Console\Application->doRunCommand() at /var/www/vendor/symfony/framework-bundle/Console/Application.php:97 Symfony\Bundle\FrameworkBundle\Console\Application->doRunCommand() at /var/www/vendor/symfony/console/Application.php:273 Symfony\Component\Console\Application->doRun() at /var/www/vendor/symfony/framework-bundle/Console/Application.php:83 Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at /var/www/vendor/symfony/console/Application.php:149 Symfony\Component\Console\Application->run() at /var/www/bin/console:42

A similar problem with Laravel's queue worker (and a solution) is reported here:laravel/framework#31660

The solution is to remove indices on thequeue_name andavailable_at columns. After removing these indices, I could not reproduce the issue anymore. Also, I did not notice any performance degradations.

okwinza, danielkoster, CoalaJoe, bobvandevijver, huynguyen93, chm0815, tinpansoul, and javierdlahoz reacted with thumbs up emojipatrickhilker reacted with hooray emojixfifix reacted with heart emojipatrickhilker reacted with rocket emoji
@fabpot
Copy link
Member

IIUC, this issue only exists on MySQL. It this is the case, the change should only be done for MySQL and not for all database engines.

@jeroennotenjeroennotenforce-pushed thefeature/remove-index-from-messenger-database-table branch 3 times, most recently from790721a toee0c97bCompareAugust 26, 2021 10:30
@jeroennoten
Copy link
ContributorAuthor

@fabpot, you are right. To be sure, I tested with PostgreSQL and I could not reproduce the issues there.

So I changed this to only apply to MySQL. I also added a unit test for these changes.

@jeroennotenjeroennoten changed the title[Messenger] Remove indices in messenger table to prevent deadlocks while removing messages when running multiple consumers[Messenger] Remove indices in messenger table on MySQL to prevent deadlocks while removing messages when running multiple consumersAug 26, 2021
@jeroennotenjeroennotenforce-pushed thefeature/remove-index-from-messenger-database-table branch 2 times, most recently from364079e tof7cc0b1CompareAugust 26, 2021 10:55
… removing messages when running multiple consumersSELECT ... FOR UPDATE row locks also locks indices. Since locking rows and indices is not one atomic operation,this might cause deadlocks when running multiple workers. Removing indices on queue_name and available_atresolves this problem.
@jeroennotenjeroennotenforce-pushed thefeature/remove-index-from-messenger-database-table branch fromf7cc0b1 to8c3c0a3CompareAugust 26, 2021 10:55
@fabpot
Copy link
Member

Thank you@jeroennoten.

@fabpotfabpot merged commit7080940 intosymfony:4.4Aug 26, 2021
This was referencedAug 30, 2021
@bobvandevijver
Copy link
Contributor

Thank you@jeroennoten, I just saw this exact error in production for the very first time this weekend. Very nice to see it has already been resolved!

@jeroennoten
Copy link
ContributorAuthor

@bobvandevijver, you're welcome!

Don't forget to runbin/console messenger:setup-transports after updating Symfony to the latest version to apply these changes to the database. The command will update the existing table or create a new one if it does not exist.

@jeroennotenjeroennoten deleted the feature/remove-index-from-messenger-database-table branchAugust 30, 2021 15:16
@bobvandevijver
Copy link
Contributor

We're using migrations to define the table, but thanks for the tip!

@palttamas
Copy link

palttamas commentedSep 3, 2021•
edited
Loading

@jeroennoten removing the indexes fixes the lock issue but caused us significant performance issues. I was searching the issues and it seems to me that multiple consumers are not working very well for others either, be it redis or doctrine transport, no matter.

oleg-andreyev reacted with thumbs up emoji

@nicolas-grekas
Copy link
Member

I'm proposing to revert this PR and replace it by another approach in#45888

nicolas-grekas added a commit that referenced this pull requestMar 31, 2022
…ks using soft-delete (nicolas-grekas)This PR was merged into the 4.4 branch.Discussion----------[Messenger] Add mysql indexes back and work around deadlocks using soft-delete| Q             | A| ------------- | ---| Branch?       | 4.4| Bug fix?      | yes| New feature?  | no| Deprecations? | no| Tickets       |Fix#42868| License       | MIT| Doc PR        | -#42345 removed some indexes because they create too many deadlocks when running many concurrent consumers.Yet, as reported in the linked issue, those indexes are useful when processing large queues (typically the failed messages queue).#45007 is proposing to add an option to force the indexes back, but I don't like it because it requires ppl to learn about the issue. I looked for a more seamless solution and here is my proposal.Instead of possibly triggering the deadlock during `ack()`/`reject()`, I propose to use a soft-delete there, and do the real delete in `get()`. This makes ack/reject safe because they don't alter any indexes anymore (the delete was), and this moves deadlock exceptions to the same function that creates the locks. This allows the retry mechanism in `DoctrineReceiver` to recover from at most 3 consecutive deadlock exceptions. There can be more, and in this case, the consumer will stop. But this should be much less likely.(yes, I did create a reproducer to work on this issue ;) )Commits-------12271a4 [Messenger] Add mysql indexes back and work around deadlocks using soft-delete
@dipa-damcodipa-damco mentioned this pull requestAug 4, 2023
Sign up for freeto join this conversation on GitHub. Already have an account?Sign in to comment

Reviewers

@fabpotfabpotfabpot approved these changes

@srozesrozeAwaiting requested review from sroze

@lyrixxlyrixxAwaiting requested review from lyrixx

Assignees

No one assigned

Projects

None yet

Milestone

4.4

Development

Successfully merging this pull request may close these issues.

6 participants

@jeroennoten@fabpot@bobvandevijver@palttamas@nicolas-grekas@carsonbot
[8]ページ先頭
©2009-2025 Movatter.jp