DO FOR <record> TRANSACTION

[KennyAround]

Progress: 10.2B
Backend: Linux
Frontend: Windows client

A table lock is reported in the Appserver. I wonder if this is the cause of the problem,

DO FOR order-control TRANSACTION:

END.

where order-control is a control table having only a single record for parameters used in order processing.

My question here is whether order-control is exclusively locked during the whole transaction block?

 

[GregTomkins]

For all intents and purposes, yes, 'order-control' will be exclusively locked for the duration of whatever happens between the DO and END statements.

There are also situations where records may remain locked beyond the end of the transaction, but those don't appear to apply here. Also, technically speaking, the record won't actually lock until a FIND (or similar) statement retrieves it, which could be deep inside the DO block; at the point the block begins, it doesn't know which record you are after.

BTW, I think you are using the term 'table lock' loosely and actually you mean 'record lock'.

 

[KennyAround]

Thanks.

It seems that I found where the problem is,

DO FOR order-control TRANSACTION:
  FIND FIRST order-control EXCLUSIVE-LOCK.
  vUpdateNo = order-control.UpdateNo + 1.
  order-control.UpdateNo = vUpdateNo.

  FOR EACH ORDER EXCLUSIVE-LOCK
    where....
    , EACH ORDER-LINE
        where...
    <a large block of codes or order and order-line calculations>
  END.
END.

In the above code-block, the order-control is locked with a scope throughout the whole transaction block (strongly scoped). When other programs tried to use a share-lock for the same record, and for an unknown reason the AppServer crashed over this order-control record.

AppServer status;

  Usr Name     Chain #                  Rec-id Table Lock Flags   Tran State          Tran ID
  158 Ordercal REC  2045                  3201   112 EXCL  L      Active              60561226
   31          REC  2045                  3201   112 SHR    Q H   Dead                   0
   24          REC  2045                  3201   112 SHR    Q H   Begin               60560025
   25          REC  2045                  3201   112 SHR    Q H   Begin               60560124
   35          REC  2045                  3201   112 SHR    Q H   Dead                   0
   23          REC  2045                  3201   112 SHR    Q H   Begin               60560226
   40          REC  2045                  3201   112 SHR    Q H   Dead                   0
   32          REC  2045                  3201   112 SHR    Q H   Dead                   0

The first line shows that the record was locked exclusively, other programs requesting for a share-lock died out.


My last question here is, in case of a rollback, the following two transaction blocks behave the same or behave differently.

Block 1:

DO FOR order-control TRANSACTION:
  FIND FIRST order-control EXCLUSIVE-LOCK.
  ...

  FOR EACH ORDER...
    , EACH ORDER-LINE...

  END.
END.

Block 2,

DO TRANSACTION:
  FIND FIRST order-control EXCLUSIVE-LOCK.
  ...

  FOR EACH ORDER...
    , EACH ORDER-LINE...

  END.
END.

Is there any difference between Block 1 and Block 2 in case of a rollback?

 

[GregTomkins]

Short answer: NO DIFFERENCE

Long answer:

1) There would be differences if order-control is referenced outside of the DO block. My 'NO' is based on the assumption that this is not the case.

2) When you say 'rollback' I assume you mean of the entire transaction, as would occur when an AppServer crashes. Progress also has the notion of subtransactions, which are triggered by some errors and the UNDO statement; in this case, the rollback will not necessarily encompass the whole transaction.

BTW, the code you posted (eg. with the exclusive-lock on the control record, followed by a possibly lengthy update of order records) is generally a bad idea if this runs while other processes/users are trying to do things. On the other hand, this kind of logic is OK if it's the kind of thing that runs overnight when nobody is around. On the other other hand, if you are doing this for performance purposes, the gain is likely not worth the risk of locking things up. On the other other other hand, there are reasons other than performance for doing things this way. For example, one reason could be that you need to guarantee that order-control.update-no is sequential. Some accountants have built entire careers around making sure of important things like order numbers being sequential. It's a classic bike-shed way of distracting them. But I digress.

 

[KennyAround]

Short answer: NO DIFFERENCE

Long answer:

1) There would be differences if order-control is referenced outside of the DO block. My 'NO' is based on the assumption that this is not the case.

2) When you say 'rollback' I assume you mean of the entire transaction, as would occur when an AppServer crashes. Progress also has the notion of subtransactions, which are triggered by some errors and the UNDO statement; in this case, the rollback will not necessarily encompass the whole transaction.

BTW, the code you posted (eg. with the exclusive-lock on the control record, followed by a possibly lengthy update of order records) is generally a bad idea if this runs while other processes/users are trying to do things. On the other hand, this kind of logic is OK if it's the kind of thing that runs overnight when nobody is around. On the other other hand, if you are doing this for performance purposes, the gain is likely not worth the risk of locking things up. On the other other other hand, there are reasons other than performance for doing things this way. For example, one reason could be that you need to guarantee that order-control.update-no is sequential. Some accountants have built entire careers around making sure of important things like order numbers being sequential. It's a classic bike-shed way of distracting them. But I digress.

Thanks.

You are right. I consider that the bad coding practice of the ex-programmer who wrote the code. The "DO FOR order-control" shouldn't be used here, as the UpdateNo isn't strictly needed to be sequential. I asked simply because I am curious about what would happen during a rollback.

 

