INGRES SERIALIZATION FAILURE DEADLOCK Error. Need Help

[karthickrn]

Hi All,

Am trying to insert record using the below query, the query works fine for small bunch of insertion but fails for inserting records more than 250000.

Am not sure is this the INGRES limitation or DBAs would have predefined any rule.

The error am getting for insertion above 250000 records is

: Return :-49900 SQLERROR
: Warning:
: 0[W] 1[ ] 2[ ] 3[ ] 4[ ] 5[ ] 6[W] 7[ ]
: Message:
: E_US1264 The query has been aborted. (Fri Mar 5 10:14:48 2010)
: Rows :0
: GEC Diagnostic:
:
: SERIALIZATION FAILURE
: DEADLOCK
: Examine warning 6

INSERT INTO tab1
(se,
dt,
ae
)
SELECT DISTINCT
a.se,
a.dt,
a.ae,
FROM tab2 a, tab3 c, tab4 g
where ........;

Can anyone help me to overcome this issue.

Much thanks in advance

RNK

[nick.bolton]

The E_US1264 indicates that the transaction logfile filled up.

The size of the transaction logfile can be ameneded in cbf (it requires a restart); and effectively limits the amount of uncommitted change that can be hanging around at any point in time.

Alternatively, break the SQL up into smaller result sets and commit between each set.

[karthickrn]

thanks for your reply ....I'll break into smalller sets and check ... but in the case of handling insertions with session tables .. Kindly let me know whether commit can still be used.

[kschendel]

If by session tables you mean tables created with "declare global temporary table", yes you can use commit. However be aware that session temp tables do not log any changes and do not contribute to the transaction log file filling up.