Accessing databases through multiple threads

Most applications require store information. The options and scenarios are diverse. Let's focus on the scenario in which multiple remote clients need to store centralized information. No matter if the remote client is a standalone application, website or mobile app

   In general, it is not advisable to let the remote clients communicate with the database directly. It is always better to create a middleware to manage the persistence of the entire system. Not only for safety reasons but because we have a single point of entry to the database so that we can enhance and manage performance in a unified way.

So customers send and receive data from the server and he will be responsible for managing the persistence of this information. In small databases  with few connections, not much to worry about. With notions of SQL you can make a small server with good performance.

But when you are facing scenarios involving large numbers of users or large transactions, you must use more advanced techniques. There are two major problems to deal:

•      Large number of users:

      This is more a question of architecture, but it is always good practice create a new thread for each new connection to a remote client. Of course, you have to limit the number of threads depending on your environment if you want to ensure quality to  everyone connected.
You cannot share the same connection with all the threads or rather, you can do but as database providers implement sessions  in a synchronized way, the commands will not be executed until their turn. You will be within a FIFO (first in, first out) queue.

The idea is to create a new database session for each external connection ( owned by the new thread). Each remote client will get or keep your data in parallel as if he just connected to the database.

So we're done ... isn’t it?

Unfortunately, this is not possible or at least not always. You can’t open a new  session for each incoming connection (and therefore the same number of threads) because sessions database are costly both in terms of resources and licenses (depending on how each provider licensing).

 The solution to this problem is to create a pool of database connections. Each thread will request a free connection pool when needed and released when he finishes what he has to do. Thus, each thread will not own a connection. Actually there is no sense to maintain a connection to the database without using. In short, each thread will request a connection when needed, will do the job, and then release the connection.

…but  it can still happen that when needed, there are no available  sessions  to the database in the pool. In this case, the thread waits until another thread release a database session. In this scenario, the user will likely experience some delay. If this happens frequently, you should ask if you can increase the number of sessions in the pool, or if we have reached the maximum capacity, purchasing more resources to enable your server to handle this load.

In relation to pool connections, there are some providers (providers including databases) that provide implementations that can be used via API. We have used some of them and they work quite well. You also have the option of making your own implementation. It will cost you some time, but on the other hand, you will have more control over what you do and you might particularize and optimize it as it suits us.

If you choose to do it yourself, a good idea is to open the database sessions when the server starts because  the creation of a database connection has a high cost. Therefore, if the connection is opened at start up, there will be no perception of the cost of establishing the connection for remote clients

•   Long transactions or lot of queries / commands involved.

      There are other scenarios or mixed scenarios in which the issue is that you have to run a lot of queries for the same user request. The perception of the users is that your request takes longer than expected. How to deal with this?

Depends deeply on the scenario in which we find ourselves. In fact, to solve this problem requires insight into the performance of your application. We can use a similar strategy to that we used on the last point.

We will create a specific thread for each group of linked commands. Take an example, if you are reading data from a client and their orders, you can think of parallel load data from customer and every order, because, no apparent dependence. So, again, we will create different threads, requesting a connection to the pool, and run these commands in parallel. As a result of this approach, users will wait  only the slowest group of commands that are executed in parallel.

If a branch is heavier than the rest, we can choose to return part of the data while the heavier parts are still running. If you do this, the remote client will need to know in order to inform the user about it. At the end of the heavier parts, send the information to the client so that you can complete the user request.

This approach has some risks that you need to know:

1.   You will  deal with some threads in parallel, so you are in charge of joining together when they finish their duties. Be careful because you have to ensure that the information you are accessing is already loaded. Use semaphores or other mutual exclusion mechanism to control access to these areas.  
2. Beware of deadlocks that can cause connection pool usage. Release the session as long as you do not need it. For example, if you create a new thread from another thread already has a session, the latter may end up locked (depending on the size of the pool and context). You could be waiting for her son before releasing your session while he is also waiting for an  available session. If this behavior is widespread we could have a global lock the entire application. This scenario may seem unlikely, but possible oop often suffer this problem. To address this, as mentioned, let's release the database session when we don't need anymore. But be careful, when dealing with hierarchies of objects and multithreading is not as straightforward ensure this behavior. A simple solution is to restrict parallel to the objects that have no dependency downwards. In a graph of tree , we would only  put in parallel the leaves because they have no dependencies.

Combining this two techniques you can get a good performance on your intensive database access applications.

First Footage

It’s time to make our dreams come true. We have been writing some tech entries about how to do a multiplayer game. Now, you can see how  we have implemented this ideas . Look at the following video: 4 players/snakes online trying to survive:

You can detect easily some client time tick corrections (just notice that client tick is not always the same). In the other hand, it’s not easy to detect that there is one correction at client side. During the game, one movement arrives late to the server so when the consolidated server movements gets back to the client, he’s forced to correct the initial prediction and moves based on server movements. As a user, you will see how an initial movement is quickly modified(our head changes his position).

The best of all is that everything works in a smooth way.

Cheating Latency

