none
VFP Record Locks

    Question

  • Hi,

    I converted my old FP2.0 LAN programs to VFP, currently running 6.0 (because I am still having trouble drawing my screens with version 9.0).  Anyway, I have noticed a problem with record locking which I can't figure out.

    I set my  record locks, as before.  All of my programs have record or file locks which were manually set.  Under VFP, however, it seems that if 2 users access the same record, only 1 of the users' information is input into the table.  And, the error handling routines I had to handle the simultaneous record locking don't seem to work.

    I found an incredible tool, isrlocked(), but it seems that this only shows whether the record is locked on my user screen, not if another user locks the record.  So, I tried writing a program that would test if the record is locked and, if so, skip to the next record.  This also doesn't work.

    I am getting very frustrated because data is getting overwritten or lost.  Is there somewhere I can get an explanation of the differences in the file and record locks between FP and VFP? 

    I am reproducing my program below (please remember that I am not a professional programmer, just a business owner who has used Foxpro (and dBase II before that) for the last 25 years.

    Thanks for your help.

    Alan

    *skiptest.prg October 2, 2011 - the program assumes that a remote user is accessing and locked record #5.
    clear
    use bills
    go 5
    ****************************************************************
    ?rlock()
    if sys(2011) = "Record Unlocked" && i.e., unable to lock record here
        ??chr(07)
        do while sys(2011) = "Record Unlocked" .and. .not. eof()
        @2,1 say 'Defs.prg reports File or record cannot be locked'
        @3,1 say 'Skipping to next open file. . .'
        @4,1 say 'Reporting at Stage 1'
        skip
            do while lastname # " "
            skip
            enddo
        ?rlock()
            if eof()
            ??chr(07)
            ?'EOF reached unexpectedly'
            return
            endif
        enddo
    endif        
    ?recn()
    ?rlock()                                
    unlock
    Monday, October 03, 2011 7:54 PM

Answers

  • Well, you can take advantage of the REPROCESS settings available. A simple replacement would be

    IF NOT RLOCK()

      Do your.prg

    ENDIF

    And skip the IF ERROR()=108 etc. 

    Overall I wouldn't handle failing locks by retrying anyway. Either rlock() first to make sure when the user wants to save, he can save, and forget about the recommendation to lock as short as possible. Additional to that give the user a feedback someone else is editing the record(s) he wants to edit. 

    Or don't use manual locking at all, but use automatic locking in conjunction with optimistic locking and buffereing, with the capabilites of tableupdate() to report conflicts and with OLDVAL() and CURVAL() to incorporate changes made by concurrent users into the changes of the current user to save without overwriting the changed already made.

    Bye, Olaf.

    • Marked as answer by ostrowlaw Wednesday, October 05, 2011 9:04 PM
    Wednesday, October 05, 2011 3:22 PM

All replies

  • While your code is a bit redundant, eg rlock() already returns .t. if the lock succeeds and thus you don't need sys(2011), but it does not overwrite anything, it just tries to lock a record. I wonder what kind of program would lock just any record it can lock. You normally will try to lock the one record you want to write to, and not skip to any other record.

    What are you doing there?

    Bye, Olaf.

     

    Tuesday, October 04, 2011 8:08 PM
  • Well, of course the real program positions the pointer at the record I want to lock and then attempts a rlock().  I was just using a test program to see if I could simulate an error code which used to be produced in FP2.0.  For some reason, unlike in FP 2.0, the rlock() does not actually lock the record, or if it does lock the record it does not return an error code when another person tries to write to a locked record.  Under FP 2.0, when I attempted to access a record which was locked, an error code was generated and my on error program worked.  My error program appears below.  However, VFP apparently works differently, an error code is not generated and the error program does come up.  So, 2 people could attempt to write to the same record at the same time, which is a disaster.  What is happening is that the second person's data is lost, and that is a problem.

    The little sample program I sent yesterday was an attempt to have VFP skip to the next unlocked record.  This would work fine if I can somehow get VFP to send the appropriate error message.  Here is my old FP program which worked perfectly for years:

    IF ERROR() = 108 .OR. ERROR() = 109 .OR. ERROR() = 1705
    @10,1 SAY "Trying to access record..."

    *-----Try accessing for 300 loops
    RCOUNT = 1
        DO WHILE RCOUNT <= 1000 .AND. .NOT. RLOCK()
        RCOUNT = RCOUNT + 1
        @10,1 SAY "Trying to access record, retries: " +LTRIM(STR(RCOUNT))
        ENDDO
    *-----If still not available, present message

        IF .NOT. RLOCK()
        @10,1 CLEAR
        @10,0 TO 16,60 DOUBLE
        @12,1 SAY 'ERROR.PRG <R>etry, or e<X>it program' GET ;
        ERRACTION PICTURE "!"
        READ
        SET CONFIRM ON
        SET BELL ON

        DO CASE
        CASE ERRACTION = 'X'
            UNLOCK ALL
            CLOSE DATABASES
            RETURN TO MASTER
        CASE ERRACTION = 'R'
            RETRY
        CASE ERRACTION = 'X'
            UNLOCK ALL
            CLOSE DATABASES
            RETURN TO MASTER
        OTHERWISE
            RETRY
            ENDCASE

    ******
        @10,1 SAY "File or Record is locked.  Try again later!"
    RCOUNT = 1
        DO WHILE RCOUNT <= 600
        RCOUNT = RCOUNT + 1
        ENDDO
        UNLOCK ALL
        CLOSE DATABASES
        UNLOCK ALL
        RETURN TO MASTER
        ELSE
        @24,1 CLEAR
        RETRY
        ENDIF (NOT RLOCK())

    SET CONFIRM ON
    SET BELL ON

    RETURN

    Tuesday, October 04, 2011 8:46 PM
  • I don't know the old foxpro enough to say if or what has changed, but I can assure you rlock still locks and only the user with the lock can write to the locked record.

    Maybe SET REPROCESS is a setting new to you, maybe it has changed it's default. The default setting is 0 attempts (see help on SET REPROCESS, which means, quote:" If nAttempts is 0 (the default value) and you issue a command or function that attempts to lock a record or file, Visual FoxPro tries to lock the record or file indefinitely." So in this case RLOCK() waits until the lock can be achieved. Additional to waiting a message is displayed, quote: "Visual FoxPro displays the system message, "Attempting to lock... Press Escape to Cancel," while attempting to lock the record or file."

    But furthermore, the help says: If an ON ERROR routine is in effect and a command is attempting to lock the record or file, the ON ERROR routine takes precedence over additional attempts to lock the record or file. The ON ERROR routine is immediately executed. Visual FoxPro does not attempt additional record or file locks and does not display the system message.

    I did some tests and found out the last statement of the help is not true, at least I didn't got RLOCK() to trigger an error. Instead it simply returns .F.

    If I test with REPLACE somefield WITH somevalue instad of RLOCK(), the error handler is triggered, so maybe it depends on what command does the lock.

    From that perspecitve your first routine just trying to RLOCK() will never trigger an error, but if you manually lock some record of the test table in a different foxpro session (start foxpro twice, really), then you get .F. from the RLOCK() on already locked record(s), and you can't write to these records.

    I don't see what other setting does influence the non triggering of ON ERROR via RLOCK(), but from the top of my head before having read about the behaviour of REPROCESS in detail, I would have said anyway, that RLOCK() never errors. It either returns .T. or .F., which is what you want, indeed, isn't it? Do you really want fails of RLOCK()  to trigger error handling? To be "punished", just because you try to lock a record? No - you want to get the info about success or failure and continue respectively.

    Bye, Olaf.

    • Edited by Olaf Doschke Wednesday, October 05, 2011 11:25 AM
    Wednesday, October 05, 2011 11:20 AM
  • Thanks Olaf,

    Your post is exactly what I am starting to suspect.  After 25 years of having a program operate with certain rules, it is very unsettling to have the rules changed.

    So, the "on error" routines are meaningless.  I just need to rewrite a procedure something like if rlock() = ".t.", then wait 10 seconds, retry, wait another 10 seconds, etc.  This way, it should work.

    Back to the drawing board.

    I will try to rewrite this weekend let you know the result.

    Wednesday, October 05, 2011 3:08 PM
  • Well, you can take advantage of the REPROCESS settings available. A simple replacement would be

    IF NOT RLOCK()

      Do your.prg

    ENDIF

    And skip the IF ERROR()=108 etc. 

    Overall I wouldn't handle failing locks by retrying anyway. Either rlock() first to make sure when the user wants to save, he can save, and forget about the recommendation to lock as short as possible. Additional to that give the user a feedback someone else is editing the record(s) he wants to edit. 

    Or don't use manual locking at all, but use automatic locking in conjunction with optimistic locking and buffereing, with the capabilites of tableupdate() to report conflicts and with OLDVAL() and CURVAL() to incorporate changes made by concurrent users into the changes of the current user to save without overwriting the changed already made.

    Bye, Olaf.

    • Marked as answer by ostrowlaw Wednesday, October 05, 2011 9:04 PM
    Wednesday, October 05, 2011 3:22 PM
  • Thanks, I was banging my head against a brick wall trying to figure out what to you was quite simple.  I guess I could not believe that the functions did not operate as advertised.  OK, I went back, added a short procedure file at the end of the program using your suggestion, i.e., IF NOT RLOCK(), etc., and the problem appears to be solved.  No elaborate programming or changes were required, just the "spark" you provided.

    Thank you again for your kind assistance.

    Regards,

    Alan

    Wednesday, October 05, 2011 9:06 PM