Table locking? - Forum - OpenEdge Development - Progress Community
 Forum

Table locking?

This question is answered

Hello, I have a table that is concurrently updated by just two users. They never take the same record.
However, they both receive the error 2624: "<file-name> in use by <user> on <tty>. Wait or choose CANCEL to stop (2624)"

The message disappears in about one second, or less.

As far as I know, Progress locks the record and not the table.
So, what can be happening? What can I be doing wrong?

Openedge 11.6.2 32 bits, Client-Server, Windows server, Windows 8 workstations

Ezequiel Montoya
Lima - Perú
Verified Answer
  • You appear to be attempting a pessimistic locking strategy.

    Furthermore you are attempting to use a SHARE-LOCK and never actually EXCLUSIVE-LOCK the record.  This is never going to work.

    You should do something more like this:

    DO:
    
       define buffer upd_invTarjeta for invTarjeta.
    
       DEFINE VARIABLE rFila AS ROWID       NO-UNDO.
    
       IF VALID-EVENT(FOCUS, "VALUE-CHANGED")  THEN DO:
    
          ASSIGN Barra.  /* what is this? */
    
          FIND InvTarjeta WHERE invtarjeta.periodo = giperiodo
    
              AND invtarjeta.perinv = giperinv
    
              AND InvTarjeta.NroTarj = Barra NO-LOCK NO-ERROR.
    
          IF AVAILABLE invtarjeta THEN DO for upd_invTarjeta transaction:
    
              find upd_invTarjeta exclsuive-lock where recid(  upd_invTarjet ) = recid (invTarjeta ) no-wait no-error.
    
              if not available ( upd_invTarjeta ) then
                do:
                  message "invTarjeta is locked...".  /* or however you want to handle that problem... */
                  return no-apply.
                end.
    
              ASSIGN
    
                  upd_InvTarjeta.FlgBarrido = YES
    
                  upd_InvTarjeta.InvPeso    = InvTarjeta.SdoNeto
    
                  upd_InvTarjeta.InvNroMet  = InvTarjeta.NroMet.
    
              rFila = ROWID(InvTarjeta).
    
              FIND CURRENT invtarjeta NO-LOCK NO-ERROR.
    
              qhInvTarjeta:REPOSITION-TO-ROWID(rFila).
    
          END.
    
          ELSE DO:
    
             /* Other stuff */
    
          END.
    
    /*   FIND CURRENT InvTarjeta NO-LOCK NO-ERROR. */
    
       Barra:SCREEN-VALUE = "".
    
       APPLY "ENTRY" TO Barra.
    
       RETURN NO-APPLY.
    
    END.



    Please excuse the terrible formatting -- "Communities" is not made for sharing code.

    The key points are:

    1) First try to find the record NO-LOCK.
    2) If it exists attempt to gain an EXCLUSIVE-LOCK on the upd_* buffer for that record.
    2a) all references to the upd_* buffer are STRONG SCOPED to the transaction block (the "DO FOR upd_* TRANSACTION" part of things. This is critical.
    2b) if the compiler complains that there is already a transaction active then you need to show more code and you have a bigger problem than you think you do.
    2c) if the compiler complains that you cannot strong scope the upd_* buffer then you have a typo.
    3) Testing for success of the exclusive-lock and handling failure as a contingency is the "optimistic" part of things. You are assuming that you will usually get the lock.

    --
    Tom Bascom
    tom@wss.com

