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 <Knut.Hatlen@sun.com> wrote:
Adam B <cruxic@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