Thank you all. Yes I have seen these deadlocks too which lead me to the original inquiry, and will have to explore the workaround.


From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
To: Derby Discussion <derby-user@db.apache.org>
Sent: Monday, August 24, 2009 4:18:06 AM
Subject: Re: Running stats while online

T K <sanokistoka@yahoo.com> writes:

>>  I have sometimes seen timeouts and deadlocks as a result of this when
> updating the statistics while other threads are accessing the same table.
>
> Have you seen this with 10.5 and its new approach as well? Or just prior
> versions?

I've seen this with the new approach too.

> Also, would an explicit LOCK TABLE before running stats help in preventing
> these deadlocks?

Perhaps some of them (note that compress table already does this), but
you still have the problem with many prepared statements being
recompiled concurrently after the updating has been completed. Jeff's
suggestion (disabling the statement cache) should help prevent many of
the deadlocks, as it disables sharing of compiled plans between prepared
statements in different threads.

Disabling the statement cache may have other negative effects (like
slower preparing of statements, and bigger memory footprint because
compiled plans can no longer be shared) so I wouldn't recommend doing it
until you find that this actually is a problem for your
application. Also, even regardless of this issue, it's probably a good
idea to make your application detect timeouts and retry if a query times
out.

--
Knut Anders