How many database connection should use in a MMO game server?

Question

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.

Answer 1

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.

Answer 2

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.