Running into a fun scenario where I have many servers running and I am doing my best to route them via IPC but finding this may allow double read before a necessary write takes place. This in turn may give me false counts/values.

I'm looking to setup a database or some type of solution that I can do exactly:

I read database for available server based on lowest counts. The server I pick I update its counts by 1 all in one action. I could have 100s of requests coming in over web socket and need this count accurate for each request to not over-load a server.

My fear is two workers perform the same request at the same time, I'd want one to finish their read/write first before the other but not slow other requests down.

Is this do-able with MySQL or is there a faster solution? I think I need ACID compliant.

CodePudding user response：

Yes, use MySQL (or other database) for common storage between any number of clients. Use "transactions" to keep the clients from stepping on each other:

START TRANSACTION;
do several reads/writes with database, but don't take too long
COMMIT;

When doing reads, use this if you might be changing the rows fetched:

SELECT ... FOR UPDATE;

That way you effectively prevent other threads from messing with the rows before you get to a subsequent UPDATE or DELETE.