Hello!

I would like to ask for tips on how to handle database concurrency in a client server application in c#. I am using SQL Server 2008 r2 as my server.

I have minimum to zero ideas on how to handle such and would really appreciate any help that you guys can give.

In order for my client and server to communicate, I have created three classes for it.

I have my first class which I call the ClientConnectionManager, this class manages all incoming connections between the client and the server, it is the one that adds, and removes clients within its own hashmap of users.

public class ClientConnectionManager
    {
        private static RichTextBox serverLogsContainer;
        public static Hashtable users = new Hashtable(50);
        public static Hashtable connections = new Hashtable(50);

        private TcpClient tcpClient;
        private Thread thrListener;
        private TcpListener tcpListener;
        private bool isServerRunning = false;

        public ClientConnectionManager(RichTextBox console)
        {
            ClientConnectionManager.serverLogsContainer = console;
        }

        public void StartServer()
        {
            this.thrListener = new Thread(new ThreadStart(WaitConnection));
            this.thrListener.Start();
            ClientConnectionManager.serverLogsContainer.AppendText("Server Started");
        }

        private void OpenServerPort()
        {
            this.tcpListener = new TcpListener(IPAddress.Any, 1986);
            this.tcpListener.Start();
            this.isServerRunning = true;
        }

        private void WaitConnection()
        {
            while (isServerRunning)
            {
                tcpClient = tcpListener.AcceptTcpClient();
                ClientConnection tcpConnection = new ClientConnection(tcpClient);

                for (; ;)
                {
                    ConnectionThreadMessages returnValue = tcpConnection.WaitForClientRequests();

                    if (returnValue == ConnectionThreadMessages.Disconnect)
                    {
                        RemoveUser(tcpClient); //Remove user from client list
                        break;
                    }

                    else if (returnValue == ConnectionThreadMessages.AddUser)
                    {
                        AddNewUser(tcpClient, tcpConnection.newClientName); //Add user to client list
                    }
                }
            }
        }

        public static void AddNewUser(TcpClient client, string userName)
        {
            ClientConnectionManager.users.Add(userName, client);
            ClientConnectionManager.connections.Add(client, userName);
            ClientConnectionManager.UpdateLog("\nConnection accepted from " + connections[client]);
        }

        public static void RemoveUser(TcpClient tcpUser)
        {
            if (connections[tcpUser] != null)
            {
                UpdateLog("\n" + connections[tcpUser] + " was disconnected.");
                users.Remove(connections[tcpUser]);
                connections.Remove(tcpUser);
            }
        }

        public static void UpdateLog(string message)
        {
            ClientConnectionManager.serverLogsContainer.Invoke(new MethodInvoker(delegate { serverLogsContainer.AppendText(message); }));
        }
    }

And then I have a class called ClientServerCommunicationMessage which I use to relay information between the client and the server. If the object is to be sent by the client, it would contain, the type of query the client would like to make, the storedprocedure it would like to call and the parameters if there are any. After the server receives this object the server sends a reply using another instance of the same type that contains an acknowledgement that whatever query the client has to make has been approved, the object also contains a dataset in which the queried data is placed.

Here is the code for that:

[Serializable]
    public class ClientServerCommunicationMessage
    {
        private string queryFlag;
        private string clientIdentity;
        private string password;
        private ClientResponses clientResponses;
        private ServerResponse serverResponse;
        private List<Parameter> listOfQueryParameters;
        private DataSet queryResults;

        public string QueryFlag
        {
            get { return queryFlag; }
            set { queryFlag = value; }
        }
        public string ClientIdentity
        {
            get { return clientIdentity; }
            set { clientIdentity = value; }
        }

        public string Password
        {
            get { return password; }
            set { password = value; }
        }

        public ClientResponses ClientResponses
        {
            get { return clientResponses; }
            set { clientResponses = value; }
        }

        public ServerResponse ServerResponse
        {
            get { return serverResponse; }
            set { serverResponse = value; }
        }

        public List<Parameter> ListOfQueryParameters
        {
            get { return listOfQueryParameters; }
            set
            {
                for (int i = 0; i < value.Count; i++)
                {
                    this.listOfQueryParameters.Add(value[i]);
                }
            }
        }

        public DataSet QueryResults
        {
            get { return queryResults; }
            set { queryResults = value; }
        }

        public ClientServerCommunicationMessage() { }
    }

And for the last class I have something that I call a ClientConnection. This class is the one that intercepts requests from the user and acts accordingly according to the request. This is the class I am having most trouble with.

