PreparedStatement table locking

[Brett Wooldridge <br...@gmail.com>]

Devs,

I've been looking at bug#4279 again today...

https://issues.apache.org/jira/browse/DERBY-4279

..and thinking of possible solutions, when a question arose.  First
and foremost, the deadlock described in 4279 is caused by the fact
that preparing a statement requires a table lock (shared) in Derby.
Why is this, technically?  If the requirement that a table lock is
needed to prepare a statement can be removed, this deadlock can be
fixed.

Alternatively, if the requirement that a table lock is need cannot be
removed, a possible resolution for 4279 is to remove the concept that
prepared statements are shared across connections and instead make the
statement cache per-connection.  While this increases the memory
overhead slightly -- I have to believe that the artifacts of a
prepared statement are in fact extremely small -- it removes a lot of
shared-cache synchronization code and probably increases concurrency
in general.  If you've been in that code, the synchronization is
pretty hairy (as you can see from the comments in 4279 as well) and
there are synchronization blocks in there but commented out for
reasons no existing developers can explain.

In fact, now that I think of it, it would be great if the requirement
for a table lock could be removed when preparing a statement AND the
cache made per-connection (to simplify the code to a point that humans
can understand).

I understand there is probably an edge case whereby performance would
be degraded compared to existing code -- that being a scenario in
which connections are created and discarded frequently.  But that is a
scenario easily solved by connection re-use, either explicit or by use
of a connection pool.

Thoughts?  I'm willing to put in some work if either of these
approaches is acceptable.  I already put in considerable time on 4279
over a year ago, but eventually abandoned it (as you can see in the
comments) due to synchronization issues in the shared cache.

Regards,
Brett Wooldridge