Mysql/Database & Threads

[cardmaster316]

While browsing through the other posts i read that accessing a database to insert/load/update ect can cause the processing function to block while this is happening.

Does this mean that: void ThreadFunction () will continue fine but: if (ProcessPlayerPacket(buffer, player, true)) function wont be usable untill it has finshed dealing with the custom database packet?

If so would creating a second process function to deal with only database querys fix that, like having: if (ProcessDatabasePacket(buffer, player, true)) above the process player packet function, Would that allow if (ProcessPlayerPacket(buffer, player, true)) to continue as normal?

[ArenMook]

You don't want a blocking call to happen in Update() as it will cause a stutter in your game. Anything involving blocking should be done on a worker thread instead.

[cardmaster316]

Could you give some example code of how to do this and also where please?

[ArenMook]

1. ...
2. Thread thread = new Thread(SomeFunction);
3. thread.Start();
4. ...
5.  
6. static void SomeFunction ()
7. {
8.     ...do whatever here, for as long as you like -- it won't block the main thread.
9. }

[cardmaster316]

Oh ok thats kind of how i am doing it at the moment then i think with another thread running all on standalone sevrer, But i have an entity class:

1.    public class TNEntity
2.     {
3.         public string username;
4.         public string password;
5.         public string email;
6.         public string ip;
7.         public int points;
8.     }

and then 4 Dictionarys:

1. Dictionary<string, TNEntity> mAllEntitys = new Dictionary<string, TNEntity>();
2.     Dictionary<string, TNEntity> mOnlineEntitys = new Dictionary<string, TNEntity>();
3.     Dictionary<string, TNEntity> mNewEntitys = new Dictionary<string, TNEntity>();
4.     Dictionary<string, TNEntity> mUpdateEntitys = new Dictionary<string, TNEntity>();

On server load its loading all players into the first dictionary which im then using in custom packets for login & register to check against accounts or load the user into the online dictionary

1.             case Packet.LoginRequest:
2.                     {
3.                 Console.WriteLine("Players = " + mAllEntitys.Count);
4.  
5.                 int count = 0;
6.                 string Name = reader.ReadString();
7.                 string Password = reader.ReadString();
8.                 string result = "";
9.  
10.                 foreach (TNEntity entity in mAllEntitys.Values)
11.                 {
12.                     if (entity.username == Name)
13.                     {
14.                         count++;
15.                         string hashedPassword = HashSHA1(Password + entity.email.ToString());
16.                         if (entity.password == hashedPassword)
17.                         {
18.                             TNEntity entity1 = new TNEntity();
19.                             entity1.username = entity.username;
20.                             entity1.password = entity.password;
21.                             entity1.email = entity.email;
22.                             entity1.ip = entity.ip;
23.                             mOnlineEntitys.Add(entity1.username, entity);
24.  
25.                             Console.WriteLine("Online: " + mOnlineEntitys.Count);
26.                             result = "Success";
27.                         }
28.                         else result = "Incorrect Password!";
29.                     }
30.                 }
31.                 if (count == 0)
32.                     result = "Incorrect Username!";
33.  
34.                 BinaryWriter writer = BeginSend(Packet.LoginResponse);
35.                 writer.Write(result);
36.                 EndSend(true, player);
37.                         break;
38.                     }
39.             case Packet.RegisterRequest:
40.                     {
41.                 string Username = reader.ReadString();
42.                 string Password = reader.ReadString();
43.                 string Email = reader.ReadString();
44.                 string result = "";
45.                         Console.WriteLine(player.address + " is attempting to register the account: " + Username);
46.  
47.                 int count = mAllEntitys.Values.Count(entity => entity.username == Username);
48.  
49.                         if (count != 0)
50.                         {
51.                             result = "Username Taken!";
52.                         }
53.                         else
54.                         {
55.                     string hashedPassword = HashSHA1(Password + Email.ToString());
56.  
57.                     TNEntity entity = new TNEntity();
58.                     entity.username = Username;
59.                     entity.password = hashedPassword;
60.                     entity.email = Email;
61.                     entity.ip = player.address;
62.                     mAllEntitys.Add(entity.username, entity);
63.                     mNewEntitys.Add(entity.username, entity);
64.  
65.                     result = "Success";
66.                         }
67.  
68.                 Console.WriteLine(player.address + " register " + Username + " = " + result);
69.                 BinaryWriter writer = BeginSend(Packet.RegisterResponse);
70.                 writer.Write(result);
71.                 EndSend(true, player);
72.                 break;
73.                     }

