Non-blocking MySQL updates with java?

Question

For a multiplayer game I'm working on I'd like to record events to the mysql database without blocking the game update thread so that if the database is busy or a table is locked the game doesn't stop running while it waits for a write.

What's the best way to accomplish this?

I'm using c3p0 to manage the database connection pool. My best idea so far is to add query update strings to a synchronized list with an independent thread checking the list every 100ms and executing the queries it finds there.

Answer 1

Place updates on a BlockingQueue. Have a separate thread wait on the queue with take(), followed by drainTo() to consume all pending updates and push them to the server in one go. Use a single multi-row INSERT for maximum efficiency.

This approach ensures that updates hit the server without any gratuitous delay due to polling frequency, while making chunkier, and thus more efficient, requests as volume climbs.

Answer 2

My best idea so far is to add query update strings to a synchronized list with an independent thread checking the list every 100ms and executing the queries it finds there.

This sounds good to me if you don't want to block your main thread, except that don't use synchronized list and 100ms polling, but use some BlockingQueue implementation instead. LinkedBlockingQueue should do the job, if you make it unbounded it will never block your main thread.

Answer 3

I think an "INSERT DELAYED" might be what you're looking for, as the INSERT returns immediately, and MySQL will handle all the threading issues for you. There is no equivalent UPDATE statement, however, so you'd need to rewrite your code so you're only using INSERTs or REPLACEs.