Managing Long Transactions Using Standard DBMS Technology

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.

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.

The unit of versioning in the product is a database table. A table in the database can be version-enabled, which means that all rows in the table can now support multiple versions of data. The versioning infrastructure is not visible to the database end users. After a table has been version-enabled, users automatically see the correct version of the record in which they are interested.

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.