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 Adam B <cr...@gmail.com> on 2008/12/30 23:54:31 UTC
timeouts and long-running transactions
Hello all,
I'd like to confirm that the behavior I'm seeing is expected:
We have a multithreaded application against an embedded derby database
(10.4). While one thread is chugging away doing many thousands of inserts
inside a transaction other threads are getting SQLExceptions about "a lock
could not be obtained withing the time requested". The exceptions
consistently happen trying to do a SELECT from a table that is not modified
by the big transaction in the other thread so I don't think we are dealing
with a deadlock. If it matters, we are using the default isolation level:
TRANSACTION_READ_COMMITTED.
I did some searching and found this in the derby documentation:
Even if a transaction is not involved in a deadlock, it might have to wait a
considerable amount of time to obtain a lock because of a long-running
transaction or transactions holding locks on the tables it needs.
[http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts89097.html]
Is it true that a long-running transaction will essentially lock the entire
database?
Much thanks.
- Adam
Re: timeouts and long-running transactions
Posted by Adam B <cr...@gmail.com>.
Thanks for the info Knut! After enabling those diagnostic properties I
found that the big transaction was, in fact, updating the table in question.
On Fri, Jan 2, 2009 at 5:55 AM, Knut Anders Hatlen <Kn...@sun.com>wrote:
> Adam B <cr...@gmail.com> writes:
>
> > Hello all,
> >
> > I'd like to confirm that the behavior I'm seeing is expected:
> >
> > We have a multithreaded application against an embedded derby database
> > (10.4). While one thread is chugging away doing many thousands of
> inserts
> > inside a transaction other threads are getting SQLExceptions about "a
> lock
> > could not be obtained withing the time requested". The exceptions
> > consistently happen trying to do a SELECT from a table that is not
> modified by
> > the big transaction in the other thread so I don't think we are dealing
> with a
> > deadlock. If it matters, we are using the default isolation level:
> > TRANSACTION_READ_COMMITTED.
> >
> > I did some searching and found this in the derby documentation:
> >
> > Even if a transaction is not involved in a deadlock, it might have to
> wait
> > a considerable amount of time to obtain a lock because of a
> long-running
> > transaction or transactions holding locks on the tables it needs.
> > [
> http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts89097.html]
> >
> > Is it true that a long-running transaction will essentially lock the
> entire
> > database?
>
> No, it will only lock rows in the tables that it accesses. SELECT
> operations against tables that the long-running transaction doesn't
> touch, should not run into lock conflicts with the long-running
> transaction.
>
> Running your application with derby.locks.monitor=true,
> derby.locks.deadlockTrace=true and derby.language.logStatementText=true
> will give you more information about which transactions are involved in
> the lock conflict and which statements they have executed.
>
> Hope this helps,
>
> --
> Knut Anders
>
Re: timeouts and long-running transactions
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Adam B <cr...@gmail.com> writes:
> Hello all,
>
> I'd like to confirm that the behavior I'm seeing is expected:
>
> We have a multithreaded application against an embedded derby database
> (10.4). While one thread is chugging away doing many thousands of inserts
> inside a transaction other threads are getting SQLExceptions about "a lock
> could not be obtained withing the time requested". The exceptions
> consistently happen trying to do a SELECT from a table that is not modified by
> the big transaction in the other thread so I don't think we are dealing with a
> deadlock. If it matters, we are using the default isolation level:
> TRANSACTION_READ_COMMITTED.
>
> I did some searching and found this in the derby documentation:
>
> Even if a transaction is not involved in a deadlock, it might have to wait
> a considerable amount of time to obtain a lock because of a long-running
> transaction or transactions holding locks on the tables it needs.
> [http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts89097.html]
>
> Is it true that a long-running transaction will essentially lock the entire
> database?
No, it will only lock rows in the tables that it accesses. SELECT
operations against tables that the long-running transaction doesn't
touch, should not run into lock conflicts with the long-running
transaction.
Running your application with derby.locks.monitor=true,
derby.locks.deadlockTrace=true and derby.language.logStatementText=true
will give you more information about which transactions are involved in
the lock conflict and which statements they have executed.
Hope this helps,
--
Knut Anders