Re: lock table

Austin Castro wrote: 7 Sep 99, 10:38: > Hi Guys, > Our company has decided to re-design our operations. We have seized > this > opportunity to turn on transaction on our production database since > most of our programs require modification. Naturally this has caused > some problems. My particular problem at this point concerns mass > updates and deletes. We have a finite number of locks available, so > the following update statement fails with error 458 long transaction > aborted > > update table_name > set field_name = "value" > where field2 = where_criteria > > I understand that it locks each record as it updates it so if the > table > has more records than the available locks, then we get this long > transaction aborted error. I really don't want to have to write > programs to do these mass updates and mass deletes. My next step was > to try the lock table statement through sql - here's what I tried > > begin work; > lock table table_name in exclusive mode; > delete from table_name > where 1=1;> commit work; > > The table has in 36000 records. The above sql statement also bombs > with > error 458. Undaunted, I tried to set the isolation level to dirty > read, but alas, that too resulted in error 458. I'm working with > informix 7.24 running on solaris 2.6. Here's one more piece of info, > an onstat -k reveals 70,000 total locks, 2 active, and 32768 hash > buckets. I'm not sure what the hash buckets indicate but the total > number of locks is more than the total number of records in the table > so I'm not SUPPOSED to be getting this error, right? > I'm kind a new to transaction logging so I might be interpreting > things > wrongly or missing something. Any help would be greatly appreciated. > Thanx. > > Austin Sorry but you are wrong. :-DD The long tansaction error was caused because you intent to do a big tansaction and the number of logical LOG (not lock) files are small for this. To solve that, increase the number of logical log files. Take care. :-DD --------------------------------------- Isidre PONS ROCA BASE - Gesti' d'Ingressos Locals (Diputacio de Tarragona) Servei de Sistemes de Informacio Av President Lluis Companys 12-C 43005 - Tarragona SPAIN Tel # +34 977 236731 Fax # +34 977 227302 http://www.altanet.org ipons@dtgna.altanet.org ---------------------------------------