"Could not serialize - transaction aborted" error

[luigi_salvini@libero.it]

Hi,

I am currently working with Datastage 7.5.2 and Netezza DBMS and we are experiencing a problem similar to others submitted in this forum, though I was not able to find a solution: while performing (Server Job) an UPDATE on a Netezza table with ODBC stage we sometimes get the annoying error Could not serialize - transaction aborted .

Is there anyone who can help with this?

Best regards

Luigi Salvini

[ray.wurlod]

Welcome aboard.

How is the property "Transaction Isolation Level" set?

[ray.wurlod]

Is it possible to have one of the jobs update a temporary table and move these updates to the main table at a convenient time?

[luigi_salvini@libero.it]

Is it possible to have one of the jobs update a temporary table and move these updates to the main table at a convenient time? ...

Well this is not possible as we have several tens of jobs already developed and your solution would mean a major rework of the whole project... and this effort just to take into account a concurrent update... Our expectation (I mean...implicit expectation...) was that a simple concurrent update would not give problems for a professional DBMS like Netezza and with Datastage

Even if we consider the System Administrative Manual of Netezza

Concurrent Transaction Serialization and Queueing, Implicit Transactions
An implicit transaction is a single statement that is not framed by a BEGIN statement.

When an Implicit Transaction Fails Serialization
The system responds as follows for an implicit transaction failing serialization:
The system waits for the completion of the transaction that caused the serialization
conflict.
Once that transaction finishes, either by commit or abort, the system resubmits the
waiting requests.

Well, in the two jobs where I reproduced the error, we are *not* in this case because when I run the second job, the "transaction failed" error is immediate (there is no wait of the first job to finish).

Maybe Datastage wraps update statements with BEGIN / END, so Netezza considers them as Explicit transactions..

Concurrent Transaction Serialization and Queueing, Explicit Transactions
An explicit transaction is one that is framed within a BEGIN statement.
When an Explicit Transaction Fails Serialization
By default, a query issued by an explicit transaction fails immediately if the system detects a serialization failure.
When the Number of Explicit Transactions Exceeds the Limit
Explicit transactions are not queued but are forced to be read-only. If the transaction attempts to modify any non-temporary data, the command fails and the transaction aborts. You can modify the system behavior so that explicit transactions are queued until the number of transactions falls below 31:
Issue the command
SET begin_queue_if_full = true
In this case, the transaction requests framed by a BEGIN statement will queue until the concurrent transactions falls below 31, at which time data modification operations are permitted. Note that SELECT statements also queue, but that you can allow them to complete by issuing a SET SESSION READ ONLY command before issuing the BEGIN statement.

Any help with this is appreciated... Thank you...

Luigi