Cannot DELETE a record from work area

[Anonymous]

Why can't I delete a record in a cursor? In FoxPro, DELETE command works once a DBF is opened, whether it was opened as Shared or Exclusive.

Getting run-time error "Lock required (Record has to be locked)"

Code: Select all

Function TestDelete()
	
	Close Databases
	Set Exclusive Off
	Var lcDbfName =  "C:Worklm5AppDataxSharp_Test_Parent.dbf"

	Use (lcDbfName) Alias WA1 Shared In 0
	Set Deleted On
	Delete Next 1
	
	Field cFilename 
	
	Scan For !Deleted()
		? Recno()
		? cFilename 
	Endscan
	
	Wait

End Function

2020-04-27 14_06_23-Window.png (107.96 KiB) Viewed 926 times

[lumberjack]

Hi Matt,

Why can't I delete a record in a cursor? In FoxPro, DELETE command works once a DBF is opened, whether it was opened as Shared or Exclusive.
Getting run-time error "Lock required (Record has to be locked)"

You set Exclusive off, that means your going to allow multi user "network" access to DBFs

Code: Select all

Function TestDelete()
	
	Close Databases
	Set Exclusive Off
	Var lcDbfName =  "C:Worklm5AppDataxSharp_Test_Parent.dbf"

	Use (lcDbfName) Alias WA1 Shared In 0
	Set Deleted On
	IF RLock()
		Delete Next 1
	ELSE
		? "Record lock error"
	ENDIF
	Field cFilename 
	
	Scan For !Deleted()
		? Recno()
		? cFilename 
	Endscan
	
	Wait

End Function

[FoxProMatt]

In FoxPro, if we have SET EXCLUSIVE OFF, yes it means that any other user can open the DBF, but even so, any user who has it open can still delete one or more records, and that deletion will appear in all other work areas for all users who have that DBF open.

Example: If a DBF is opened as Shared in work area on User 1's computer, and User 2 also opens same DBF (would have to be in Shared mode), then lets say User 1 deletes a record in the DBF, well then that record will become deleted on User 2's work area also.

Apparently FoxPro monitors the disk file for changes or something because I just tested what I explained to you above and it does indeed work the way I described.

EXCLUSIVE OFF means anyone can open and anyone can add, delete, modify.

EXCLUSIVE ON means no one else can open

[lumberjack]

Matt,

In FoxPro, if we have SET EXCLUSIVE OFF, yes it means that any other user can open the DBF, but even so, any user who has it open can still delete one or more records, and that deletion will appear in all other work areas for all users who have that DBF open.

Example: If a DBF is opened as Shared in work area on User 1's computer, and User 2 also opens same DBF in Shared mode, then lets say User 1 deletes a record in the DBF, well then that record will become deleted on User 2's work area also.

Apparently FoxPro monitors the disk file for changes or something because I just tested what I explained to you above and it does indeed work the way I described.

EXCLUSIVE OFF means anyone can open and anyone can add, delete, modify.

EXCLUSIVE ON means no one else can open

This is quite different from how Clipper/VO done it. What if you delete a record while somebody else is editing the same record and hit the save button after you have deleted?

It was always at our conferences emphasized how to manage multi-user systems and the one that was repeated over and over again, generic RLock()/UnLock functions/methods. Robert and DevTeam will have to investigate this in more detail.

[FoxProMatt]

What if you delete a record while somebody else is editing the same record and hit the save button after you have deleted?

I don't know the details of all the potential race conditions that can pop up.

I know there is a RLock() function and probably other stuff too. My apps don't run against actual DBF files since I use cursors of records from Sql Server. I usually don't even dabble in this kind of code, but I was just testing some basic FoxPro DBF commands and coding and this one stumped me as to why it wouldn't work.

We'll see what Master Robert says about this.

[robert]

Matt,

What if you delete a record while somebody else is editing the same record and hit the save button after you have deleted?

I don't know the details of all the potential race conditions that can pop up.

I know there is a RLock() function and probably other stuff too. My apps don't run against actual DBF files since I use cursors of records from Sql Server. I usually don't even dabble in this kind of code, but I was just testing some basic FoxPro DBF commands and coding and this one stumped me as to why it wouldn't work.

We'll see what Master Robert says about this.

According to the VFP documentation (the chapter "Locking Data") locking is done automatically.

I tried a simple example in FoxPro and monitored its file access with procmon and indeed:
FoxPro silently automatically locks the record when you use the DELETE command for a file that is opened in shared mode.
It does the same for REPLACE statements.
Of course this can cause concurrency problems when 2 users are trying to access the same record.
I tried it by updating the same record from 2 processes on the same machine and guess what: the second process simply overwrites the changes from the first process with no warning at all..
And when one process is editing a value (for example in a grid) and the second process tries to edit the same record then that process keeps on trying to lock that record. I am not sure if it tries to lock that forever, but it certainly did try that for a very long time. Most likely this can be configured somewhere. I saw a reference to "SET REPROCESS".
I tried that with "SET REPROCESS TO 10" and then indeed after several retries when editing a table I see the error message "Record is in use by another user".
So I guess that a correct implementation of the REPLACE, DELETE and RECALL commands in FoxPro should not only update the data but also include automatic locks and unlocks with error handling when locking fails.


Robert

[lumberjack]

Hi Robert,

According to the VFP documentation (the chapter "Locking Data") locking is done automatically.

Not sure if this will help but this code is coming from my Clipper days and used in VO and is still applicable:

Code: Select all

