Lock Table Overflow, Increase -L on server (915) - Forum - OpenEdge General - Progress Community

Lock Table Overflow, Increase -L on server (915)

 Forum

Lock Table Overflow, Increase -L on server (915)

This question is not answered

Hi guys, 

I have a very simple logic which updates each and every record of table atask and acut. 

This logic runs on appserver. 

But after running, in app server log Lock Table Overflow, Increase -L on server (915) . And the application crashes.

Code snippet: 

for each atas exclusive-lock where atas.xlevc = '1'
                                                       and atas.xstac LT '900'
                                                       and atas.gmacc = '5'
                                                       break by atas.gmacc by atas.atasnrank:
   if first-of(atas.gmacc) then do:
         vRank = 0.
   end.
   vRank = vRank + 1.
   assign atas.atasnrank = vRank.

   for each acut exclusive-lock where acut.xlevc = '1'
                                                          and acut.atasn = atas.atasn:
                 assign acut.acutnrank = atas.atasnrank.

     end.
end.

1. I have tried increasing -L parameter in server (no solution)

2. I have tried using do transaction block. (no solution)

3. I used (find current) inside the block using buffers, but it also results no solution. 

Is there any way to solve this error (preferably not increasing -L because -L has 8192 default which is enough). 

Best,

Kushal

All Replies
  • The issue is that you are locking out every record in the for each as the transaction is scoped to that block. Best practise is to use named buffers for a transaction and to scope them as small as possible.

    Something like this:

    define buffer batas for atas.

    define buffer bacut for acut.

    for each atas no-lock:

     do for batas transaction:

       find batas exclusive-lock where rowid(batas) eq rowid(atas) no-error.

       batas.atasnrank = vRank.

     end.

     for each acut no-lock:

       do for bacut transaction:

         find bacut excluisve-lock where rowid(bacut) eq rowid(acut) no-error.

         bacut.acutrank = atas.atasrank.

       end.

     end.

    end.

    I hope that makes sense.

  • "For each atas" is a transaction block (btw, from the logical point of view it's better to specify this explicitly with the "transaction" keyword). "For each atas" is a block inside the transaction. All locks on the acut records will be kept until the end of the "for each atas" block. Is it what the logic of updates needed?

  •  Yes.

  • @James Palmer :

    Thanks for the information.

    Did try this , still the lock table overflow issue.

  • If the application logic requires a large transaction then a database needs a large lock table.

  • If you tried what I did and still got a lock table overflow then you're either not doing it right, or you have posted a snippet of code rather than all of it and the transaction is scoped bigger than the snippet you posted. If it's a snippet, then

    message transaction view-as alert-box.

    Just before the for each. That will tell you if you're inside a transaction already.

    If you need to be able to back out all of the changes on failure, then maybe cache the records you're updating in a temp-table and then update them, then write them back to the database in a tight loop with small transaction scopes.

  • Compile the program with the LISTING option and look at the transaction table at the bottom.  Could be you are doing something to scope the transaction to a larger block than what you have shown us.  Also, if you use the transaction keyword on the blocks which you think should be the transaction scope, the compiler will complain if this is already inside a transaction.  Note that it is possible the transaction arises in the program that calls this one.

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

  • Thomas Mercer-Hursh

    Note that it is possible the transaction arises in the program that calls this one.

    And that is something COMPILE ... LISTING will not tell you.
    Which gets annoying very fast, especially when you run into cases where stuff fails because a transaction was handled incorrectly in a completely unrelated bit of code, which happens to be running persistently to allow for maximum scope creep.
    That's one of the use cases I used a long time ago to argue that we need a way to catch the actual transaction scope at runtime, and that's why we now have the 4GLTrans logging showing just that.
  • > Note that it is possible the transaction arises in the program that calls this one.

    Would it be a good practice to issue a warning if a transaction is already opened /before/ an explicit transaction block?:

    IF TRANSACTION THEN
    MESSAGE "Warning: transaction is already opened"
      VIEW-AS ALERT-BOX WARNING.
    
    DO TRANSACTION:
    ...
    END.
    
  • > Would it be a good practice to issue a warning if a transaction is already opened /before/ an explicit transaction block?

    That would reduce your ability to reuse code. Do transaction indicates that what is about to follow always has to happen within a transaction, but does not necessarily exclude the possibility that some other code might call it as part of more changes that should all happen within a single transaction.

  • I would issue a warning only for the blocks with the large transactions as, for example, in the first post of the topic.

  • To fix,  remove  "break by" , implement the same   behavior with variable..

  • "Break by" adds only one extra lock.

  • For single FOR EACH just one extra lock,

    For nested FOR

    FOR EACH dbtable1 EXCLUSIVE-LOCK BREAK BY dbtable1.field1:

       FOR EACH dbtable2 EXCLUSIVE-LOCK WHERE dbtable2.field1 = dbTable1.field1:

       END.

    END.

    8192 ERROR on my settings

  • FOR EACH Customer EXCLUSIVE-LOCK BREAK BY Customer.State:
      FOR EACH Order OF Customer EXCLUSIVE-LOCK:
        ACCUMULATE "Order" (COUNT).
      END.
    
      FIND FIRST DICTDB._DbStatus NO-LOCK.
      IF (ACCUM COUNT "Order") + 2 NE DICTDB._DbStatus._DbStatus-NumLocks THEN
      DISPLAY (ACCUM COUNT "Order") DICTDB._DbStatus._DbStatus-NumLocks.
    END.
    

    Total locks =  "Order" locks + 2.

    But if you will remove the "break by" then

    Total locks =  "Order" locks + 1.