I am having troubles with thinking up of a way to:

1) make sure there is a delay between the conversation of the server and the client i.e., when the user sends his message, there should be a delay wherein the clientwould have to wait for the reply of the server; I don't know how to do that.

2) How to handle concurrency in the database when I have I am updating the database in the same time as someone is querying from it.

3) How to make the client or the server wait for a set of consecutive message objects between them i.e., I have to make sure that when a user logs in he is existing in the database, so that would be one message object, I would then reply to the client with another object, and the client reads that object's certain member that indicates if whether he is in the database or not, after receiving the client then sends his password within another object, in which the server processes it and returns another object indicating that the login was a success.

Here is my incomplete and messy messy code for the ClientConnection

public class ClientConnection
    {
        public string newClientName;
        private TcpClient clientTCPConnection;
        private NetworkStream messageStreamReader = null;
        private ClientServerCommunicationMessage connectionMessage = new ClientServerCommunicationMessage();
        private ClientResponses _clientResponses;
        private ServerResponse _serverResponse;
        private DatabaseDataAccessor dataAccessorObject;
        byte[] messageStreamBuffer;

        public ClientConnection(TcpClient client)
        {
            this.clientTCPConnection = client;
        }

        public ConnectionThreadMessages WaitForClientRequests()
        {
            while ((GetClientRequestType(clientTCPConnection) != ClientResponses.DisconnectClient))
            {
                this.dataAccessorObject = new DatabaseDataAccessor();

                if (connectionMessage.ClientResponses == ClientResponses.Operations_RequestForConnection)
                {
                    ClientServerCommunicationMessage serverReplyMessage = new ClientServerCommunicationMessage();
                    ConnectionThreadMessages returnValue = ConnectionThreadMessages.KeepConnected;

                    if ((bool)dataAccessorObject.ExecuteScalar(connectionMessage.ListOfQueryParameters,
                        EnumValueUtil.GetStringValueOfEnumValue(StoredProcedures.Operations_CheckIfKServeEmployeeExists)))
                    {

                    }

                    else
                    {

                    }

                    serverReplyMessage.ServerResponse = ServerResponse.Server_ConnectionApproved;
                    messageStreamReader = clientTCPConnection.GetStream();
                    messageStreamBuffer = CommunicationMessageConverter.ObjectToByteArray(serverReplyMessage);
                    messageStreamReader.Write(messageStreamBuffer, 0, messageStreamBuffer.Length);
                    this.newClientName = connectionMessage.ClientIdentity;

                    return returnValue;
                }

                else if (connectionMessage.ClientResponses == ClientResponses.AccountsManagement_RequestForConnection)
                {
                    //same as above
                }

                //else if handle sofia login request

                else if (connectionMessage.ClientResponses == ClientResponses.AccountsManagement_RequestForQuery)
                {
                    ClientConnectionManager.UpdateLog("\nNew " +
                        connectionMessage.ClientResponses.ToString() +
                        " requsted by " +
                        ClientConnectionManager.connections[clientTCPConnection]);

                    ClientServerCommunicationMessage serverReplyMessage = new ClientServerCommunicationMessage();
                    serverReplyMessage.ServerResponse = ServerResponse.Server_QueryApproved; //SET SERVER RESPONSE

                    //GET QUERY FLAG -- JOB OF DBMANAGER
                    //CHECK QUERY LIST IF QUERY FLAG EXISTS -- JOB OF DBMANAGER
                    //CREATE ENUM FOR QUERY LIST FOR EACH MODULE
                    //IF QUERY EXISTS GET PARAMETERS
                    //PASS EVERYTHING TO DB MANAGER
                    //GET DATASET FROM THE MANAGER
                    //STORE EVERYTHING IN NEW TRANSACTION OBJECT
                    //WRITE TRANSACTION OBJECT ON THE WIRE


                    //serverReplyMessage.QueryFlag = "query";
                    //messageStreamReader = clientTCPConnection.GetStream();
                    //messageStreamBuffer = TCPConverter.ObjectToByteArray(serverReplyMessage);
                    //messageStreamReader.Write(messageStreamBuffer, 0, messageStreamBuffer.Length);

                    return ConnectionThreadMessages.KeepConnected;
                }

                else if (connectionMessage.ClientResponses == ClientResponses.Operations_RequestForQuery)
                {
                    ClientConnectionManager.UpdateLog("\nNew " +
                        connectionMessage.ClientResponses.ToString() +
                        " requsted by " +
                        ClientConnectionManager.connections[clientTCPConnection]);

                    ClientServerCommunicationMessage serverReplyMessage = new ClientServerCommunicationMessage();

                    serverReplyMessage.ServerResponse = ServerResponse.Server_QueryApproved; //SET SERVER RESPONSE
                    //check if query exists

                    serverReplyMessage.QueryResults =
                        this.dataAccessorObject.ExecuteQueryStatement(connectionMessage.ListOfQueryParameters, connectionMessage.QueryFlag);



                    messageStreamReader = clientTCPConnection.GetStream();
                    messageStreamBuffer = CommunicationMessageConverter.ObjectToByteArray(serverReplyMessage);
                    messageStreamReader.Write(messageStreamBuffer, 0, messageStreamBuffer.Length);


                    return ConnectionThreadMessages.KeepConnected;
                }

                this.dataAccessorObject = null;
            }

            return ConnectionThreadMessages.Disconnect;

        }

        private ClientResponses GetClientRequestType(TcpClient client)
        {
            NetworkStream clientRequestsStreamReader = client.GetStream();

            messageStreamBuffer = new byte[512000];

            clientRequestsStreamReader.Read(messageStreamBuffer, 0, messageStreamBuffer.Length);

            connectionMessage = (ClientServerCommunicationMessage)CommunicationMessageConverter.ByteArrayToObject(messageStreamBuffer);

            return connectionMessage.ClientResponses;
        }

        private void CloseConnection()
        {
            //tcpClient.Close();
            //streamReader.Close();
            //streamWriter.Close();
        }
    }

