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).