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
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?
[mention:ae2ea2f6412743fc8be36c522f414ef0:e9ed411860ed4f2ba0265705b8793d05] 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.
[quote user="Thomas Mercer-Hursh"]
Note that it is possible the transaction arises in the program that calls this one.
> 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.
You are right George, Error on my site. Sorry.