lock table overflow error

W

whwar9739

Member
Hey all,

I am getting a lock table overflow error and would like to solve it without increasing the -L option since 90% of the time I do not have administrative rights to do so. I have tried, in as many places as possible, to release a record once I have no use for it via the RELEASE statement. Is there any other way to 'clear' out the lock table within a program? I used the release statement any time I used the CREATE or FIND or FOR EACH statements. Any help would be appreciated, thanks.
 
The only way to have a smaller lock table is to have fewer locks. There are some good business reasons in special circumstances why higher than usual numbers of locks are needed and -L the only solution, but most of the time the fault is the programming.

One thing that might be catching you, since it isn't obvious, is that if you have a loop with an exclusive lock and it has to search a lot of records before it finds one to process, i.e., some version of table scan, all the unused records encountered in the scan are locked until it finds the right one that meets the where clause specification. Once it finds one, only that record is locked, but by that time the lock table may be blown. The right solution here is to do the loop no-lock and then refind the record inside the loop in a buffer with exclusive lock.

Other than that, no global fixes and no ways to clear a lock table other than by not locking the record.
 
Is there a way then to be able to run the procedure and check to see what is in the lock table? Maybe I am not releasing something that I should be??
 
What is the current value of -L of your database?

Make sure your transaction isn't bigger then you expect it to be....
Release statement does not move the records out of the lock table. That only happens after the transaction is finished.

protop (http://www.greenfieldtech.com/) or promon can give you insight in who is locking what...

Casper.
 
After doing some research I found out that my program had a transaction at the outermost level causing the do-loops in my procedure to rack up the lock table in a hurry.

I ended up putting a DO TRANSACTION:/END. around each little segment of my code to break it up a bit and that took care of the issue.

Thanks to all who replied.
 
This is a basic technique, which should be not only a basis for new programming, but one of the first things you do when you encounter a problem like this or even are just working to clean up an old program. Do Transaction is one tool, but most of the time you should be able to solve the problem with strong scoping, i.e, Do With BufferName and putting access in internal procedures with a buffer scoped to the procedure.

And, of course, when it doesn't go like it seems it should Compile List is a very clear starting point.
 
You must log in or register to reply here.
Back
Top