Concurrency with HikariCP
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?
java multithreading jdbc concurrency hikaricp
edited Nov 20 at 8:19
user7294900
20.6k103258
asked Nov 20 at 7:34
uneq95
541314
add a comment |
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?
java multithreading jdbc concurrency hikaricp
edited Nov 20 at 8:19
user7294900
20.6k103258
asked Nov 20 at 7:34
uneq95
541314
1
Why don't you just test it?
– JB Nizet
Nov 20 at 7: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 at 7:49
@JBNizet I tested it as you suggested and the results turned out to be the same as answered.
– uneq95
Nov 20 at 8:23
add a comment |
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?
java multithreading jdbc concurrency hikaricp
edited Nov 20 at 8:19
user7294900
20.6k103258
asked Nov 20 at 7:34
uneq95
541314
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?
java multithreading jdbc concurrency hikaricp
java multithreading jdbc concurrency hikaricp
edited Nov 20 at 8:19
user7294900
20.6k103258
asked Nov 20 at 7:34
uneq95
541314
edited Nov 20 at 8:19
user7294900
20.6k103258
asked Nov 20 at 7:34
uneq95
541314
edited Nov 20 at 8:19
user7294900
20.6k103258
edited Nov 20 at 8:19
user7294900
20.6k103258
edited Nov 20 at 8:19
user7294900
20.6k103258
20.6k103258
asked Nov 20 at 7:34
uneq95
541314
asked Nov 20 at 7:34
uneq95
541314
asked Nov 20 at 7:34
uneq95
541314
541314
1
Why don't you just test it?
– JB Nizet
Nov 20 at 7: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 at 7:49
@JBNizet I tested it as you suggested and the results turned out to be the same as answered.
– uneq95
Nov 20 at 8:23
add a comment |
1
Why don't you just test it?
– JB Nizet
Nov 20 at 7: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 at 7:49
@JBNizet I tested it as you suggested and the results turned out to be the same as answered.
– uneq95
Nov 20 at 8:23
1
1
Why don't you just test it?
– JB Nizet
Nov 20 at 7:37
Why don't you just test it?
– JB Nizet
Nov 20 at 7:37
1
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 at 7:49
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 at 7:49
@JBNizet I tested it as you suggested and the results turned out to be the same as answered.
– uneq95
Nov 20 at 8:23
@JBNizet I tested it as you suggested and the results turned out to be the same as answered.
– uneq95
Nov 20 at 8:23
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 20 at 7:46
user7294900
20.6k103258
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 at 8:06
2
@uneq95 you must close connection when finish using it, Hikari won't do it for you, see stackoverflow.com/questions/2225221/…
– user7294900
Nov 20 at 8:09
Won't the pool have to open a new connection, if I close the existing connection?
– uneq95
Nov 20 at 8:12
2
@uneq95 after closing connection, the connection will return to connection pool
– user7294900
Nov 20 at 8: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 at 8:15
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53388228%2fconcurrency-with-hikaricp%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 20 at 7:46
user7294900
20.6k103258
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 at 8:06
2
@uneq95 you must close connection when finish using it, Hikari won't do it for you, see stackoverflow.com/questions/2225221/…
– user7294900
Nov 20 at 8:09
Won't the pool have to open a new connection, if I close the existing connection?
– uneq95
Nov 20 at 8:12
2
@uneq95 after closing connection, the connection will return to connection pool
– user7294900
Nov 20 at 8: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 at 8:15
add a comment |
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.
answered Nov 20 at 7:46
user7294900
20.6k103258
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 at 8:06
2
@uneq95 you must close connection when finish using it, Hikari won't do it for you, see stackoverflow.com/questions/2225221/…
– user7294900
Nov 20 at 8:09
Won't the pool have to open a new connection, if I close the existing connection?
– uneq95
Nov 20 at 8:12
2
@uneq95 after closing connection, the connection will return to connection pool
– user7294900
Nov 20 at 8: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 at 8:15
add a comment |
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.
answered Nov 20 at 7:46
user7294900
20.6k103258
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.
answered Nov 20 at 7:46
user7294900
20.6k103258
edited Nov 20 at 7:53
answered Nov 20 at 7:46
user7294900
20.6k103258
answered Nov 20 at 7:46
user7294900
20.6k103258
answered Nov 20 at 7:46
user7294900
20.6k103258
20.6k103258
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 at 8:06
2
@uneq95 you must close connection when finish using it, Hikari won't do it for you, see stackoverflow.com/questions/2225221/…
– user7294900
Nov 20 at 8:09
Won't the pool have to open a new connection, if I close the existing connection?
– uneq95
Nov 20 at 8:12
2
@uneq95 after closing connection, the connection will return to connection pool
– user7294900
Nov 20 at 8: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 at 8:15
add a comment |
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 at 8:06
2
@uneq95 you must close connection when finish using it, Hikari won't do it for you, see stackoverflow.com/questions/2225221/…
– user7294900
Nov 20 at 8:09
Won't the pool have to open a new connection, if I close the existing connection?
– uneq95
Nov 20 at 8:12
2
@uneq95 after closing connection, the connection will return to connection pool
– user7294900
Nov 20 at 8: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 at 8:15
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 at 8:06
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 at 8:06
2
2
@uneq95 you must close connection when finish using it, Hikari won't do it for you, see stackoverflow.com/questions/2225221/…
– user7294900
Nov 20 at 8:09
@uneq95 you must close connection when finish using it, Hikari won't do it for you, see stackoverflow.com/questions/2225221/…
– user7294900
Nov 20 at 8:09
Won't the pool have to open a new connection, if I close the existing connection?
– uneq95
Nov 20 at 8:12
Won't the pool have to open a new connection, if I close the existing connection?
– uneq95
Nov 20 at 8:12
2
2
@uneq95 after closing connection, the connection will return to connection pool
– user7294900
Nov 20 at 8:14
@uneq95 after closing connection, the connection will return to connection pool
– user7294900
Nov 20 at 8: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 at 8:15
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 at 8:15
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53388228%2fconcurrency-with-hikaricp%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Why don't you just test it?
– JB Nizet
Nov 20 at 7: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 at 7:49
@JBNizet I tested it as you suggested and the results turned out to be the same as answered.
– uneq95
Nov 20 at 8:23