 |
AppletTalk.com
Java discussions newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Robert Klemme
Guest
|
 Posted: Tue Mar 01, 2005 6:02 pm Post subject: Single connection, multiple threads, one Statement per threa |
 |
|
Hi folks,
from what I read *every* JDBC driver is supposed to be capable of handling
concurrent accesses from several threads at a time. However the spec
leaves it completely open how much concurrency a driver implementation
allows.
I'm interested to hear what you folks experienced with a single Connection
and one statement per thread in a multithreaded environment. Did you
actually experience increased concurrency compared to using a single
statement synchronized? The drivers I'm interested in are
- Oracle thin
- MaxDB
- jTDS
Here's what I found so far:
- jTDS seems to queue requests for different Statements of a single
connection (tested with "waitfor delay ..." and "{call foo1}" where foo1
does the waiting).
- Oracle thin behaves similar (tested with "begin dbms_lock.sleep(30);
end;" and "{call foo1}" where foo1
does the waiting).
So that suggests there's no way to efficiently work with the same
connection (and thus transaction) concurrently with existing drivers. Any
comments on this? Thanks a lot!
Kind regards
robert
|
|
| Back to top |
|
 |
Alin Sinpalean
Guest
|
| Posted: Tue Mar 01, 2005 8:09 pm Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
Robert Klemme wrote:
| Quote: | Hi folks,
from what I read *every* JDBC driver is supposed to be capable of
handling
concurrent accesses from several threads at a time. However the spec
leaves it completely open how much concurrency a driver
implementation
allows.
|
Not quite. According to the spec, a driver can choose to support a
maximum number of statements per connection, which can be 1 (as is the
case with the JDBC-ODBC bridge). As long as the DatabaseMetaData method
that returns this value (don't remember the name exactly) returns 1 (or
whatever the maximum is) all is ok according to the spec.
| Quote: | - jTDS seems to queue requests for different Statements of a single
connection (tested with "waitfor delay ..." and "{call foo1}" where
foo1
does the waiting).
|
jTDS does queue requests on the same Connection, indeed, but only as
long as there is one request executing. If you have two statements A
and B and statement A executes a SELECT query that immediately starts
returning results and statement B tries to execute a second query
before statement A finished processing the whole ResultSet, then the
rest of A's ResultSet will be cached (to memory first, then disk if a
certain limit is reached) and statement B will execute. So from the
point of view of result processing jTDS supports real concurrency
between statements; if you want more than one query executing at the
same time, though, then that is obviously impossible. I don't think any
driver/DBMS supports more than one statement executing on a single
connection.
| Quote: | - Oracle thin behaves similar (tested with "begin
dbms_lock.sleep(30);
end;" and "{call foo1}" where foo1
does the waiting).
|
Sorry, no idea here.
| Quote: | So that suggests there's no way to efficiently work with the same
connection (and thus transaction) concurrently with existing drivers.
|
As I explained above, if what you're looking for is concurrency in
processing results then you have it; if what you're looking for is to
use multiple Statement objects just as you would use separate
connections, then no, that's not possible.
Anyway, I don't think anyone will recommend using a single Connection
over multiple threads. Just because it works, it doesn't mean it's the
way to do it. Pretty much all server applications use connection pools
and each thread gets a separate connection. The only advantage with
using a single connection is that (as you observed) you could run
everything in a single transaction; for that, however, there are
distributed transactions; performance will still suck with them
however, compared to plain transactions.
Alin,
The jTDS Project.
|
|
| Back to top |
|
|
Daniel Dittmar
Guest
|
| Posted: Tue Mar 01, 2005 9:51 pm Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
Robert Klemme wrote:
| Quote: | Hi folks,
from what I read *every* JDBC driver is supposed to be capable of handling
concurrent accesses from several threads at a time. However the spec
leaves it completely open how much concurrency a driver implementation
allows.
I'm interested to hear what you folks experienced with a single Connection
and one statement per thread in a multithreaded environment. Did you
actually experience increased concurrency compared to using a single
statement synchronized? The drivers I'm interested in are
- Oracle thin
- MaxDB
- jTDS
|
For MaxDB, only one SQL request can be on the network. This means that
for executeUpdate, you won't gain anything, requests are synchronized on
the Connection object. When iterating through ResultSets, you might see
some gains as the driver gets the rows in bunches. So while thread A
waits for the next bunch of one ResultSet, thread B can translate the
data of a second ResultSet from SQL to Java.
Using one connection per thread is still the better idea.
Daniel
|
|
| Back to top |
|
|
joeNOSPAM@BEA.com
Guest
|
| Posted: Tue Mar 01, 2005 11:22 pm Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
The fact is that the DBMS is going to consider the connection as a
single user sending a series of queries and updates, and will
certainly only be doing one thing at a time for this user. The
driver will cooperate with the DBMS as much as it needs to act
like that single user. Some DBMSes allow a stream of input, such
as sending one query before the prior query is finished or the
returns are processed, and the driver may well allow that (though
certainly serialized at the socket/query level). Some DBMSes that
have an RPC-based protocol will only allow one request at a time,
and the driver will then enforce that.
If you want performance and non-interaction, use a connection
per thread. Use pooling.
Joe Weinstein at BEA
|
|
| Back to top |
|
|
Robert Klemme
Guest
|
| Posted: Wed Mar 02, 2005 9:44 am Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
"Alin Sinpalean" <alin (AT) earthling (DOT) net> schrieb im Newsbeitrag
news:1109707788.340308.188390 (AT) z14g2000cwz (DOT) googlegroups.com...
| Quote: | Robert Klemme wrote:
Hi folks,
from what I read *every* JDBC driver is supposed to be capable of
handling
concurrent accesses from several threads at a time. However the spec
leaves it completely open how much concurrency a driver
implementation
allows.
Not quite. According to the spec, a driver can choose to support a
maximum number of statements per connection, which can be 1 (as is the
case with the JDBC-ODBC bridge). As long as the DatabaseMetaData method
that returns this value (don't remember the name exactly) returns 1 (or
whatever the maximum is) all is ok according to the spec.
|
I don't see how this contradicts my statement above. It is indeed at the
discretion of the driver how much concurrency it allows. You just confirm
that with your statement. According to [1] a driver may even support
concurrently executing statements if the db supports it.
| Quote: | - jTDS seems to queue requests for different Statements of a single
connection (tested with "waitfor delay ..." and "{call foo1}" where
foo1
does the waiting).
jTDS does queue requests on the same Connection, indeed, but only as
long as there is one request executing. If you have two statements A
and B and statement A executes a SELECT query that immediately starts
returning results and statement B tries to execute a second query
before statement A finished processing the whole ResultSet, then the
rest of A's ResultSet will be cached (to memory first, then disk if a
certain limit is reached) and statement B will execute. So from the
point of view of result processing jTDS supports real concurrency
between statements; if you want more than one query executing at the
same time, though, then that is obviously impossible.
|
Thank you for the indepth explanation! Regarding what others wrote this
seems to be a common behavior across JDBC drivers. Good to know.
| Quote: | I don't think any
driver/DBMS supports more than one statement executing on a single
connection.
|
Quoting from [1]: "Some database APIs, such as ODBC, provide mechanisms
for allowing SQL statements to execute asynchronously." So it may very
well be that there is a db and driver that support concurrent execution on
the same connection.
| Quote: | - Oracle thin behaves similar (tested with "begin
dbms_lock.sleep(30);
end;" and "{call foo1}" where foo1
does the waiting).
Sorry, no idea here.
So that suggests there's no way to efficiently work with the same
connection (and thus transaction) concurrently with existing drivers.
As I explained above, if what you're looking for is concurrency in
processing results then you have it; if what you're looking for is to
use multiple Statement objects just as you would use separate
connections, then no, that's not possible.
|
Too bad, because that's exactly what I'm looking for. :-(
| Quote: | Anyway, I don't think anyone will recommend using a single Connection
over multiple threads. Just because it works, it doesn't mean it's the
way to do it. Pretty much all server applications use connection pools
and each thread gets a separate connection.
|
I know that and that's what we usually do. But I'm trying to optimize
performance here and unfortunately using a connection per thread (which
means a TX per thread) has different semantics. It's not that big issue
with SQL Server as that supports dirty reads - but Oracle unfortunately
doesn't and using multiple TX causes some weired locking issue that we
haven't been able to track down. So doing everything in one transaction
but in parallel seems the most obvious solution.
| Quote: | The only advantage with
using a single connection is that (as you observed) you could run
everything in a single transaction;
|
Well, you pinpointed the reason why I was looking for this in the first
place. :-)
| Quote: | for that, however, there are
distributed transactions; performance will still suck with them
however, compared to plain transactions.
|
Apparently we have a different understanding of distributed TX: IMHO this
term refers to a scenario with a single actor making transaction safe
changes to several resources (for example databases). Typically this is
implemented using two phase commit. Is there another usage of the term
"distributed transaction" that I'm not aware of?
Again, thanks for calling.
Kind regards
robert
[1] http://java.sun.com/products/jdbc/driverdevs.html#997566
|
|
| Back to top |
|
|
Robert Klemme
Guest
|
| Posted: Wed Mar 02, 2005 9:44 am Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
"joeNOSPAM (AT) BEA (DOT) com" <joe.weinstein (AT) gmail (DOT) com> schrieb im Newsbeitrag
news:1109719376.299718.184860 (AT) l41g2000cwc (DOT) googlegroups.com...
| Quote: | The fact is that the DBMS is going to consider the connection as a
single user sending a series of queries and updates, and will
certainly only be doing one thing at a time for this user.
|
Although the databases in question seem to behave according to what you
describe I don't think this is a strict requirement that falls off the
relational and transactional model. In fact, databases are capable of
doing a lot in parallel, so they would "simply" need to extend this to
doing things in parallel for a single tx. But I can see why this is not
too likely to happen.
| Quote: | The
driver will cooperate with the DBMS as much as it needs to act
like that single user. Some DBMSes allow a stream of input, such
as sending one query before the prior query is finished or the
returns are processed, and the driver may well allow that (though
certainly serialized at the socket/query level). Some DBMSes that
have an RPC-based protocol will only allow one request at a time,
and the driver will then enforce that.
If you want performance and non-interaction, use a connection
per thread. Use pooling.
Joe Weinstein at BEA
|
Thanks Joe, unfortunately this is not a option in this case (see my other
posting).
Kind regards
robert
|
|
| Back to top |
|
|
Alin Sinpalean
Guest
|
| Posted: Wed Mar 02, 2005 3:01 pm Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
Robert Klemme wrote:
| Quote: | I don't think any
driver/DBMS supports more than one statement executing on a single
connection.
Quoting from [1]: "Some database APIs, such as ODBC, provide
mechanisms
for allowing SQL statements to execute asynchronously." So it may
very
well be that there is a db and driver that support concurrent
execution on
the same connection.
|
Executing a statement asynchronously doesn't mean that you can execute
more than one statement on the same connection. It simply means that
the calling thread can do some other work in the meantime and it will
be notified when the statement completes. The whole point is that it's
generally more complicated (or at least less standardized) to create
and use threads in other languages, so anynchronous requests are used
there.
| Quote: | Anyway, I don't think anyone will recommend using a single
Connection
over multiple threads. Just because it works, it doesn't mean it's
the
way to do it. Pretty much all server applications use connection
pools
and each thread gets a separate connection.
I know that and that's what we usually do. But I'm trying to
optimize
performance here and unfortunately using a connection per thread
(which
means a TX per thread) has different semantics. It's not that big
issue
with SQL Server as that supports dirty reads - but Oracle
unfortunately
doesn't and using multiple TX causes some weired locking issue that
we
haven't been able to track down. So doing everything in one
transaction
but in parallel seems the most obvious solution.
|
You are trying to avoid certain shortcommings of using multiple
transactions, but have you thought that the DBMS would have to deal
with the same stuff internally if it allowed simultaneous execution of
multiple statements? And I have never seen any documentation explaining
if and how dirty reads for example would work with different statements
on the same connection.
| Quote: | for that, however, there are
distributed transactions; performance will still suck with them
however, compared to plain transactions.
Apparently we have a different understanding of distributed TX: IMHO
this
term refers to a scenario with a single actor making transaction safe
changes to several resources (for example databases). Typically this
is
implemented using two phase commit. Is there another usage of the
term
"distributed transaction" that I'm not aware of?
|
Distributed transactions mean that you can work with several resources
within the same transaction, indeed. But this doesn't mean the
resources have to be of different types; they could all be connections
to the same database working with the same data. And this would use two
phase commit indeed. But again, I have no idea what should happen if
data is updated through one connection and accessed through the other.
Alin,
The jTDS Project.
|
|
| Back to top |
|
|
joeNOSPAM@BEA.com
Guest
|
| Posted: Wed Mar 02, 2005 5:55 pm Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
Hi. DBMSes will never do any parallelization of user queries on a
connection. This is because there is a strong transactional
reliance on the order in which DBMS resources are locked. If
two users each do the same SQL to query (and lock) DBMS
resource A then to query (and lock) resource B, but parallelity
causes one of these to lock B first, then go for A, it would cause
a deadlock.
DBMSes may certainly employ parallelism while processing
a given single query, but will always serialize a single user's
(connection's) requests.
Joe Weinstein at BEA
|
|
| Back to top |
|
|
Robert Klemme
Guest
|
| Posted: Thu Mar 03, 2005 10:55 am Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
"joeNOSPAM (AT) BEA (DOT) com" <joe.weinstein (AT) gmail (DOT) com> schrieb im Newsbeitrag
news:1109786134.845473.280910 (AT) f14g2000cwb (DOT) googlegroups.com...
| Quote: | Hi. DBMSes will never do any parallelization of user queries on a
connection. This is because there is a strong transactional
reliance on the order in which DBMS resources are locked. If
two users each do the same SQL to query (and lock) DBMS
resource A then to query (and lock) resource B, but parallelity
causes one of these to lock B first, then go for A, it would cause
a deadlock.
|
Hm, but this situation can occur even without concurrent execution per
transaction - it's sufficient that both transactions use different code to
access the same resources in different order. OTOH concurrency per
transactions would certainly increase the likelyhood of deadlocks.
| Quote: | DBMSes may certainly employ parallelism while processing
a given single query, but will always serialize a single user's
(connection's) requests.
|
Yeah, I guess you're right. Too sad... :-)
Thanks again!
Kind regards
robert
|
|
| Back to top |
|
|
Robert Klemme
Guest
|
| Posted: Thu Mar 03, 2005 10:59 am Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
"Alin Sinpalean" <alin (AT) earthling (DOT) net> schrieb im Newsbeitrag
news:1109775680.908184.266980 (AT) g14g2000cwa (DOT) googlegroups.com...
| Quote: | Executing a statement asynchronously doesn't mean that you can execute
more than one statement on the same connection. It simply means that
the calling thread can do some other work in the meantime and it will
be notified when the statement completes. The whole point is that it's
generally more complicated (or at least less standardized) to create
and use threads in other languages, so anynchronous requests are used
there.
|
That's true.
| Quote: | You are trying to avoid certain shortcommings of using multiple
transactions, but have you thought that the DBMS would have to deal
with the same stuff internally if it allowed simultaneous execution of
multiple statements? And I have never seen any documentation explaining
if and how dirty reads for example would work with different statements
on the same connection.
|
Good point, very good point!
| Quote: | for that, however, there are
distributed transactions; performance will still suck with them
however, compared to plain transactions.
Apparently we have a different understanding of distributed TX: IMHO
this
term refers to a scenario with a single actor making transaction safe
changes to several resources (for example databases). Typically this
is
implemented using two phase commit. Is there another usage of the
term
"distributed transaction" that I'm not aware of?
Distributed transactions mean that you can work with several resources
within the same transaction, indeed. But this doesn't mean the
resources have to be of different types; they could all be connections
to the same database working with the same data. And this would use two
phase commit indeed. But again, I have no idea what should happen if
data is updated through one connection and accessed through the other.
|
I guess the same locking policies as for several single transactions would
be employed. Anyway, the overhead is likely to be too high to make DTX a
good solution.
Thank you for your time!
Kind regards
robert
|
|
| Back to top |
|
|
Lee Fesperman
Guest
|
| Posted: Sat Mar 05, 2005 12:37 am Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
Robert Klemme wrote:
| Quote: |
"joeNOSPAM (AT) BEA (DOT) com" <joe.weinstein (AT) gmail (DOT) com> schrieb im Newsbeitrag
news:1109786134.845473.280910 (AT) f14g2000cwb (DOT) googlegroups.com..
Hi. DBMSes will never do any parallelization of user queries on a
connection. This is because there is a strong transactional
reliance on the order in which DBMS resources are locked. If
two users each do the same SQL to query (and lock) DBMS
resource A then to query (and lock) resource B, but parallelity
causes one of these to lock B first, then go for A, it would cause
a deadlock.
Hm, but this situation can occur even without concurrent execution per
transaction - it's sufficient that both transactions use different code to
access the same resources in different order. OTOH concurrency per
transactions would certainly increase the likelyhood of deadlocks.
DBMSes may certainly employ parallelism while processing
a given single query, but will always serialize a single user's
(connection's) requests.
Yeah, I guess you're right. Too sad... 
|
At first, I was surprised at Joe's assertion that no DBMS would allow parallel (atomic)
requests/actions by the same session (connection), even though that is exactly what we
do (that is, serialize user actions in the same session). After thinking about it, I
remember the rationale.
Deadlocks need not be a problem because it is the same session performing the locks. In
fact, this could occur. For instance, a session could have two updatable resultsets on
the same table. Both would do separate readlocks and then updatelocks on the same row.
I'm not sure if other DBMSs support this anyway. However, parallel updates to the same
row would create simultaneous update problems on the row.
The only way I can see to avoid this restriction would be for the DBMS to use temporary
sub-locks within a session. This could be a lot of overhead/complexity (new types of
deadlocks) as opposed to simply serializing user actions under a session.
--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
|
|
| Back to top |
|
|
joeNOSPAM@BEA.com
Guest
|
| Posted: Sat Mar 05, 2005 12:56 am Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
Hi Lee. My point wasn't about a deadlock within a given session/user,
but rather that if two connections are concurrently executing the
same series of operations in the same order (as they are typically
programmed specifically to avoid deadlocks), but if the DBMS did
not rigidly serialize the actions of a given connection, it might
lock things in different orders for the same client submissions,
and cause a deadlock that was specifically coded against by the
client architecture.
Joe Weinstein at BEA
|
|
| Back to top |
|
|
Lee Fesperman
Guest
|
| Posted: Tue Mar 08, 2005 9:48 am Post subject: Re: Single connection, multiple threads, one Statement per t |
|
|
[email]joeNOSPAM (AT) BEA (DOT) com[/email] wrote:
| Quote: |
Hi Lee. My point wasn't about a deadlock within a given session/user,
but rather that if two connections are concurrently executing the
same series of operations in the same order (as they are typically
programmed specifically to avoid deadlocks), but if the DBMS did
not rigidly serialize the actions of a given connection, it might
lock things in different orders for the same client submissions,
and cause a deadlock that was specifically coded against by the
client architecture.
|
Right, you did mention 2 queries in your posting.
For this to be a problem, there would need to be 2 threads each executing a query on the
same connection. However, serialization by the DBMS doesn't actually solve the problem.
Say the first thread is executing query A and the second query B. Since the threads are
not synchronized (if they were, there would be no problem), the order of execution could
be A followed by B, or it could be B followed by A. Because of thread timings, this
could be different for each session. There is nothing the DBMS could do about this.
BTW, I thought of an example of an action that would have to be serialized --- a call to
a stored procedure. The procedure could execute several SQL commands. Interspersing two
procedure sequences in the same session/connection could produce incorrect results.
--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
