Getting Updated Value while using lock?

Question

this is my first Procedure.

define frame LockFrame Customer.Name Customer.CreditLimit Customer.Balance.
pause.

DO TRANSACTION:
    for each Customer exclusive-lock:
    assign Customer.CreditLimit = Customer.CreditLimit + 5.
    pause 1 no-message.
    display Customer.Name Customer.CreditLimit Customer.Balance.
    end.
end.

and thsi is my second Procedure.

define frame LockFrame Customer.Name Customer.CreditLimit Customer.Balance.
pause.

DO TRANSACTION:
    for each Customer exclusive-lock:
    assign Customer.Balance= Customer.Balance + 2.
    pause 1 no-message.
    display Customer.Name Customer.CreditLimit Customer.Balance.
    end.
end.

When I run first Procedure and just after second procedure, I have to get the value Updated by first (here CrditLimit).(and vice versa) But I am not able to run second since the record is locked by the first.It is showing an error message. I think problem is with my locking.Please help on this.

score 4 · Accepted Answer · answered Mar 12 '12 at 13:07

This is exactly what you should expect.

The record is locked, exclusively for the benefit of the first procedure, until the transaction commits. The commit will occur at the 2nd END statement.

I'm not sure why you have PAUSE in there -- you should never block on IO inside a transaction block -- that will only lead to problems (like users locking each other while they get up and go get coffee...)

You almost NEVER really want to enclose an update of an entire table in a transaction (which is what your DO TRANSACTION block is doing). Even if you think that is what you want to do, or have been told that that is what you must do it is probably wrong. This is usually the result of confusing a "business transaction" with a "database transaction" -- they are not the same thing -- especially when large amounts of data are in play.

A better way to write your code (apply the same concept to both samples):

define buffer updCustomer for customer.

for each customer no-lock /* where whatever */:

  /* maybe some no-lock logic... */

  do for updCustomer transaction:

    find updCustomer exclusive-lock where recid( updCustomer ) = recid( customer ).
    assign
      updCustomer.creditLimit = customer.creditLimit + 5.
    .

    display
      updCustomer.name
      updCustomer.creditLimit
      updCustomer.balance
    .

  end.

  pause 1 no-message.

end.

Using NO-LOCK on the FOR EACH allows selection logic or other logic to run without needing the lock. Using the update buffer and the DO FOR ... TRANSACTION block tightly scopes the record lock and the transaction to that single block. Placing the PAUSE outside the block prevents the "user goes to get coffee" problem.

Perfect for me sir..just started learning progress.I was not aware how to lock one record only instead of locking entire table . now i got it.I was not aware of recid function also.That pause placement actually i need as you suggested. Thanks for the valuable clear answer. — Bhavin Bhaskaran, Mar 13 '12 at 05:58

score 0 · Answer 2 · edited May 23 '17 at 11:49

I hesitate to comment on any answer that Tom has given, since I consider him authoritative. But I want to point out two small things.

First of all, the use of RECID might be better as ROWID. ROWID is recommended for use by Progress (see http://documentation.progress.com/output/OpenEdge102a/oe102ahtml/wwhelp/wwhimpl/common/html/wwhelp.htm?context=dvref&file=dvref-15-48.html) since RECID is "supported for backward compatibility. For most applications, use the ROWID function, instead."

But that's minor, really. What's also important in my opinion is what Tom did in his example for you - he defined a buffer ("define buffer updCustomer for customer.") that he used in the update. I want to encourage you to use buffers EVERY time you work with a record, especially if you get into using persistent or super procedures, or if you are using functions or internal procedures.

Why? Defining a buffer ensures that the scope of the buffer you are updating is limited to the place where you defined it. As an example, Progress will "leak" the default buffer into your super procedure if you aren't careful. Imagine this scenario...a program that finds a record, calls a function in a super procedure to do "some stuff" and then deletes the record.

FIND MyTable WHERE MyTable.fk = fkValue NO-LOCK NO-ERROR.
UpdateOtherStuff(MyTable.fkValue).
DeleteMyRecord(MyTable.fkValue).

But in "UpdateOtherStuff", it does some work including this...

FOR EACH MyTable:
    If MyTable.Thing = 'ThingOne' THEN LEAVE.
    /* other processing here... */
END.

You might be surprised when you find that the super procedure shares the default "MyTable" buffer with your program, and ends up repositioning the record somewhere you don't want...so that the call to "DeleteMyRecord()" has a different record than you expect.

The problem would be solved if "UpdateOtherStuff" had a "DEFINE BUFFER ... FOR MyTable" at the top, even if it was "DEFINE BUFFER MyTAble for MyTable" (strange as that looks...).

That's why Tom's example, including a DEFINE BUFFER..., should be a template for the work you do in the ABL.

This question was asked previously - see https://stackoverflow.com/a/5490130/1433147.

Getting Updated Value while using lock?

2 Answers2