RE: lock table
You could also change the sql to process less records at one time - ie.
break it up into subsets using some column from the table, such as a date
field, in the where clause.
Dianne
-----Original Message-----
From: Rob Finegan [mailto:rfinegan@fenix2.dol-esa.gov]
Sent: September 08,1999 4:00 AM
To: 'Luse Harold'; Austin Castro
Cc: informix-list@iiug.org
Subject: RE: lock table
Depending on the type of load you are performing, another easy solution is
to simply change the logging status of the database to 'no logging' during
mass updates.
-----Original Message-----
From: Luse Harold [SMTP:hluse@vth.colostate.edu]
Sent: Tuesday, September 07, 1999 6:08 PM
To: Austin Castro
Cc: informix-list@iiug.org
Subject: 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
====================================================================