How to solve the limited resource pattern with two replicated db?
Hi everyone, I'm trying to understand if I can switch my application to a replicated environment with two master db.
I have an application that implements the limited resource patter, so it uses the read lock and read committed isolation to be sure just one user will take the resource.
For example, it manages some events with limited capacity, when you want to subscribe to the event, it has to lock the table while inserting your name so, the subscripters count is checked before the commit.
With a single database with InnoDB engine it works very well. I'm trying it with a master-master replication between two different servers and the lock simply doesn't work. Probabily it is not replicated.
So I'm wondering, how usually is solved the problem with a replicated environment? How can I be sure to not give the same "seat" to two subscribers just because they are quering different servers at the same time?