And then i start a 2nd thread running at server load:

1. mThread = new Thread(ThreadFunction);
2.                 mThread.Start();
3.         DatabaseListenThread = new Thread(DataBaseThread);
4.         DatabaseListenThread.Start();

Which periodically checks the update user dictionary and new user dictionarys for entrys and sorts out the database side:

1. void DataBaseThread()
2.     {
3.         #if MULTI_THREADED
4.         for (;;)
5.         #endif
6.         {
7.             lock (DbLock)
8.             {
9.                 if (mNewEntitys.Count != 0)
10.                 {
11.                     Dictionary<string, TNEntity> tempDict2 = new Dictionary<string, TNEntity>(mNewEntitys);
12.                     mNewEntitys.Clear();
13.                     Initialize();
14.                     foreach (TNEntity E in tempDict2.Values)
15.                     {
16.                         if (OpenConnection())
17.                         {
18.                             string query2 = "INSERT INTO Account (Username, Password, Email, ip, Points) values('" + E.username + "','" + E.password + "','" + E.email + "','" + E.ip + "','" + E.points + "');";
19.                             MySqlCommand cmd2 = new MySqlCommand(query2, connection);
20.                             cmd2.ExecuteNonQuery();
21.                             CloseConnection();
22.                         }
23.                     }
24.                     tempDict2.Clear();
25.                 }
26.                 if (mUpdateEntitys.Count != 0)
27.                 {
28.                     Dictionary<string, TNEntity> tempDict = new Dictionary<string, TNEntity>(mUpdateEntitys);
29.                     mUpdateEntitys.Clear();
30.                     Initialize();
31.                     foreach (TNEntity E in tempDict.Values)
32.                     {
33.                         if (OpenConnection())
34.                         {
35.                             string query = "UPDATE Account SET Points ='" + E.points + "', ip ='" + E.ip + "' WHERE Username = '" + E.username + "'";
36.                             MySqlCommand cmd = new MySqlCommand();
37.                             cmd.CommandText = query;
38.                             cmd.Connection = connection;
39.                             cmd.ExecuteNonQuery();
40.                             CloseConnection();
41.                         }
42.                     }
43.                     tempDict.Clear();
44.                 }
45.             }
46.             #if MULTI_THREADED
47.             Thread.Sleep(50000);
48.             #endif
49.         }
50.     }

That was as far as i got after reading the forum but then thought id check on the situation.

[cardmaster316]

Rather than as i was doing messing with dictionarys and a seperate main thread could you just do this:

In your processplayerpacket void add:

1. case Packet.TestRequest:
2.             {
3.                 Console.WriteLine(" Test begun");
4.  
5.                 new Thread(() =>
6.                 {
7.                     Thread.CurrentThread.IsBackground = true;
8.  
9.                     //Do Some heavy thread blocking tasks
10.  
11.                     BinaryWriter writer = BeginSend(Packet.TestResponse);
12.                     writer.Write("Complete");
13.                     EndSend(true, player);
14.  
15.                     Console.WriteLine("Task Complete");
16.                 }).Start();
17.                
18.                 break;
19.             }

Would that be cause any problems?

[ArenMook]

Make sure to lock() stuff before modifying them. In your first post you were accessing the same dictionary from two threads without ever locking it. Off the top of my head I'm guessing you will want to lock something before using BeginSend() as well.