1.3 Lock Management with Workspace Manager

In addition to locks provided by regular Oracle AI Database transactions, Workspace Manager provides two types of version locks. These locks are primarily intended to eliminate row conflicts between a parent workspace and a child workspace. You can enable locking for the workspace, the session, or specified rows, or some combination:

• Lock at the workspace level (SetWorkspaceLockModeON procedure) if the data changes are in one or a few workspaces, or if you want all data changes in the workspace to be locked.

• Lock at the session level (SetLockingON procedure) if the data changes are being made in many workspaces. When locking is enabled for a session, Workspace Manager locks rows in all workspaces in which the session participates.

• Lock specific rows (LockRows procedure) either to lock the rows before they are updated or to automatically lock rows after they are inserted (or updated if they satisfy the WHERE clause after the update).

Workspace or session locks persist for the duration of the workspace or session, respectively, or until the workspace is merged or rolled back.

Like database locks, Workspace Manager locks can be exclusive or shared:

• Exclusive locks - The locks are very similar to database transaction locks in that once an exclusive lock is placed on a record, no other user in the database can change the record except for the session (user) that locked it. When exclusive locking is enabled for a user, any row that the user changes is locked exclusively. In addition, the parent row to that row is also locked exclusively. Thus, exclusive locking can be used to eliminate data conflicts between a child and its parent workspace. (However, see Exclusive Locking and Row Versions for information about row versions with exclusive locking, and how the timing of an exclusive lock with respect to an update operation can affect whether other users can update a row.)

• Shared locks - Once a shared lock is placed on a row, only users in the workspace in which it is locked are allowed to modify it. Shared locks are also placed on the parent version of the row, thus protecting the row from conflicts. The benefit of shared locks over exclusive locks is that all users in the workspace where the row is locked can access the row for changes. An ideal use for this kind of lock is on a row that needs to have no conflicts with its parent, but that needs to be changed by a collection of users participating in a group project. Note that shared locking must be individually enabled for each session in the workspace.

Workspace-exclusive locks and version-exclusive locks are forms of exclusive locking that control which users can and cannot change data values, but (unlike exclusive locking) they do not prevent conflicts from occurring. Workspace-exclusive locks lock rows such that only the user that set the lock can change the values in the current workspace; however, other users in other workspaces can change the values. Version-exclusive locks lock rows such that only the user that set the lock can change the values (and that user can be in any workspace); no other users (in any workspace) can change the values.

The following table indicates, for a row locked by a specific user in a specific workspace, which users in which workspaces can and cannot modify the row. For example, the first two entries in this table mean that when a shared (S) lock is placed on a row, any user in the workspace in which the row was locked can modify the row, but any user in a workspace different from the workspace in which the row was locked cannot modify the row.

Table 1-3 Workspace Manager Lock Modes and Data Modification Ability

Lock Mode	User	Workspace of User	Can Modify?
Shared (S)	Any user	Workspace in which row was locked	Yes
Shared (S)	Any user	Different from workspace in which row was locked	No
Exclusive (E)	User that locked the row	Workspace in which row was locked	Yes
Exclusive (E)	User that locked the row	Different from workspace in which row was locked	No
Exclusive (E)	Different user from the one that locked the row	Any workspace	No
Workspace Exclusive (WE)	User that locked the row	Any workspace	Yes
Workspace Exclusive (WE)	Different user from the one that locked the row	Different from workspace in which row was locked	Yes
Workspace Exclusive (WE)	Different user from the one that locked the row	Workspace in which row was locked	No
Version Exclusive (VE)	User that locked the row	Any workspace	Yes
Version Exclusive (VE)	Different user from the one that locked the row	Any workspace	No

Locking a row does not affect workspace merge, refresh, and rollback operations, but it affects what can be done with the row after these operations. You can control these workspace operations by using workspace privileges, calling the FreezeWorkspace procedure, and checking the workspace xxx_LOCK view or views (described in xxx_LOCK Views) before performing the operations.

The xxx_LOCK static data dictionary views (described in xxx_LOCK Views) contain information about locks in each version-enabled table.

For information about Workspace Manager locking with DML operations on tables with referential integrity constraints, see Locking with DML Operations on Tables with Referential Integrity Constraints.

• Exclusive Locking and Row Versions
  
• Locks Taken for Workspace Manager Operations
  

1.3.1 Exclusive Locking and Row Versions

