Read-Write phase lag in Databases with multiple clients.

Unity Engine
1

Hello! :smile:

I guess this is a known problem in informatic engineering, but I have not enough academic formation, so It would be great if you could give me some answers to this problem.

I have a MySQL database storing users profiles.

From server-side, there is a module which updates users profiles automatically (daily earnings, profit loss, etc.) in the Database, and it does it with top priority.

From client-side, online players can also modify their profiles while playing.

Now comes the problem. Let me explain:

When an user performs an action (variable updates), the variables related with that action are compared with the real DB variables. If they aren’t the same, then a variables update is sent to the client, letting him decide what to do with new variables, and nothing else. Otherwise, the variable update is performed and the Database modifies the desired values. But, if the server-side module modifies that set of variables just after the variable comparison and just before the variable update, then the final result is may not be the expected.

For example, if my character has 100$ and I spend it all, and between the Read-Write process the server side module automatically deducts another 100$ (because they have not been deducted yet), then I will have -100$ at the end of the process.

Anyone could indicate me a way to aviod this? :face_with_spiral_eyes:

PD: I’ve thought about making the Read-Write process in the same SQL query, beacuse (I guess) that different queries are not processed at the same time, aren’t they? :face_with_spiral_eyes:

2

So, you have two clients and each sends a request, and they both hit the same server logic which tries to deduct $100? The read-write process takes milliseconds, so I’m a bit surprised if you’re running into this issue. Does your server logic check to make sure there is enough money in the account before it deducts it?

3

It sounds like a classic race condition. Unfortunately, you should probably be looking into transactional MySQL backends like innodb. Usually you’ll place a lock on the player’s account value, check it, validate it, then update it, all in a single transaction. If it doesn’t work, you return an error. It is sort of opening a can of worms though, because it’s easy to mess up and end up with deadlocking instead. Good luck.

4

Actually, reading and updating data is never instant, isn’t it? Therefore, there is always the possibility of changing the read value by a new one before the update, so that update can be carried out on an unknown value.

On the other hand, as Tempest said, the Read-Write time takes milliseconds, and probably I should not consider the above problem. Anyway, to be 100% onto a sure, I would consider a final review in order to check if the variable value exceeds its range, by sheer chance.

Another noob question: Can I use If…Else, as arithmetic oprations in SQL queries? :o

Thanks!:slight_smile:

5

If written correctly this can easily be avoided.
Transactual databases queries among a few additional things were brought in place to overcome exactly this kind of limitation.

Problem here is that you will potentially not get away cheapsaking so you might have to license MSSQL or ORACLE at the end of the day if its for critical operations.

That or you need to do a PHP handling that queues up transactions and ensures that the same account is never getting hit by a new operation while one is still performing.

6

Both MySQL InnoDB and PostgreSQL support transactions and row level locking (if you are using MyISAM tables in MySQL, change immediately ito InnoDB tables). You can also use application level locks using MySQL’s GET_LOCK(). Something like this:

GET_LOCK(“user2110”, 5); //This creates an application lock named user2110 (application defined) with a timeout of 5 seconds.
Check if user has enough money.
UPDATE mytable SET money = (money - 100) WHERE userid = 2110; //Remove money from account
RELEASE_LOCK(“user2110”);

A second thread trying to do the same thing will block on it’s GET_LOCK statement until the first has released its lock.

Hope this helps.