FUNCTION NetTry(bAction AS CODEBLOCK, nRetry := 1 AS WORD, cError := '' AS STRING, lConfirmOverRide := FALSE AS LOGIC) AS LOGIC
	LOCAL lRetry := TRUE, lSuccess := FALSE AS LOGIC
	LOCAL nTried, nTimer AS WORD
	nRetry := Max(nRetry, 1)
	WHILE lRetry .and. !lSuccess
		nTried := Min(5, nRetry)
		WHILE ! ( lSuccess := Eval(bAction) ) .and. ( nTried > 0 )
			nTimer := SetTimer( NULL_PTR, 01, 01, NULL_PTR )
			nTried--
		ENDDO
		KillTimer(NULL_PTR, nTimer)
		IF !lSuccess
			IF lConfirmOverRide
				lRetry := FALSE
			ELSE
				lRetry := (YesNoBox{oSetupVars:Owner, ProcName(), ;
									'*** ' + cError + ' error ***' + CRLF + 'Retry?'}:Show() ==  BOXREPLYYES)
			ENDIF
		ENDIF
	ENDDO
	RETURN lSuccess

Usage:

Code: Select all

IF RLock() .OR. NetTry({|| RLock()}, 5, "Record lock error")

We could also create a NETTRY #command for the above if there are a need to extend the XBase command set...

[FoxProMatt]

Robert - You will also have to eventually deal with the CURSORSETPROP() function which allows for row or table buffering to be in place in an app.

By default default, Buffering is disabled and all changes are written to disk file immediately, but there are 4 types of Buffering that can be used, and if so the changes are only written under different circumstances. The most aggressive (defensive) one (value 5) actually requires an explicit call to a function name TableUpdate() to write your changes to disk.

With level 5, you can modify multiple fields in multiples in a local cursor and the changes are not written to disk until TableUpdate() is called, and even then you can call TableUpdate() for the current row or all rows. You can also call TableRevert() to totally undo changes to current or or even all rows.

Code: Select all

CURSORSETPROP("Buffering", 2)

1 -> No buffering. The default value.
2 -> Pessimistic record locks which lock record now, update when pointer moves or upon TABLEUPDATE( ).
3 -> Optimistic record locks which wait until pointer moves, and then lock and update.
4 -> Pessimistic table locks which lock record now, update later upon TABLEUPDATE( ).
5 -> Optimistic table lock which wait until TABLEUPDATE( ), and then lock and update edited records.


TableUpdate()
Commits changes made to a buffered row, a buffered table, cursor, or cursor adapter.

Code: Select all

TABLEUPDATE( [nRows [, lForce]] [, cTableAlias | nWorkArea] [, cErrorArray] )

TableRevert()
Discards changes made to a buffered row or a buffered table or cursor and restores the OLDVAL( ) data for remote cursors and the current disk values for local tables and cursors.
Note - On a network, the data currently on disk might differ from the data on the disk when the table was opened or the cursor was created. Other users on the network might have changed the data after the table was opened or the cursor was created.

Code: Select all

TABLEREVERT( [lAllRows [, cTableAlias | nWorkArea] ] )

There are also 2 other functions OldVal() and CurValue() that come into play with apps that use Buffering, so add that to the list of thing to (eventually) address.


Search for "table buffering" on the Index tab of the FoxPro Help File chm, and start reading from there.

[mainhatten]

Hi Robert,

According to the VFP documentation (the chapter "Locking Data") locking is done automatically.

I tried a simple example in FoxPro and monitored its file access with procmon and indeed:
FoxPro silently automatically locks the record when you use the DELETE command for a file that is opened in shared mode.
It does the same for REPLACE statements.
Of course this can cause concurrency problems when 2 users are trying to access the same record.
I tried it by updating the same record from 2 processes on the same machine and guess what: the second process simply overwrites the changes from the first process with no warning at all..
And when one process is editing a value (for example in a grid) and the second process tries to edit the same record then that process keeps on trying to lock that record. I am not sure if it tries to lock that forever, but it certainly did try that for a very long time. Most likely this can be configured somewhere. I saw a reference to "SET REPROCESS".
I tried that with "SET REPROCESS TO 10" and then indeed after several retries when editing a table I see the error message "Record is in use by another user".
So I guess that a correct implementation of the REPLACE, DELETE and RECALL commands in FoxPro should not only update the data but also include automatic locks and unlocks with error handling when locking fails.

All correct, but mirroring the state of thngs used in FPDos and FPW apps. Vfp kept that to allow old code to run, including blocks if manual pessimistic locking was chosen, but since vfp introduced the concepts of "cursor" and "(table) buffering" the game has changed a lot. Changes are made to copies of data on 1..n cursors and in the end persisted via call(s) to tableupdate to their data sources.

Locking mode is the optimistic pattern you also see in Ado.Net, IIRC one of the enums in vfp became a property, but the range of options/mechanisms was identical on first read, even if evoked minimally different, as Matt described. THAT is the stuff I described months ago as the "vfp DNA", as it enables you to exchange remote data sources while working with "local tables", which are the copies of the original data source.

So while you are correct in stating that old style stuff had to check for locks and race conditions, that area changed a lot and you had to decide on conflict avoidance pattern most apt for your app and invest a little in update conflict resolution.

I think I grouped the needed functions when I sent the language description, at least 3 important ones are missing in Matts list: GetNextModified(), GetFldState() and SetFldState().

BTW: If you have not already read it, xBase commands only lock specific records, SQL update/delete (uncertain on insert) .only lock the header, as they are meant to work on sets (which is NOT the way vfp does it when you log TableUpdate() with cursoradapter hook methods you see single update calls - but perhaps it does make half sense to lock the table till all CRUD is done...)

my 0.02€
thomas

[mainhatten]

[You will also have to eventually deal with the CURSORSETPROP() function which allows for row or table buffering to be in place in an app.

Matt,
a lot is implemented already in ADO.Net for cursors. It might be prudent to implement dbf table buffering via ADO.Net "cursors" to have a single, almost finished implementation.

regards
thomas