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
