This discussion is archived
4 Replies Latest reply: Aug 2, 2013 1:55 AM by user12018196 RSS

Row locking issue with version enabled tables

user12018196 Newbie
Currently Being Moderated

I've been testing the effect of locking in version enabled tables in order to assess workspace manager restrictions when updating records in different workspaces and I have encountered a locking problem where I can't seem to update different records of the same table in different sessions if these same records have been previously updated & committed in another workspace.

 

I'm running the tests on 11.2.0.3.  I have ROW_LEVEL_LOCKING set to ON.

 

Here's a simple test case (I have many other test cases which fail as well but understanding why this one causes a locking problem will help me understand the results from my other test cases):

 

--Change tablespace names as required

create table t1 (id varchar2(36) not null, name varchar2(50) not null) tablespace XXX;

alter table t1 add constraint t1_pk primary key (id) using index tablespace XXX;

 

exec dbms_wm.gotoworkspace('LIVE');

insert into t1 values ('1', 'name1');

insert into t1 values ('2', 'name2');

insert into t1 values ('3', 'name3');

commit;

 

exec dbms_wm.enableversioning('t1');

exec dbms_wm.gotoworkspace('LIVE');

exec dbms_wm.createworkspace('TESTWSM1');

exec dbms_wm.gotoworkspace('TESTWSM1');

 

--update 2 records in a non-LIVE workspace in preparation for updating in different workspaces later

update t1 set name = name||'changed' where id in ('1', '2');

commit;

quit;

 

--Now in a separate session (called session 1 for this example) run the following without committing the changes:

exec dbms_wm.gotoworkspace('LIVE');

update t1 set name = 'changed' where id = '1';

 

--Now in another session (session 2) update a different record from the same table.  The below update will hang waiting on the transaction in session 1 to complete (via commit/rollback):

exec dbms_wm.gotoworkspace('LIVE');

update t1 set name = 'changed' where id = '2';

 

I'm surprised records of different ids can't be updated in different sessions i.e. why does session 1 lock the update of record 2 which is not being updated anywhere else.  I've tried this using different non-LIVE workspaces with similar results.  I've tried changing table properties e.g. initrans with and still get a lock.  The changes to table properties are successfully propagated to the _LT tables but not all the related workspace manager tables created for table T1 above.  I'm not sure if this is the issue.

 

Note an example of the background workspace manager query that may create the lock is something like:

 

UPDATE TESTWSM.T1_LT SET LTLOCK = WMSYS.LT_CTX_PKG.CHECKNGETLOCK(:B6 , LTLOCK, NEXTVER, :B3 , 0,'UPDATE', VERSION, DELSTATUS, :B5 ), NEXTVER = WMSYS.LT_CTX_PKG.GETNEXTVER(NEXTVER,:B4 ,VERSION,:B3 ,:B2 ,683) WHERE ROWID = :B1

 

