I've been working on a distributed mmo cluster system, in which different business logic handle is distributed to different server.

In this system, I have a character server and a guild server. Character server owns all characters' base info, guild server owns all guilds' base info and all guild members' base info.

Both the character server and the guild server own character's level. When the character server updates a character's level, it first commits an update transaction to the database asynchronously. After the commit returns, the character server will publish a character-level-update message, the guild server will then subscribe the message and update the corresponding guild member's level.

My problem is, if the character server commits the update transaction, but crashes before the commit returns or before it publishes the character-level-update message, and then we restart the character server, characte level in the character sever will be the newest value, but guild member level in the guild server will still be the old value. Can anyone give any suggestion to solve this problem? Or is there anything wrong in my design? Thanks!

Here below is my cluster design:

Put both actions (increase the level in char server and guild server) into a single transaction. You can archive this by a two-phase commit. It is important, that you need to track your transaction state (use a persistent state tracking). If it fails during server shutdown, you either need to roll it back or to continue it once you restart the server .

Thanks to Ashaman73, but maybe there's some simpler solution?

You mentioned two-phase-commit, does that mean I should update two table (within a single transaction) ? The character table and guild member table ? But actually, I don't have a guild member table. I just have a guild table, with a key field (guild_id) and a blob field (guild_member_id_list). The field guild_member_id_list contains all members' id, that is character id. Any member info the guild server needs can be acheived from character server by the member id. So what I want is, when the character server changed some character's level, only one table needs to be updated, that is the character table. Then the character server publishes a specific type of message to told the newest character level to all other servers including the guild server, who are interested with character level and have already subscribed this type of message. Those subscriber servers just store the character level locally, they won't store the character level to any database table.

To make it more clear and simple, let's say I have a character server and a chat server. The chat server doesn't have to maintain any persistent data in any database table, everything it maintains is temporary and is just in its local RAM. What the chat server needs is the name, gender, and some other mutable states (e.g. level, battle state) of those characters who are in some specific chat channel. I wish all these character data the chat server needs would just come from the character server, not from any real database table, and the chat server doesn't have to persist any of these data to any database table. Yeah, it looks like some data replication mechanism.

So does my design I metioned above have any problem? I really can't find a simle and sutable solution now.

your problem is that you're storing the same data in two different databases on two different servers

Thanks to Norman Barrows, but maybe I didn't make my case clear. Actually, I have only one database shared by two or more different servers. The character server updates the character table in the database, while the guild server updates the guild table in the same database.

So, first: Why do you need character servers separate from guild servers? Is this actually an operational requirement, or are you just "planning ahead"?

Yes, the reason I separate character server from guild server is just "planning ahead", I'm just trying "different logic is distributed in different process/server".

If you can put characters and guilds into the same database, then that's a lot easier.

Actually, as I replied above to Norman Barrows, my characters and guilds are just in the same database. So how to to in a lot easier way?

Second: Why does the guild server need the character level at all?

Some logic validation in the guild server will depend on guild member level.

Can it fetch the character level from the source of truth (character server) when it's needed?

Yes, what I want is the guild server gets character level from the character server. But I want the character level "to be informed of/broadcasted/published passively by the character server to any other server including the guild server only when the character server changed it, and the guild server stores the newest level locally as temporary data in it's RAM" instead of "to be fetched proactively by the guild server from the character server whenever the guild server needs it", yes, "passively informed of", not "proactively fetched", because I think "passively informed of" will be simpler, more graceful and more effective. Besides that, the key is, in my logic handle design, the character server must first ensures the character-level-update transaction has already been committed sucessfully before it can inform of the newes character level to the guild server (and other servers). So my problem is, if the character server commits sucessfully, but crashes unfortunately before it informs, then the guild server (and other servers) won't know the newest character level, while the character level in the database already changed to the newest. Still worse, if we then restart the character server, it will reconstruct local memory data from database, then it will hold the newest character level, while the other servers still hold the old character level.