db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: disabling locking
Date Tue, 31 Mar 2009 15:01:13 GMT
"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).


> In practice, on the full application (which has an index here), I see
> it sometimes wait for a lock and sometimes not.

It seems reasonable this could have to do with the query plan
selection, I agree.


> Dumping the lock table (SYSCS_DIAG.LOCK_TABLE) just before the update,
> the only difference between the indexed and non-indexed versions I see
> is the lock count on the table-lock:
>
>   Locks (no index):
>     XID, TYPE,  MODE, TABLENAME, LOCKNAME,  STATE, TABLETYPE, LOCKCOUNT, INDEXNAME
>     164, ROW,   X,    TEST,      (1,8),     GRANT, T,         1,         null
>     162, ROW,   X,    TEST,      (1,7),     GRANT, T,         1,         null
>     162, TABLE, IX,   TEST,      Tablelock, GRANT, T,         1,         null
>     164, TABLE, IX,   TEST,      Tablelock, GRANT, T,         1,         null
>
>   Locks (with index):
>     XID, TYPE,  MODE, TABLENAME, LOCKNAME,  STATE, TABLETYPE, LOCKCOUNT, INDEXNAME
>     167, ROW,   X,    TEST,      (1,8),     GRANT, T,         1,         null
>     165, ROW,   X,    TEST,      (1,7),     GRANT, T,         1,         null
>     165, TABLE, IX,   TEST,      Tablelock, GRANT, T,         2,         null
>     167, TABLE, IX,   TEST,      Tablelock, GRANT, T,         2,         null

Not sure why the lockcount is 2; it could be because the base table is
visited in two scans, one due to the inserted row, and one time due to
the insert of the index row...

>
> Btw., can anybody explain what the intent-lock on the table is
> actually doing here (i.e. why there is one)?

This is standard for locking based systems when there is a mix of row
and table locks (otherwise it would be hard for a table locking
operation to see that other transactions have some row locked).

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

Hope this helps,

Dag

Mime
View raw message