Does using DerbyDB require synchronization?

[derbyfan195 <hu...@gmail.com>]

Just started using Derby in my Java code and realized some weird
synchronization issues. I have multiple threads accessing the same Derby
Database (embedded one) through a single connection. They issue read-only
queries, i.e., select, each with its own statement, and does some operations
on the returned ResultSet using ResultSet.next() operation. However, I
noticed sometimes, one of these next() calls would fail and gave me an error
of next() is not permitted, make sure that AUTO_COMMIT is turned OFF. I
don't know what this error means or why I need to turn AUTO_COMMIT off. Do I
need any synchronization among the statement objects obtained from the same
connection object? Any help would be appreciated.

-
Hai
-- 
View this message in context: http://www.nabble.com/Does-using-DerbyDB-require-synchronization--tp19379460p19379460.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Does using DerbyDB require synchronization?

[Mike Matrigali <mi...@sbcglobal.net>]

Mamta Satoor wrote:
> I think if the resultsets are created with held over commit, then
> auto-commit on or off should not matter.
No, held result sets are still affected by commits, just not closed.
After a commit the only allowed action on a held cursor is a next or
a close, other actions may return unexpected errors in the use case
this user is describing.
> 
> Mamta
> 
> On 9/9/08, Knut Anders Hatlen <Kn...@sun.com> wrote:
>> derbyfan195 <hu...@gmail.com> writes:
>>
>>> Knut,
>>>
>>> Thanks for your response. I'm more interested in not having to establish
>>> separate connections for each thread as I've noticed establishing connection
>>> in Derby takes a while, so if I can use the same connection among the
>>> different threads (with some synchronization), that would be the ideal case
>>> for me at the moment.
>>>
>>> What puzzles me is that you said when you close a ResultSet on a connection,
>>> all other ResultSets lose their position. My understanding is that from a
>>> Connection, you get a Statement, and from a Statement, you get a ResultSet.
>>> Having multiple ResultSet from the same Statement is troublesome, but I
>>> thought ResultSets from different Statements should never have conflict with
>>> each other, i.e., if I close a ResultSet on Statement A, it shouldn't affect
>>> another ResultSet that's opened on Statement B. Is this understanding not
>>> correct?
>> Yes, I believe this is correct when auto-commit is turned off. When
>> auto-commit is turned on, closing one ResultSet may affect all
>> ResultSets in that Connection, even if they were created by different
>> Statements.
>>
>> --
>> Knut Anders
>>
> 

Re: Does using DerbyDB require synchronization?

[Mamta Satoor <ms...@gmail.com>]

I think if the resultsets are created with held over commit, then
auto-commit on or off should not matter.

Mamta

On 9/9/08, Knut Anders Hatlen <Kn...@sun.com> wrote:
> derbyfan195 <hu...@gmail.com> writes:
>
> > Knut,
> >
> > Thanks for your response. I'm more interested in not having to establish
> > separate connections for each thread as I've noticed establishing connection
> > in Derby takes a while, so if I can use the same connection among the
> > different threads (with some synchronization), that would be the ideal case
> > for me at the moment.
> >
> > What puzzles me is that you said when you close a ResultSet on a connection,
> > all other ResultSets lose their position. My understanding is that from a
> > Connection, you get a Statement, and from a Statement, you get a ResultSet.
> > Having multiple ResultSet from the same Statement is troublesome, but I
> > thought ResultSets from different Statements should never have conflict with
> > each other, i.e., if I close a ResultSet on Statement A, it shouldn't affect
> > another ResultSet that's opened on Statement B. Is this understanding not
> > correct?
>
> Yes, I believe this is correct when auto-commit is turned off. When
> auto-commit is turned on, closing one ResultSet may affect all
> ResultSets in that Connection, even if they were created by different
> Statements.
>
> --
> Knut Anders
>

Re: Does using DerbyDB require synchronization?

[derbyfan195 <hu...@gmail.com>]

