search

rebus-org / Rebus.MySql

:bus: MySQL integration for Rebus
https://mookid.dk/category/rebus
Other
7 stars 6 forks source link

Change transaction isolation mode to REPEATABLE READ #20

Closed kendallb closed 3 years ago

kendallb commented 3 years ago

We have been having some issues in production where a single message ends up getting consumed at the same time by multiple threads, which should not happen. I have checked over the SQL code for the transport again and the only thing that makes sense to me is that somehow the first SELECT FOR UPDATE is not blocking correctly, and multiple end up getting through at once.

Reading through the different isolation levels with MySQL, I suspect the issue is using READ COMMITTED, so I have changed it to use the more restrictive REPEATABLE READ which is the only isolation mode we use for our code (ended up with that years ago) and it's also the default. Could be related to our Percona cluster, but either way I think this is a safer choice. We are monitoring with this change to see if it happens again. If so, we will need to implement a more explicit thread lock for that code (probably using my new semaphore code).

Rebus is MIT-licensed. The code submitted in this pull request needs to carry the MIT license too. By leaving this text in, I hereby acknowledge that the code submitted in the pull request has the MIT license and can be merged with the Rebus codebase.

mookid8000 commented 3 years ago

Good catch! It's out as Rebus.MySql 3.0.0-b6 on NuGet.org now

kendallb commented 3 years ago

Confirmed this appears to have resolved the issue for us in production as well.

mookid8000 commented 3 years ago

Alright, cool! 😎 Are we getting near a proper 3.0.0 release do you think?

kendallb commented 3 years ago

I am going to clean up the exclusive lock stuff so we can get that in there.