old system, being upgrade, but observe same in all oracle versions (11.2.4, 12.2 and 19.9) being moved through to get to latest.
Investigating unique constraint error when two sessions update a versioned table in the LIVE workspace, when a child workspace has been started in a third session. Also inconsistent outcome when making update to non versioned table compared to a versioned table.
(tables also have OLS applied)
have version_table
ID number PK, updateN number, someTxt vharchar2(100)
1, 0, 'John'
The actual version_table_LT has the ID, wm_version PK.
ID number PK, updateN number, someTxt vharchar2(100), ..., wm_version
1, 0, 'John', 0
The code for its sins does following in a update procedure
update version_table set updateN = updateN + 1 where id = 1;
If third session starts a workspace.
dbms_wm.createworkspace('CHILDofLIVE')
the id of the CHILDofLIVE workspace is 12.
If first session that is in the LIVE workspace calls the update procedure, but does not commit.
Looking at the contents of the version_table_LT table shows
ID number PK, updateN number, someTxt vharchar2(100), ..., wm_version
1, 0, 'John', 0
1, 1, 'John', 11 <-- 1 less than the CHILDofLIVE workspace.
Then second session also in the LIVE workspace calls the update procedure. It will hang waiting on the first session.
If the first session commits, the second session fails with a unique constraint...
The issue is that both sessions effectively try to insert the same PK (1,11) versioned row to handle the fact that the CHILDofLIVE needs to see the un-updated row, as well there being an updated version.
If there is no workspace in play, then there is no unique constraint error.
But the second sessions, update to updateN is lost, so after session 2's update the versioned_table shows
ID number PK, updateN number, someTxt vharchar2(100)
1, 1, 'John'
If we do similar update on a nonversioned table then the second update is not lost
we end up with
ID number PK, updateN number, someTxt vharchar2(100)
1, 2, 'John'
This seems very inconsistent, application is just performing updates, but gets different results.
Anybody else seen similar
John