Code execution paused because of record lock in trigger

juninhopolo

New Member
Working with transactions, I found the following problem / imagined scenario.


A record needs to be updated and for that I used strong scope, transaction and proper lock, like so:


Code:
def buffer customer for customer.


do for customer trans.

    find customer exclusive-lock where customer.cust-num = 1 no-error no-wait.

    if avail customer then do:

        assign customer.name = 'John Doe'.

    end.

end.

My code was getting stuck right after the ASSIGN statement and doing some basic debug (aka MESSAGE) I found out that it was waiting for a record from another table to be available. It turns out my customer table have a trigger that updates another table (that have the record the code above "need" locked by another process / user).


Is there any way to know or trap this condition, so I can silent handle that instead of waiting for the execution resume (record available again) or crash (lock timeout)? Something along the lines of "NO-ERROR NO-WAIT".
 
Last edited:

Cringer

ProgressTalk.com Moderator
Staff member
The trigger code needs updating then to better handle the record not being lockable. And then you need to find the code that is locking the record for too long and fix that too.
 

TomBascom

Curmudgeon
If you are on a reasonably up to date release the STOP-AFTER option on a DO block might help.

Something along these lines:
Code:
define buffer customer for customer.

do for customer transaction:

  find customer exclusive-lock where customer.cust-num = 1 no-error no-wait.

  if available customer then
    do stop-after 5 on stop, undo leave:
      assign
        customer.name = 'john doe'.
      .
    end.

end.

As James says though, that trigger code needs to be fixed. It shouldn't be allowed to do that.
 

juninhopolo

New Member
Sorry for the delay in the response. I was doing some tests and research. That trigger needs refactoring indeed. Besides that, here's what I come up with based on the STOP-AFTER code snippet suggested.
Code:
define variable allDone as logical initial false no-undo.

define buffer customer for customer.

do for customer transaction stop-after 5 on stop undo, leave.

    find customer exclusive-lock where customer.cust-num = 1 no-error no-wait.

    if available customer then do:

        assign customer.name = 'John Doe'.

        validate customer.

    end.

    assign allDone = true.

end.

message allDone

    view-as alert-box info buttons ok.

The use of the allDone variable allow us to know if the block with the STOP-AFTER clause reached the end (I would love to come up with some nicer way of doing that, maybe with RETURN ERROR instead of LEAVE, but it's ok for now).

The use o VALIDATE:

I know that in the docs says:
Because validation is done automatically, you rarely have to use the VALIDATE statement

but here's my explanation for that. In the docs also says:
The AVM automatically validates a record when a record in the record buffer is replaced by another, a record's scope iterates or ends, the innermost iterating subtransaction block that creates a record iterates, or a transaction ends.

so, my code was not paused right after the ASSIGN statement because of the ASSIGN itself. The record scope (and in this case also the transaction scope) ended, and then the triggers were "called" outside the DO block with STOP-AFTER (since the validation occurs when the scope is ended), not producing any effect.

These conclusions were based in my tests an interpretation of the documentations, I am open to hear your thoughts on that. Thanks again!
 
Last edited:

TomBascom

Curmudgeon
You could probably avoid VALIDATE like so (again, not actually tested):

Code:
define variable allDone as logical no-undo initial false.

define buffer customer for customer.

do stop-after 5 on stop undo, leave:

  do for customer transaction:

      find customer exclusive-lock where customer.cust-num = 1 no-wait no-error.

      if available customer then do:

          assign customer.name = 'John Doe'.

          /* validate customer. */

      end.

  end.  /* "customer" goes out of scope here */

  assign allDone = true.

end.

message allDone view-as alert-box info buttons ok.

Unsolicited style notes:

1) Please do not abbreviate keywords. It does not help readability.

2) UPPER CASE is for making your code look like the documentation and kbase samples. Or COBOL. Personally, I don't want anyone associating my code with those examples. It also wears out the bits and hurts my eyes.

3) Adding FIRST after every FIND is not useful and, in many cases, is actively misleading or an outright bug. Don't do it. No matter what you may have heard it is not a "best practice" nor is it "faster". I could go on. And on...
 

juninhopolo

New Member
Thank you all. I rewrote the code in lowercase, not abbreviating and removing the "FIRST" in "FIND" statement. All these tips helps me a lot. I'll do some tests, but as always, this was really helpful.
 
Top