multi-master vs ha-datasource
I run a 2 node jboss 4.2.3 cluster on top of a 2 node mysql master-master replication.
Each jboss instance only talks to its local mysql.
I learned that replication eventually breaks if both jboss instances write simultaneously, due to the asynchronous nature of replication: this brings the databases out of synch in such a way that i need to resolve a conflict by hand.
Example: both instances insert a record with the same value in a column with a "unique" constraint: if it happens at the same time, it commits on each side, then replicates to the other side, where it rolls back due to the constraint.
To prevent such a situation from happening, i want to make sure that both jboss instances use one single "master mysql node" for performing sql-updates. The ha-datasource configuration does not give me such option.
My server application happens to be writing only as a consequence of remote java clients invoking particular update-methods. So, if i could make all clients go to the coordinator-node for doing update-methods, i'm safe.
(1) I did not find a load balancing strategy capable of preferring the coordinator for updates (i found round-robin, first-available, and first-available-identical-all-proxies). Perhaps i can make this myself somehow? I'ld need some active way of tracking the coordinator node in each client. Hints?
(2) Another brute force but simple work-around to this could be to configure SET GLOBAL read_only=1 on all mysql nodes, except the one under the jboss coordinator. If a client would do an update on a non-coordinator node, the database would refuse it and throw an exception "readonly". If i could have a client proxy that understands this situation and "retries" on the next jboss instance, i'm happy: eventually we'ld reach the coordinator, where it would commit. So i'm looking for the kind of exception that would trigger this client-proxy-failover-attempt (which is happening when using @Clustered and shutting down a node).
(3) Another idea was to not use ha lookup:1100, but have my client code take control over which jboss instance it invokes, using the lookup:1099 on each node. Then, if i could track somehow which node is the currently coordinator, i'ld be able to send my update-method-invocations to the right one.