MySQL and multithreading

zoultrex

First of all, Happy new year everyone!

Now to the question... I am implementing Database access to photon and so far so good, it was quite simple and easier than I thought but I know it can be just done in any way, im concerned about multithreading and everything else.

Let me explain first why im posting this here and not in a csharp forum. What I understand to be the problem to be avoided is for a function to be called and make the whole server stop until its finished, so thats where the multithreading comes in right?
I have no idea how can MySQL do that, or even if it can do that in the first place, but Im making sure to avoid it anyway.
I have come across this problem with a for loop and I could clearly see the system hang for about 200 miliseconds then coming back to normal.

I am following this page about singleton and multithreading by msdn:
http://msdn.microsoft.com/en-us/library/ff650316.aspx

The same as this msdn page describes is what I have seen throughout the internet like in csharp forums, people recommending to make one class to access the database returning the data after one function call like a query.

So, should I implement my database access class as a normal class that can be called and instantiated at any time, or should I make a static class or should I implement a singleton design pattern?

Im a bit confused because I think that if one call is made to the database access class is made and then it has not yet fetched the data while another call to the class is made this will be a big problem right? or not?

I think that what I have to ensure is that the database access class is accessible at any time even if one of the previous queries has not yet returned data, if Im correct, which is the correct deign patter to go for so I can have the database class accessible at all times in a safe way?

thank in advance!

Tobias

I don't do a lot of server engineering, so I can't help in detail. As always, there are lots of different ways to solve this and finding the "best" depends on the context as much as your programming style.

You might want to check out some database abstraction layers or "O/R mapping" frameworks. One of those is LightSpeed (http://www.mindscapehq.com/products/lightspeed) but there are many more. If none of those works for you, you could at least learn from the best how they handle database requests.

Boris

As long as you are not locking anything or accessing shared variables when accessing the database you can use a singleton, but there are othre factors that can cause a blocking thread. If you are using Lite and have the LiteGame access the DB this will block all operation processing of the LiteGame instance until the db access is completed. This happens because every operation on a LiteGame runs on the same fiber which basically means everything happens after each other.
Another problem to watch out for are lost updates (e.g. two threads increment the same value at the same time) due to multi-threaded db access. You should look into concurrency control to solve this.

princefarr

If you choose to share the connection between threads, you must be sure that a thread runs out completely before another connection to use it (use a message, mutex or a critical part of protecting links). Alternatively, you can write your own pool of connections.

tito.soriano

If you are using Lite and have the LiteGame access the DB this will block all operation processing of the LiteGame instance until the db access is completed.

So using a singleton or a database abstraction layers or "O/R mapping" frameworks would help solve this issue?

I have extended the LiteLobby Application and have extended the "LiteGame" with my own custom operations to handle saving data from clients to a SQL Server database. Sounds like this isn't the correct way of saving data.

Boris

tito.soriano wrote:

So using a singleton or a database abstraction layers or "O/R mapping" frameworks would help solve this issue?

singleton or not, it depends on the code it contains.

tito.soriano wrote:

I have extended the LiteLobby Application and have extended the "LiteGame" with my own custom operations to handle saving data from clients to a SQL Server database. Sounds like this isn't the correct way of saving data.

It's one way, and if your game is fine with a bit more latency it's ok. But if there are lots of operations like 10 movements per sec from each client you don't want to do this inside the LiteGame thread. Instead I suggest that you create an additional fiber for each game and enqueue all db operations for your game to the fiber. This way all db operations happen async to the game operations.

tito.soriano

Thanks for the quick response.

Instead I suggest that you create an additional fiber for each game and enqueue all db operations for your game to the fiber. This way all db operations happen async to the game operations.

I see that a fiber is created everytime a "Room" created. Where should I add this additional fiber? Is there somewhere in your sample code that this is done so I can use that as a starting point?

Boris

i'd instantiate another PoolFiber inside the constructor of your LiteGame subclass

Boris

one more thing: make sure to add a try-catch block with log.Error to all actions that you enqueue - you won't notice exceptions otherwise.