Sorry for getting in the middle of this...
>1. Who said the lock isn't required?
Well, you did. Sort of. If it is required but we won't receive an error if an attempt to get it fails, then I'd say it is *not* required.
>2. Previous versions of VFP worked this way and no one complained.
There is first time for everything. BTW, this reminds me my own company policy of "it's not a bug if nobody complained".
Seriously, the reason I in particular ran into this was that we are slowly rewriting an old FP2.5 code into something 3-tier like. So, say, I've isolated UI from BL from data access. It helps if BL sends to the data access SQL requests that are portable. I was very excited to know that VFP is getting closer to the SQL syntax "others" have. For instance, it is much better to have this one line:
....oDBManager.ExecuteDelete("DELETE FROM Resources WHERE TaskId IN (SELECT id FROM Task WHERE ParentId = ?piPid)")
and even if the SQL syntax is slightly different between database engines, I can store the DELETE text outside in some sort of resource file. But it is still one line! Only it doesn't work well as we just found out. So the alternative is to somehow make this code portable:
LOCAL ARRAY aTemp(1)
SELECT id FROM Task WHERE ParentId = m.iPid INTO ARRAY aTemp
IF _TALLY > 0
TRY
BEGIN TRANSACTION
FOR EACH iTaskId in aTemp
DELETE FROM Resource WHERE TaskId = m.iTaskId
ENDFOR
END TRANSACTION
CATCH ...
IF TXNLEVEL() > 0
ROLLBACK
ENDIF
ENDTRY
ENDIF
Which is much bigger challenge.
>3. Record locking refreshes the record from disk. Is it better to look for a JOIN match first given that the record's content may be different after it is locked?
Is there another way to refresh record content without locking it?
Why is this reasoning different from checking for "filter condition"?
>If record's content is changed, it may invalidate result of the JOIN operation. Moreover, the record may no longer satisfy the filter condition - the JOIN was done for nothing.
I understand that we are getting into that "grey area" of uncommited changes, repeatable reads, etc. for which "the big guys" have 4 transaction isolation levels.
I understand that VFP has much fewer cards to play with. But...
I would accept the "dirty read" level where VFP would find the records first based on both "join" and "filter" conditions, then attempt to lock them (ALL!), then delete or update.
However, I would much better prefer the "repeatable read" level where VFP would find the records first based on both "join" and "filter" conditions, then attempt to lock them (ALL!), then see if they still match the criteria, then delete or update.
How is that for a plan?
--Andrew