Aleksey,

For me all this differences between "filter" and "join" conditions do not really exist. All I see is a condition, which a record must satisfy in order to be upated or deleted.
I understand that internally it is different. And yes, it is a performance hit to re-verify JOIN condition. You got me here.
But still, what I am looking for is some consistency. And maybe we do have to have "isolation levels".

For example. And this is just that - an example.

1. Dirty reading. Records are found (both "filter" and "join") by looking at their "last known" values. If a record is locked it still fits because of its "last known" state.

2. Read commited. Same as above only locked records are ignored because who knows what will be in them once the lock is released.

At this point, for both #1 and #2 levels, *all* records that are found are attempted to be locked. If the lock fails, there is nothing else to do except to unlock those that were locked and throw an exception.

3. Repeatable read. For the records found and locked as per #2 above, the condition is checked again. This *could* be slow, but, I repeat, I do not see a difference between "filter" and "join". It could be, however, that due to complexity of the Joins we would have
3.1. Only Filter condition is re-applied
3.2. Both Filter and Join are

It's just as it is now, I am having hard time to understand what's going on...

--Andrew