We all know it's possible to have multiple Statements from one Connection.
Where it breaks down is whether or not you can have multiple *threads* using one Connection.
The line that everyone would probably agree with is "avoid if you can". But I'd kinda like to know, "is it possible", and I'm getting a bit of conflicting advice from previous javaranch posts.
Topic: multiple threads dealing db In which Peter den Haan offered: This would mean that multiple threads can use one and the same Connection (but don't have to), as long as they all used their own Statements
Which is in fact, the exact situation I'm wanting to code. One connection object, and say 5 threads, each with their own PreparedStatement of the one and same connection. The threads are 'processing' threads, and they are all doing exactly the same thing (and have the same PreparedStatement).
But here's another thread... Topic: Can I reuse connection ? In which Andy Bowes offered: You will encounter huge problems if multiple threads are attempting to use the same connection simultaneously. Awww... This doesn't seem right, given what the API says about java.sql and javax.sql objects required to be threadsafe.
From the same topic, read Avi Abrami's entire reply (won't quote it here). Basically Avi says "no it's fine, the other threads will block". Which *IS* thread safe, but not performant or concurrent.
Also, (ok, a small quote)... if two separate threads invoke the "executeQuery()" method (in class "java.sql.Statement"), the second thread is blocked, until the first thread invokes the "close()" method.
This seems pretty lame, for lack of a more technical word. If I close the statement, my ResultSet goes away. So one thread would have to completely finish with it's ResultSet, before the next thread would even get to retrieve its ResultSet. This is not my idea of performant multi-threading and since I'm wanting to multi-thread to increase performance, it looks like I'll be using a Connection object *per* thread. I was hoping to conserve database resources by just using the one.
Has anyone else had any experience in this area? (Connections and threads).
Only if the pool contained enough connections for each of my threads. My threads are not going to be 'returning' the connection to the pool at the end of each 'processing cycle'. They each have several different statements (not just the one i mentioned above).
The pool paradigm suggests that the object using the connection should return it to the pool at the earliest convenience, to be a good citizen. My threads will be coded in such a way that they will not return the connection until they are completely done with their work, which could mean hundreds of loops.
I might code that differently, to return the connection after each 'iteration' of work, but with 3-4 statements each, it seems like a waste to construct and tear-down those statements each time through the loop. I'd rather obtain and keep a connection until I'm 'completely done'.
So the 'pool' then, is exactly as large as my number of threads. Because I don't want a situation where some of those threads might starve for want of a connection from a shared pool. And I also don't want this process to starve other threads, when the greedily obtain and retain a connection for so long.
Mike, if you are using one connection and each thread has its own statements, there won't be a problem. The only would be DB resource competition, but the DB will efficiently handle this. If you are using the same Statement/PreparedStatement in each thread there will be problems ( what if one thread sets a parameter, then another thread overwrites the one set in the first thread, or in one thread you are halfway through iterating through a resultset and in another thread you use that same statement to create another resultset, the first one will be closed ( as stated earlier ). But even if it was thread safe, wouldn't you get better performance by using different statements? You could have 2 or more statements accessing the DB at the "same" time during DB idle periods, not having to wait for the one statement to be free for use before you can perform more DB operations.
I should clarify something... I said: Which is in fact, the exact situation I'm wanting to code. One connection object, and say 5 threads, each with their own PreparedStatement of the one and same connection. The threads are 'processing' threads, and they are all doing exactly the same thing (and have the same PreparedStatement).
What I meant to say was that the threads have their own PreparedStatement objects, and do not share any objects (except maybe the Connection), but that the Statement objects were constructed with exactly the same SQL. That is: All threads are instances of a 'worker class' that implements Runnable. So the work the threads are doing is exactly the same, while each still has its own objects.
My only remaining concern is the ability to use one Connection object between multiple Statements 'concurrently'. Take this scenario: Going on Avi's post regarding blocking, my first worker thread should enter the loop, and effectively 'block' any other thread from using their Statements, until I call ps.close(). Which is not good. Or is that not what will happen?