In our last posts we have been dedicating our efforts to reduce the client perceived latency. Let’s check what   we have  focused:

-          We have configured TCP to flush data as soon as it has been generated.

-          We decided to mix TCP & UDP whenever is possible.

-          We create control layers to sync client and server to get a smooth gameplay.

-          We made our own Object Serialization .

All points above are about  technical issues. Now, let’s turn inside our app and let's check if we can enhance its performance.

Summarizing how the server works  will show us some clues about which point can be improved. Let’s look inside:

-        The server and the remote clients wait, initially,  the same gap of time (tick) for each turn.

-          The server wakes up each time tick and consolidates the players movements sent previously by the remote clients.

-        The server sends back a message containing the consolidated movements and tells every client if they have advanced or delayed related to the objective lag between them.

-          The remote clients make some adjustments on the client side to compensate changing latency related to the server.

-          In order to make this work, clients begin their tick before the server in order to compensate each own latency plus certain buffer.

Now, let’s think what we can do to improve this algorithm:

First Enhancement: Try to send  consolidated movements once all player movements have arrived.

As we have seen, the  server  waits until the end of the tick to send back consolidated movements to the client. That’s how it should be, you may think. Sure? Is it strictly mandatory to wait until the end of the server tick if we already have all movements?

Let’ see... We have nothing to do once all movements are received, at server side, except waiting to the conclusion of the tick. So, if we advance the message containing the consolidated movements,  remote  clients will receive sooner that movements and they will paint non own movements before.

As a result, remote clients will see a more fluid and dynamically game and that’s one of our main goals.

            Risks? If the end client  tick time is so close  to the server end tick , we will be in risk of arriving late for the following movements. That’s where our algorithm, described in our last post, works. The following client tick will be shorter than usual in order to keep the same gap related to the server. If it happens very often  we will only feel a faster game and, in fact, this is what we want. Anyway, we always can wait a minimum of time before sending back consolidated movements. In our case, Snake the Net, we have not implemented this guarantee, except at LAN modes,  because we want a fast game and unfortunately, the latency itself introduces more time that we wanted at minimum.

We implemented this approach with sensible results and I recommend to apply similar strategies wherever  is possible.

Second Enhancements: Extra time for the lazy clients

  Let’s think in the opposite way. What if we have lazy clients? What if we add some extra milliseconds to the server tick and then we tell him to recover that time by reducing his tick client? By doing this, we avoid to correct our clients despite he is a really lazy client. We make him to do a fast tick but, in the other hand, we avoid to correct him. In our case this enhancement  help  us by reducing corrections and improving stability. Of course, you have to control if a lazy client is actually a disconnected client. Again, sense common. If he is late several consecutive times, we will disconnect him and replaced by a bot, that they always arrive at time ;-)

Mixing TCP and UDP to reduce latency in real-time connections.

Let’s start to use our best cards to deal with latency issues. Remember that previously (last entry) we chose to implement all dialogs between client and server based on TCP protocol. We also deactivated Nagle’s, algorithms among other strategies, that caused our app to experience high latencies.

      We have to make a step forward.

TCP packet delivery control adds  extra unknown sized traffic between client and server that we will pay with lack of gameplay. Every packet has to be acknowledged by the receiver and, in case of data lost, a new packet will be send with its consequent acknowledge. It's easy to understand  how this data interchange can impact in our mission to be fast.

So, let’s think about what kind of traffic we are sending  between client and server?. Can we tolerate some packet lost?. 

Basically, analyzing almost every game,  we have two kind of commands:

1.     The Commands that exchange client and server in order to configurate how the game will be (and prepare it before it starts) must be synchonized. We have to assure that we respect the order sequence described in our interface contract between client and server (see last entry). For example, We can’t pay a drink if we have not already ordered. At this time, the game has not began and it’s not critical if we experience additional delayed milliseconds. So we can and should keep using tcp protocol to implementing these commands. We can fully enjoy TCP packet deliver control since it doesn’t matter at all some little delay.

2.     The Commands inside a game need to be quick. It would seems quite obviously but once inside a game every millisecond saved worths a penny. We will be dealing with ticks about half a second because noone would play our game if we see our snake moving fast as a truck. We have to squeeze our brain to get this part of the game as fast as possible keeping on mind that we are playing across internet. This means different lag from players, changing player latency  during the game and  Players leaving the game once started.

Gathering these premises, it seems clear that we need to keep under control what we really send throught the net. So, we  have to avoid sending extra packets as our main objective… and which protocol is sending extra packets out of our control? TCP.

We need to avoid tcp acknowledge traffic that is generated by TCP deliver control packet. By doing this, we will reduce some data traffic but we will loose also the guarantee that packets are going to arrive at time and at the order that we sent from the other point.

In other words, we are going to use UDP wherever we can and add some extra functionality to guarantee order and delivery issues that we need during the game.

Focusing on those commands related to the game itself there are two kind of subcommands:

1. List of movements consolidated by the server:

