Managing Long Transactions Using Standard DBMS Technology

Merging and Rolling Back Workspace Changes
Workspaces can be merged or rolled back.

Merging a workspace involves applying changes made in a workspace to its parent workspace, after which the workspace that had been merged ceases to exist (that is, it is removed).
Rolling back a workspace involves deleting either all changes made in the workspace or all changes made after a savepoint (that is, an explicit savepoint). Generally, a workspace cannot be rolled back when it has active users. Rollback of a workspace leaves behind the workspace structure for future use; only the data in the workspace is deleted.

Conflict Resolution
On a merge operation, the changes in the workspace are incorporated in its parent workspace. Rows that are changed in the child and parent workspace may lead to data conflicts. Conflicts are detected at merge time and presented to the user in conflict views. There is one conflict view per table, as described in Section 3.13. This view lists the primary key of the rows in conflict and also the column values of the rows in the two workspaces that form the conflict.

Conflicts have to be resolved manually by using a conflict resolution procedure. When there are no conflicts between the parent and child workspaces, the data in the two workspaces can be merged. Conflicts must be resolved before a merging or refreshing operation can be performed. Typically, you discover that conflicts exist when you attempt to merge or refresh a workspace and encounter an exception that refers to conflicts.

Freezing and Unfreezing Workspaces
A workspace can be frozen or unfrozen. If a workspace is frozen, no changes can be made to data in version-enabled rows, and access to the workspace is restricted. In addition, some procedures automatically freeze one or more workspaces.

Removing Workspaces
A workspace can be removed by deleting the workspace structure with a single operation. In addition, it is possible to remove the entire tree of workspaces. This will remove the workspace and all its descendant workspaces. However, a workspace cannot be removed when it has users in it.

Lock Management
In addition to locks provided by conventional Oracle short 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. Locking is enabled at a session level and is a session property independent of the workspace that the session is in. When locking is enabled for a session, it locks rows in all workspaces in which it participates. The two types of version locks are:

Exclusive locks - The locks are very similar to short transaction locks in that once an exclusive lock has been 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.
Shared locks - Once a shared lock has been 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 which needs to have no conflicts with its parent, but which 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.

Locks persist for the duration of the workspace. Merge or rollback of the workspace removes the locks.

Privilege Management
Workspace Manager has a set of privileges that are separate from standard Oracle database privileges. Workspace Manager workspace-level privileges allow the user to affect a specified workspace, and system-level privileges allow the user to affect any workspace. For example, user privileges can be granted, revoked both at the workspace and system level. As with any DBMS security, these procedures require that the user have sufficient privilege to revoke the specified privilege from the specified user. The user that granted a privilege can revoke it.

Import and Export Considerations
Standard Oracle database import and export operations can be performed on versionenabled databases; however, the following considerations and restrictions apply:

A database with version-enabled tables can be exported to another Oracle database only if the other database has Workspace Manager installed and does not currently have any version-enabled tables or workspaces (that is, other than the LIVE workspace).
Only database-wide import and export operations are supported for version-enabled databases. No other export modes (such as schema, table, partition, and workspace) are supported.

Referential Integrity Support
Version-enabled tables can have referential integrity constraints, including constraints with the CASCADE and RESTRICT options; however, the following considerations and restrictions apply:

If the parent table in a referential integrity relationship is version-enabled, the child table must be version-enabled also. (The child table is the one on which the constraint is defined.) For example, consider the following EMPLOYEE and DEAPRTMENT table definitions, with a foreign key constraint added after the creation (that is, the DEPT_ID in each EMPLOYEE row must match an existing DEPT_ID in a DEPARTMENT row).

CREATE TABLE employee (
employee_id NUMBER,
last_name VARCHAR2(32),
first_name VARCHAR2(32),
dept_id NUMBER);
CREATE TABLE department (
dept_id NUMBER,
name VARCHAR2(32);
ALTER TABLE employee ADD CONSTRAINT emp_forkey_deptid
FOREIGN KEY (dept_id) REFERENCES department (dept_id)
ON DELETE CASCADE;

DEPARTMENT is considered the parent and EMPLOYEE the child in the referential integrity relationship in this example; and if DEPARTMENT is version-enabled, EMPLOYEE must be version-enabled also. In this relationship definition, when a DEPARTMENT row is deleted, all its child rows in the EMPLOYEE table are deleted (cascading delete operation).

Referential integrity constraints cannot be added when a table is version-enabled; they must be defined before a table is version-enabled. A child table in a referential integrity relationship is allowed to be version-enabled without the parent table being versionenabled. A version-enabled table cannot be both a child and a parent in a referential integrity relationship, unless it is a self-referential constraint (that is, the same table can be both the parent and child table in a referential integrity relationship).

Summary
Traditional DBMS concurrency control techniques are well defined and adequate for handling short-duration transactions. These techniques have proven highly robust for handling very large numbers of short, discrete transactions that are open for milliseconds or minutes. However, these concurrency control techniques are insufficient for handling applications that must support long-duration transactions, that is, where a change to a design file may take weeks or months to commit. Since locking limits concurrency, it is undesirable to have a file locked for an extended period of time in today’s complex, collaborative, web-based user environments. This paper highlighted how users can deploy Oracle Workspace manager for performing long-transaction management essential for maintaining and AM/FM and CAD databases.

Oracle’s approach implements an optimistic version management approach using a standard Oracle RDBMS environment. A complete long transaction event is made up of many short transaction check points, or intermediate commit stages. With Workspace Manager, only one user can update a particular version of a database at a time, but any number of users can read the data. Workspace Manager overcomes the general problem of checkout since there is no initial retrieval time, no copying of data, and the user has access to the whole database at all times. Changes are stored in the database as different workspaces. By permitting a user to create a new version of a database to update, while maintaining a copy of the old file, greater concurrency and atomicity is allowed. The ongoing results of the long transaction are stored persistently.