Asynchronous Database Access

From Arianne
Jump to: navigation, search


At the moment marauroa is very vulnerable to database lag: If the database blocks a query or update statement for more than 200ms, it is noticeable as lag in game. More than a couple of seconds and all players are disconnected. This basically means that the game has to be taken offline for any kind of database maintenance. Even if there is no database maintenance going on, heavy disk io can cause database lag. Another common source of lag is the handling of logins.

Kinds of database operations

Most of the database operations in marauroa are write only, making it easy to put them into a queue for asynchronous processing. Read operations are more difficult to handle because the data returned by them is needed. That is obviously the reason why it is read in the first place.

There are three kinds of read operations:

  1. Read operations performed at server start.
  2. Read operations performed on login of players (including account verification and loading the selected character)
  3. Read operations performed in context of a write operations (after an insert into one table, the row id must be read in order to make an insert into another table)

Strategies to handle waiting for required data

  1. I think we can ignore this issue, and just hang on server start until the database is responsive again. It is now possible to add zones while the game is running so the approach described in the next section could be used for zones, too.
  2. When a player logs in, the account has to be verified, then a list of the characters is read and finally the RPObject associated with the selected character has to be loaded. All these operations can be done asynchronously so that only this player has to wait for the database but the game world can continue to progress.
  3. This basically means that we cannot simply delay pre-generated SQL-commands but have to enqueue smart objects with logic.

Mixing of Synchronous and Asynchronous database access

Synchronous and Asynchronous database can be mixed in general, which makes migration easier.

Data that is written to a set of tables asynchronously, however, has to be read asynchronously, too. So for example if saving RPObjects (characters) is delayed, requests for loading of RPObjects has to be put into the same queue to ensure those operations are executed in the correct order. But reading the account information to verify the password can be done synchronously.

Freezing the state vs. life objects

At the time the write operation is triggered the code has to decide whether the objects to be written should be copied or the original should be used. In most cases it seems to be a good idea to make a copy so that there are no problems with co-modifications. In the case of the itemlog, this is even a requirement because we may lose modifications of it done in the mean time. In some cases, however, it is required to keep an additional reference to the original object in order to save the database id of a newly created row there.


Marauroa has a DBCommandQueue, which is singleton and has a background thread to execute enqueued DBCommands. DBCommand is an interface that specifies an execute()-method, that processed the database request by invoking the DAO-classes. It is called from the background thread of DBCommandQueue.

The following example for a simple write operation assumes that GameEvent implements/extends DBCommand:

    new GameEvent(attacker.getName(),
    "attack", target.getName());

Note that this example works for case 3), too.

Since we ignore 1) for now, this leaves us with 2): We need a way to receive the data produced by an read operation:

UUID uuid = DBCommandQueue.get().enqueueAndAwaitResult(
    this, new GetCharacterList(username));

The DBCommandQueue will process the GetCharacterList at some future time in the background thread. It will then put the GetCharacterList which now contains the results from the database in a second queue ("waiting to be fetched by program"). Here comes the tricky part: We need a way to notify the program in the thread that requested the operation about the results. The easiest way is to remember the requesting thread by Thread.getCurrentThread() and provide a List<DBCommand> getResults() method, which fetches all results of Commands enqueued by the current thread.

We had to decide here whether we wanted an interrupt or polling based approach: An interrupt based approach would then distribute the results to various program parts of the current thread using an interface DBCommandResultReceiver<T extends DBCommand>, which is implemented by those classes who requested the data. We chose a a polling based approach. Here, the program parts look for specific results. The program part that sends the character list back to the client calls:

List<GetCharacterList> DBCommandQueue.get().getResults(this).