Keep on mind that the server is imperative and is in charge of guarantee that every client has the same view of the game. So, we decided to send, once every tick ends, a message containing all the movements consolidated by the server. With this message, the remote client will move non own players and will check if his anticipated movement can be confirmed. If we are lucky, everything is ok but if the remote client movement does not arrive or arrive to the server once the tick is ended we may be in troubles. In fact, we will face with remote/server inconsistencies if the remote client has changed the movement direction because the server won’t realize and the server will have sent back a movement with no change direction.  This means that the remote client will be forced to move back to the last tick scenario and move again based on server moments.

This means that we have to implement some movement buffer mechanism to allow this movement back described above. It also  means that we have no alternative except keep using TCP protocol. This messages has to arrive to the remote clients  sorted and every tick (we can allow certain delay). Otherwise, we would have to implement packet delivery control because it matters the order of list of movements and specially because it would make by far more complex  the implementation of the movement back algorithm. Let’s think how would we do if we can't assure that every tick will receive a confirmation of our predictions.

Yes, you may think, let's send the whole game board every tick. This means more and more data to send and that is one of rules that we can’t break .In fact, remember that we move non own players once list of movements are read by the client. So, the worst thing we can expect is some addicional lag between own player and other remote players.

2. Movement command that the client sends to the server:

Maybe the most important command that we need to be quick is the movement sent by the client to the server. It have to arrive before the server closes the turn tick. So, every movement received later is discarded and it will be used current direction to make the player server movement. This, as describes before,  will produce an ammendment to the remote client that is late once he gets the summarized message sent by the server. This discard issue it’s pretty the same as loosing  a client movement message. So we can use the same strategy.

The main idea is that a client message late can be considered as a client message lost. So we don’t care at all if it’s acknowledged by the server. We only want to arrive early. The acknowledge is implicit by the consolidated server movements that the server will send once the tick ends. So, we got it. We have made our custom delivery control message and this means that we don’t need and we don’t want TCP Protocol.

Let’s use UDP protocol for this commands. By doing this, clients are going to arrive sooner so it means that less movements are going to be rejected. This will allow us to reduce server tick time and we will get a faster game!!! In the other hand, we will loose some packets but we  will reduce at the minimum expression the lag between client and server that’s is our main goal.


So summarizing, we finally will use a mixed combinations of protocols to keep our net dialogue coherent and a fluid online game with little gap between server and client.

Choosing Transfer Propotocol

Once our app architecture is designed, we have to define a communication interface between clients and server. This interface shows how client and server can interact  in a non ambiguos way. This dialog have to be determinist in order to make an affordable implementation.
You can see the detailed flow or commands in the following diagram. 

 If we want to create a new type of client we just need to focus on implementing this interface  contract. It doesn’t matter the tecnology below our implementation. At the lowest level, The interface contract just tells us which stream of bytes need to be sent in order to achieve some funcionality.
Going one step down, it has to rely on a network protocol. Since we doesn’t want to reinvent the wheel, we want to interchange data through internet and in a fast way (minimizing latency)  there are just two options realistic: TCP or UDP protocols.
For non familiars, these two propotocols are capable of sending and receiving packets of data between to points. TCP is a step upper udp (ISO tower) since it assures you that packets are going to be received at the other side and at the same order that they have been sent. Of course, this capabilities are not for free. In a non visible way, this controls is done internally by TCP so it’s never an easy choice which protocol to choose.

Let’s suppose that we choose TCP because of these out of the box funcionalities. Later we are going to review this decission. By choosing TCP, we know that network will be such  a data pipeline. Data are going to arrive to our peer and in the order we wanted.
In order to optimize TCP protocol with the objective to deal with high latencies you have to take some decissions:

• Deactivate  Nagles algorithm. By doing this, packets are going to be sent once ordered. It would seems quite obvious but it’s not. Nagles algorithm tries to fill every packet sent  in order to minimize global congestion. This works fine when you want exchange big amounts of data but this solution hits you severely if you want to reduce your latency. Through our tests we have saved  an average of 200-300 ms by deactivating this algorithm. Easy to do but difficult to know. At the end, one of the best decissions we made.

• Related to the last point , we have to assure to flush buffers. This is not a TCP protocol issue or not just a protocol issue. At a prior level, but also a problem, java streams try to gather all information together in order to send once a packet is full. Once again, we are struggling against the latency. We can’t afford this behaviour so, let’s flush everything. Always send and flush.

• Personal serialization when needed. Java serialization are so easy to do but it’s a real big black box. You know that you are  sending your classes  across the net but you don’t know at all how this really works. You just implement serializable  interface and it works! But… again, we want to minimize our transfer data. This is not an issue related to the latency but the size of the information that we deal with. Java Serialization puts in the wire recursively all the hierarchy of a  class. You can calculate  the amount of data that  we are  going to send. A sniffer will show you this in detail. How to improve? Implement Externalizable interface and override writeExternal and readExternal methods in order to just send and read what you really need to rebuild your class at the other point of the net. It’s not difficult to do and you can reduce an incredible amount of data transfered.