[TomBascom]

Actually...

"DO FOR tableName" strong scopes the tableName buffer. Which means that you cannot reference it outside the block in a way that leaks into the block. Rather than being "bad practice" it is usually the preferred way to construct an update block.

find customer no-lock where custNum = 1.
display name.

do for customer transaction:
  update custNum.
end.

results in:

** Illegal nested block statement reference to table Customer. (243)
** Could not understand line 4. (196)

when you try to compile it. (This is a good thing.)

An alternative:

define buffer upd_Customer for Customer.

find customer no-lock where custNum = 1.
display name.

do for upd_Customer transaction:
  find upd_Customer exclusive-lock where custNum = 1.
  update upd_Customer.custNum.
end.

FIND FIRST, on the other hand, is definitely bad practice.

 

[KennyAround]

Actually...

"DO FOR tableName" strong scopes the tableName buffer. Which means that you cannot reference it outside the block in a way that leaks into the block. Rather than being "bad practice" it is usually the preferred way to construct an update block.

That depends. If the update block is to update the order-control table then "DO FOR order-control" can be used. However in my case, the update block is designated for the updating of the ORDER and ORDER-LINE tables, not the order-control table. So extending the scope of order-control actually unnecessarily jeopardise other concurrent programs, I think.

 

[TomBascom]

Your original code doesn't extend the scope of order-control. It clarifies it and, depending on what lies above, limits it. The fact that you have operations on order and order-line nested inside the order-control block is, perhaps, a problem but that depends on what you are trying to do and is unrelated to the use, or non-use of FOR order-control.

 

[KennyAround]

Your original code doesn't extend the scope of order-control. It clarifies it and, depending on what lies above, limits it. The fact that you have operations on order and order-line nested inside the order-control block is, perhaps, a problem but that depends on what you are trying to do and is unrelated to the use, or non-use of FOR order-control.

I actually tested that. the "DO FOR order-control" followed by "find order-control exclusive-lock" will actually lock the record till the end of the transaction block, which is not what is expected as concurrent programs are using share-lock to request for the same record. order-control is a single record table. If this table record is locked for a long time, other programs will timeout in case of an exclusive-lock and may crash in case of a share-lock (that's what happened in my case). As a common sense, the looping of an order or order line table could be infinitively long, it is thus not a good practice to lock a control record with it until this is very necessary.

In this case, to exclusively lock a highly demanded control record with a large transaction block is a bad idea, and it's a bad practice if this is totally unnecessary and avoidable as in my case.

 

[KennyAround]

Your original code doesn't extend the scope of order-control. It clarifies it and, depending on what lies above, limits it. The fact that you have operations on order and order-line nested inside the order-control block is, perhaps, a problem but that depends on what you are trying to do and is unrelated to the use, or non-use of FOR order-control.

Ok, I understand what you meant to say now. You are right, if you mean that it has nothing to do with the use of "FOR order-control" as long as it is a transaction block then the scope of order-control will last through the block, the "FOR order-control" is just making it more precise. Then you must be right.

While I was talking about something else, it's apple and orange.

Anyway, thanks.

 

[LarryD]

I'm making the assumption that you need to update order or order-line or other tables with the value of the vUpdateNo ?

If so, here is a potential rework of the logical progression of your code to minimize the time the order-control record is locked:

def temp-table ttUpdate no-undo
    field tt-table as char
    field tt-recid as recid.


DO FOR order-control TRANSACTION:
  vUpdateNo = ?.
  empty temp-table ttUpdate.
  FOR EACH ORDER EXCLUSIVE-LOCK
    where....
    , EACH ORDER-LINE
        where...
    <a large block of codes or order and order-line calculations>
    
    <whenever you update a table with the vUpdateNo do this>
    create ttUpdate.
    assign ttUpdate.tt-table = <tablename>
           ttUpdate.tt-recid = recid(<tablename>).
  END.


  /* then since this is already in a transaction block, re-reading the original records
    that need updated should still be quickly retrieved and all transaction undo's are still in effect. */
    
    FIND FIRST order-control EXCLUSIVE-LOCK.    /* without no-error? you're not checking for already locked ? */
    assign
       order-control.UpdateNo = order-control.UpdateNo + 1
       vUpdateNo = order-control.UpdateNo.


    for each ttUpdate:
        if ttUpdate.tt-table = "table1"
        then do:
            find <table1> exclusive-lock where recid(table1) = ttUpdate.tt-recid.
            assign <table1>.somefield = vUpdateNo.
        end.
        else
        if ttUpdate.tt-table = "table2"
        then do:
            find <table2> exclusive-lock where recid(table2) = ttUpdate.tt-recid.
            assign <table2>.somefield = vUpdateNo.
        end.
        .... and so on ...
    end.
    empty temp-table ttUpdate.
END. /* of do for transaction */

Note:
If you have only 1 table to update with this value, forget the temp-table and just save the recid in a temp variable and just do a single find exclusive on that table.

This was done quickly, so YMMV.

 

[TomBascom]

Yes, that is what I am saying.

Your issue with a long lock is what I meant by "perhaps a problem"