Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
1996-10-22
2004-06-22
Rones, Charles (Department: 2175)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000, C707S793000
Reexamination Certificate
active
06754656
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Field of the Invention
This invention relates generally to the field of database management, and more particularly to a system and method within the field of database management for providing concurrent access to database contents by individually locking partitions of a table without locking the entire table.
2. Description of the Related Art
The need to provide concurrent access to database contents is a recurring requirement in current database technology. Concurrency applies to multiple applications requiring access to the same data at the same time through one database management system (DBMS), and to multiple database systems accessing the same data at the same time. The former circumstance is referred to as the “non-data-sharing environment”, while the latter is denoted as the “data-sharing environment”. Today, databases are increasingly large, and requirements to access their contents are growing geometrically. Further, growing numbers of independent operations are seeking concurrent access.
Typically, database access is provided by way of application programs (“applications”) that employ transactions, basic units of recovery and concurrency in database processing. Concurrent application access is highly desirable.
The virtually universal technique of concurrency control is locking. In this regard, an application will acquire a lock on an object in the database in which it has an interest for reading, inserting, deleting, or changing. In order to ensure that the object will not change while the application is accessing it, the database management system provides a lock giving the application access to the object, while preventing other applications from modifying the object for so long as the application holds the lock.
In a relational database system, contents of a database are represented as tables of data values. Each table corresponds to a relation. In a relational database, a table can be divided into partitions. Each partition contains a portion of the data in the table. A table may be divided into partitions based upon a range of values for a specified key. For example, in the language of the well-known DB2 database system available from International Business Machines Corporation, Armonk, N.Y., the syntax of a CREATE TABLESPACE statement includes a NUMPARTS clause that identifies the created table space as partitioned and sets the number of partitions. Partitions on a table in partitioned table space are characterized by a PART clause in a CREATE INDEX statement. By partitioning a table, partitions containing more frequently-used data can be placed on faster devices, and parallel processing of data can be improved by spreading partitions over different DASD volumes, with each I/O stream on a separate channel path. This speeds up and increases the efficiency of access to data in a relational database.
However, when access is granted to an application, the entire table is locked, even if only a subset of the data (e.g. data for only a few partitions) will be accessed. When a table is partitioned, locking the entire table may degrade concurrency and database system performance.
It is an object of the present invention, therefore, to provide a practical and economic means by which concurrent access to a partitioned table may be enhanced, without degrading database system performance. It is a further object of the present invention to provide a means for serializing access to a partitioned table in a relational database without requiring locking of the entire table when a serialized application requires access to less than all of the partitions of the table.
SUMMARY OF THE INVENTION
Our invention involves, in connection with the operation of a database system, the use of selective partition locking that allows the database system to lock only those partitions of a partitioned table space to which access is sought by a first application. Other partitions of the same table are left available for concurrent access by one or more second applications, regardless of the level of the lock on the partitions accessed by the first application.
With selective partition locking, only the partitions that are accessed by an application are locked. This allows highly concurrent access to separate portions of data in a table. For example, if a first application requires a shared (S) lock for reading data from partition A, while a second application requires an intent exclusive (IX) lock to update data in partition B, the incompatibility between the lock types will not result in delay of one application because the table will not be wholly locked on behalf of the other application. Therefore, both applications can run concurrently, using the same table. Without selective partition locking, the entire table would be locked and access for the applications would be serialized with respect to the table.
Our invention further provides for locking any partition of a table at the lowest lock state needed to serialize access to data in that partition. Thus, for example, if a first application is reading from partition A and updating in partition B, it can acquire an intent shared (IS) lock on partition A, and an IX lock on partition B. As a result, concurrent access for a greater number of processing activities can be tolerated in all partitions of the table. Without selective partition locking, the entire table would be locked by an IX lock.
Our selective partition locking invention also improves the performance of data-sharing systems. In a data-sharing environment without selective partition locking, several systems accessing the same partitioned table space might experience an unacceptably high rate of unnecessary lock contentions because the entire table space is locked. In order to reduce unnecessary lock contentions, selective partition locking associates each lock request for a page or row lock with a particular partition lock, rather than with the table space lock. This reduces the number of page and row locks that need to be propagated beyond a local resource lock manager to a global lock manager in order to resolve locking conflicts.
We also contemplate programming-level implementation of our selective partition locking by expansion of the syntax of a LOCK TABLE statement to include the specification of a specific partition to lock. This affords an application programmer with the ability to boost the performance of a query which accesses many rows in a particular partition, without hindering access to data in other partitions of the table.
Other features and advantages of the present invention will become apparent from the following more detailed description, taken in conjunction with the accompanying drawings, which illustrate, by way of example, the principles of the invention.
REFERENCES:
patent: 4399504 (1983-08-01), Obermarck et al.
patent: 4716528 (1987-12-01), Crus et al.
patent: 4727485 (1988-02-01), Keshlear et al.
patent: 4933848 (1990-06-01), Haderle et al.
patent: 4965719 (1990-10-01), Shoens et al.
patent: 5226143 (1993-07-01), Baird et al.
patent: 5301290 (1994-04-01), Tetzlaff et al.
patent: 5448727 (1995-09-01), Annevelink
patent: 5455942 (1995-10-01), Mohan et al.
patent: 5551027 (1996-08-01), Choy et al.
patent: 5625811 (1997-04-01), Bhide et al.
patent: 5625815 (1997-04-01), Maier et al.
patent: 5675797 (1997-10-01), Chung et al.
patent: 5692174 (1997-11-01), Bireley et al.
patent: 5692182 (1997-11-01), Desai et al.
patent: 5758345 (1998-05-01), Wang
patent: WO 91/03024 (1991-03-01), None
IBM Technical Disclosure Bulletin, “Method for Performance Enhancement in the Parallel Execution of Static Database Transactions”, Aug. 1994, vol. 37, No. 8, pp. 609-614.
“Integrated Concurrency-Coherency Controls for Multisystem Data Sharing”, Daniel M. Dias, Balakrishna R. Iyer, John T. Robinsion & Philip S. Yu, I.E.E.E. Trans on Software Engineering, vol. 15, No. 4, New York, New York (1989).
“Efficient Locking and Caching of Data In the Multisystem Shared Disks Transaction Environment”, 3rd Inv. Conf. on Extending Database Technology, (Vien
Cornwell Karelle Lynne
Couch Barbara Tanya
Lyle Robert William
Teng James Zu-Chia
Watts Julie Ann
Rones Charles
Sawyer Law Group LLP
LandOfFree
System and method for selective partition locking does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with System and method for selective partition locking, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and System and method for selective partition locking will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3319421