Informix error -346

Hal van den Assem

unread,

Feb 12, 1997, 3:00:00 AM2/12/97

to HalVanD...@unn.unisys.com

An ESQL/C program, which processes a huge amount of data in one
single transaction, causes the SQL-error Code -346.

I use Informix-SE INFORMIX-SE Version 7.10.UD1, on a Unisys
SMP5400 Unix machine.

Could someone confirm or explain that the error is caused by
a 'too large' transaction!?
How can I determine the size of a transaction, and how to put more
updates and inserts into 1 single transaction!? (enlarge a transaction)
If possible, I do not want break-down the workload into smaller
transactions.

Please advice!

Many thanks,

Hal van den Assem

Nils Myklebust

unread,

Feb 14, 1997, 3:00:00 AM2/14/97

to

Hal van den Assem <HalVanD...@unn.unisys.com> wrote:

:An ESQL/C program, which processes a huge amount of data in one

On my installation finderr 346 says:

-346 Could not update a row in the table.

While processing an UPDATE, the database server received
an unexpected error. Check the accompanying ISAM error
code for more detailed information on the cause. Possible
causes include hardware errors and locking conflicts.

So how about giving us that isam error.
You may have any number of errors.
It's not to usefull to speculate too much without the appropriate data
available.

Nils.My...@idg.no
NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org

Frido van Orden

unread,

Feb 14, 1997, 3:00:00 AM2/14/97

to

Hal van den Assem wrote:
>
> An ESQL/C program, which processes a huge amount of data in one
> single transaction, causes the SQL-error Code -346.
>
> I use Informix-SE INFORMIX-SE Version 7.10.UD1, on a Unisys
> SMP5400 Unix machine.
>
> Could someone confirm or explain that the error is caused by
> a 'too large' transaction!?
> How can I determine the size of a transaction, and how to put more
> updates and inserts into 1 single transaction!? (enlarge a transaction)
> If possible, I do not want break-down the workload into smaller
> transactions.

Informix locks far more records than you would think while updating a
record. Most important cause for this is that referenced tuples (by
foreign key constraints) are locked as well. If you insert/update/delete
records with lots of foreign keys, transactions grow large very fast.
For some large transactions in an environment with little concurrent
users you might need as much as 200000 locks.

Frido

Rob Weight

unread,

Feb 14, 1997, 3:00:00 AM2/14/97

to

The CISAM error is more imortant for finding the cause of this error
than just the SQL error. My experience, however, has been that this
kind of error can occur when your system runs out of locks or your
logfile may be using up all the space in the filesystem it resides in.
If you have to do one large transaction you could try locking the
table(s) affected, which will side-step the record locking problems.

In answer to how to see how large the transaction is, I zero out the
logfile and start the process and see how large the logfile gets
before it either stops or completes. Not real elegant, but it works.

Hope that helps,

-robw

On Wed, 12 Feb 1997 19:12:39 -0800, Hal van den Assem
<HalVanD...@unn.unisys.com> wrote:

>An ESQL/C program, which processes a huge amount of data in one
>single transaction, causes the SQL-error Code -346.
>
>I use Informix-SE INFORMIX-SE Version 7.10.UD1, on a Unisys
>SMP5400 Unix machine.
>
>Could someone confirm or explain that the error is caused by
>a 'too large' transaction!?
>How can I determine the size of a transaction, and how to put more
>updates and inserts into 1 single transaction!? (enlarge a transaction)
>If possible, I do not want break-down the workload into smaller
>transactions.
>

Daniel Wright

unread,

Feb 14, 1997, 3:00:00 AM2/14/97

to

Just exactly what everyone else said. I'm going from memory, but I bet
ISAM is -113.
Ways you can reduce the number of locks required, at the expense of
concurrency:
Lock the table or database in exclusive mode...this will also buy you
performance.

I'm not sure it would affect the number of locks, but cursors for update
and insert cursors are helpful in such large transactions (at least for
performance).

Robert Donat

unread,

Feb 26, 1997, 3:00:00 AM2/26/97

to Hal van den Assem

Hal van den Assem wrote:
>
> An ESQL/C program, which processes a huge amount of data in one
> single transaction, causes the SQL-error Code -346.
>

> Could someone confirm or explain that the error is caused by
> a 'too large' transaction!?
> How can I determine the size of a transaction, and how to put more
> updates and inserts into 1 single transaction!? (enlarge a transaction)
> If possible, I do not want break-down the workload into smaller
> transactions.

> Hal van den Assem
I'm not an expert on SE, but in Online, you would have two possible
problems:

Locks and long transactions.

If you are attempting to lock too many rows/pages at a time, you will
run out of locks, which can be determined by onstat -k. Also, if you
have a transaction that takes up more than 50-70% of your logspace, it
will be rolled back because it can not be recovered from if the database
were to crash. Your long transaction high water marks can be varied,
but if it fails and is unable to rollback, the worst thing that could
happen is a complete restore (from tape) ---IN ONLINE. For SE, though,
if memory serves, you don't have transaction logging. I hope this will
help. My guess is you're out of locks. Lock the table in exclusive
mode if you can in SE.