Re: deleting 300,000 records with 50000 locks

Sukumar When you do an insert, it also locks the index. So if you have 3 indexes on the table (call it tbl1) called idx1, idx2 and idx3 and if from sysindexes you find that: sysindexes.levels = 2 for idx1 sysindexes.levels = 3 for idx2 sysindexes.levels = 4 for idx3 then assuming row locking, in addition to the row locked for the tbl1, there will be 2+3+4 reads done for the index which will cause locks to be allocated. So for each insert you effectively need 10 lock requests, and 4 simultaneous locks while the record gets updated. This may explain the large number of locks. A solution may be dropping or disabling the indexes before you load and creating or enabling the indexes after. HTH Sujit Pal ______________________________ Reply Separator _________________________________ Subject: deleting 300,000 records with 50000 locks Author: Sukumar Konduru <sukumar@gte.net> at Internet Date: 4/29/98 8:57 AM Hi I need to delete 300,000 transactions for every 3 months. We have more than 1 million transactions in this table. What I do is as follows. 1. Prepare dbschema for the table 2. unload to a file required transactions. i.e recent 3 months transactions. 3. Drop the table 4. create table with dbschema 5. load from file. i.e load from "file.dat" insert into tablename We have only 50,000 locks. Only way I am able to load records in to database is by splitting into lesss than 10,000 records and inserting records from these small files one by one . Why I am not able to even insert 40,000 records at a time , inspite of having 50,000 locks. When I try to insert even 10,000 records, onstat shows that 40000 locks are in use. Is every inserting transactions needs 4 locks?. It is surprising me. I can not use "BEGIN WORK", because we do not have more log files. All these problems are due to lack of RAM Could you please mail me sukumar@gte.net Thank you sukumar konduru