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
docs.

> 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 :-) ).

[snip]
> 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!


  Cheers,

  Ronald


Mime
View raw message