4

I have a java program which updates a table in oracle database.

I have tried it using a single JDBC connection and it's very slow and takes hours to complete.

I'm trying to use HikariCP to make a connection pool and have multiple threads get separate connections from the pool.

Suppose I have 6 threads and 5 database connections in the pool and 5 of the threads call the HikariDataSource.getConnection() method. Will each of them get a separate db connection object?

If yes, then, will the thread be in blocked/ waiting state, when it calls the getConnection method or it executes the remaining code with a null connection?

If no, how do I get them separate connections?

Ori Marko
  • 56,308
  • 23
  • 131
  • 233
uneq95
  • 2,158
  • 2
  • 17
  • 28
  • 1
    Why don't you just test it? – JB Nizet Nov 20 '18 at 07:37
  • 1
    Generally speaking with connection pools you want to get a connection per _unit of work_ not per, for example, thread. A unit of work on a database is typically a transaction. This may seem wasteful, constantly taking and returning connections - but it's exactly this that makes pools valuable; you may be able to have many fewer connections than workers. – Boris the Spider Nov 20 '18 at 07:49
  • @JBNizet I tested it as you suggested and the results turned out to be the same as answered. – uneq95 Nov 20 '18 at 08:23

1 Answers1

5

Will each of them get a separate db connection object?

Each thread ask connection, if available gets a separate db connection object

If yes, then, will the thread be in blocked/ waiting state, when it calls the getConnection method or it executes the remaining code with a null connection?

If no available connection it will wait until connection is released to pool and take it, if it won't get connection until timeout defined, it will throw a timeout exception

If no, how do I get them separate connections?

Irrelevant, because each thread will get different connection

About HikariCP and concurrency:

HikariCP contains a custom lock-free collection called a ConcurrentBag. The idea was borrowed from the C# .NET ConcurrentBag class, but the internal implementation quite different. The ConcurrentBag provides...

  • A lock-free design
  • ThreadLocal caching
  • Queue-stealing
  • Direct hand-off optimizations

...resulting in a high degree of concurrency, extremely low latency, and minimized occurrences of false-sharing.

Ori Marko
  • 56,308
  • 23
  • 131
  • 233
  • You seem right. I am trying to test it using 2 connections in db pool and 3 threads. The 3rd thread is always in the waiting state. How do I return back the connection to the pool? – uneq95 Nov 20 '18 at 08:06
  • 2
    @uneq95 you must close connection when finish using it, Hikari won't do it for you, see https://stackoverflow.com/questions/2225221/closing-database-connections-in-java – Ori Marko Nov 20 '18 at 08:09
  • Won't the pool have to open a new connection, if I close the existing connection? – uneq95 Nov 20 '18 at 08:12
  • 2
    @uneq95 after closing connection, the connection will return to connection pool – Ori Marko Nov 20 '18 at 08:14
  • So, the close() method doesn't really close the connection, it releases any database resources (cursors, handles, etc) the connection may be holding on to, right? – uneq95 Nov 20 '18 at 08:15