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):

--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:

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.

--update record in both workspacesexec 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;

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.