Twitter  Facebook  YouTube  E-Mail  RSS
The One Man MMO Project
The story of a lone developer's quest to build an online world :: MMO programming, design, and industry commentary
Recording Microtransactions for an MMO
By Robert Basler on 2012-01-11 18:59:01
Homepage: www.onemanmmo.com email:one at onemanmmo dot com

I've been playing Guild Wars quite a bit over the Christmas vacation. I have to admire their business model. The simplicity of it is wonderful. Sell the game, sell some account-wide addons in a secure, off-the-shelf online store, and you're done. It's a shame it's an impossible business model for an indie. The cost of selling that first package is simply too high a barrier without a huge advertising budget.

With my requirement for in-game microtransactions I spent a lot of time over Christmas thinking about how to manage and record those transactions.

My design criteria ended up being:

  • Transactions must be resistant to programming error.
  • Every transaction must record information about who is involved in the transaction should there ever be any problems with criminal activity.
  • Every transaction must be recorded in sufficient detail to be reversable. If there's fraud, I want to be able to run back the clock.
  • Transactions that the user has seen the result of must survive a server crash.
  • The system must be secure - it is recording real money transactions.

I've done a fair amount of accounting over the years for my various small businesses, so my first thought was to look into how commercial accounting software systems operate. I spent a bit of time googling around and with the help of Stack Overflow I was able to find a specification document for a General Ledger which gave me the basic table design for a double-entry accounting system.

I never could have predicted in a million years that my little indie game would need me to develop a double-entry accounting system.

Double-Entry Accounting

The idea of double-entry accounting is that you keep track of everything in accounts. Each account is either a Debit or a Credit account. If you sum up all the debit accounts, and then sum up all of the credit accounts, the totals must match. If they don't - you've made an error.

Each transaction you do - buy a buff in the store, purchase some in-game currency, whatever - updates at least two accounts. Each transaction must have at least one debit and one credit entry and unsurprisingly, when you sum the debit entries and the credit entries of a transaction, the sums must be equal. Accounts work like this:

  • If you debit a debit account, its value increases.
  • If you credit a debit account, its value decreases.
  • If you debit a credit account, its value decreases.
  • If you credit a credit account, its value increases.

Double-Entry Economy

The beauty of this system is that you can put your game's entire economy in it. Earned currency, purchased currency, the player's inventory, in-game vendor inventories, in-game entities, everything. A sample game economy might look like this:

economy.jpg

The first five entries are the total size of the game economy. The next five are the available items - the stuff the game company owns that is not allocated to vendor stores or players accounts. The next five are the accounts for a single player, or an NPC vendor. There would be a whole bunch of these sets. The last five let you record how many of every type of game item have been used by all of the players of the game which is interesting from a statistical point of view.

In general, items move from the available accounts, to the player accounts, to the spent/destroyed/used accounts.

This type of system is ruthless about pointing out errors. At the end of the day, if it doesn't balance, something has gone horribly wrong. Now to be clear, you can still make an error with a double-entry transaction. If a transaction updates the wrong accounts or has the wrong amounts, but its debits and credits still balance, the system won't detect the error.

Where my design diverges from conventional accounting is that the values in all the accounts aren't the same currency. There are a whole bunch of "currencies" - one for each type of item you're tracking with the system. I have a "currency" for purchased currency, one for earned currency, and one for every type of in-game item you can buy. To put the entire game economy in a double-entry system, I have to do one extra step in each transaction, which is to make sure that the account we are doing a debit or credit on uses the same "currency" as the transaction entry. If the "currency" mismatches, it is flagged as an error. To implement this I have four account types: Earned $, Purchased $, Entities and Buffs. Buff is actually a whole bunch of accounts - one for each type of buff, and each buff account lists the ID of the buff in the account, and that is also compared to determine if the account types match.

Every transaction in the game is recorded as a transaction record with overall information about the transaction, plus a bunch of debit and credit entry records with updates for individual accounts. So to buy a buff for example you would:

  • Debit the Available account for the buff by 1.
  • Credit the Player's account for the buff by 1 to increase their inventory of the buff.
  • Debit the Players Earned $ account by 5.
  • Credit the Spent Earned $ account by 5.

Database Stored Procedures and Transactions

One of the major goals of all this was to make it so that if the user has seen the result of a transaction, such as purchasing a new item from the store, that even if the server crashes, the transaction is recorded in the database. Now I'm lucky in that I usually have lots of time to record transactions, so a slow database isn't going to hang up the player's in-game responsiveness.

The other major goal was that transactions were recorded entirely or not at all. To get the all-or-nothing behaviour out of the database I'm using MySQL's START TRANSACTION and COMMIT/ROLLBACK functionality to either fully commit or fully reverse every transaction. I've written a little stored procedure which I call with all the data needed to write out the transaction and update all of the accounts it effects. Because it is all embedded in one procedure, I can do that pretty quickly. To use the transaction features, I had to add support for creating InnoDb tables, as transactions are not supported in the default MyISAM tables. In MySQL 5.5 InnoDb is the default so this step isn't necessary anymore. I get a result back from my procedure that tells if it committed or not (using user-defined variables which are helpfully stored per-session) so to do a transaction the C side SQL looks like this:

SET @result = 0;
CALL do_transaction( @result, bunch, of, other, parameters );
SELECT @result;

One thing that is important about the design of the changes recorded in the transactions is that they are all relative, not absolute changes, so I have set mBalance = mBalance + 10 not set mBalance = 20 in my procedure. That way we don't need to read and write rows manually to update them and worry about someone else updating an account while we're updating it. The only danger is that we might run an account out-of-range, however there is only one game server updating each user's accounts so the server can keep an eye on those, and I don't really care if the system accounts go out of range. If the account database thinks the economy has $-1,000,000, that isn't really an issue, I can just add more $ when I have time.

To guarantee the correctness of the system, we need to validate the transactions produced by the game servers. Each transaction is validated before it is sent to the MySQL procedure. If the transaction doesn't balance, it is thrown out as an error, but a programmer needs to go and investigate the cause (a bug.) I also have a function that can check that the entire economy is balanced by doing a SUM on all the debit accounts, then a SUM on all the credit accounts - so that's a little slow.

To make bug-tracking easier, I added a four character code parameter to each transaction so that I can match each transaction to the lines of code that created it. This is much more efficient than storing the file name and line of code.

A tricky aspect of writing the procedure to write the transactions was that I wanted to write several new transaction rows to the database in my procedure, but procedures don't support any sort of variable arguments feature. Instead I ran across this handy little SPLIT function from Stack Overflow. With SPLIT, I can send a single string parameter with all the parameters for the variable number of debit/credit entries. Performance on this is going to suck a bit, but what are you gonna do?

One thing I found kind of troublesome is doing SELECT operations in stored procedures from C++ code. It turns out that the MySQL API does a call to check the status of any CALL operation which means you need to enable CLIENT_MULTI_RESULTS when you do mysql_real_connect() and then process multiple sets of results via mysql_next_result(). I didn't want to redo my database system to support this, so my SELECT operations are done from the C++ code. Luckily, reads don't need to be as secure as writes.

Security

I'm still figuring out some of the security issues, but because the system is designed to manage in-game money, security needs to be taken seriously. The accounting database should be separate from other databases and hardened as much as possible (just like the user account database.) One interesting method I came across is to restrict access to these tables and make it so all updates to the tables are done through stored procedures. I'm using stored procedures, now to go read up on MySQL security again.

Summary

I am completely sold on MySQL procedures, particularly for insertions where they greatly reduce the amount of data that needs to be sent to the server. The security aspects are nice as well.

The performance of the MySQL server will be critical, and is the only part of this design that worries me. All the other systems I've developed so far can be cleanly distributed over many servers, this one can't. Most users will produce many transaction records in a minute so there is going to be a whole lot of data pouring into the database. Fortunately only the accounts need to be maintained in the live environment, transaction records can be siphoned off to archival storage once they are written.

I've got one little detail to finish, then I'll be moving on... to audio I believe. I found this cool website soundrangers.com which has some really nice music which is affordable, as well as a million sound effects - also affordable.

New Comment

Cookie Warning

We were unable to retrieve our cookie from your web browser. If pressing F5 once to reload this page does not get rid of this message, please read this to learn more.

You will not be able to post until you resolve this problem.

Comment (You can use HTML, but please double-check web link URLs and HTML tags!)
Your Name
Homepage (optional, don't include http://)
Email (optional, but automatically spam protected so please do)
Multiply: 2 and 6 = (What's this?)

  Admin Log In



[The Imperial Realm :: Miranda] [Blog] [Gallery] [About]
Terms Of Use & Privacy Policy