Update a record with NO-LOCK status - Forum - OpenEdge Development - Progress Community

Update a record with NO-LOCK status

 Forum

Update a record with NO-LOCK status

This question is not answered

We just came across an odd situation. In one of our programs we FIND a record EXCLUSIVE and then run another program. In that program we FIND the record again, but now NO-LOCK and then update it. 

Yeah, I know. It's a bug in our code. 

But whaddayaguess? It just works. The record gets updated without any problem. Is this a bug or just Progress protecting me against myself? Below a test script against the sports db (or here on the ABL dojo).

DEFINE BUFFER bCust FOR customer.

FIND bCust NO-LOCK WHERE bCust.custNum = 1.
MESSAGE bCust.name VIEW-AS ALERT-BOX.

DO TRANSACTION:
  FIND bCust EXCLUSIVE-LOCK WHERE bCust.custNum = 1.
  RUN updateRecord.
END.

FIND bCust NO-LOCK WHERE bCust.custNum = 1.
MESSAGE bCust.name VIEW-AS ALERT-BOX.

PROCEDURE updateRecord:
  DEFINE BUFFER bCust FOR customer.
  FIND bCust NO-LOCK WHERE bCust.custNum = 1.
  ASSIGN bCust.name = 'Hello world ' + STRING(TIME).
END. 