Knut Anders Hatlen wrote:
> 
> derbyfan195 <hu...@gmail.com> writes:
> 
>> Knut,
>>
>> Thanks for your response. I'm more interested in not having to establish
>> separate connections for each thread as I've noticed establishing
>> connection
>> in Derby takes a while, so if I can use the same connection among the
>> different threads (with some synchronization), that would be the ideal
>> case
>> for me at the moment. 
>>
>> What puzzles me is that you said when you close a ResultSet on a
>> connection,
>> all other ResultSets lose their position. My understanding is that from a
>> Connection, you get a Statement, and from a Statement, you get a
>> ResultSet.
>> Having multiple ResultSet from the same Statement is troublesome, but I
>> thought ResultSets from different Statements should never have conflict
>> with
>> each other, i.e., if I close a ResultSet on Statement A, it shouldn't
>> affect
>> another ResultSet that's opened on Statement B. Is this understanding not
>> correct? 
> 
> Yes, I believe this is correct when auto-commit is turned off. When
> auto-commit is turned on, closing one ResultSet may affect all
> ResultSets in that Connection, even if they were created by different
> Statements.
> 
> -- 
> Knut Anders
> 
> 

Thanks much! I'm wondering what would be the best solution here with minimal
code change. One option is to use a semaphore among the different threads so
only one thread will be able to read that database table. Our app is not
performance critical, so losing some performance is ok for us. But is there
something even easier? e.g., configure the database table so only one reader
can access it at any time? That won't even require code change. Any
thoughts?

-
Hai
-- 
View this message in context: http://www.nabble.com/Does-using-DerbyDB-require-synchronization--tp19379460p19394514.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Does using DerbyDB require synchronization?

[Knut Anders Hatlen <Kn...@Sun.COM>]

derbyfan195 <hu...@gmail.com> writes:

> Knut,
>
> Thanks for your response. I'm more interested in not having to establish
> separate connections for each thread as I've noticed establishing connection
> in Derby takes a while, so if I can use the same connection among the
> different threads (with some synchronization), that would be the ideal case
> for me at the moment. 
>
> What puzzles me is that you said when you close a ResultSet on a connection,
> all other ResultSets lose their position. My understanding is that from a
> Connection, you get a Statement, and from a Statement, you get a ResultSet.
> Having multiple ResultSet from the same Statement is troublesome, but I
> thought ResultSets from different Statements should never have conflict with
> each other, i.e., if I close a ResultSet on Statement A, it shouldn't affect
> another ResultSet that's opened on Statement B. Is this understanding not
> correct? 

Yes, I believe this is correct when auto-commit is turned off. When
auto-commit is turned on, closing one ResultSet may affect all
ResultSets in that Connection, even if they were created by different
Statements.

-- 
Knut Anders

Re: Does using DerbyDB require synchronization?

[derbyfan195 <hu...@gmail.com>]

Bryan Pendleton wrote:
> 
>> separate connections for each thread as I've noticed establishing
>> connection
>> in Derby takes a while,
> 
> Another way to address this is to use connection pooling. Use a separate
> connection for each thread, but when a unit of work is complete, commit
> the connection's transaction, then return it to the pool, and re-acquire
> the connection again from the pool later when you are ready to perform
> the next unit of work.
> 
> In my application, I have many (hundreds) of threads, each with their
> own connection to the database, but I very rarely have more than 10-12
> active connections because the connections are pooled and re-used among
> the various threads.
> 
> thanks,
> 
> bryan
> 
> 
> 

Thanks for your response. This is definitely the way to go if performance is
an issue, but performance is not a issue for our app, and I just need to get
this app to work as quickly as possible, so I'm looking for a solution that 
requires minimal change to the code. Please have a look at my response to
Knut
and see if any of my proposals are sound. Thanks a lot!

-
hai

-- 
View this message in context: http://www.nabble.com/Does-using-DerbyDB-require-synchronization--tp19379460p19394591.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Does using DerbyDB require synchronization?

[Bryan Pendleton <bp...@amberpoint.com>]

> separate connections for each thread as I've noticed establishing connection
> in Derby takes a while,

Another way to address this is to use connection pooling. Use a separate
connection for each thread, but when a unit of work is complete, commit
the connection's transaction, then return it to the pool, and re-acquire
the connection again from the pool later when you are ready to perform
the next unit of work.

In my application, I have many (hundreds) of threads, each with their
own connection to the database, but I very rarely have more than 10-12
active connections because the connections are pooled and re-used among
the various threads.

thanks,

bryan

Re: Does using DerbyDB require synchronization?

[derbyfan195 <hu...@gmail.com>]

Knut,

Thanks for your response. I'm more interested in not having to establish
separate connections for each thread as I've noticed establishing connection
in Derby takes a while, so if I can use the same connection among the
different threads (with some synchronization), that would be the ideal case
for me at the moment. 

