Re: lock table

On Tue, 7 Sep 1999, Austin Castro wrote: > 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 > The long transaction aborted is not a result of locks, but of logical logs. In a transaction logging database, the logs will fill up as the engine processes the data. When the number of logs, since the start of the transaction, exceeds the value of LTXHWM set in the onconfig file, the engine will assume that it has reached a point where it has insufficient logs remaining to continue, encounter and error, and process a rollback (should one occur). Therefore, it initiates a rollback just to be safe. To allow for larger trnsactions, increase the size, or number, of your logical logs. Check out the onparams command in the Online Dynamic Server Administrator's Guide. ==================================================================== Harold Luse Phone: (970) 491-4120 Veterinary Teaching Hospital Fax: (970) 491-4123 Colorado State University Pager: (970) 229-8173 Fort Collins, Colorado USA E-mail: hluse@vth.colostate.edu ====================================================================