db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Life is hard, and then you die" <ron...@innovation.ch>
Subject Re: disabling locking
Date Tue, 31 Mar 2009 23:56:33 GMT
On Tue, Mar 31, 2009 at 07:50:23AM -0700, Bryan Pendleton wrote:
> >As another data-point, if I create an index for the 'version' column
> >then it doesn't hang (at least not in this example):
> Indeed, careful design and use of indexes is crucial to avoiding
> concurrency problems such as these, as well as for achieving
> satisfactory performance on queries.

I'm well aware of the performance implications of indexes; but I was
not aware that they affected/changed the locking behaviour. Maybe it's
just me, but I think this could be useful information to add to the

> It's a little surprising that you are having trouble getting Derby
> to use the index. This may be an artifact of having very small tables
> and/or out-of-date statistics, as these situations can cause Derby
> to make unexpected query plan choices.

Yes, some of my tests have a small dataset, or one in which a large
percentage of the rows are affected; so I think it's normal for some
of those that Derby would sometimes pick a table-scan instead of the
index (in hindsight things are always so clear :-) ).

> Your observation that it's not possible to use optimizer overrides
> (the --DERBY_PROPERTIES special comment) for searched UPDATE and
> DELETE statements is interesting; I wasn't aware that we had that
> restriction. This sounds like a useful enhancement request.

Done: http://issues.apache.org/jira/browse/DERBY-4136

> It's good to hear that you are having better success now that you
> have indexes in place. Hopefully you will be able to get the
> performance and concurrency you need once you get more experience
> using the indexes.

I've always had the indexes in place - Derby just wasn't always using
them :-( (which is fine as long as the choice of using an index vs a
table-scan is solely a performance issue). And as I said above, it
didn't occur to me (nor do I find it immediately obvious) that the
using (or not) of the indexes would change the locking behaviour.
Since it does, one could argue that having the query-planer decide
whether to use an index or not based solely on performance
considerations is not entirely valid. Thank goodness for the optimizer
overrides, though!



View raw message