You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Patrick Holthuizen <pa...@eaze.org> on 2010/09/11 10:31:16 UTC
question: is it possible to use a single connection for multiple
threads when auto-commit is on?
Hello,
In the Derby documentation I find that there are some restrictions when
using a single connection with multiple threads. For example and especially:
Here is a review of the potential pitfalls of sharing a single
/Connection/ among multiple threads.
* Committing or rolling back a transaction closes all open
/ResultSet/ objects and currently executing /Statements/, unless
you are using held cursors.
If one thread commits, it closes the /Statements/ and /ResultSets/
of all other threads using the same connection.
Now wonder if this is also true with auto-commit on? For example:
THREAD 1 THREAD 2
PreparedStatement s1 =
c.prepareStatement("SELECT * FROM T1");
ResultSet rs1 = s1.executeQuery();
rs.next();
...use record
PreparedStatement s2 =
c.prepareStatement("UPDATE T1
SET F1 = "value"
WHERE F2 =
"anotherValue");
rs.next();
...use record
Do I run into troubles when the second rs.next() is called?
Thank you in advance.
Sincerely,
Patrick Holthuizen
Re: question: is it possible to use a single connection for multiple threads when auto-commit is on?
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Patrick Holthuizen <pa...@eaze.org> writes:
> Hello,
>
> In the Derby documentation I find that there are some restrictions when
> using a single connection with multiple threads. For example and especially:
>
> Here is a review of the potential pitfalls of sharing a single
> /Connection/ among multiple threads.
>
> * Committing or rolling back a transaction closes all open
> /ResultSet/ objects and currently executing /Statements/, unless
> you are using held cursors.
>
> If one thread commits, it closes the /Statements/ and /ResultSets/
> of all other threads using the same connection.
>
>
> Now wonder if this is also true with auto-commit on? For example:
>
>
> THREAD 1 THREAD 2
> PreparedStatement s1 =
> c.prepareStatement("SELECT * FROM T1");
> ResultSet rs1 = s1.executeQuery();
> rs.next();
> ...use record
>
> PreparedStatement s2 =
>
> c.prepareStatement("UPDATE T1
> SET F1 = "value"
> WHERE F2 =
> "anotherValue");
>
> rs.next();
> ...use record
>
> Do I run into troubles when the second rs.next() is called?
No, I think this should work. Note that the quote from the manual said
that the ResultSet would be closed on commit "unless you are using held
cursors." Since Derby uses held cursors by default, the auto-commit
caused by the update statement in the above code will not close the
ResultSet.
If thread 1 first had called
c.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
non-held cursors would have been used, and the second call to rs.next()
would have failed.
--
Knut Anders