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 Wed, 01 Apr 2009 00:17:50 GMT
On Tue, Mar 31, 2009 at 05:01:13PM +0200, Dag H. Wanvik wrote:
> "Life is hard, and then you die" <ronald@innovation.ch> 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...



View raw message