There is only one instance for the ClientConnectionManager, and the ClientConnection is threaded within the ClientConnectionManager.

SORRY for the lengthy post, I am really at a loss here.

THANK YOU FOR READING!

I first have to ask if it is necessary for you to work at such a low level.
It has been a long time since I worked at the network level to perform database operations and I've never thought to do this in C#.

Do you control both the client and the server code?

Hi, thanks for the reply!

Yes, we're required to work at this low level, this is for a small project that our school is requiring us for graduation.

Yes we both control the client and the server codes.

Making the client "wait" can be achieved by including a "Thread.Sleep(milliseconds)" at your server side, after you receive the client message.

For concurrency, the first thing I can think of, is to store any database objects being used in-memory first at your server, along with the User ID that it belongs to. When you receive a command to access any data, you check your in-memory model first, if it exists, check the User ID, if that matches, carry on with whatever you're doing. Otherwise, bail out back to the client and say the record is locked.
You can also do this at the database, by updating your tables to include 2 new columns, "isLocked" and "lockedBy" which are Boolean and UserIDKey respectively. Perform the same steps as above, only do it on the database instead.

There is also a way to get the database to do this for you, but as you said you're working at low level, I don't know if this would be allowed or not.


Honestly, I don't like your login method. This seems bad design and also opens up the server to attack. So for a login, send both, encrypted and return one message that is ambiguous, "Username or Password not recognised" for example. This stops someone for checking for usernames in the database.
Unless you have a specific method that needs to wait for multiple messages, then it should already know how many it needs to wait for. If you want to wait for an unexpected amount of data, you should send a server message that includes how much data you're going to receive.

Here are some examples;
COMMAND|DATALENGTH|DATA|EOF
COMMAND|NUM_MESSAGES|[MESSAGE_LENGTH|MESSAGE]|EOF

Where | is your delimiter and [] indicates a sub array of the internal description


Hope some of this helps =)

commented: Lots of informative stuff!! +3

Hi thank you so much for these information!

I think we would be allowed to make the database do concurrency for us, how is this achieved?

And could we just like, create a hashmap within the server's memory that would contain a list of user's and the corresponding tables they are using during the system's run? There a lot of tables for the database as of the moment and, I am guessing that your methodology can be achieved through this with the same effect? Please correct me though if I sound wrong and obnoxious.

As for the login method, I will do your suggestion and also create a new list of messages that will indicate if whether login was a success or not.

I have to ask though if my interpretation of what you were saying here::

Unless you have a specific method that needs to wait for multiple messages, then it should already know how many it needs to wait for. If you want to wait for an unexpected amount of data, you should send a server message that includes how much data you're going to receive.

is correct.

You were trying to say that we should create or add functionality to our methods that exchange a numerous amount of messages to relay, constantly; messages that indicate the status of the server and the client's "conversation". Is this correct?

Hi thank you so much for these information!

