| GISdevelopment.net ---> GITA 2001 ---> System Architecture |
Managing Long Transactions Using Standard DBMS Technology
Xavier R. Lopez and Arun Gopalan
Oracle Corporation
1 Oracle Drive, Nashua, NH 03062
The need to manage complex, long-duration database transactions is common in today's engineering applications, AM/FM, geographic information systems (GIS), and computer aided design (CAD) applications. Conventional database management systems (DBMS) are designed to handle short duration transactions, such as those in financial applications. However, these techniques are insufficient for engineering applications where a design can take days to complete and multiple users must access the same database. Traditional 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, distinct transactions that are open for milliseconds or minutes. A conventional short transaction locks all records that are changed until the transaction is completed (either merged or rolled back). These concurrency control techniques, however, are insufficient for handling applications that must support long-duration transactions.
Oracle Corporation
1 Oracle Drive, Nashua, NH 03062
Oracle8i Workspace Manager provides a long transaction framework built on a workspace management system. It uses a series of short transactions and multiple data versions to implement a complete long transaction event that maintains atomicity and concurrency. Changes are stored in the database as different workspaces. Users are permitted to create new versions of data to update, while maintaining a copy of the old data. The ongoing results of the long transaction are stored persistently, assuring concurrency and consistency
Concepts
Workspace management refers to the ability of the database to hold different versions of the same record (that is, row) in one or more workspaces. Users of the database can then change these versions independently. There are two fundamental benefits of versioning in a database system:
- Versioning improves concurrent access of data in the database. In a database without the versioning ability, users wanting to change the same record are serialized by means of locks. Relaxing the locking strictness to improve concurrency leads to undesirable side effects, such as cascading rollbacks.
- Multiple what-if analyses can be run against the data simultaneously. Each analysis works on a separate version of data. After the analyses are complete, the results can be stored in the database for quick lookup.
Usage
A workspace is a virtual environment that one or more users can share to make changes to the data in the database. Workspace management involves managing one or more workspaces that can be shared by many users.
Workspace Hierarchy
There can be one or more versions of a row in a workspace from one or more versionenabled tables. The current or active version in a workspace refers to the version number in which the changes are currently being made. All changes made in a workspace are made by conventional short transactions. There can be a hierarchy of workspaces in the database. For example, a workspace can be a parent to one or more workspaces. By default, when a workspace is created, it is created from the topmost, or LIVE, database workspace. (Workspace names are case sensitive, and the workspace name of the live database is spelled LIVE. The length of a workspace name must not exceed 30 characters.)
Figure 1-1 shows a hierarchy of workspaces. Workspace 1 and Workspace 4 were formed off the LIVE database workspace; Workspace 2 and Workspace 3 were formed off Workspace 1, and Workspace 5 was formed off Workspace 4. After Workspace 1 was created, a user executed a GotoWorkspace operation specifying Workspace 1, and then executed CreateWorkspace operations to create Workspace 2 and Workspace 3. A comparable sequence was followed with Workspace 4 and Workspace 5.
Figure 1-1 Workspace Tree
Role of Savepoints
A savepoint is a point in the workspace to which operations can be rolled back. It is analogous to a firewall, in that by creating a savepoint you can prevent any damage to the "other side" of the wall (that is, operations performed in the workspace before the savepoint was created).
Explicit savepoints can thus be created and later used to effect partial rollbacks in workspaces. In Figure 1-2, SP1, SP2, SP3 and SP4 are explicit savepoints that have been created in the workspaces indicated. (Savepoints are indicated by dashed lines in Figure 1-2.)
Figure 1-2 Savepoints
In addition, implicit savepoints are created automatically whenever a new workspace is created. Thus, in Figure 1-2 two implicit savepoints (SPa and SPd) are created in the LIVE workspace corresponding to Workspace 1 and Workspace 4 creation, two implicit savepoints (SPb and SPc) are created in Workspace 1 corresponding to Workspace 2 and Workspace 3 creation, and one implicit savepoint (SPe) is created in Workspace 4 corresponding to Workspace 5 creation. An implicit savepoint is needed so that the users in the child workspace get a view of the database that is frozen at the time of the workspace creation.
Workspace Manager uses the name LATEST to designate a logical savepoint that refers to the latest version in the workspace. LATEST is often the default when a savepoint is an optional parameter for a procedure.
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.
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.
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.
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.