Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2002-02-28
2004-10-19
Mizrahi, Diane D. (Department: 2175)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000
Reexamination Certificate
active
06807541
ABSTRACT:
FIELD OF THE INVENTION
The present invention relates to processing of database queries in a relational database system.
BACKGROUND OF THE INVENTION
In a relational database, data is typically stored in the forms of records, each record being a member of a relation or table of records, and having fields defined for that relation. Fields in one relation may be linked to fields in other relations to form a relational database.
Such databases are increasingly used in multi-user and multi-processing or multi-threaded environments, where the records are being regularly read and updated as part of the business routine of the sponsoring organization. Such environments pose a challenge to database software design, for the reason that record updates and reads need to be managed to ensure accuracy and consistency of results. For example, a simultaneous update and read of data from a record, poses a risk that the data read from the record will be internally inconsistent, for the reason that only some but not all of the data in the record was updated prior to the read. Furthermore, if a given field is read at the same time that it is being updated, the resulting data value may be incomplete or inaccurate, creating errors such as a data type mismatch. Simultaneous updating of records poses a similar risk of inaccuracy or inconsistency, for the reason that some, but not all, of the changes made by one update may be overwritten by changes made by the other update, leaving inconsistent or inaccurate data in the records.
In view of these issues, in some implementations, database software includes methods for locking records to ensure that updates and reads of records are accurate and consistent. For example, in one conventional scheme, as part of a read transaction, a record being read is first locked, to prevent any updating of that record outside of that transaction until the read transaction is complete. If a record is being updated, then that record will be locked for updating, and the record will not be read, until it becomes available and can be locked for the read transaction. After the read transaction is completed, all locked records are unlocked. Similarly, as part of an update transaction, a records that is subject to the update transaction is locked for updating, to prevent any reading or updating of that record outside of that transaction until the transaction is complete. If the record has already been locked for reading, then the record will not be updated, until it becomes available and can be locked for the update transaction. After the update transaction is completed, all locked records are unlocked (and the changes made are thereby “committed”).
While record locking schemes of the kind described ensure consistency by enforcing a transaction-based updating and reading of data, and further ensure that uncommitted records are not read, these schemes suffer from drawbacks, namely, delays inherent in locking all records involved in both read and update transactions.
One improvement, used in products of the assignee of the present invention, is multi-level scoping of record locks. Specifically, in record lock scoping, each record lock, when created by a thread, is associated with that threads' process. When the record is being actively read or updated by a thread, the record lock is associated with the thread that is actively updating or reading the record. The scoping of the lock affects the conflict behavior of the lock, and in some cases alleviates delays. Specifically, if a thread is seeking to create a read lock on a record on behalf of a process, while the same process holds an update lock on the record, the read lock can be established. Similarly, if a thread seeks to create an update lock on a record on behalf of its process while the same process holds a read lock on the record, the update lock can be established. In both cases, the use of multi-level (process and thread) scoping of locks avoids a conflict and a delay. The delay is avoided when locks are created at the process level, because later thread-scoped locking will detect and resolve any conflicts. Specifically, when a thread seeks to actively read a particular record, it must associate the lock on the record with the thread. At that point, if another thread in the same process, (or another process) holds an update lock on the record, a conflict will occur. Similarly, when a thread seeks to actively update a particular record, it must associate the lock on the record with the thread. If another thread in the same process (or another process) holds a read or update lock on the record, a conflict will occur. Notably, if a thread seeks an update lock while the same thread has a read lock, no conflict will occur: because the same thread is performing the read and update, there is an inherent serialization of activity that will protect record integrity.
While scoping of record locks, as described above, relieves some of the delays inherent in the record locking approach, while maintaining transactional integrity, in some cases maintaining transactional integrity, and the resulting delays, are unnecessary. As a consequence, it has been known to permit read transactions without record locking. In this case, inconsistent results may be obtained, for the reason that uncommitted updates created by other processes or threads, may be reflected in the read results. However, such inconsistencies may be acceptable in some situations, as defined by the database programmer.
Unfortunately, departing from the record locking scheme described above, by permitting unlocked reading of data, invites errors. The record locking scheme described above, inherently enforces serialization of activity with respect to individual records; that is, by virtue of the use of locking, a particular record cannot be read and updated simultaneously. If unlocked read transactions are permitted, data can be extracted from a record that is currently being modified. As noted, dramatic errors may result if an individual record is simultaneously read and updated. Accordingly, some type of serialization must take place at the record level when unlocked reads are permitted.
One known way to enforce serialization, independently of record locking, uses check counters. In this scheme, a counter is associated with each record. Each time the record is updated, the counter for the record is incremented. When a record is read, therefore, it can be determine whether the record was updated while being read, by accessing the counter prior to and after the read, and if the counter values do not match, retrying the read. This solution, while workable, has the drawback that potentially multiple retries may be required before a record is successfully read, and there is substantial overhead in maintaining and updating the counters separately from record locking. To avoid the second problem, each counter may be made applicable to a group of records, rather than an individual records, but in that case the likelihood for retries increases.
Another known solution is the use of independent “seizing” of records at the record level. Thus, an update transaction would need to acquire an exclusive seize on each record prior to updating that record, and release the seize when modification is complete. A read transaction would acquire a shared seize on each record while extracting data from it, and release the seize when reading is complete. (This step may be taken by every read transaction or, in a combined approach also using a check counter, only by those read transactions that have initiated retry(s) due to check counter changes.) When a shared seize is attempted on an exclusively seized record, or vice-versa, a conflict is registered, and the transaction attempting to create the second, conflicting seize, is paused until the record is released from the existing seize. A drawback of this approach is, again the overhead required to manage the seizes separately from record locking.
SUMMARY OF THE INVENTION
The foregoing difficulties are overcome in accordance with principles of th
Bender Daniel Dean
McKinley Thomas Owen
Mech Robert Paul
Petrillo Robert Anthony
Rocheleau Richard Miles
Mizrahi Diane D.
Mofiz Apu M
Wood Herron & Evans LLP
LandOfFree
Weak record locks in database query read processing does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Weak record locks in database query read processing, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Weak record locks in database query read processing will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3272373