Data Persistence Implementation

 
Post new topic   Reply to topic    mudlab.org Forum Index -> Coding
View previous topic :: View next topic  
Author Message
Stormy



Joined: 09 May 2007
Posts: 6
Location: Northern Virginia

PostPosted: Sat Jul 28, 2007 4:03 am    Post subject: Data Persistence Implementation Reply with quote

I'm writing a game in Ruby and using MySQL to store my data. What I would like to do, in a perfect world, is update data every time it changes -- such as update health after a player gets hit or his location with each move. I know that this design isn't very good, especially since, with npcs involved, there could be thousands of updates a second. So, I'm thinking about flagging objects as dirty and running a separate thread to cycle through the game objects and save the dirty ones.

My question, since I don't know a lot about DBMSs, is whether or not this will work well, considering that not all saved data is a game object (so I'd save them outside of the cycle) and I obviously need to be able to read data too. I can, of course, sleep the thread for 5 or 10 seconds between cycles, but that won't be a great solution if the database is locked-up for a noticeable amount of time every 5 or 10 seconds. Though, from what I think I know, the other query would jump in front of the next thread query, so I'm not sure if this would be an issue.

One solution, if the lockup is an issue, would be to pause/restart the cycle, but hopefully someone can tell me if that would be necessary or if there's an entirely different, better solution.

Thanks in advance for any help,
Stormy
Back to top
View user's profile Send private message
Author Message
chaos



Joined: 24 Aug 2007
Posts: 35
Location: New Jersey

PostPosted: Tue Aug 28, 2007 12:52 am    Post subject: Reply with quote

Some thoughts:

1) In general, the sort of model you describe should work well with MySQL MyISAM tables. If, for some reason, you wind up having a lot of problems with lock contention, try InnoDB tables, which use row locking rather than table locking. There are tradeoffs to using them, though, and I'd say try out MyISAM first.

2) It'd probably be productive to wrap your game-object and non-game-object entities that need to be synced to the database in a single abstraction of some kind so that they can both be processed by the same updating thread, rather than worrying about database updates coming from different parts of your design.

3) What will make the most difference to your locking issues, I believe, is whether you can mostly establish a model where any given row has exactly one object in your program that's responsible for interacting with it. What this does for you is remove the need to do the frequent SELECTs that locking would harm in the fashion you're concerned about. If, say, the user object for player Bob knows that row 27738 in the user table is Bob's data and only said user object is going to update it, then it only needs to do a SELECT to pull row 27738's data once, when it first loads. After that, it only needs to do UPDATEs to sync the database to its state, and they're off in a background thread, so that's great.

4) You might see delays, but you shouldn't see "lockup" in the course of normal operation; MySQL's locking model doesn't permit deadlock.
Back to top
View user's profile Send private message Visit poster's website
Author Message
shasarak



Joined: 29 Jun 2005
Posts: 134
Location: Emily's Shop

PostPosted: Thu Aug 30, 2007 2:30 pm    Post subject: Re: Data Persistence Implementation Reply with quote

Stormy wrote:
What I would like to do, in a perfect world, is update data every time it changes -- such as update health after a player gets hit or his location with each move. I know that this design isn't very good, especially since, with npcs involved, there could be thousands of updates a second. So, I'm thinking about flagging objects as dirty and running a separate thread to cycle through the game objects and save the dirty ones.

If I were you I would start to think about transactions. The way this works is that within any given operation, objects are tagged as dirty when changed, and all of the changes are committed at the end of the operation. This is slightly less intensive than updating after each change, because it works out as one query per object rather than one query per changed field (plus you can batch the queries). But the more important aspect has to do with stability and what happens if there's an error.

Suppose you have a sword of life-draining which drains energy from the creature that has been hit and transfers it to the wielder. Suppose that the wielder's energy is increased, but then the code errors and the target's energy is never decreased. Now you have a nasty bug: the sword wielder can hit something and gain infinite energy without harming his target.

Transactions help stop this from being quite such a bad problem. If the entire operation does not successfully complete then a well-designed transaction mechanism will roll back the entire transaction, right to the beginning of the operation. That means that the sword-wielder object will be detected as "dirty but impossible to commit" and will therefore be flushed out of cache and reloaded from the database the next time it is needed. That resets it back to the status it had before the weapon hit, and the impact of the bug is correspondingly reduced.

You could get a similar problem if the process of committing to the DB were to error in the middle of the transaction. Again, if it's done properly, the DB will be able to roll back all of the previous queries and restore the database to its state prior to the beginning of the transaction while (at the same time) the object cache flushes every object involved in the transaction and reloads from the DB.

(I don't know if MySQL supports transactions, but if it doesn't then it's crap and you shouldn't be using it. Smile I expect it does.)

Committing changes asynchronously creates all kinds of problems if something goes wrong in between an object being flagged as dirty and the actual commit. In the life-draining example, the changes to wielder and target might be committed 15 seconds apart; what happens if the database connection goes down during those 15 seconds? Or if some error happens in the MUD code? You could end up successfully committing the wielder's boost but not the target's drain.

In general, if any given operation completes successfully, all changes relating to that operation should be committed before you go on to do anything else.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    mudlab.org Forum Index -> Coding All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2002 phpBB Group
BBTech Template by © 2003-04 MDesign