Re: Oracle JDBC connection class (10.1 vs 10.2)

On Jun 14, 5:14 pm, Thomas Olszewicki <Thom..._at_cpas.com> wrote:
> We have come across rather puzzling difference in JDBC documentation
> for JDBC Connection class in Oracle v 10.2 versus 10.1.
>
> Totally different recommendations for sharing connection object
> between threads.
>
> >From Oracle 10.2 JDBC Developer's Guide :
>
> "The Oracle JDBC drivers provide full support for, and are highly
> optimized for,
> applications that use Java multithreading.
> Controlled serial access to a connection, such as that provided by
> connection caching,
> is both necessary and encouraged.
> However, Oracle strongly discourages sharing a database connection
> among multiple threads.
> Avoid allowing multiple threads to access a connection
> simultaneously.
> If multiple threads must share a connection, use a disciplined begin-
> using/end-using protocol."
> URL references:
> http://download-east.oracle.com/docs/cd/B19306_01/java.102/b14355/apx...
>
> Same fragment>From Oracle 10.1 and before (9.x)
>
> "The Oracle JDBC drivers provide full support for programs that use
> Java multithreading.
> The following example creates a specified number of threads and lets
> you determine
> whether or not the threads will share a connection.
> If you choose to share the connection, then the same JDBC connection
> object
> will be used by all threads (each thread will have its own statement
> object, however).
> Because all Oracle JDBC API methods are synchronized,
> if two threads try to use the connection object simultaneously,
> then one will be forced to wait until the other one finishes its
> use."
> URL references:
> http://download-east.oracle.com/docs/cd/B14117_01/java.101/b10979/tip...
>
> Does it mean that Oracle took a giant step backward ?
>
> At the same time Oracle claims there are no significant changes in
> JDBC driver
> between version 10.2 and 10.1.
>
> Tests provided with JDBC v 10.1 work the same way in 10.2.
>
> Has anybody come across any other Oracle documentation describing this
> change or
> any other related topic.
>
> (We didn't find anything at Google or Oracle documentation, or we
> don't know what key words to search)
>
> TIA
>
> Thomas Olszewicki
> CPAS Systems Inc.

Hm. Odd indeed. Just let me throw in a few thoughts on this: * It would seem that they have overworked that part of the docs, so it may be a case of enlightenment on the part of the Oracle developers. * It would seem to me that you indeed *can* use a connection (vie multiple stmt obj) from several threads without further sync. * However, I think under normal circumstances this is something that one wouldn't want to use: Since the connection object is used to commit() you would have to have some (weird?) case where you want to have multiple threads executing statements against the DB for a single transaction. (And then you would have to sync the commit() anyway.) (AutoCommit is oc evil :) )

So my take on the 10.1 version would be that it was written from a Java perspective whereas 10.2 has been modified for something making more sense on the DB side of things.
> Controlled serial access to a connection, such as that provided by
> connection caching,
> is both necessary and encouraged.

... they key would be "Controlled serial access " I think.

> However, Oracle strongly discourages sharing a database connection
> among multiple threads.
> Avoid allowing multiple threads to access a connection
> simultaneously.

... well, "strongly discourages" doesn't sound like not working, just like "don't do it".

cheers,
Martin

p.s.: Oh, and don't forget: friendly Oracle/JDBC will do an implicit commit(!) on conn.close(); Received on Thu Jun 14 2007 - 15:06:44 CDT