Answered Lock Table Overflow, Increase -l On Server (915)

Potish

Member
I have a program reading data from a JSON response. The data is initially read into temp-tables and after the JSON has been successfully processed I copy the temp-table data to the database. One of the temp-tables sometimes is getting 7,600+ rows of data which causes the copy from temp-table to database to get the error 'Lock table overflow, increase -L on server (915)'. I tried to use the RELEASE stmt in the for each each loop but that did not help. How can I resolve this without changing my startup parameters? I don't have any control over number of rows that can be passed in JSON response.

My current code looks as follows

Code:
define temp-table ttproducts  like products.

/* RUN CODE TO READ JSON DATA TO TEMP-TABLE */

for each ttproducts:
create products.
buffer-copy ttproducts to products.
release products.
end.

/* Error looks to occur during processing of 'For each ttHAPIproducts above' */
 

Cringer

ProgressTalk.com Moderator
Staff member
Code:
define temp-table ttproducts  like products.
define buffer bproducts for products.

/* RUN CODE TO READ JSON DATA TO TEMP-TABLE */

for each ttproducts:
  do for bproducts transaction: 
    create bproducts.
    buffer-copy ttproducts to bproducts.
  end.
end.
 

Cringer

ProgressTalk.com Moderator
Staff member
In explanation of the above - you need to sort your transaction scoping out. The above will make a transaction for each ttproducts record that is strongly scoped to the bproducts buffer. Your code was creating a weakly scoped transaction for the whole for each block.
Best practise is to always have strongly scoped small transactions.
 

Potish

Member
In explanation of the above - you need to sort your transaction scoping out. The above will make a transaction for each ttproducts record that is strongly scoped to the bproducts buffer. Your code was creating a weakly scoped transaction for the whole for each block.
Best practise is to always have strongly scoped small transactions.

Thank you very much for the suggestion. I did try the code change suggested but it had not resolve the problem. The program is still getting the error.
 

TheMadDBA

Active Member
In addition to what Cringer said... this approach will help you determine if you have a transaction started somewhere else. You can also compile your code with the listing option to see where transactions start and end.

The bottom line is: Never allow the 4GL to decide the transaction scope. Always control the transaction scope in your code so that it does what you want it to do.
 

Cringer

ProgressTalk.com Moderator
Staff member
Thank you very much for the suggestion. I did try the code change suggested but it had not resolve the problem. The program is still getting the error.
This would suggest, as the Mad One is implying, that the transaction scope is bigger than your program.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
The lock table (_Lock) is a shared resource on the server side. You could have a program that tries to obtain only a single record lock and gets a 915 error, if the lock table happens to be full due to the activity of other users.

Is this a test database where your session is the only client?
 

GregTomkins

Active Member
Most likely in "/* RUN CODE TO READ JSON DATA TO TEMP-TABLE */" you have some code that triggers a transaction to start, which would wrap the whole thing in one big update. The most likely cause of this is an EXCLUSIVE-LOCK (on a real DB table, not a temp-table) or an assignment to a database field.

One way to solve this would be to put DO TRANSACTION: END around "/* RUN CODE TO READ JSON DATA TO TEMP-TABLE */", but that is usually the wrong thing to do.
 

Potish

Member
Most likely in "/* RUN CODE TO READ JSON DATA TO TEMP-TABLE */" you have some code that triggers a transaction to start, which would wrap the whole thing in one big update. The most likely cause of this is an EXCLUSIVE-LOCK (on a real DB table, not a temp-table) or an assignment to a database field.

One way to solve this would be to put DO TRANSACTION: END around "/* RUN CODE TO READ JSON DATA TO TEMP-TABLE */", but that is usually the wrong thing to do.

Thank you. I tried your suggestion and adding the DO TRANSACTION: END around "/* RUN CODE TO READ JSON DATA TO TEMP-TABLE */", did resolve the problem. I do have one EXCLUSIVE-LOCK to a database table under the section that reads the JSON data but after the ASSIGN statement I do FIND CURRENT table NO-LOCK so I did not think it would create a problem. I will keep looking to make sure I did not miss something.
 

GregTomkins

Active Member
All that matters is that there is an EXCLUSIVE-LOCK (on a real DB table, not a temp-table). The fact that you later NO-LOCK it, or whatever, doesn't prevent it's transaction-starting effect.

The downside to slapping DO TRANSACTION around it is that now it will be possible for the code inside /* RUN CODE TO READ JSON DATA TO TEMP-TABLE */ to succeed but the code inside FOR EACH ttProducts to fail, and your DB may end up in some inconsistent state. This may or may not be a problem, you need to think about the context. Good luck!
 
Top