All Replies
  • I'm not sure if this is more or less worrying?

    DEFINE BUFFER bCust FOR customer.
    
    FIND bCust NO-LOCK WHERE bCust.custNum = 1.
    MESSAGE bCust.name VIEW-AS ALERT-BOX.
    
    DO TRANSACTION:
      FIND bCust EXCLUSIVE-LOCK WHERE bCust.custNum = 1.
      RUN updateRecord.
    END.
    
    FIND bCust NO-LOCK WHERE bCust.custNum = 1.
    MESSAGE bCust.name VIEW-AS ALERT-BOX.
    
    PROCEDURE updateRecord:
      FIND customer NO-LOCK WHERE customer.custNum = 1.
      ASSIGN customer.name = 'Hello world ' + STRING(TIME).
    END. 
    
  • Locks are on a *record*, not a buffer. When you refind that record no-lock (within the same session), you already have an exclusive-lock on it. 

    Rick Terrell 
    Principle Consultant, Professional Services 
    Progress

    Sent from my iPhone
  • This is not a bug. The record in the transaction is still locked and re-reading no-lock will make no difference until the transaction ends. Only then will the lock on the record be downgraded to no-lock. If you had not re-read no-lock then the lock will be share-lock after the transaction I believe.

  • I don’t believe this is a problem.
     
    We only load one copy of a physical record into memory.  You have the named buffer and the actual buffer which end up pointing to the same record.
     
    You have gotten an exclusive lock in the DO TRANS block then RUN the updateRecord procedure where the FIND ends up pointing to the one record in memory which is in exclusive lock.
     
    Brian Maher
    Principal Engineer, Technical Support
    Progress
    Progress
    14 Oak Park | Bedford, MA 01730 | USA
    phone
    +1 781 280 3075
     
     
    Twitter
    Facebook
    LinkedIn
    Google+
     
     
    ProgressNext2018_Email_Signature
     

  • The result is the expected, because the transaction is oriented on the first buffer not on the second, even when they are called equal, they occupy two different references in memory.

    If you want to keep the transaction you must not generate a new buffer, nor perform the no-lock search.

    DEFINE BUFFER bCust FOR customer. (this is a buffer1)

    FIND bCust NO-LOCK WHERE bCust.custNum = 1.

    MESSAGE bCust.name VIEW-AS ALERT-BOX.

    DO TRANSACTION:

     FIND bCust EXCLUSIVE-LOCK WHERE bCust.custNum = 1.

     RUN updateRecord.

    END.

    FIND bCust NO-LOCK WHERE bCust.custNum = 1.

    MESSAGE bCust.name VIEW-AS ALERT-BOX.

    PROCEDURE updateRecord:

     (This process could update the record as long as it is not another pointer.)

     DEFINE BUFFER bCust FOR customer. (This is a buffer2)

     FIND bCust NO-LOCK WHERE bCust.custNum = 1. (This is the same record but not the same pointer, this pointer is in no-lock mode)

     ASSIGN bCust.name = 'Hello world ' + STRING(TIME).

    END.

  • But when you do 'RELEASE bCust.' before  'RUN updateRecord'; than error message 'record has NO-LOCK' status' appears. The record lock is still exclusive, but limbo.

  • Patrick,

    This is on purpose. When you already have a lock and then you request another, the higher strength lock is retained if the new lock request is of lower strength than the existing. If the new lock request is higher strength than existing, then the higher strength lock is requested.

    Also, the 4GL runtime manages buffers in such a way that if two buffers in the same session, but with different names or the same name in nonintersecting scopes, refer to the same record, they are combined so you only get one copy of the record. This is necessary because the database has only one copy of the record.

    -gus

    > On May 2, 2018, at 9:11 AM, Patrick Tingen wrote:

    >

    > Update from Progress Community

    >

    > Patrick Tingen

    >

    > We just came across an odd situation. In one of our programs we FIND a record EXCLUSIVE and then run another program. In that program we FIND the record again, but now NO-LOCK and then update it.

    >

    > Yeah, I know. It's a bug in our code.

    >

    > But whaddayaguess? It just works. The record gets updated without any problem. Is this a bug or just Progress protecting me against myself? Below a test script against the sports db (or here on the ABL dojo).

    >

    > DEFINE BUFFER bCust FOR customer.

    >

    > FIND bCust NO-LOCK WHERE bCust.custNum = 1.

    > MESSAGE bCust.name VIEW-AS ALERT-BOX.

    >

    > DO TRANSACTION:

    > FIND bCust EXCLUSIVE-LOCK WHERE bCust.custNum = 1.

    > RUN updateRecord.

    > END.

    >

    > FIND bCust NO-LOCK WHERE bCust.custNum = 1.

    > MESSAGE bCust.name VIEW-AS ALERT-BOX.

    >

    > PROCEDURE updateRecord:

    > DEFINE BUFFER bCust FOR customer.

    > FIND bCust NO-LOCK WHERE bCust.custNum = 1.

    > ASSIGN bCust.name = 'Hello world ' + STRING(TIME).

    > END.

    >

    >

    >

    > View online

    >

    >

    > You received this notification because you subscribed to the forum. To stop receiving updates from only this thread, go here.

    >

    > Flag this post as spam/abuse.

    >

  • Thanks all for the clarification. In a way it makes sense because ultimately - as you all pointed out - there is only one copy of the record. Still, I find it mildly disturbing that the compiler doesn't bark at constructions like this:

    find first customer no-lock.
    customer.name = ''.

    But then again, I think there is enough room for improvement in the compiler department Angel

  • Looking at it, I cannot understand what is fundamentally different in this code as opposed to my first example:

    DO TRANSACTION:
      FIND bCust EXCLUSIVE-LOCK WHERE bCust.custNum = 1.
      bCust.name = '1'.
      FIND bCust NO-LOCK WHERE bCust.custNum = 1.
      bCust.name = '2'.
    END.
    


    Instead of running another procedure, I do exactly the same, only in the same code block. This time, the ABL says:

    ** bCust record has NO-LOCK status, update to field not allowed. (396)
    ** Unable to update bCust Field. (142)

  • Patrick, this is the same when you doing a RELEASE. The lock is still exclusive but it is limbo. So when you have a limbo exclusive lock, than you will get error message 396. In my opinion it is a wrong error message, because the record does not have NO-LOCK, it is has an EXCLUSIVE limbo lock. Also you could discuss if NO-LOCK is a record status at all.

    This inconsistency in behavior is very confusing and difficult to see. So it would be nice if somehow the compiler could see this.

  • >  it is has an EXCLUSIVE limbo lock

    Should ASSIGN statement upgrade the lock to EXCLUSIVE as it does with SHARE lock?

  • > On May 3, 2018, at 6:36 AM, George Potemkin wrote:

    >

    > Should ASSIGN statement upgrade the lock to EXCLUSIVE as it does with SHARE lock?

    >

    >

    >

    no. this is not necessary. the point of the “limbo” lock state is to optimize the lock release in order to eliminate future database actions, especially in the case of tcp/ip clients. the client no longer knows the lock state or anything else about the record because the buffer has been released (disconnected).

    when you find it again, the database remembers the exclusive lock state but the client does not know.

  • Gus,

    Can you further explain this?

    gus bjorklund

    When you already have a lock and then you request another, the higher strength lock is retained if the new lock request is of lower strength than the existing. If the new lock request is higher strength than existing, then the higher strength lock is requested.

    If I am understanding you, how does one release a record lock at the end of a transaction?

    Will this not downgrade to a NO-LOCK?

    DO TRANSACTION:

      FIND cust EXCLUSIVE-LOCK.

      ASSGN cust.name = "ABC".

      FIND cust NO-LOCK.

    END TRANSACTION.

      

    Jeff Ledbetter
    Product Architect | Roundtable Software

  • Jeff, what is the point of the second FIND.  The lock is going to be released at the end of the transaction anyway.

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

  • Thomas Mercer-Hursh

    Jeff, what is the point of the second FIND.  The lock is going to be released at the end of the transaction anyway.

    From what I've always understood, an EXCLUSIVE-LOCK downgrades to a SHARE-LOCK and one has to re-find NO-LOCK to remove the lock (or use RELEASE). 

    Jeff Ledbetter
    Product Architect | Roundtable Software