Oracle9i introduced a new feature that relates to concurrency, Workspace Manager.
Workspace Manager allows current, proposed, and historical versions of a row to exist together in the same table. It ensures that each user sees the appropriate version of a row along with the rest of the data in the database. For instance, a group may be working on a set of changes to an overall compensation package, but not want proposed changes to be seen until they have received final approval.
Workspace Manager uses workspaces to accomplish this. A workspace is a virtual environment that one or more users share to make changes to the data in the database. Users enter a workspace to make changes to data. A workspace logically groups collections of new row versions in one or more version-enabled tables, and isolates these versions until they are explicitly merged with production data or until they are discarded. A user in a workspace sees all of the data in the database, as it existed when the workspace was created or last refreshed, as well as the changes made from the workspace. Changes to data made from within a workspace are only visible to users within the workspace.
To use workspaces to version data in a table, you must first enable the table for versioning. Workspace Manager can version-enable one or more user tables in the database. The unit of versioning is a row. When a table is version-enabled, all rows in the table can support multiple versions of the data. Versioned rows are stored in the same table as the original rows. The versioning infrastructure is not visible to the users of the database, and application SQL statements to select, insert, modify, and delete data continue to work in the usual way with version-enabled tables. Workspace Manager version-enables a table by renaming the table, adding a few columns to the table to store versioning metadata, creating a view on the version-enabled table using the original table name and defining INSTEAD OF triggers on the view for SQL DML operations.
Savepoints are used to create new row versions, and allow users to roll back changes to rows in version-enabled tables and see the state of the database as it existed as of a particular milestone. Modifying a row after a savepoint is created causes a new version of that row to be created. Subsequent changes are applied to this new version in the workspace until another savepoint is created. Users can compare differences between any two savepoints.
A rollback operation deletes changes made in the workspace. A user can delete either all changes made since the workspace was created or only changes made after a savepoint. The user can limit the rollback to one or more version-enabled tables.
A table can be version-enabled with the history option. If the history option is specified, Workspace Manager adds a timestamp to the row version every time it is changed. The history option works with savepoints to provide a history of changes made to each row version created by a savepoint. This allows users in a workspace to go back to any point in time and view the entire database from the perspective of changes made in that workspace. The history option allows you to choose either to make a copy of the row version each time a change is made to it or to overwrite values in the row version with the most recent changes. If you keep all changes when version-enabling a table, you keep a persistent history of all changes made to all row versions in the table.
Workspaces can be organized into hierarchies, to implement cascading groups of changes. Child workspaces can be merged and refreshed with their parent workspace. Merging a workspace involves applying changes made in a child workspace to its parent workspace. Refreshing a workspace involves applying changes made in the parent workspace to a child workspace. With Oracle Database 10g, you can even allow a child workspace to have more than one parent workspace. This feature allows a user in the child workspace to see all of the changes made across all of the parent workspaces in one place before they are merged into production. A user can work in more than one workspace in a particular session. A user with workspace privileges can freeze access to a workspace to allow no access to the workspace, read-only access, or single-writer access.
You can choose to allow two or more workspaces to version the same row at the same time. This allows multiple proposals to be developed concurrently or to create multiple variations of a data scenario for "what-if" analysis. When a row is changed in both the child and parent workspace (usually through the merge of another child workspace into the parent), a data conflict is created. Conflicts can be checked and resolved at any time. Conflicts are automatically detected when a merge or refresh operation is requested. The list of conflicts is presented to the user in a conflict view. Conflicts are resolved manually using a Workspace Manager procedure. For each conflict, you can choose to keep the row from the child workspace, the row from the parent workspace, or the common base row's original data values. You must resolve conflicts before you can perform a merge or refresh operation.
Workspace Manager provides exclusive and shared version locks to prevent conflicts between workspaces. These locks eliminate row conflicts between a parent workspace and a child workspace. They can also be used to reserve rows you will be updating in the future. You can enable locking on a workspace, for a user session, on specified rows, or a combination of the three.
Workspace Manager is tightly integrated with the Oracle database. Oracle Database 10g Workspace Manager enhancements include the ability to export and import version-enabled tables, to use SQL*Loader to bulk load data into version-enabled tables, to trigger events based on workspace operations, and to define workspaces that are continually refreshed.