What puzzles me is that you said when you close a ResultSet on a connection,
all other ResultSets lose their position. My understanding is that from a
Connection, you get a Statement, and from a Statement, you get a ResultSet.
Having multiple ResultSet from the same Statement is troublesome, but I
thought ResultSets from different Statements should never have conflict with
each other, i.e., if I close a ResultSet on Statement A, it shouldn't affect
another ResultSet that's opened on Statement B. Is this understanding not
correct? 

Looking forward to your reply. Thanks!

-
Hai


Knut Anders Hatlen wrote:
> 
> derbyfan195 <hu...@gmail.com> writes:
> 
>> Just started using Derby in my Java code and realized some weird
>> synchronization issues. I have multiple threads accessing the same Derby
>> Database (embedded one) through a single connection. They issue read-only
>> queries, i.e., select, each with its own statement, and does some
>> operations
>> on the returned ResultSet using ResultSet.next() operation. However, I
>> noticed sometimes, one of these next() calls would fail and gave me an
>> error
>> of next() is not permitted, make sure that AUTO_COMMIT is turned OFF. I
>> don't know what this error means or why I need to turn AUTO_COMMIT off.
>> Do I
>> need any synchronization among the statement objects obtained from the
>> same
>> connection object? Any help would be appreciated.
> 
> Hi,
> 
> You can turn auto-commit off by calling conn.setAutoCommit(false). The
> reason why it may help, is that with auto-commit on, the transaction
> (Connection) is committed automatically each time a ResultSet is closed,
> and when the transaction is committed, all ResultSets in that connection
> lose their positions (and, depending on the holdability setting, they
> may be closed).
> 
> That said, whereas using the same connection concurrently from different
> threads should work, I would recommend that you have a separate
> connection for each thread. That would give you fewer problems with the
> different threads changing the transaction state for each other, and it
> could improve the performance since Derby could then execute more
> queries in parallel.
> 
> -- 
> Knut Anders
> 
> 

-- 
View this message in context: http://www.nabble.com/Does-using-DerbyDB-require-synchronization--tp19379460p19392670.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Does using DerbyDB require synchronization?

[Knut Anders Hatlen <Kn...@Sun.COM>]

derbyfan195 <hu...@gmail.com> writes:

> Just started using Derby in my Java code and realized some weird
> synchronization issues. I have multiple threads accessing the same Derby
> Database (embedded one) through a single connection. They issue read-only
> queries, i.e., select, each with its own statement, and does some operations
> on the returned ResultSet using ResultSet.next() operation. However, I
> noticed sometimes, one of these next() calls would fail and gave me an error
> of next() is not permitted, make sure that AUTO_COMMIT is turned OFF. I
> don't know what this error means or why I need to turn AUTO_COMMIT off. Do I
> need any synchronization among the statement objects obtained from the same
> connection object? Any help would be appreciated.

Hi,

You can turn auto-commit off by calling conn.setAutoCommit(false). The
reason why it may help, is that with auto-commit on, the transaction
(Connection) is committed automatically each time a ResultSet is closed,
and when the transaction is committed, all ResultSets in that connection
lose their positions (and, depending on the holdability setting, they
may be closed).

That said, whereas using the same connection concurrently from different
threads should work, I would recommend that you have a separate
connection for each thread. That would give you fewer problems with the
different threads changing the transaction state for each other, and it
could improve the performance since Derby could then execute more
queries in parallel.

-- 
Knut Anders

Re: Does using DerbyDB require synchronization?

[Valentin Cozma <vc...@elfyard.com>]

derbyfan195 wrote:
> Just started using Derby in my Java code and realized some weird
> synchronization issues. I have multiple threads accessing the same Derby
> Database (embedded one) through a single connection. They issue read-only
> queries, i.e., select, each with its own statement, and does some operations
> on the returned ResultSet using ResultSet.next() operation. However, I
> noticed sometimes, one of these next() calls would fail and gave me an error
> of next() is not permitted, make sure that AUTO_COMMIT is turned OFF. I
> don't know what this error means or why I need to turn AUTO_COMMIT off. Do I
> need any synchronization among the statement objects obtained from the same
> connection object? Any help would be appreciated.
>   

I had some similar situation .

In the beginning I used derby in embedded mode , prepared statements 
with single connection / single thread.

Then I switched to single connection / multiple threads.

For me the temporary solution was to synchronize all java methods that 
had database access.

Now I'm using connection pool with prepared statements.


> -
> Hai
>