May 9, 2006: On solving workspace conflicts

On May 7^th, I demonstrated how data can be versioned in workspaces and then be merged or refreshed. This worked well as long as I didn't change the same row in two different workspaces. However, when the same row is changed in two different workspaces, it creates what the Oracle documentation calls a conflict. Such conflicts cannot be resolved automatically because there is no way for Oracle to determine which version of the row is the version to keep. Manual conflict resolving is required in such cases. In this article, I am going to demonstrate that.

Here's the table that is going to be versioned:

create table wm_conflict_test (
  a  varchar2(10) not null primary key,
  b  varchar2(10) not null
);

Versioning is enabled on the table:

begin
  dbms_wm.enableversioning('wm_conflict_test');
end;
/

I load the table with names of cities that consist of two words. The first word is the primary key:

insert into wm_conflict_test values ('New'  , 'York'      );
insert into wm_conflict_test values ('Santa', 'Fe'        );
insert into wm_conflict_test values ('Los'  , 'Angeles'   );
insert into wm_conflict_test values ('San'  , 'Diego'     );
insert into wm_conflict_test values ('El'   , 'Paso'      );
insert into wm_conflict_test values ('Fort' , 'Worth'     );
insert into wm_conflict_test values ('Saint', 'Petersburg');

commit;

A workspace named TEST_WS_CONFLICT is created:

begin
  dbms_wm.createworkspace('TEST_WS_CONFLICT');
end;
/

I move to the TEST_WS_CONFLICT workspace...

begin
  dbms_wm.gotoworkspace('TEST_WS_CONFLICT');
end;
/

... and change (udpate) three rows in that workspace:

update wm_conflict_test set b = 'Barbara'   where a = 'Santa';
update wm_conflict_test set b = 'Orleans'   where a = 'New'  ;
update wm_conflict_test set b = 'Francisco' where a = 'San'  ;

commit;

The content of the table in the TEST_WS_CONFLICT is:

select * from wm_conflict_test order by a;

A          B
---------- ----------
El         Paso
Fort       Worth
Los        Angeles
New        Orleans
Saint      Petersburg
San        Francisco
Santa      Barbara

Then I go back to the LIVE workspace.

begin
  dbms_wm.gotoworkspace('LIVE');
end;
/

The data in the LIVE workspace does not reflect the updates made in the TEST_WS_CONFLICT workspace:

select * from wm_conflict_test order by a;

A          B
---------- ----------
El         Paso
Fort       Worth
Los        Angeles
New        York
Saint      Petersburg
San        Diego
Santa      Fe

I do three updates in the LIVE workspace as well. The important thing here is: I have already updated the rows identified by Santa and New in the TEST_WS_CONFLICT workspace.

update wm_conflict_test set b = 'Cruz'   where a = 'Santa';
update wm_conflict_test set b = 'Mexico' where a = 'New'  ;
update wm_conflict_test set b = 'Louis'  where a = 'Saint';

commit;

I now have the following situation:

The two rows that I changed in both the LIVE and the TEST_WS_CONFLICT workspace (New and Santa) create a conflict. I can use wm_conflict_test_conf view to see these conflicts. But first, I have to go to the TEST_WS_CONFLICT workspace. For a reason I don't really understand, the conflicts are not visible in this view while being in the LIVE workspace.

begin
  dbms_wm.gotoworkspace('TEST_WS_CONFLICT');
end;
/

column wm_workspace format a16

select * from wm_conflict_test_conf;

WM_WORKSPACE     A          B          WM_
---------------- ---------- ---------- ---
TEST_WS_CONFLICT New        Orleans    NO
BASE             New        York       NO
LIVE             New        Mexico     NO
TEST_WS_CONFLICT Santa      Barbara    NO
BASE             Santa      Fe         NO
LIVE             Santa      Cruz       NO

I need to resolve these conflicts. A conflict is basically resolved in three steps.

Step one: call dbms_wm.beginresolve.

begin
  dbms_wm.beginresolve('TEST_WS_CONFLICT');
