"Life is hard, and then you die" <[email protected]> writes:
>> 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 As far as I know, the index is not locked, the base row is. > 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).. After having consulted the index the base table row is locked during update. >> 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... Unless somebody has some other insights, maybe so. I would try to verify this, though, maybe the base row isn't attempted locked in this case (since the index row doesn't qualify) - I am not sure. Sorry if I am a bit vague here, I don't know this part of the code too well. Dag
