Take the 2-minute tour ×
Game Development Stack Exchange is a question and answer site for professional and independent game developers. It's 100% free, no registration required.

Game server links to database, how many database connection should use? The data update works like this: update role object to database every X minutes, commit all updates every Y minutes.

There are 3 ways:

  1. Only one database connection
  2. One worker thread one database connection and set transaction level to "Read Uncommitted". Worker thread loop all roles in it to do update & commit periodically
  3. One game session one database connection and set transaction level to "Read Uncommitted". A dedicated thread to loop all connections to do update & commit periodically

Method 1 has a problem which database connection object needs shared by multiple worker threads, for JDBC, connection class is thread safe, but I don't know what about C++ such as MySql driver. If it have to use mutex to make thread safe, there will be a performance degrade: all worker threads have to synchronize their data frequently.

Method 2 has a problem to update old data. One map in game server run in dedicated worker thread. For example, role A in map 1 which compute by thread 1. role A transport to map 2 which compute by thread 2. This might cause thread 1's commit call after thread 2's commit call, so the older update which already executed in thread 1 commit after thread 2's commit.

Method 3 seems is a waste of system resource. Because in MMO there is no need to be transaction isolation, any change in a game session should be immediately seen by other sessions. In contrast with web app, so many connections has litte meaning. However the "Read Uncommitted" isolation level can let other session read data which has not committed yet.

==================================

I forget there is a thing called connection pool. Generally it is thread safe implemented so I do not need to care synchronization problem anymore. And the connection number is dynamic so I can leave problem of how many connection number to it. Only I do is get a connection from it. So, method 1 & 3 both practical.

share|improve this question
1  
"Because in MMO there is no need to be transaction isolation" - you shouldn't be so sure about that. Think about trading, for example. When trade operations aren't transactional you could have an exploit which allows players to duplicate items and money when they get the timing right. –  Philipp Jan 24 '13 at 12:09
    
What you said is a typical scenario of bank: two machine access one database, each one have a transaction. The later committed transaction might found the data which it assumed was changed by forer transaction committed by another machine. Finally, database report consistency is broken, so later commit is failed and rollback. But this won't happen in MMO server. And if do player's trade to be transactional, what will be too complex –  jean Jan 24 '13 at 12:36
    
It does happen on MMO servers actually, which is why player trade is often one of the few parts of the system which is protected by transactions. –  Kylotan Jan 24 '13 at 17:49
    
This question could (should) be generalized and posted on a Q&A forum for database discussion –  Tim Holt Jan 24 '13 at 18:09

2 Answers 2

up vote 2 down vote accepted

I'm not sure how much of this is MMO dependent.

If you just have one connection and share it across threads, then maybe that would hold up your app when you lock access to that connection - but if you're making so many writes to an SQL database that this is a concern for your MMO, you're probably making too many writes anyway.

Whether a 'read uncommitted' approach will work for you depends a lot on how your application works. Often in an MMO you're never reading 'live' data anyway - you read once at the start to get it into memory and then it's write only until that data is unloaded from the simulation (eg. when a player logs out). But if you're doing explicit reads, and you have multiple possible writers to that data, you're in for data mismatches this way.

Personally, I would always stick with ensuring there is only ever one writer, and if I need to read live data during a session, I would try and use only one reader too, in the same thread and connection as the writer. This ensures consistency. In the parts of your application that do not need consistency, eg. read-only displays, they can use separate connections and threads.

share|improve this answer
    
Voting up because I agree with your first sentence - this isn't an MMO/Game question, it's a database question. –  Tim Holt Jan 24 '13 at 18:26
    
As you said MMO server read data from memory, so it is impossible to happen a commit fail except you do it in purpose or hardware failure. The data in memory is highly consistent because no other machine share it. In case of a hardware failure, there is log db which can used to compensate. –  jean Jan 25 '13 at 2:22
    
The only reason you'd write is because you've just changed something in memory, and 2 sequential changes in memory could get reordered if committed concurrently, resulting in a rollback or even the wrong value stored. I have even seen this happen in practice. –  Kylotan Jan 25 '13 at 2:44
    
I can imagine "reorder" like this: player upgrade a weapon and destory it soon afterwards. If "update weapon" be reordered with "destory weapon", this indeed is a commit fail. But how it happen? And what should do when a commit fail happen? Kick players offline and clear all memory data? I think this is a bug of server however it can be protected by transaction to "make data right". –  jean Jan 25 '13 at 3:00
    
If you have 2 threads both writing to one database, this problem can happen at any time, and transactions won't help. As I wrote above, I suggest ensuring you only ever have 1 writer, and if you need consistent reads, only 1 reader too. –  Kylotan Jan 25 '13 at 13:41

Since you're talking about JDBC I'm assuming you're using an SQL database.

First of all, even though the JDBC specs theoretically state that all JDBC driver implementations should be thread safe, it doesn't really go into much detail, instead it even states that it's ok if said "thread safety" is achieved by serializing all concurrent queries which may lead to a major bog down in performance. If you chose to use JDBC, try using it via a wrapper library that creates a configurable JDBC connection pool. There's tons of those, check those from Apache for example.

As far as connecting to SQL databases using drivers created for other languages (like C++) I don't even think there's any spec guaranteeing thread safety at all, so again, unless you find a connection pool library you shouldn't use them.

What you can also do is try one of the NoSQL databases. They're made for dealing with large quantities of data and can prove in some cases to be faster than SQL databases. (Not always mind you, but in the context of a MMO this may be the case). I recommend Mongo DB. They have drivers for all major languages (Java and C++ included) and the driver natively supports connection pooling so you don't have to bother with that at all. There's a learning curve, especially for assimilating the Mongo DB equivalent of SQL transactions and such, but it could prove fitting to your need.

share|improve this answer
    
I am currently developing an MMORPG and use MongoDB as database backend and so far my experiences are positive. Its schemaless nature gives you a lot of agility during development because you can easily add, change and remove fields without having to run any ALTER TABLE commands on the database. In my last online game project where we had an SQL database we frequently had to write update scripts to port the database and its content to a new version. I never had need for that in my current project which uses MongoDB. –  Philipp Jan 24 '13 at 12:22
    
I am using mongodb for a while, it's non-transaction manner is convenient for game. It can be done by use only one volatile collection object between theads. But I want to know, how to do data presistence by a SQL database? –  jean Jan 24 '13 at 12:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.