end;
/

Step two: call dbms_wm.resolveconflicts. For the row identified by New, I want to keep the version from the LIVE workspace (being TEST_WS_CONFLICT's PARENT workspace) while for the row identified by Santa, I want to keep the version from the TEST_WS_CONFLICT workspace (CHILD):

begin
  -- Keep New Mexice from Parent (LIVE)
  dbms_wm.resolveconflicts(
    workspace    => 'TEST_WS_CONFLICT',
    table_name   => 'wm_conflict_test',
    where_clause => 'a = ''New'' ',
    keep         => 'PARENT');

  -- Keep Santa Barbara from TEST_WS_CONFLICT
  dbms_wm.resolveconflicts(
    workspace    => 'TEST_WS_CONFLICT',
    table_name   => 'wm_conflict_test',
    where_clause => 'a = ''Santa'' ',
    keep         => 'CHILD');

end;
/

commit;

Step three: call dbms_wm.commitresolve.

begin
  dbms_wm.commitresolve('TEST_WS_CONFLICT');
end;
/

Let's see if we still have a conflict:

select * from wm_conflict_test_conf;

No, we don't, the conflict was resolved:

no rows selected

Do we have the data that we expect:

select * from wm_conflict_test order by a;

Yes: New Mexico is taken from the LIVE workspace while Santa Barbara is taken (or kept) from the TEST_WS_CONFLICT workspace:

A          B
---------- ----------
El         Paso
Fort       Worth
Los        Angeles
New        Mexico
Saint      Petersburg
San        Francisco
Santa      Barbara

Now, let's see what data we find in the LIVE workspace:

begin
  dbms_wm.gotoworkspace('LIVE');
end;
/

select * from wm_conflict_test order by a;

A          B
---------- ----------
El         Paso
Fort       Worth
Los        Angeles
New        Mexico
Saint      Louis
San        Diego
Santa      Cruz

Although the conflict was resolved, the data in the LIVE workspace is still the same as before. The LIVE workspace must first be merged:

begin
  dbms_wm.mergeworkspace('TEST_WS_CONFLICT');
end;
/

What data is there now?

select * from wm_conflict_test order by a;

The data in the LIVE workspace is now the same as in the TEST_WS_CONFLICT workspace except for Saint Louis which was changed in the LIVE workspace and is therefore not affected by the merge:

A          B
---------- ----------
El         Paso
Fort       Worth
Los        Angeles
New        Mexico
Saint      Louis
San        Francisco
Santa      Barbara

Same update in both workspaces

I am going to update the same row in both workspaces with the same data. This shouldn't actually create a conflict, since both workspaces contain the same data. But let's see if Oracle is clever enough to detect this:

Update 1, in the LIVE workspace:

update wm_conflict_test set b = 'Lauderdale' where a = 'Fort';

commit;

Going to the TEST_WS_CONFLICT workspace:

begin
  dbms_wm.gotoworkspace('TEST_WS_CONFLICT');
end;
/

Update 1, in the TEST_WS_CONFLICT workspace:

update wm_conflict_test set b = 'Lauderdale' where a = 'Fort';

commit;

select * from wm_conflict_test_conf;

Although the same row was updated with the same data, there is still a conflict:

WM_WORKSPACE     A          B          WM_
---------------- ---------- ---------- ---
TEST_WS_CONFLICT Fort       Lauderdale NO
BASE             Fort       Worth      NO
LIVE             Fort       Lauderdale NO

This conflict can of course be resolved like before, but I won't do that now to save some space.

Cleaning up

begin
  dbms_wm.gotoworkspace('LIVE');
end;
/

begin
  dbms_wm.removeworkspace('TEST_WS_CONFLICT');
end;
/

begin
  dbms_wm.disableversioning('wm_conflict_test');
end;
/

drop table wm_conflict_test;

May 9, 2006: On solving workspace conflicts

Same update in both workspaces

Cleaning up

Links

More on Oracle


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger