How singleton is used to manage database connection?

Question

This may be a very old, many times asked question. But I am not able to find a proper answer to it, so asking again.

For the database connections, we always use a singleton object. When the database is being accessed by thousands of users, how does the performance is maintained? I mean if there are thousands of requests per second, how the database connection is managed since we are using a singleton? Are the database requests serialized? Or a singleton is not used in these cases?

I know it is a kind of dumb question, but I am seriously confused. If anyone can give some reference reading link, it will be nice.

Thanks.

Answer 1

I'm not sure whether you've confused the use of a plain singleton with a service locator. Both of them are design patterns. The service locator pattern is used by applications to ensure that there is a single class entrusted with the responsibility of obtaining and providing access to databases, files, JMS queues, etc.

Most service locators are implemented as singletons, since there is no need for multiple service locators to do the same job. Besides, it is useful to cache information obtained from the first lookup that can be later used by other clients of the service locator.

By the way, the argument about

"it's to ensure that there is always only one active connection to your DB"

is false and misleading. It is quite possible that the connection can be closed/reclaimed if left inactive for quite a long period of time. So caching a connection to the database is frowned upon. There is one deviation from this argument; "re-using" the connection obtained from the connection pool is encouraged as long as you do so with the same context, i.e. within the same HTTP request, or user request (whichever is applicable). This done obviously, from the point of view of performance, since establishing new connections can prove to be an expensive operation.

Answer 2

i recommend to use connection pooling
http://www.java2s.com/Code/Java/Database-SQL-JDBC/PooledConnectionExample.htm

Answer 3

Even though you haven't put anything about sessions/transactions/ORM, I think your question comes from Hibernate, JPA or other ORM background.

As such, for any transaction to happen, we need an entityManager or session. These sessions could be created for each transaction.

Now by using factory pattern, we can get as many similar objects as we want... But the factory itself should be singleton. So in DB operations, the entityManagerFactory or sessionFactory objects are kept as singletons.

When you think about it, it makes sense because after all a sessionFactory represents a configuration(DB, UserID, password, connection pool size, caching, etc). What you need to perform DB transaction is not the factory but the object(session) created by the factory. These you can have as many as you want. But if you have multiple factories, it just is unnecessary creation of same (similar) objects.

We use connection pooling in plain jdbc as well as ORM.

Answer 4

If your database connection creating singleton is stateless (which it should be, or at least should be immutable), its pretty simple.

When your web application is accessed by thousands of users simultaneously, there are actually thousands of threads, one per user. Each thread has its own Program Counter which keeps track of what instruction thread is currently processing. When a thread would access a public method of your singleton, for example myDBConnectionManager.getConnection(), it would start executing instructions specified within. Therefore, it is a thread that is actually creating a database connection by reading instructions specified in myDBConnectionManager.getConnection() method. The methods of singleton are only manuals that instruct threads what to do.

This way, your application can create millions of connections at the same time with a singleton as long as it is able to create millions of threads simultaneously.