Lock Table overflow error

[Ach]

Hi All -

The objective is to move transactions to a history table and this is my code:

For each order no-lock:

status default "Moving Orders to History.." + string(order.order-no) + ".." + string(order.order-date).

create order-hist.
assign order-hist.entity = order.entity
order-hist.order-no = order.order-no
order-hist.order-date = order.orer-date
(more fields) .
End.

After a few records, I get the error 'Lock table Overflow. Increase -L on server".

In the DB startup script the -L is 10000 and -l is 1000.

What's the solution? Is it possible to put the create statement inside a transaction of 100 and then exit out and come back in to do another 100?


Thanks in advance for your help !

 

[BobyIsProgress]

Hello,

First, for your next post use '<code>' tags for more readable code:


Second for you question you need to learn about transaction and how it works: Progress Customer Community
Third to help you and give you a solution, you will have to do it like this

DEFINE BUFFER upd-Order-hist FOR order-hist.

FOR EACH order NO-LOCK:

    STATUS DEFAULT "Moving Orders to History.." + STRING(order.order-no) + ".." + STRING(order.order-date).
    DO FOR upd-Order-hist TRANSACTION:
        CREATE upd-Order-hist.
        BUFFER-COPY order-hist TO upd-Order-hist ASSIGN /* Buffer-Copy will copy every field in the ordre-hist buffer record to your history table if all your field are equally named and have the same format */
            <more fields> . /* If you have fields that don't match you could use the ASSIGN statement as follow */
    END.
END.

Like this you will only have one record locked a time and not as many as you have to history.
It's really important to keep your transaction as small as possible.

Best Regards,

BobyIsProgess

 

[Patrice Perrot]

Hi,

I thought that on a "for each no-lock : create . ... end.", the transaction is "by default" for an iteration of the for each (1 record by transaction).

If I am correct, to have a lock table overflow in this case , 3 options :
1/ the "for each" is fired in a transaction
2/ there is a trigger on order-hist (or on order if you delete it) which locked a lot of record
3/ another session locked a lot of records

Have a look on the "-diag*" DB startup parameters (-diagDir -diagEvtLevel -diagPause -diagPrefix -diagFormat ...) it could give you a lot of information on the cause of the lock table overflow .

Best regards
Patrice

 

[Ach]

Hello,

First, for your next post use '<code>' tags for more readable code:
View attachment 2873

Second for you question you need to learn about transaction and how it works: Progress Customer Community
Third to help you and give you a solution, you will have to do it like this

DEFINE BUFFER upd-Order-hist FOR order-hist.

FOR EACH order NO-LOCK:

    STATUS DEFAULT "Moving Orders to History.." + STRING(order.order-no) + ".." + STRING(order.order-date).
    DO FOR upd-Order-hist TRANSACTION:
        CREATE upd-Order-hist.
        BUFFER-COPY order-hist TO upd-Order-hist ASSIGN /* Buffer-Copy will copy every field in the ordre-hist buffer record to your history table if all your field are equally named and have the same format */
            <more fields> . /* If you have fields that don't match you could use the ASSIGN statement as follow */
    END.
END.

Like this you will only have one record locked a time and not as many as you have to history.
It's really important to keep your transaction as small as possible.

Best Regards,

BobyIsProgess

Thanks for replying.

Now I get a warning 'WARNING--TRANSACTION keyword given within actual transaction level'

 

[Ach]

Hi,

I thought that on a "for each no-lock : create . ... end.", the transaction is "by default" for an iteration of the for each (1 record by transaction).

If I am correct, to have a lock table overflow in this case , 3 options :
1/ the "for each" is fired in a transaction
2/ there is a trigger on order-hist (or on order if you delete it) which locked a lot of record
3/ another session locked a lot of records

Have a look on the "-diag*" DB startup parameters (-diagDir -diagEvtLevel -diagPause -diagPrefix -diagFormat ...) it could give you a lot of information on the cause of the lock table overflow .

Best regards
Patrice

Thanks for replying. There are no triggers in the table bit it's possible that another session locked records.

 

