Limit of repeat/undo units per session reached. (9456)

[mra]

Running Progress version 9.1D on Linux RedHat.

We're starting to get the following error: "Limit of repeat/undo units per session reached. (9456)"

The problem occurs in a persistent procedure running on an Appserver. This procedure is used to poll the database for changes. Within the procedure several calls to both internal and external procedures are executed.


According to Progress KB ID: P4071 and ID: P8015 this is a known bug in version 9.1, which is caused by a 32 bit integer overflowing.
The knowledgebase talks about rewriting "REPEAT blocks" to "DO WHILE blocks", but this is where my understanding begins to fail.

As far as I understand, every procedure call, and every "transaction" increments this counter. A "FOR EACH" block with NO-LOCK, and without the TRANSACTION keyword is not a repeat/undo block in itself.

But - even though we're using "for each no-lock" without transaction, if the loop updates temp-tables (for return values to the application), the loop will still be a repeat/undo block... or?

To sum things up:

1. What constructs will start a repeat/undo block?
2. What constructs should I avoid using?
3. How can I recode my procedures to minimize repeat/undo blocks?
   1. Will the use of NO-UNDO on DEFINE TEMP-TABLE.... have any effect?
   2. Is FOR EACH ... no-lock without then TRANSACTION keyword, considered a repeat/undo block if updates to temp-tables occur in the loop?
   3. Will a cursor and constructs like WHILE NOT Q:QUERY-OFF-END DO: be a solution to this problem?

Any input will be greatly appreciated, thanks.

Regards
Mike

 

[joey.jeremiah]

I remember seeing this discussion @PEG a while back.http://tinyurl.com/2h84ve

 

[mra]

Hi Joey!

Thanks for the reply m8.

I've read the PEG discussion, and also received a reply from Progress support, which basically said "apart from using DO WHILE, we don't have any information to give you...". The details about how 4GL works in this situation, is not given by Progress development.

In short I've found out, that:
Repeat/Undo units (or Accept/Reject units) is used by almost every Progress construct. Every unit increments an internal counter (32 bit for version<=9 and 64 bit for version>=10). Theres is little you can do to avoid the problem except the following:

1. Use cursors and DO WHILE NOT Q:QUERY-OFF-END instead for FOR EACH/REPEAT.
2. Reduce the use of function/procedure calls (aaargh! ).
3. Reconnect once in a while.

For me:
1. Is a major reprogramming task.
2. Is by principle out of the question.
3. This seems to me, like the only viable solution right now.

Thanks for your help
Regards
Mike

 

[joey.jeremiah]

or upgrade

 

[TomBascom]

I'm having a bit of trouble understanding why this is a major problem.

What are you doing that causes you to run into this limit?

If you have a specific case where it's happening then there is a fairly simple work-around but it's not like you have to change every single program that you write.

 

[mra]

Hi Tom!

I'll try to explain my situation. We're building Dispatching systems for Taxi-Company's. Trips are being booked into the system by operators, internet, batch systems and the like. The number of bookings per day can be several thousand. For every booking a number of cars a being tested for best match (by distance to the customer, attributes of the car etc.).

Special operators are assigned the task of monitoring the booking/dispatching process, and to serve them live data the system polls the database for new or updated information. The server doing this goes through a cycle of polling bookings, cars and messages, implemented by 3 4GL routines. The routines are not complicated at all, but does involve dynamic queries, and a fair amount of subroutines.

This system runs 24/7/365, which means, that we run into this problem every second month on some customers. When Progress discoverers that this error has occurred, it disconnects and our application doesn't have a clues as to what is going on.

Rewriting the 4GL is of cause possible, but since this is a high volume thing, which is hard for me to reproduce in-house. And since I can not be 100% sure that my changes will solve the problem (No matter how I rewrite Progress support can't guarantee that the problem will not surface again), I tend to choose the lesser evil of reconnecting the application every 24h. This will be a much smaller change, which will be easier to test, because it doesn't change any logic.

 

[TomBascom]

Ok, that makes sense.

There is also a limit of 2 billion transactions that you would hit sooner or later...

Restarting once a day seems like a sensible approach. If you design it in upfront you can probably make it painless. And it might be a useful hook to a fast software update process too.

Next question... If you are currently in the process of developing this why are you using v9?

 

[timk519]

I've run into this problem myself, and if you're not doing iterative transactions, the solution is to replace your "REPEAT" statements with "DO WHILE". That'll give you the looping construct w/out the downside of bumping that particular transaction counter.

Also, if PSC TS says they don't know what's going on, they're not doing their homework. I went down this path with PSC TS a couple years ago, so there has to be a KB entry covering it in the system.

 

[mra]

To Tom
We do not upgrade, because, we're moving our application away from Progress
The most vital parts of our application is build using C and sql89/C. Progress does not support embedded SQL in version 10 (instead they promote using ODBC). The performance of the SQL92 interface is not nearly good enough for practical use, plus we find it very inhibiting, not being able to change the database schema online.

To timK519
I've read the K-base entries, and talked to support. And they've given me the same answer you do "change repeat to for each". But the can't assure me, that this will work in all cases (ex. Update temp-tables in the loop, is that a Repeat/Undo block???) and they can not tell med what other constructs I should avoid.

Support is another reason why we're moving away from Progress, it's only thanks to ProgressTalk that we've stayed for so long :awink:

regards
Mike

 

[joey.jeremiah]

I can certainly empathize with your situation.


The level of support, professionalism and so on is highly dependent on the local distributor.

Some distributors may have other businesses and may be moving away from Progress.

They may not have been involved or kept up to date with whats been happening in the community and product list.

I've heard a recent but not uncommon case where a client wanted to know more about a product that the distributor hadn't even heard about.

Actually it was what's "Architect" ?


I think its definitely a problem and a very important one that PSC isn't paying attention to or mismanaging.

On the other hand they may not have alot of choices but so do you


Saying all that, version 9.1d is somewhat outdated and missing features oriented towards enterprise, 24x7 customers.

Like, online schema changes and from SQL.

Like wise alot has happened with the SQL support and the ODBC/JDBC drivers especially performance wise.


I would not say that C and SQL89 is a step forward, to say the least.

How about downloading test drive off PSDN.COM (for free) and trying out the latest version.

 

[joey.jeremiah]

BTW you should join PEG if you haven't already

 

[tamhas]

SQL support in current versions is night and day compared to what you are running. So are a lot of other things. If you dump Progress based on the version you have, it is like dumping some 10 year old garment because it is out of style. Well, yeah, its old and by no means state of the art.

And, if you think ProgressTalk is saving grace in support, then you really ought to check out peg.com. Also night and day.