Simultaneous Update

[ryoung011]

We are building a data warehouse.

We have Slowly Changing Dimension jobs that write to three (two if Type 1 SCD) sequential files:

Type II SCD:
If record has Type 1 Change
- write to 'Type 1 Update' file (to update all records by Natural Key)
If record has Type 2 Change
- write to 'Type 2 Update' file (to expire prior version by Surrogate Key)
- write to 'Insert' file (to insert new version with new Surrogate Key)
If record has Type 1 AND Type 2 Change
- write to 'Type 1 Update' file (to update all records by Natural Key)
- write to 'Type 2 Update' file (to expire prior version by Surrogate Key)
- write to 'Insert' file (to insert new version with new Surrogate Key)

From here we start a separate UPSERT job which collects the output links from all three files into one Oracle OCI stage. Each of the input links are set with Transaction Isolation of "Serializable' and Transaction Size of 10,000 rows.

looks like this:

File:TYPE1UPDATE--------------
\
File:TYPE2UPDATE------------------- OCI
/
File:INSERTNEW----------------

The SQL:
TYPE1UPDATE
UPDATE <dimension>
SET <all type 1 attributes>
WHERE <natural key> = :<natural key column>

TYPE2UPDATE
UPDATE <dimension>
SET expiration_dt = <some date>
WHERE <surrogate key> = :<surrogate key column>

So, it is possible that the same row could be updated by the Type 1 link and Type 2 link.

The issue:
We are randomly getting the following error
ORA-08177 can't serialze access for this transaction

Are we doing this correctly? Any advice?

Thanks in advance,

Rick

[roy]

Hi,
your probably getting to many concurrent inserts/updates or have reached a maximum connections limit.
must you have the isolation level set to serializable? (will commited read not do?)

is DS working alone on the DB at the time or are there any other users/applications?

you probably will solve this by combining your input links to one link.

The sad thing is that in this case if you run the insert that failed again it will most likely be inserted with no problems, but it will fail the DS insert.

I never got to 100% isolation and remedy for this problem, it happens now and then and difficult to reproduce.
AFAIK,it is less likely to happen when DS runs solo on the DB though.

IHTH,