On Tue, Mar 31, 2009 at 05:01:13PM +0200, Dag H. Wanvik wrote: > "Life is hard, and then you die" <[email protected]> writes: > > > On Mon, Mar 30, 2009 at 02:24:51AM -0700, Life is hard, and then you die > > 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): > > > > CREATE TABLE TEST (c1 VARCHAR(100), version BIGINT) > > CREATE INDEX TEST_v ON TEST(version) > > This seems reasonable, since Derby would not need to visit and lock > the row in the basetable that does not qualify (locks are set on the > base table conglomerate, not the index conglomerate).
Hmm, so you're saying it lock the index as a whole for the duration of the update only? I'm trying to reconcile this with your earlier statement where you said Right, but I think that since this is an update operation, it will try to get an (exclusive) lock right away (otherwise the row might change between the point where we qualify it and try to update it).. [snip - thanks for the explanations] > I think the solution you came up with (the updatable result set and > the query override) should work (mostly, see below), since then the > index would be then used. > > Note: Knut mentioned that if the index is not unique, the > SELECT.. WHERE could qualify more than one row, so the index scan > might try to also lock the row *after* the first qualifying row, say one > whose value of version=4. If that row was just inserted, you could > still see a hang.. Unfortunately the indexed columns are definitely not unique (well, sometimes they are, but during update activity they certainly aren't). I.e. in general yes, the selects will find several rows to be updated or deleted. So am I back to square one then? "Work mostly" is not an option... Cheers, Ronald