I think we would be allowed to make the database do concurrency for us, how is this achieved?

Unfortunately I'm not a Database Admin, so I don't know how to configure it to do that for you. I typically rely on the ORM I use (Entity Framework or nHibernate) to sort that out for me, or roll my own using a pattern I gave above. This link looks useful though

And could we just like, create a hashmap within the server's memory that would contain a list of user's and the corresponding tables they are using during the system's run? There a lot of tables for the database as of the moment and, I am guessing that your methodology can be achieved through this with the same effect? Please correct me though if I sound wrong and obnoxious.

Sure, if you want to lock the entire table. I don't think this would be a good idea and leads to deadlock more readily. But this may be a requirement of your software. How you do it is up to you, just pick what is appropriate for your requirements.

Speaking of deadlock, this is something you need to approach very carefully and test rigorously. Although I'm sure you'll know what deadlock is, I'll explain just in case. It is when one part of a program holding a resource is waiting for another part of the program to release a different resource, which is waiting for the first part of the program to release its resource. In this case, neither will give up their resource as they're waiting for the other.

How you handle deadlock is up to you and is written about in droves all over the web :)

As for the login method, I will do your suggestion and also create a new list of messages that will indicate if whether login was a success or not.

:D

You were trying to say that we should create or add functionality to our methods that exchange a numerous amount of messages to relay, constantly; messages that indicate the status of the server and the client's "conversation". Is this correct?

In a perfect world the entire thing would be stateless, but I guess that isn't going to happen without a major re-write. Unfortunately I don't think I know enough about your communication model to begin commenting on how you do it now.

But the way I previously mentioned is how I would handle multiple messages. That means that your receiving method knows exactly how much is coming in.

Alternatively, you can tag your messages with an "EventNumber" and a command. When you send a message to the server, it makes a note of the "EventNumber" and when you push the related responses back to the client, it sends the "EventNumber" with it.
In your client, your calling method registers with the communication class and gives it the event number as an ID. When the class receives a message, it checks with the registered delegates and calls the one with the respective ID.

There are many ways to do it and without refining or giving a complete design of your protocol I will find it difficult to help you any further without giving you generic ideas around the topic. ^^

Hmm actually I think I'm beginning to realise what your server actually does regarding "conversation". Personally, I think you're putting too much "knowledge" in the server.
You need to separate out more. The server should not and does not need knowledge about what the client is doing or is going to do.

It gets a command, processes it and gives a response. If you need business logic to handle that request, that's fine, but it shouldn't need to track *where* in the order of operation it is related to the client.

eg.
Client Receives Command From User (LOGIN)
Client Sends Message to Server (LOGIN|USERNAME|Bob|PASS|1234)
Server Receives Message from Client
Server Checks Command (LOGIN) and runs the "CheckLogin(Username, Password)"
Server Gets Result of "CheckLogin"
Server Sends Result to Client (TRUE/FALSE)
Client Received Message
Client Processes Message (TRUE = Login, FALSE = Show Error)


That would be how I would operate the server/client communication. Same for editing a row:

Client Receives Message from User (GETUSER(47))
Client Sends Message (GETUSER|USERID|47)
Server Receives Message from Client
Server Checks Command (GETUSER)
Server Sends Parameters to GETUSER Method (GetUser(47))
Server Does Database Fetch
Server Serialises and Returns the Result To Client ([USERDATA])
Client Receives ([USERDATA]) and DeSerialises into (UserObject)


This does not rely on the server knowing what the client is doing. Receive command, respond to it, don't wait for something to come next.
The client is expecting a response from the server, but the server doesn't need to know that. It just sends the data, and doesn't care if it is correct. That's the job of the client. =)

Okay I think I understand where you are getting at, and I am now refining some parts of the server's code and I am in the process of creating an enumaration of messages that the server should receive and how it should act upon it.

So I and my partner here have decided that we'll go with a hashmap of users with the corresponding table they are editing.

BUT, with regards to deadlock handling, I think we are at a loss. We have concluded that we should handle deadlock handling at the client's side.

When a client hits the submit button on their forms the client sends a message to the server that they are about to submit new information or that they are finished with the editing they have done. The server receives the message and checks its own hashtable if whether the table to be written on is in use, if it is in use the server sends a message to the client that it is in use, the client receives it and a pop up message on the client window appears telling that someone is using the table, the changes or the new data is not pushed onto the table and the client is encouraged to try persisting again after someone has released the lock on the table.

