Multithreading – One Thread for All Database Operations vs One Thread per Operation

iomultithreadingserver-side

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 ?

Best Answer

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.

Related Topic