The timing of an exclusive lock with respect to an update operation in a child workspace can affect which version, if any, of the row can be updated in a parent workspace. For example, when a table is version-enabled in the LIVE workspace, each original row is assigned version 0. Assume that a workspace named W1 is created as a child of the LIVE workspace. When workspace W1 is created, the following things happen:

• Version 1 is assigned to the LIVE workspace (but no additional row is created).

• Version 2 is assigned to workspace W1 (but no additional row is created). Queries in workspace W1 still return version 0 of the row that is in the LIVE workspace.

Using this example, if a user in workspace W1 places an exclusive lock on a row before it updates the row, only that user in workspace W1 can update the row. Specifically:

• Version 0 of the row is locked, preventing any update of the row from any workspace until the row is unlocked.

• The lock can be placed from workspace W1 (or a descendent workspace of W1) because version 0 is the current physical row for the workspace.

• When a user in workspace W1 updates the row, a new row (version 2) is created that is visible only from workspace W1 and any of its child workspaces.

However, if the row is not locked in the LIVE workspace and if a user in workspace W1 updates the row and then places an exclusive lock on the row, a user in the LIVE workspace can update the row. Specifically:

• A new row (version 2) is created that is visible only from workspace W1 and any of its child workspaces.

• Version 2 of the row is locked. No user in workspace W1 other than the user that placed the lock, or no user in any child workspace of W1, can update the row or create a new version of the row.

• Version 0 of the row in the LIVE workspace is not locked. If a user in the LIVE workspace or a sibling workspace of W1 updates the row, a new version (version 1) of the row is created. (Version 0 is not locked because it is no longer the current version of the row for users in workspace W1; rather, version 2 is the current version of the row in that workspace.)

In other words, an exclusive lock after an update does not lock previous versions of the row in workspaces above the locking workspace in the workspace tree or in other branches of the workspace tree.

1.3.2 Locks Taken for Workspace Manager Operations

Workspace Manager automatically takes locks when performing certain operations related to DBMS_WM subprograms. Workspace Manager checks:

• If the required lock can be successfully requested

• If the workspace is frozen in a mode that is incompatible with the requested operation

If the required lock cannot be requested or if the workspace is frozen in an incompatible mode, an error is generated.

Table 1-4 lists Workspace Manager operations and the incompatible freeze modes for certain types of workspaces (parent workspace, current workspace, intermediate multiparent workspace) to be locked for the operation. In Table 1-4:

• N/A means not applicable; that is, no freeze modes are incompatible.

• Exclusive + means that in addition to an exclusive lock, no sessions can be in the workspace.

• Sub eXclusive means the SX_MODE constant as used in the DBMS_LOCK procedure, described in Oracle AI Database PL/SQL Language Reference. SX_MODE can be used on an aggregate object to indicate that exclusive locks are being acquired on sub-parts of the object.

• Shared Sub eXclusive means the SSX_MODE constant as used in the DBMS_LOCK procedure, described in Oracle AI Database PL/SQL Language Reference. SSX_MODE can be used to indicate that the entire aggregate object has a shared lock, but some of the sub-parts may also have exclusive locks.

• Sub Shared means the SS_MODE constant as used in the DBMS_LOCK procedure, described in Oracle AI Database PL/SQL Language Reference. SS_MODE can be used on an aggregate object to indicate that share locks are being acquired on subparts of the object.

• Shared(1) means shared, except Shared Sub eXclusive when ROW_LEVEL_LOCKING=OFF.

For CompressWorkspaceand CompressWorkspaceTree, when the current version within any affected workspace is contained in a compressible range with at least one other version,  an attempt is made to acquire a Shared Sub eXclusive lock on that workspace.  If it fails to be acquired, no error is raised, but the current version is not compressed.

When the MergeTable and MergeWorkspace procedures are executed with the create_savepoint parameter set to true, the parent workspace lock taken is an SSX (Shared Sub eXclusive) lock.

Table 1-4 Operations and Incompatible Freeze Modes for Workspace Types

