My SQL Server Can Only Handle 2 players?

Question

I am developing a game using TCP. The clients send and listen the server using TCP. When the server receives a request, then it consults the database (SQL Server Express / Entity Framework) and sends a response back to client.

I'm trying to make a MMORPG, so I need to know all the players locations frequently, so I used a System.Timer to ask the server the location of the players around me.

The problem:

If I configure the timer to trigger for every 500ms a method that asks the server the currently players location, then I can open 2 instances of the client app, but it's laggy. If I configure to trigger for every 50ms, then when I open the second instance, the SQL Server throws this exception often:

"The connection was not closed. The connection's current state is open."

I mean, what the hell? I know I am requesting A LOT of things to the database in a short period, but how do real games deals with this?

Here is one code that throws the error when SQL Server seems to be overloaded (second line of the method):

private List<CharacterDTO> ListAround()
    {
        List<Character> characters = new List<Character>();
        characters = ObjectSet.Character.AsNoTracking().Where(x => x.IsOnline).ToList();

        return GetDto(characters);
    }

maybe my question could be moved to gamedev.stackexchange.com. I just noticed that. — alansiqueira27, Mar 09 '14 at 21:19
I just learned that I shouldn't store in persistent memory (database) the players locations in real time. Instead, I should store in RAM Memory and update to database less frequently. I'm going to try that! — alansiqueira27, Mar 09 '14 at 21:45
Could you at least post the code you use to make the TCP connection? — Slugart, Mar 09 '14 at 21:59
@Slugart, the problem is with SQL Server. Not the TCP. The error occurs when I am using a query to database. — alansiqueira27, Mar 09 '14 at 22:18
could you post the code where you connect to the sql server? — Slugart, Mar 09 '14 at 22:21
Maybe your issue is related to threads: http://stackoverflow.com/questions/12521695/multithreading-entity-framework-the-connection-was-not-closed-the-connections — Slugart, Mar 09 '14 at 22:30

score 1 · Answer 1 · answered Mar 09 '14 at 23:17

Your real problem is ObjectSet is not Thread Safe. You should be creating a new database context inside ListAround and disposing it when you are done with it, not re-using the same context over and over again.

private List<CharacterDTO> ListAround()
{
    List<Character> characters = new List<Character>();

    using(var ObjectSet = new TheNameOfYourDataContextType())
    {
        characters = ObjectSet.Character.AsNoTracking().Where(x => x.IsOnline).ToList();

        return GetDto(characters);
    }
}

score 0 · Accepted Answer · answered Mar 09 '14 at 23:10

I resolved the problem changing the strategy. Now I don't update the players positions in real time to the database. Instead, I created a list (RAM memory) in the server, so I manage only this list. Eventually I will update the information to the database.

My SQL Server Can Only Handle 2 players?

2 Answers2