0

I am working on a server application that should handle many requests. There is one thread per each request and each request has a Database operation.

Creating connection for each request and doing the DB operation will waste time. I/O operations on each thread may cause many context-switches that will waste time too

I think it's better to have one thread for DB Operations (named DB_Thread ). when a new request arrives, a new thread will be created for the request, the new thread will add it's operation in a list and wait. DB_Thread will do operations from the list. new thread wait until the operation ends. in this situation there is one thread with I/O operations and when other threads are waiting for DB_Thread, there is much fewer context-switches

Is it a good solution ? or not ? and why ?

  • Why not just share the connection between the request-threads? – doubleYou Nov 27 '17 at 11:10
  • Im afraid when the list grows as there is no restriction of number of concurrent request that I am assuming! – Zico Nov 27 '17 at 11:14
  • Creating connection for each request and doing the DB operation will waste time. I/O operations on each thread may cause many context-switches that will waste time too All intensive database applications, use pools for that. Thus this is not a problem. – Walfrat Nov 27 '17 at 15:51
4

Is it a good solution ? or not ? and why ?

Databases have been among us for a long time, and a lot of people already did the thinking for us ;)

You have a problem of Transactional Serializability.

Basically, this answer the question :

"What happens to the data when a Database receive a lot of transactions (each having a read/write/create/destroy operation)?"

And they already solved with Two-phase locking concurrency control.

So, doesn't matter how your application layer is implemented, each database already have it own Serializability and Concurrency Control inside of it.

With that in mind, lets breakdown your question...

Creating connection for each request and doing the DB operation will waste time.

To avoid this problem, you can use database connection pools that will have free connections for you incoming requests, and will create another ones automatically for you.

I/O operations on each thread may cause many context-switches that will waste time too.

Unless your application has some constraints, the cost of context switch is really small (1~7 microseconds) - as discussed on Stack Overflow.

To improve IO performance you can use native calls to do IO on a lower level, like java NIO buffers/channels, Memory Mapped Files to avoid User Space copy, (and so on...)

I think it's better to have one thread for DB Operations (named DB_Thread ). when a new request arrives, a new thread will be created for the request, the new thread will add it's operation in a list and wait. DB_Thread will do operations from the list. new thread wait until the operation ends. in this situation there is one thread with I/O operations and when other threads are waiting for DB_Thread, there is much fewer context-switches

This creates a unnecessary bottleneck, since if this (single) DB_Thread is busy with some work (of one thread).

And worse, YOU have to deal with all the sync of all of this. Once you have transactions happening, the database already builds a precendence graph to check if the current state of the database is correct or not. If (for some reason) it detects an inconsistent state, it will start to solve it and rollback (some if possible) changes to be at a consistent state again.

This will cause your transactions to fail and rollback the changes made by them.

Them, at your application level, the threads are notified of the fail and have to implement handling of the fail.

If it sounds too complicated, IT IS! ;)

So that's is more than enough reason to don't go that way.

In your case, YOU will need to implement that your DB_Thread will not lock waiting for the database to serialize all the operations and commit your transaction and them notify each thread that the DB transaction is successful or not.


Keep It Stupidly Simple (KISS), don't reinvent the well.

  1. Let database transactions be handled by the database.
  2. Let IO transactions be handled by an IO solution/library/ (...)
  3. If you need to synchronize both transactions, use a transaction manager that can handle that situation.

Focus more on integration tests to make sure everything is working.

3

Database operations typically consist of multiple stages and allow for quite some parallel execution. Your single thread will block that.

You're right that creating a new Connection for every request is big a waste of time.

I'd do the database operations on the request threads, but use a Connection pool with a configurable number of Connections.

Then you have no thread synchronization issues (they're handled in the Connection pool) and have a straightforward implementation of your requests. The number of Connections in the pool controls the amount of parallelism. If you are getting too many requests, requests will be waiting until the next Connection gets available.

You can (and should) benchmark the scalability with different numbers of Connections to find out how "parallel" your database is.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.