2

In my project, developers use a single instance of Connection instead of a connection pool on an Oracle 12c.

Using a pool is a common practice and Oracle itself documents it: http://docs.oracle.com/database/121/JJUCP/get_started.htm#JJUCP8120.

But JDBC 4.2 specification says:

13.1.1 Creating Statements

Each Connection object can create multiple Statement objects that may be used concurrently by the program.

Why using a pool of connections instead of a single connection, if it's possible to use statements to manage concurrency?

Community
  • 1
  • 1
Pleymor
  • 2,611
  • 1
  • 32
  • 44
  • This answer: http://stackoverflow.com/a/5949505 mentionned that an Oracle connection could only have 1 active statement. But I couldn't verify this info in Oracle documentation. – Pleymor Mar 14 '17 at 15:51

4 Answers4

2

The Oracle Database Dev Team strongly discourages using a single Connection in multiple threads. That almost always causes problems. As a general rule we will not consider any problem report that does this.

A Connection can have multiple Statements and/or ResultSets open at one time but only one can execute at a time. Connections are strictly single threaded and blocking. We try to prevent multiple threads from accessing a Connection simultaneously but there are a few odd cases where it is possible. These are all but guaranteed to cause problems. (It is not practical to fix or prevent these cases mostly for performance reasons. Just don't share a single Connection across multiple threads.)

Douglas Surber
  • 622
  • 4
  • 9
0

If a client connects to the database via a dedicated server connection then that database session will only serve that client . If the client connects to the database via shared server connection, then a given database session may serve multiple clients over its lifetime.

This is documented here.

Also, at any one point in time, a session can only execute one thing at a time. If that wasn't the case, then running things in parallel wouldn't spawn multiple other sessions!

Boneist
  • 22,910
  • 1
  • 25
  • 40
0

A single connection cannot execute several statements concurrently.

BobC
  • 4,208
  • 1
  • 12
  • 15
  • But JDBC specification mentions a connection can create multiple statements to be used concurrently (thanks, I've updated my question). – Pleymor Mar 23 '17 at 19:46
0

Yes one connection can execute more that one statement. It will be the programmer to chose connection pooling setting or multiple statements when executing over more than one thread. Most databases in the market can handle multiple statements in one connection.

user4534599
  • 16
  • 1
  • 2