All Replies
  • Some code will probably go a long way to helping identify the issue.

  • some statements like FOR EACH ... EXCLUSIVE-LOCK will lock records they don't use if the WHERE phrase doesn't exactly match an index and it has to retrieve more records than it needs in order to figure out which records apply.

  • To expand on what Tim has said ... if you do a FOR EACH NO-LOCK and then, having found the record, then refind the record exclusive lock, then you will only ever lock one record.  But, if you do a FOR EACH EXCLUSIVE-LOCK and the criteria are such that it has to read a lot of records until it finds the one which fits all criteria, i.e., there is no index which supports going to the record directly, then it will lock all the records it has to read while doing the search until it fnds the exact record.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • You can check the transaction scope whether it uses optimistic lock or pessimistic lock...If its bulk transaction update then its possible code logic may exclusively lock large set of records. You may need to revisit/optmize the code to get rid of this issue.

  • Thanks everyone!

    It's not a FOR EACH, it is a FIND.

    It's an "optimistic" lock, I guess.

    The following code is where the error occurs. It's the RETURN trigger for a fill-in named "Barra":

    DO:

       DEFINE VARIABLE rFila AS ROWID       NO-UNDO.

       IF VALID-EVENT(FOCUS, "VALUE-CHANGED")  THEN DO:

          ASSIGN Barra.

          FIND InvTarjeta WHERE invtarjeta.periodo = giperiodo

              AND invtarjeta.perinv = giperinv

              AND InvTarjeta.NroTarj = Barra SHARE-LOCK NO-ERROR.

          IF AVAILABLE invtarjeta THEN DO:

              ASSIGN

                  InvTarjeta.FlgBarrido = YES

                  InvTarjeta.InvPeso    = InvTarjeta.SdoNeto

                  InvTarjeta.InvNroMet  = InvTarjeta.NroMet.

              rFila = ROWID(InvTarjeta).

              FIND CURRENT invtarjeta NO-LOCK NO-ERROR.

              qhInvTarjeta:REPOSITION-TO-ROWID(rFila).

          END.

          ELSE DO:

             /* Other stuff */

          END.

       FIND CURRENT InvTarjeta NO-LOCK NO-ERROR.

       Barra:SCREEN-VALUE = "".

       APPLY "ENTRY" TO Barra.

       RETURN NO-APPLY.

    END.

    The table with the problem is "InvTarjeta".

    "rFila" is used to reposition the cursor on a browse.

    The program is used simultaneously for only two users. The value in "Barra" is entered by barcode scanners, both in different areas, scanning different items.

    They can´t take the same record, and still they receive the message.

    Ezequiel Montoya
    Lima - Perú
  • You appear to be attempting a pessimistic locking strategy.

    Furthermore you are attempting to use a SHARE-LOCK and never actually EXCLUSIVE-LOCK the record.  This is never going to work.

    You should do something more like this:

    DO:
    
       define buffer upd_invTarjeta for invTarjeta.
    
       DEFINE VARIABLE rFila AS ROWID       NO-UNDO.
    
       IF VALID-EVENT(FOCUS, "VALUE-CHANGED")  THEN DO:
    
          ASSIGN Barra.  /* what is this? */
    
          FIND InvTarjeta WHERE invtarjeta.periodo = giperiodo
    
              AND invtarjeta.perinv = giperinv
    
              AND InvTarjeta.NroTarj = Barra NO-LOCK NO-ERROR.
    
          IF AVAILABLE invtarjeta THEN DO for upd_invTarjeta transaction:
    
              find upd_invTarjeta exclsuive-lock where recid(  upd_invTarjet ) = recid (invTarjeta ) no-wait no-error.
    
              if not available ( upd_invTarjeta ) then
                do:
                  message "invTarjeta is locked...".  /* or however you want to handle that problem... */
                  return no-apply.
                end.
    
              ASSIGN
    
                  upd_InvTarjeta.FlgBarrido = YES
    
                  upd_InvTarjeta.InvPeso    = InvTarjeta.SdoNeto
    
                  upd_InvTarjeta.InvNroMet  = InvTarjeta.NroMet.
    
              rFila = ROWID(InvTarjeta).
    
              FIND CURRENT invtarjeta NO-LOCK NO-ERROR.
    
              qhInvTarjeta:REPOSITION-TO-ROWID(rFila).
    
          END.
    
          ELSE DO:
    
             /* Other stuff */
    
          END.
    
    /*   FIND CURRENT InvTarjeta NO-LOCK NO-ERROR. */
    
       Barra:SCREEN-VALUE = "".
    
       APPLY "ENTRY" TO Barra.
    
       RETURN NO-APPLY.
    
    END.



    Please excuse the terrible formatting -- "Communities" is not made for sharing code.

    The key points are:

    1) First try to find the record NO-LOCK.
    2) If it exists attempt to gain an EXCLUSIVE-LOCK on the upd_* buffer for that record.
    2a) all references to the upd_* buffer are STRONG SCOPED to the transaction block (the "DO FOR upd_* TRANSACTION" part of things. This is critical.
    2b) if the compiler complains that there is already a transaction active then you need to show more code and you have a bigger problem than you think you do.
    2c) if the compiler complains that you cannot strong scope the upd_* buffer then you have a typo.
    3) Testing for success of the exclusive-lock and handling failure as a contingency is the "optimistic" part of things. You are assuming that you will usually get the lock.

    --
    Tom Bascom
    tom@wss.com

  • Thank you, Tom.

    I rewrote part of the code approximately like yours.

    This is now the RETURN trigger:

    DO:

       IF VALID-EVENT(FOCUS, "VALUE-CHANGED")  THEN DO:

          FIND InvTarjeta WHERE invtarjeta.periodo = giperiodo

              AND invtarjeta.perinv = giperinv

              AND InvTarjeta.NroTarj = Barra NO-LOCK NO-ERROR.

          IF AVAILABLE invtarjeta THEN DO:

              RUN _ActualizaPesos.

             /* More stuff */

          END.

          ELSE DO:

             /* More other stuff */

          END.

       END.

       Barra:SCREEN-VALUE = "".

       APPLY "ENTRY" TO Barra.

       RETURN NO-APPLY.

    END.

    And this is the procedure _ActualizaPesos:

    DO TRANSACTION:
       DEFINE BUFFER bInvTarjeta FOR invtarjeta.
       FIND binvTarjeta EXCLUSIVE-LOCK WHERE ROWID(binvTarjeta) = ROWID(invTarjeta) NO-WAIT NO-ERROR.
       IF NOT AVAILABLE binvTarjeta THEN DO:
            /* Some stuff */
            MESSAGE invtarjeta.nrotarj "está bloqueada, por favor reintente el barrido." VIEW-AS ALERT-BOX WARNING.
            RETURN NO-APPLY.
       END.
       ASSIGN
            bInvTarjeta.FlgBarrido = YES
            bInvTarjeta.InvPeso = InvTarjeta.SdoNeto
            bInvTarjeta.InvNroMet = InvTarjeta.NroMet.
            FIND CURRENT binvtarjeta NO-LOCK NO-ERROR.
    END.
    END PROCEDURE.

    I putted the transaction part in a separated procedure, and into a redundant transaction.

    I guess this is also strongly scoped, am I right this time?

    Unfortunately I won't be able to try this until next year, I mean this tuesday.

    So thanks again and a really good 2018 for everybody!

    Ezequiel Montoya
    Lima - Perú