Operation	Parent Workspace	Incompatible Freeze Modes	Current Workspace	Incompatible Freeze Modes	Intermediate Multiparent Workspace	Incompatible Freeze Modes
Any DML	None	N/A	Shared	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION, WM_ONLY	None	N/A
BeginResolve	None	N/A	Shared Sub eXclusive	DEFERRED_REMOVAL	None	N/A
ChangeWorkspaceType	Shared	N/A	Shared Sub eXclusive	DEFERRED_REMOVAL, NO_ACCESS, ONEWRITER, ONEWRITER_SESSION, READ_ONLY	Exclusive	NO_ACCESS, ONEWRITER, ONEWRITER_SESSION, READ_ONLY
CommitResolve	None	N/A	Shared Sub eXclusive	DEFERRED_REMOVAL	None	N/A
CompressWorkspace	None	N/A	Shared	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	None	N/A
CompressWorkspaceTree	None	N/A	Shared	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	None	N/A
CopyWorkspace	None	N/A	Shared	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	None	N/A
CreateSavepoint	None	N/A	Shared (Shared Sub eXclusive when new version must be created)	DEFERRED_REMOVAL, NO_ACCESS	None	N/A
CreateWorkspace	None	N/A	Shared (Shared Sub eXclusive when new version must be created)	DEFERRED_REMOVAL, NO_ACCESS	None	N/A
DeleteSavepoint	None	N/A	Exclusive +	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	None	N/A
Export	None	N/A	Shared	DEFERRED_REMOVAL, NO_ACCESS	None	N/A
FreezeWorkspace	None	N/A	Shared Sub eXclusive	DEFERRED_REMOVAL	None	N/A
GotoWorkspace	None	N/A	Sub Shared	DEFERRED_REMOVAL, NO_ACCESSFoot 1	None	N/A
Import	None	N/A	Shared Sub eXclusive	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	None	N/A
LockRows	None	N/A	Shared	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	None	N/A
MergeTable (remove_data=>false)	Shared	NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	Sub eXclusive	DEFERRED_REMOVAL, NO_ACCESS	Sub eXclusive	NO_ACCESS
MergeTable (remove_data=>true)	Shared	NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	Sub eXclusive	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	Sub eXclusive	NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION
MergeWorkspace (remove_workspace=>false)	Shared(1)	NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	Shared Sub eXclusive	DEFERRED_REMOVAL, NO_ACCESS	Exclusive	NO_ACCESS
MergeWorkspace (remove_workspace=>true)	Shared(1)	NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	Exclusive +	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	Exclusive +	NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION
PurgeTable	None	N/A	None; table locked in Exclusive mode	None	None	N/A
RefreshTable	Shared(1)	NO_ACCESS	Shared	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	Shared	NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION
RefreshWorkspace	Shared(1)	NO_ACCESS	Shared	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	Exclusive	NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION
RemoveDeferredWorkspaces	None	N/A	Exclusive +	N/A	None	N/A
RemoveWorkspace	Shared(1)	N/A	Exclusive +	NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	Shared	N/A
RenameSavepoint	None	N/A	Exclusive +	DEFERRED_REMOVAL NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	None	N/A
RenameWorkspace	None	N/A	Exclusive +	DEFERRED_REMOVAL NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	None	N/A
RollbackResolve	None	N/A	Shared Sub eXclusive	DEFERRED_REMOVAL	None	N/A
RollbackTable	None	N/A	Shared Sub eXclusive	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	None	N/A
RollbackWorkspace	None	N/A	Shared Sub eXclusive	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	None	N/A
SetConflictWorkspace	None	NO_ACCESS	Sub Shared	DEFERRED_REMOVAL, NO_ACCESS	None	N/A
SetDiffVersions	None	N/A	Sub Shared	DEFERRED_REMOVAL, NO_ACCESS	None	N/A
SetMultiWorkspaces	None	N/A	Sub Shared	DEFERRED_REMOVAL, NO_ACCESS	None	N/A
SetWorkspaceLockModeOFF	None	N/A	Shared Sub eXclusive	DEFERRED_REMOVAL, NO_ACCESS	None	N/A
SetWorkspaceLockModeON	None	N/A	Shared Sub eXclusive	DEFERRED_REMOVAL, NO_ACCESS	None	N/A
UnfreezeWorkspace	None	N/A	Shared Sub eXclusive	DEFERRED_REMOVAL	None	N/A
UnlockRows	None	N/A	Shared	DEFERRED_REMOVAL, NO_ACCESS, READ_ONLY, ONEWRITER, ONEWRITER_SESSION	None	N/A

^Footnote 1

The LIVE workspace cannot be frozen in NO_ACCESS mode.