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
  • RELEASE doesn't do what you think it does - from the documentation:

    Verifies that a record complies with mandatory field and unique index definitions.  It clears the record from the buffer and unites it to the database if it has been changed.

    Note the absence of any mention of locks.

    --
    Tom Bascom
    tom@wss.com

  • ChUIMonster

    RELEASE doesn't do what you think it does - from the documentation:

    Verifies that a record complies with mandatory field and unique index definitions.  It clears the record from the buffer and unites it to the database if it has been changed.

    Note the absence of any mention of locks.

    ABL Essentials states:

    "If you have any doubt at all about when a record goes out of scope or when a lock is released, release the record explicitly when you are done updating it with the RELEASE statement. If you release the record, you know that it is no longer locked, and that you cannot unwittingly have a reference to it after the transaction block that would extend the scope of the lock, or even the transaction."

    Jeff Ledbetter
    Product Architect | Roundtable Software

  • Sounds like the wording in the docs needs to change.  The DB can't downgrade your exclusive lock on the record to a share lock before the end of your transaction scope.  If it did, then someone else could obtain a share lock.  Then you wouldn't be able to undo your changes if you needed to.

  • Ted Duke and I wrote an article about record locking and the RELEASE statement for Progressions circa 2002. I'm amazed at how this still trips people up. BTW, Progress documentation has quite a bit of either wrong, or considerably inaccurate information. Tom is correct.

  • I should have noted that an important part of getting the desired behavior and getting it automatically is scoping the buffer to the transaction block.  Anything else is an invitation for upping the scope of the transaction or leaving buffers in unknown states.

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

  • Jeff:

    When a record is updated, the exclusive lock required to do so CANNOT be released until after the transaction has been committed.

    This is so because if the lock is released, then another user can acquire another lock (be it share or exclusive). If that happens and your transaction is undone, the undo will FAIL cuz of the conflicting lock. This is not a good situation.

  • Any documentation, kbase, or forum posting that advocates using RELEASE is almost certainly wrong.  RELEASE belongs on the permanent keyword forget list.

    IMHO

    --
    Tom Bascom
    tom@wss.com

  • Jeff: forgot to mention that once the transaction has been committed (after the "end transaction" in your program), it could be downgraded, depending on record scope. so if the end transaction was followed by a display customer.name, a downgrade would be required.

    but if you had done a RELEASE, the record would no longer be available in the buffer.

  • Should be after the transaction…. If I remember correct.
     
    :
    END TRANSACTION.
    FIND CURRENT CUST NO-LOCK.
     
  • gus bjorklund

    Jeff:

    When a record is updated, the exclusive lock required to do so CANNOT be released until after the transaction has been committed.

    This is so because if the lock is released, then another user can acquire another lock (be it share or exclusive). If that happens and your transaction is undone, the undo will FAIL cuz of the conflicting lock. This is not a good situation.

    Of course. The prior wording threw me for a bit of a loop. 

    Jeff Ledbetter
    Product Architect | Roundtable Software

  • If you can FIND CURRENT after the transaction you are in a state of sin.

    That means that the record scope exceeds the transaction scope.

    The proper way to scope a record to a transaction block is via strong scope and a FOR statement:

    define buffer upd_cust for customer.

    for each customer no-lock:

     if customer.discount > 10 then

       do FOR upd_cust transaction: /* strong scope the “upd_cust" buffer */

         find upd_cust exclusive-lock where upd_cust.custNum = customer.custNum.

         upd_cust.discount = 10.

       end.

    end.

    --
    Tom Bascom
    tom@wss.com

  • 😊 yes, thats for sure, but my point was related to a find within the DO TRANSACTION:  ….. END TRANSACTION. To do the find no-lock within the transactionblock has no point, you have to do it after the END of transaction…. As far as I can remember 😊 a FOR block will release the record, if you don’t do a leave…
     
     
     
    //Geir Otto
     
  • goo
    ..my point was related to a find within the DO TRANSACTION:  ….. END TRANSACTION. To do the find no-lock within the transactionblock has no point, you have to do it after the END of transaction.. 
     

    I wasn't thinking when I typed my example. :)

    Jeff Ledbetter
    Product Architect | Roundtable Software