Long transactions in an RDBMS
S. Agarwal, G. Arun, R. Chatterjee, B. Speckhard, R. Vasudevan
Oracle Corporation
One Oracle Drive
Nashua, NH 030360
Abstract
Engineering design systems based on RDBMSs (relational database management systems), such as
geographical information systems (GIS), require support for long-duration transactions from the RDBMS.
These applications require long-transaction (LT) management to be tightly integrated with database
features common in RDBMSs, such as triggers, constraints, indexes, data model evolution (DDL),
import/export and bulk loading. In this paper, we will describe a Workspace Management system, which
addresses the above issues.
With the blending of LT capabilities with traditional RDBMS functions, it is now easier to embrace and
extend spatial data and operations across the enterprise. A complete system for supporting long
transactions should include support for all the well understood long transaction features such as
versioning of database rows, version trees of arbitrary depth and fan-out, hierarchical multi-user long
transactions, long-transaction locks, conflict detection and resolution, privilege management, and ability
for partial/complete refresh, publish and rollback. In addition, it should extend support for the aforementioned
RDBMS features to versioned data.
1. Introduction
The need to manage complex long-duration transactions (LT) is common in today’s GIS, web publishing,
e-commerce, and CAD applications [2,3,4,5,8]. Conventional database systems are designed to handle
short-duration transactions such as those in financial applications. The short transaction framework is
inadequate for handling applications that must support long-duration transactions. Consider the following
example:
A group of engineers designing modifications to a road network will need to manipulate digital
representations of a number of parcels, telegraph poles, and other objects (commonly represented as
spatial geometries), which may take hours, days or even weeks. While the modifications to the road
network are in progress, the database changes being done cannot be made visible to other users of the
production database, because the long transaction is incomplete. Conventional database systems achieve
this kind of isolation for short transactions using locks. But this pessimistic mode of locking becomes
impractical for long-duration transactions, as it limits concurrency to a great degree. Thus, the group of
engineers, in some sense, need to have a private “version” (copy) of the data for the duration of the
transaction, which can be made available to other users when they have finished their work. In addition,
the group of engineers need to be able to see each other’s changes made as part of the same long
transaction as the changes are happening. Regular short transactions do not allow for this kind of
collaborative data manipulation [3].
Repository [6,7] and Source Code control systems have traditionally provided comprehensive support for
versioning, which can be used to address the problem of Long Transactions. Typically, these frameworks
provide a checkout/checkin model with different levels of functionality to merge versions, refresh
versions, resolve conflicts, etc.
Long Transaction support in an RDBMS is different from what the above systems provide in the
following manner:
- RDBMSs add significant value to relational data by providing comprehensive support for features like
constraints, indexes, import/export, bulk loading, replication, triggers, data model evolution, virtual
private databases etc. Accordingly, Long Transaction management systems in an RDBMS have to support
these features in the presence of versioned relational data.
- The data being accessed in an LT is relational, on a much larger scale, and adhoc. RDBMSs
efficiently support adhoc querying and modifications wherein apriori knowledge of the data being
accessed is not very common. Hence, systems that provide support for LTs in an RDBMS have to
accommodate this need by providing a copy-on-write approach where the data need not be checked out
prior to modification.
Thus, existing checkout/checkin based multi-version systems address a problem different from that of
support for LTs in an RDBMS. In this paper, we will talk about Oracle Workspace Manager (WM), a
framework to support Long Transactions in an RDBMS, which is tightly integrated with the database and
extends support for all the features that the database natively provides
The rest of the paper is organized as follows. Section 2 describes the overall architecture of the system.
Section 3 explains the long transaction functionality of the system. Section 4 summarizes the support
provided by the system for RDBMS features on versioned relational data. Finally, Section 5 presents
conclusions and future work.
