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