Welcome, Guest
Username: Password: Remember me
This public forum is meant for questions and discussions about Visual FoxPro
  • Page:
  • 1

TOPIC:

Cannot DELETE a record from work area 27 Apr 2020 21:15 #14278

  • FoxProMatt
  • FoxProMatt's Avatar
  • Topic Author



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

Function TestDelete()
	
	Close Databases
	Set Exclusive Off
	Var lcDbfName =  "C:\Work\lm5\AppData\xSharp_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

Attachments:

Please Log in or Create an account to join the conversation.

Cannot DELETE a record from work area 27 Apr 2020 21:31 #14279

  • lumberjack
  • lumberjack's Avatar


  • Posts: 720
  • Hi Matt,

    FoxProMatt wrote: 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
    Function TestDelete()
    	
    	Close Databases
    	Set Exclusive Off
    	Var lcDbfName =  "C:\Work\lm5\AppData\xSharp_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
    ______________________
    Johan Nel
    Boshof, South Africa

    Please Log in or Create an account to join the conversation.

    Cannot DELETE a record from work area 27 Apr 2020 22:03 #14280

    • FoxProMatt
    • FoxProMatt's Avatar
    • Topic Author



    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

    Please Log in or Create an account to join the conversation.

    Last edit: by FoxProMatt.

    Cannot DELETE a record from work area 27 Apr 2020 22:10 #14281

    • lumberjack
    • lumberjack's Avatar


  • Posts: 720
  • Matt,

    FoxProMatt wrote: 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.
    ______________________
    Johan Nel
    Boshof, South Africa

    Please Log in or Create an account to join the conversation.

    Cannot DELETE a record from work area 27 Apr 2020 22:35 #14282

    • FoxProMatt
    • FoxProMatt's Avatar
    • Topic Author



    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.

    Please Log in or Create an account to join the conversation.

    Last edit: by FoxProMatt.

    Cannot DELETE a record from work area 29 Apr 2020 10:10 #14290

    • robert
    • robert's Avatar


  • Posts: 3446
  • Matt,

    FoxProMatt wrote:

    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
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    Cannot DELETE a record from work area 29 Apr 2020 12:28 #14291

    • lumberjack
    • lumberjack's Avatar


  • Posts: 720
  • Hi Robert,

    robert wrote: 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:
    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:
    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...
    ______________________
    Johan Nel
    Boshof, South Africa

    Please Log in or Create an account to join the conversation.

    Cannot DELETE a record from work area 29 Apr 2020 13:33 #14292

    • FoxProMatt
    • FoxProMatt's Avatar
    • Topic Author



    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.

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

    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.

    Please Log in or Create an account to join the conversation.

    Cannot DELETE a record from work area 30 Apr 2020 15:07 #14304

    • mainhatten
    • mainhatten's Avatar


  • Posts: 199
  • Hi Robert,

    robert wrote: 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

    Please Log in or Create an account to join the conversation.

    Last edit: by mainhatten.

    Cannot DELETE a record from work area 30 Apr 2020 15:20 #14308

    • mainhatten
    • mainhatten's Avatar


  • Posts: 199
  • FoxProMatt wrote: [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

    Please Log in or Create an account to join the conversation.

    Cannot DELETE a record from work area 30 Apr 2020 15:21 #14309

    • robert
    • robert's Avatar


  • Posts: 3446
  • Thomas,

    The support for buffered updates, combined with its support functions OldVal(), TableUpdate(), the functions you mentioned etc. are on our todo list for one of the coming builds.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    Cannot DELETE a record from work area 30 Apr 2020 15:30 #14311

    • mainhatten
    • mainhatten's Avatar


  • Posts: 199
  • lumberjack wrote: 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.

    Johan,

    If your App needs it, you can always RLock() before editing, which carries its own pitfalls. But I think MS range of option for handling update conflict is good enough for optimistic locking to prevail over pessimistic (which they offer in buffering as well).

    Best practices might have been explained in more detail ;-)

    regards
    thomas

    Please Log in or Create an account to join the conversation.

    Last edit: by mainhatten.

    Cannot DELETE a record from work area 30 Apr 2020 15:39 #14312

    • lumberjack
    • lumberjack's Avatar


  • Posts: 720
  • Hi Thomas,

    mainhatten wrote: If your App needs it, you can always RLock() before editing, which carries its own pitfalls. But I think MS range of option for handling update conflict is good enough for optimistic locking to prevail over pessimistic (which they offer in buffering as well).
    Best practices might have been explained in more detail ;-)

    I have done all my programming in a wrapper class Insert/Update/Delete in a Scatter/Gather method and they all basically called the NetTry function that I have shared a couple of days before, nothing fancy but it is solid and I have not touched them since the late '90s. Work even for standalone systems as well as multi-user client server. Less than 100 lines of code.
    ______________________
    Johan Nel
    Boshof, South Africa

    Please Log in or Create an account to join the conversation.

    Cannot DELETE a record from work area 30 Apr 2020 17:02 #14316

    • mainhatten
    • mainhatten's Avatar


  • Posts: 199
  • Hi Johan,

    lumberjack wrote: I have done all my programming in a wrapper class Insert/Update/Delete in a Scatter/Gather method and they all basically called the NetTry function that I have shared a couple of days before, nothing fancy but it is solid and I have not touched them since the late '90s. Work even for standalone systems as well as multi-user client server. Less than 100 lines of code.


    Certainly not rocket science, but you will run into edge cases where the pessimistic locked records are locked during a telephone call expected to be over in 2 minutes but took 52... I think MS nailed a good default if you have trustable ts_update or better i8_recwrite counter fields which can guarantee optimized access on bulk operations.

    regards
    thomas

    Please Log in or Create an account to join the conversation.

    Last edit: by mainhatten.

    Cannot DELETE a record from work area 01 May 2020 12:54 #14329

    • lumberjack
    • lumberjack's Avatar


  • Posts: 720
  • Thomas,

    mainhatten wrote: Certainly not rocket science, but you will run into edge cases where the pessimistic locked records are locked during a telephone call expected to be over in 2 minutes but took 52... I think MS nailed a good default if you have trustable ts_update or better i8_recwrite counter fields which can guarantee optimized access on bulk operations.

    Well it is a coding style, you can sit a year on a record in my applications and it won't matter. Very similar approach to MS on my DBFs, not that I use DBF anymore though.
    ______________________
    Johan Nel
    Boshof, South Africa

    Please Log in or Create an account to join the conversation.

    Cannot DELETE a record from work area 01 May 2020 15:04 #14330

    • Karl-Heinz
    • Karl-Heinz's Avatar


  • Posts: 774
  • Guys,

    The VO DbServer class knows the locking mode CC_OPTIMISTIC. That means - from what i heard ;-) - that the locking and replacement is made automatically. I only used CC_NONE. That means, that i´m alone responsible for the locking and replacement. Freely adopted from what Karl Lagerfeld once said: "If you´ve lost the control of your data dependecies, you have lost the control of your live" :)

    regards
    Karl-Heinz

    Please Log in or Create an account to join the conversation.

    Cannot DELETE a record from work area 01 May 2020 16:40 #14333

    • mainhatten
    • mainhatten's Avatar


  • Posts: 199
  • Hi Johan,

    lumberjack wrote: Well it is a coding style, you can sit a year on a record in my applications and it won't matter. Very similar approach to MS on my DBFs, not that I use DBF anymore though.

    For client side stuff I prefer DBF to other formats - as long as no crypto is needed. Saving numerics in binary is main reason over SQLite, portability another. Too bad there is not enough standardization in header/data formats, IMO it would have been a better transmission protocol compared to XML and even JSON, as reading effort takes less computing power.
    Multiuser remote access since SMB2 impossible for any SW unless self-used (to take blame)

    Yes, it is a matter of use case and style - my reason to only point to IMO "sane defaults".

    regards
    thomas

    Please Log in or Create an account to join the conversation.

    Last edit: by mainhatten.

    Cannot DELETE a record from work area 01 May 2020 17:34 #14334

    • FFF
    • FFF's Avatar


  • Posts: 1398
  • lumberjack wrote: not that I use DBF anymore though.

    Would love to see a "VO-StandardMDI"-Sample, backed by a PG-backend in the Gallery :whistle: .
    Regards
    Karl (X# 2.14.0.4; Xide 1.33; W8.1/64 German)

    Please Log in or Create an account to join the conversation.

    Last edit: by FFF.

    Cannot DELETE a record from work area 01 May 2020 18:01 #14335

    • lumberjack
    • lumberjack's Avatar


  • Posts: 720
  • Hi Karl,

    FFF wrote:

    lumberjack wrote: not that I use DBF anymore though.

    Would love to see a "VO-StandardMDI"-Sample, backed by a PG-backend in the Gallery :whistle: .

    Your :whistle: from long ago is in the back of my mind. I am not too far away from my next DataDriven article publication, so maybe inside of that you will see a sort of ADO.NET MDI application taking shape.
    ______________________
    Johan Nel
    Boshof, South Africa

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1