Insert performance on a multi-system transaction environment

Details Insert performance on a multi-system transaction environment Insert performance on a multi-system transaction environment

1998-08-18

2001-12-25

Alam, Hosain T. (Department: 2771)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000

Reexamination Certificate

active

06334134

ABSTRACT:

BACKGROUND OF THE INVENTION
1. Field of the Invention
This invention relates in general to computer-implemented database systems, and, in particular, to improving insert performance on a multi-system transaction environment.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data. Relational databases are organized into tables which consist of rows and columns of data. The rows are formally called tuples or records. A database will typically have many tables and each table will typically have multiple tuples and multiple columns. The tables are typically stored on direct access storage devices (DASD), such as magnetic or optical disk drives for semi-permanent storage.
In a DBMS, data records are stored in table spaces. A table space may contain one or more tables. Each table space contains a number of space map pages. Each space map page covers a number of data pages. One or more records can be stored in a single data page. All data pages within a single table space must have the same page size. Typically, a page contains 4096 bytes.
A space map page identifies the data pages that have enough free space for additional data to be inserted. Each space map page covers a fixed range of data pages. To avoid excessive updates to the space map pages, each space map page maintains only a few attributes for each data page. One of the attributes indicates how much free space remains in a data page.
When inserting a record, one or more space map pages are accessed for choosing a candidate data page. If there is a clustering index defined, the DBMS will attempt to insert the record in the same order as the clustering keys. Maintaining data records in the clustering key order enables more efficient data retrieval when the clustering index is used to retrieve a set of records within a key range.
To prevent updates from getting lost, all space map page updates are serialized. In a single-system environment, a local exclusive (“X”) page latch is held before a space map page can be updated. A local X page latch is similar to a lock. As soon as the update is completed, the local X page latch is released.
In a multi-system data sharing environment, the space map page update is serialized for all transactions across all sharing systems. Therefore, the local X page latch is extended to a global scope. The global page latch is referred to as a page P-lock (i.e. physical lock). In a data sharing environment, a space map page can only be updated when both the local page latch and the page P-lock are held in exclusive mode.
Each system in the multi-system data sharing environment maintains P-locks for pages that the system owns. For a given page, since the page P-lock is owned by a system, the system also acquires the local page latch to perform intra-system serialization for updates. The page P-lock is a global lock and can only be held in exclusive mode by one system at a time. Since each system owns the page P-locks for the pages the system owns, for inter-system serialization, an owning system can give up the ownership of the page P-lock when the lock is requested by another system. Then, the updated page is externalized to a high-speed shared electronic store or shared DASD for maintaining coherency of the data before the page P-lock is released.
The process of notifying another system to release the page P-lock is called P-lock negotiation. Since each space map page covers a wide range of data pages, the space map pages are frequently updated in an insert-intensive workload. The overhead of frequent page P-lock negotiations is very high. With high space map page contentions, this could have significant impact on the overall database system throughput. The frequent page P-lock negotiation also has significant impact on transaction response time.
Therefore, there is a need in the art for an improved method of insert performance on a multi-system transaction environment.
SUMMARY OF THE INVENTION
To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, and article of manufacture for a computer-implemented insert system.
In accordance with the present invention, data is stored in a database stored on a data storage device connected to a plurality of computers. At least one look-aside space map is allocated to each of the computers. At each computer, when data is to be inserted into the database, a look-aside space map allocated to that computer is identified, and a free data page is located using the identified look-aside space map.
An object of the invention is to provide a technique for improving insert performance on a multi-system transaction. Another object of the invention is to provide look-aside space map pages to improve insert performance.


REFERENCES:
patent: 4627019 (1986-12-01), Ng
patent: 4961134 (1990-10-01), Crus et al.
patent: 4965719 (1990-10-01), Shoens et al.
patent: 5103459 (1992-04-01), Gilhousen et al.
patent: 5455944 (1995-10-01), Haderle et al.
patent: 5455946 (1995-10-01), Mohan et al.
patent: 5546579 (1996-08-01), Josten et al.
patent: 5551027 (1996-08-01), Choy et al.
patent: 5551046 (1996-08-01), Mohan et al.
patent: 5664160 (1997-09-01), Facteau et al.
patent: 5692182 (1997-11-01), Desai et al.
patent: 5715236 (1998-02-01), Gilhousen et al.
patent: 6085296 (2000-07-01), Karkhanis et al.
patent: 6144970 (2000-11-01), Bonner et al.
“DB2 for OS/390 Version 5 Performance Topics”, International Support Organization, IBM, 2 pages (Jun. 1997).
“Methods for Space Management in Transactions Systems Supporting Fine-Granularity Locking: Record Display” Software Patent Institute Database of Software Technologies, 4 pages (Oct. 1993).

Affiliated with

Also associated with

Rating

Insert performance on a multi-system transaction environment does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Insert performance on a multi-system transaction environment, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Insert performance on a multi-system transaction environment will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-2564177