That's our plan, but I think it's kinda slow because the other users would have to wait for the others to finish before they can persist their data. Also when someone has already queried a specific set of data and then after a few seconds someone edits the table the users who already have queried wouldn't be updated of the changes.

...snip...

That's our plan, but I think it's kinda slow because the other users would have to wait for the others to finish before they can persist their data. Also when someone has already queried a specific set of data and then after a few seconds someone edits the table the users who already have queried wouldn't be updated of the changes.

And this is the main problem with concurrency. You just have to accept that. Allow people to read and have an "edit" button or something. Check for the lock before you allow them to edit data. Unfortunately, there's nothing you can do about making people wait.

Perhaps a faster way is no table locking at all, simply a "LastUpdated" field. Get a copy of the last updated hash before you edit and check it matches before you commit changes. If it has changed, check if any of the modified fields have been edited already, if so, return to the client and notify. Otherwise you can write as normal. One thing to make sure of though. Only allow one person to write at a time in your commit code!

ie. Two people commit at the same time and the code runs parallel, they will both confirm the correct unedited hash and update the database. At best, one persons changes are lost, at worst (which happened in the older days) you get a mash-up of both bits of data.

You can do this with a simple thread synchronisation object
eg

public class MyDbWriter
{
   private static readonly object padLock = new object();
   
   public WriteDB(User newUser)
   {
      lock(padLock)
      {
         /* DB Manipulation Goes Here */
      }
   }
}

If two threads try and access WriteDB at the same time, one will wait for the other (this is where your deadlock can occur)

Thanks so much for replying again and again.

I will look up into the thread synchronization you have illustrated earlier, for future references.

It is now becoming clearer how we should implement the coding for the server. What we are now trying to think of is the proper implementation of "forced refreshing" unto the server's clients.

i.e. Picture that 2 user access the same table, and its contents are displayed in their own client window's listviews.

And let's say the 1st user edits the 2nd row of the listview (thus restricting the 2nd user from submitting any edits, or editing anything for that matter), and commits his changes unto the database.

After committing, the 2nd user is left with his outdated listview and decides to edit the 2nd row (the row that was also edited by the first user), and then commits the changes.

I now ask wouldn't there be a failure in concurrency at that point? Since the 1st user's changes are now voided because the 2nd user has overwritten the 1st user's changes. Shouldn't we "force" the other users who are viewing the same table from different client windows to refresh their listviews so that they can get an updated version of the table so that they can act accordingly?

What can be the workaround for this? Is it thread synchronization? If it is the concept is very very new to me and I have yet to fully understand and have failed to see it's benefits in our application.

Thank you so much for the help.

Thanks so much for replying again and again.
I now ask wouldn't there be a failure in concurrency at that point? Since the 1st user's changes are now voided because the 2nd user has overwritten the 1st user's changes. Shouldn't we "force" the other users who are viewing the same table from different client windows to refresh their listviews so that they can get an updated version of the table so that they can act accordingly?

In a word, yes. Thread synchronisation won't help you at this point.

There are a few solutions, I will suggest a couple but you may find better ones.

A) Push updates to the client as soon as any change is made

The methodology here, is that as soon as someone pushes an update to the database, you check your client list for people viewing that same table and push the update to them. Depending on your requirements, this could be good or bad. For projects that include static displays, or information displays, (ie. Any display that only displays data, not edit it) this is a good method as the interface updates as soon as the DB is changed. For user based editing, this is bad as it can cause a lot of problems and user frustration. So you need to be selective with this method.

B) Check for changes and lock

This is probably the best all-round solution if the database is going to be interacted with by multiple users.

Here, before you allow the edit, you have to do a two-stage check:
1. Check if the row is locked. If it is, you can't edit anyway. Show a message saying that the row is currently being edited by another user and they must wait.
If the row is not locked, lock the row for this user and proceed to check number 2.

2. Has the data changed since you last read it? If so, notify the user of the changes and refresh the current data. You should still allow the edit, only they will be working with the new data.


That is pretty much it. There are other ways of dealing with it, but in my opinion, they are the best two in terms of effort required to achieve the result. Other options provide further advantages, but cost more in terms of code lines and complication.

commented: awesoooome! +3

Oh okay, understood that all. I think, we'll go with option 2. I am now going to proceed with the implementation of the said methodology, I hope I do well. Thank you so much for all the advice and help! really really grateful!

Not a problem at all, that's why I'm on this site ;) I hope you can put all this new knowledge to good use!

I suggest marking this thread as solved and posting another if you have a future query. =)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.