Any help with this will be appreciated.  Thanks in advance.

  • 1. Re: Row locking issue with version enabled tables
    Ben Speckhard Pro
    Currently Being Moderated

    Hi,

     

    I don't believe it is blocking on the update on _LT.  Rather, it is blocking on an insert into a metadata table that we maintain internally.  The first time a row is updated in multiple workspaces we add a row indicating that a split occurred between those 2 workspaces/versions. Both of these updates are simultaneously initiating the first split between LIVE and TESTWSM1.  This dml only occurs on the first one.  If you had updated row id='3' in LIVE and TESTWSM1 prior to your example above, both of the updates would have succeeded without the other committing or rolling back.

     

    Regards,

    Ben

  • 2. Re: Row locking issue with version enabled tables
    user12018196 Newbie
    Currently Being Moderated

    Hi Ben,

     

    Thanks for your quick response.

     

    I've tested your suggestion and it does work with 2 workspaces but the same problem is enountered when additional workspaces are created. 

    It seems if multiple workspaces are used in a multi user environment, locks will be inevitable which will degrade performance especially if a long transaction is used. 

    Deadlocks can also be encountered where eventually one of the sessions is rolled back by the database. 

    Is there a way of avoiding this e.g. by controlling the creation of workspaces and table updates?

    I've updated my test case below to demonstrate the extra workspace locking issue.


    --change tablespace name as required
    create table t1 (id varchar2(36) not null, name varchar2(50) not null) tablespace XXX;
    alter table t1 add constraint t1_pk primary key (id) using index tablespace XXX;


    exec dbms_wm.gotoworkspace('LIVE');

    insert into t1 values ('1', 'name1');
    insert into t1 values ('2', 'name2');
    insert into t1 values ('3', 'name3');
    commit;

    exec dbms_wm.enableversioning('t1');

    exec dbms_wm.gotoworkspace('LIVE');

    exec dbms_wm.createworkspace('TESTWSM1');

    exec dbms_wm.gotoworkspace('TESTWSM1');

    update t1 set name = name||'changed' where id in ('1', '2');
    commit;


    Session 1:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '1';


    session 2:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '2';

     

    --end of original test case, start of additional workspace locking issue:

    Session 1:
    rollback;

    Session 2:
    rollback;


    --update record in both workspaces
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '3';
    commit;
    exec dbms_wm.gotoworkspace('TESTWSM1');
    update t1 set name = 'changed' where id = '3';
    commit;

     

    Session 1:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '1';


    session 2:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '2';

    Session 1:
    rollback;

    Session 2:
    rollback;

     

    exec dbms_wm.gotoworkspace('LIVE');
    exec dbms_wm.createworkspace('TESTWSM2');
    exec dbms_wm.gotoworkspace('TESTWSM2');
    update t1 set name = name||'changed2' where id in ('1', '2');
    commit;


    Session 1:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '1';

    --this now gets locked out by session 1
    session 2:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '2';

    Session 1:
    rollback;

    Session 2:
    rollback;


    --update record 3 in TESTWSM2
    exec dbms_wm.gotoworkspace('TESTWSM2');
    update t1 set name = 'changed' where id = '3';
    commit;


    Session 1:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '1';

    --this is still locked out by session 1
    session 2:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '2';

    Session 1:
    rollback;

    Session 2:
    rollback;


    --try updating LIVE
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '3';
    commit;


    Session 1:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '1';

    --this is still locked out by session 1
    session 2:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '2';

    Session 1:
    rollback;

    Session 2:
    rollback;


    --try updating TESTWSM1 workspace too - so all have been updated since TESTWSM2 was created
    exec dbms_wm.gotoworkspace('TESTWSM1');
    update t1 set name = 'changed' where id = '3';
    commit;


    Session 1:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '1';

    --this is still locked out by session 1
    session 2:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '2';

    Session 1:
    rollback;

    Session 2:
    rollback;

    --try updating every workspace afresh
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changedA' where id = '3';
    commit;
    exec dbms_wm.gotoworkspace('TESTWSM1');
    update t1 set name = 'changedB' where id = '3';
    commit;
    exec dbms_wm.gotoworkspace('TESTWSM2');
    update t1 set name = 'changedC' where id = '3';
    commit;


    Session 1:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '1';

    --this is still locked out by session 1
    session 2:
    exec dbms_wm.gotoworkspace('LIVE');
    update t1 set name = 'changed' where id = '2';

    Session 1:
    rollback;

    Session 2:
    rollback;

  • 3. Re: Row locking issue with version enabled tables
    Ben Speckhard Pro
    Currently Being Moderated

    Hi,

     

    The reason for the block is actually the same as above.  The splits are recorded at the version/savepoint level, not necessarily just the workspace.  In your example, workspaces TESTWSM1 and TESTWSM2 have a single version and the LIVE workspace has 3 versions.  A new version is always created implicitly during a CreateWorkspace operation if the current/LATEST version has modified data.  The rows for id='1' and id='2' are modified in each of the child workspace and prior to the creation of either workspace.  However, since the id='3' row in LIVE was modified after the creation of TESTWSM1, it is in a different version than those other 2 rows.  So, when the updates for id='1' and id='2' are run, the row is attempted to be split between TESTWSM1, TESTWSM2, and the current version of LIVE.  No other row has been updated with this combination so we attempt to insert a set of rows into the metadata table.  Since this is happening simultaneously from multiple sessions, one of them is forced to block until the other transaction issues a commit or rollback.

     

    There isn't a way to avoid these dmls.  The lock being acquired in this case is a short transaction lock and they are required for the correct rows to be returned for a particular workspace.

     

    Regards,

    Ben

  • 4. Re: Row locking issue with version enabled tables
    user12018196 Newbie
    Currently Being Moderated

    Hi Ben,

     

    Thanks for the response & clarification.

     

    Regards
    John

     


Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points