Currency Transaction Log

Hi,

I’ve a virtual reality game that handles quite a bit of currency exchanges between users. This involves trading, sending currency one-way, buying things (system subtracts), receiving awards (system adds), etc. We have a currency library (at the application level) that handles these transactions and interfaces with the database.

We are looking to implement a more full-featured currency transaction log that keeps track of how, when, where, and why users transfer currency, receive it, send it, etc. A very basic logging schema is:

id
user_id
time
amount
type (add, subtract)
[…]

Using InnoDB, insert delayed isn’t an option. I’m a little concerned about performance since currency exchanges already require at minimum one UPDATE balance queries (if not two) and, on a high traffic web site, I don’t want to get bottlenecked with adding one (possibly two) new INSERTS for logging.

From a design standpoint, what might you recommend for transaction logging at an application level? Thanks in advanced.

Are you saying you want a “log” that will be used for read only reporting alongside of your transaction table, or are you saying you want to replace your existing transaction system, which would most likely require atomic operations?

What your partially describing is a fire + forget type operation which leads me to believe that your only looking for a log that atomic operations would not be run against, and that this log would only be populated AFTER the atomic operation was performed. This places almost the entire problem in your application layer. You simply need a proper way to do an insert that is non blocking. What language are you using?

Thanks for your questions.

What I’m describing is that we need a read-only transaction log of currency exchanges between users. This read-only transaction log would be written after the atomic operation was performed (i.e. step 1: currency exchanges hand, step 2: the log is written). Atomic operations need not be formally “validated” by the log. We need this log to be able to search a user, a date, an amount, and perhaps even a short, qualitative descriptor of the transaction for security and historical purposes.

In this comes my performance concerns, since these atomic operations and log-writing would occur at the same time in the application layer.

Our language is PHP with CodeIgniter on top.

Ah. Well you have two options. pctnl_fork() to fork a new process or pthreads. Understand that PHP doesn’t have true multi threading (despite the name of that package), however, it should provide you with the ability to run a non blocking operation.

Both options most likely require a recompile of PHP. Here’s my quick hack for you:

  1. Recompile php with pcntl

  2. Fork a new process that simply writes to a flat file (text file)


//atomic operation succeeded
$pid = pcntl_fork();
if ($pid == -1) {
     //couldnt fork the process, write to an error log as well as write to your flat file so it can still be picked up, however this will be a blocking operation at this point
} else if (!$pid) {
     //this is the child process, operations here are not blocking operations
     //write transaction to a flat file
     exit;
}

//rest of your code

  1. write a cron job (with a mechanism to prevent duplicate processes, look into locking a temp file) to read from flat file, insert into your log db

You’ll have to come up with a way to prevent locking of the original file so that writes can still come in, theres numerous ways of doing this. The reasons I suggest a flat file for staging is so that you don’t lose transactions if your log db goes down.