Have Geek Brain

Non-blocking MySQL updates with java?

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

What's the best way to accomplish this?

I am using c3p0 to manage a pool of database connections. My best idea is to add query update lines to a synchronized list, with an independent thread checking the list every 100ms and executing the requests it finds there.

+2


a source to share


3 answers


My best idea is to add query update lines to a synchronized list, with an independent thread checking the list every 100ms and executing the requests it finds there.



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

+2


a source


Put updates in BlockingQueue

. Ask a separate thread to wait in the queue using take()

and then drainTo()

to consume all pending updates and push them to the server in one go. Use a single multi-line INSERT for maximum efficiency.



This approach ensures that updates reach the server without any irretrievable delay due to the polling rate, making the requests louder and therefore more efficient when the volume goes up.

+2


a source


I think "INSERT DELAYED" might be what you are looking for, since the INSERT returns immediately and MySQL will handle all threading issues for you. However, there is no equivalent UPDATE statement, so you will need to rewrite your code to only use INSERT or REPLACE.

0


a source






More articles:


All Articles