Re: ISAM error: No more locks

> I am running into the error : > ISAM error: No more locks> when I try to delete all records from the following table > > ... > > There a re approx. 10,000 rows in this table. > I am able to delete more rows from other tables with no problem. > This is the only database that has unbuffered logging on it. > in my onconfig, LOCKS is set to 10000. > I have run oncheck as many ways a possible to try to find the problem. > I have dbexport/dbimport-ed the database and that did not fix it. > > Is there something I am missing? Probably, though I can't tell for sure with the information you provided. The dbschema output is appreciated, but it appears that you did not include the "-ss" switch on the command line. Run it again with this switch and you'll see a few more pieces of information - EXTENT SIZE, NEXT SIZE, LOCK MODE, IN dbspace, etc. Absent that information, I'm going to assume that the table was created with LOCK MODE ROW (even though that is not the default). I'm also going to assume that you do not have a lot of users holding a lot of locks of their own at the same time as you are running the DELETE. The reason that you are running out of locks, then, is that each row requires a lock for the data row and another lock for each index entry. It appears that you only have one index, the one supporting your foreign key, so each row would require two locks for the duration of the transaction. 10000 rows * 2 locks/row = 20000 locks. If the table had page level locking, your delete may have worked. I suspect that your other tables that you've deleted from either had no indexes or had page level locking. There are several ways to approach this. You could increase the number of locks to 25000 or 30000 (give yourself a few extra), but that would require bouncing the engine. You could drop the index, which eliminates the need to lock the index entries. For that matter, you could drop/create the table. You could alter the table to LOCK MODE PAGE, which allows you to lock up to 19 or 20 rows with a single lock, given your row size. You could try dbexport/dbimport again, but edit the sql file to remove the "*** load table ***" line following the CREATE TABLE, but I've not tried that so I don't know that it would work. The easiest way, though, is to lock the whole table in exclusive mode. This requires explicit transaction control, like: BEGIN WORK; LOCK TABLE tblinventorytrans IN EXCLUSIVE MODE; DELETE FROM tblinventorytrans WHERE 1=1; COMMIT WORK; This uses one lock to lock the whole table. Of course, no one else can get to the table while the lock is in place, but I doubt that's a problem in this case. The "WHERE 1=1" clause on the DELETE statement is to prevent isql/dbaccess from warning you that all rows will be deleted. You can omit that clause, but if you're running dbaccess in interactive mode, it will require you to answer a yes/no question before it executes the DELETE. Mark Collins mcollins@us.dhl.com The problem lies in how easily and dangerously we forget that manipulating things is not the same as understanding them.