[Rob Fitzpatrick]

Now I get a warning

"Now" implies you made a change. Are you implying that you altered your code to look like the proposal from @BobyIsProgress?

What's the solution?

That warning tells you that you were already in a transaction when the AVM executed the line that specified the transaction keyword. The solution is to review the prior code (e.g. higher in your program, or in the caller) to determine why that lingering transaction exists and fix it. If you can't find it by visual inspection, generate compile listings to see your transaction scope.

It's really important to keep your transaction as small as possible.

Correct.

 

[philippoommen1]

Hi, you can compile the program using the LISTING option
COMPILE x.p LISTING x.txt.
see the Transaction Listing in the output file.

 

[tamhas]

Note to be very clear that the message is telling you that all the updates were a part of a single transaction and that explain running out of lock table space.

 

[Ach]

Reply to everyone - Thanks for sharing your knowledge.

That lock table overflow error is not dependent on just one program doing something but also dependent on how many other programs are running at that time consuming resources and creating record locks because the total allotted to -L parameter runs short.

I found the cause of the transaction message and fixed it.

For each:
       Do transaction:
             create.
             assign 
      End.
End.

 

[TomBascom]

Is that code snippet supposed to be the cause? Or the fix?

 

[tamhas]

If it is supposed to be the fix, it is an awful fix.

 

[TomBascom]

True. If that fixes something I am very curious to see how broken the predecessor was.

 

[Ach]

What did you mean? That was the fix and why is it bad? Just to clarify there is a no-lock on for each

For each table-name no-lock:
       Do transaction:
             create hist-table.
             assign hist-table.field1 = table-name.field1
                        hist-table.field2 = table-name.field2.
       End.
End.

 

[Ach]

I have actually forgotten most of progress because have been doing Oracle and SQL Server since last 15 years and those DBs don't have these kind of problems and neither do they need to be in single user mode to make any schema changes.

 

[Rob Fitzpatrick]

This is getting off topic, but an OE DB doesn't need to be in single-user mode to make schema changes.

 

[Ach]

This is getting off topic, but an OE DB doesn't need to be in single-user mode to make schema changes.

What I meant was that you can only make changes when you are the only user connected to it. Sorry for mentioning Oracle/SQL Server

 

[Rob Fitzpatrick]

What I meant was that you can only make changes when you are the only user connected to it. Sorry for mentioning Oracle/SQL Server

I don't mind at all that you mention other platforms. It is beneficial to gain perspective from people such as yourself who use many different RDBMS including OpenEdge.

I am just correcting the record. While it is possible to run into issues with one user holding the schema lock while another is trying to make schema changes in an OpenEdge database, that doesn't have to happen. It is absolutely possible to make schema changes in an OpenEdge database opened in multi-user mode.

 

[Ach]

I don't mind at all that you mention other platforms. It is beneficial to gain perspective from people such as yourself who use many different RDBMS including OpenEdge.

I am just correcting the record. While it is possible to run into issues with one user holding the schema lock while another is trying to make schema changes in an OpenEdge database, that doesn't have to happen. It is absolutely possible to make schema changes in an OpenEdge database opened in multi-user mode.

Really? Not sure which version has that feature because whenever I make changes to the tables/fields, it always tells me that someone is connected and I can only make changes when everyone is disconnected except myself. Ours is version 10

 

[Rob Fitzpatrick]

Really? Not sure which version has that feature because whenever I make changes to the tables/fields, it always tells me that someone is connected and I can only make changes when everyone is disconnected except myself. Ours is version 10

All OE 10.x releases are quite old and are in retired status.

OpenEdge 10.0A shipped 20 years ago and the latest release in the 10.x family, 10.2B, shipped 14 years ago. So there is a considerable distinction to be made between what OpenEdge can do in modern releases, and what it used to be able to do in older releases.

https://docs.progress.com/bundle/openedge-life-cycle/page/OpenEdge-Life-Cycle.html

 

[Ach]

Nice to know that in the new versions, schema changes can be made in multi-user mode. Thanks and it is true that the distinction should be made between 'can' and 'used-to'.