Database management system and method for updating specified tuple fields upon transaction rollback

• US 6,397,227 B1
• Filed: 07/06/1999
• Issued: 05/28/2002
• Est. Priority Date: 07/06/1999
• Status: Expired due to Fees

• Current Assignee: Compaq Computer Corporation (HP Inc.)
• Original Assignee: Compaq Computer Corporation (HP Inc.)
• Inventors: Klein, Johannes, Van der Linden, Robbert C., Sharma, Anoop, Rathee, Raj K.
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): Nguyen, Tam V
• Application Number:
  US09/347,994
• Time in Patent Office: 1,057 Days
• Field of Search: 707/10,1,3,101,8,202,203
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2308 Concurrency control transaction processing G06F9/466
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 16/2445 Data retrieval commands; View definitions
• CPC Class Codes: G06F 16/24568 Data stream processing; Continuous queries
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database

Abstract

A database management system includes an execution engine that, upon rollback of an aborted transaction, has the ability to set fields of the rows that are updated or deleted by the transaction prior to aborting. In particular, when a select, update or delete statement includes a “set on rollback” clause, the respective table access operator of the execution engine modifies the “before image” of each row that is stored by the transaction log manager. The modified before image includes the field updates specified by the set on rollback clause. If a transaction associated with the statement aborts, when the transaction log manager restores the before images of each row that was deleted or updated by the transaction, the restored before images include the field updates specified by the set on rollback clause.

Claims

1. A method of processing data in a database management system, in accordance with a specified statement, comprising:

starting execution of a transaction;

establishing a table access operator for accessing a defined range of rows in a database table;

using the table access operator, performing an update or delete operation on qualifying rows, if any, from the database table, in accordance with the specified statement;

for each updated or deleted row, storing a before image of the row, the before image representing field values of the row prior to the update or delete operation;

when the specified statement includes a predefined set on rollback clause that specifies updates to be performed, in the event of a transaction rollback, to one or more fields of each row updated or deleted by the transaction, modifying the before image of said each updated or deleted row, prior to the before image being stored, by performing the specified updates to the one or more fields of the row;

in the event that execution of the transaction is aborted, performing a rollback in which the stored before images of the updated or deleted rows are restored to the database table.

2. A method of processing data in a database management system, comprising:

starting execution of a transaction;

establishing a table access operator for accessing a defined range of rows in a database table in accordance with a specified statement, the specified statement including a clause indicating modifications that are to be performed, in the event of a transaction rollback, to one or more fields of each row that is updated or deleted by the transaction;

using the table access operator, performing an update or delete operation on qualifying rows, if any, from the database table, in accordance with the specified statement; and

in the event that execution of the transaction is aborted;

performing a rollback in which the updated or deleted rows are restored to the database table so as to reverse the update or delete operation performed on the updated or deleted rows, and

modifying one or more fields of each row that is restored to the database table in accordance with the clause of the specified statement indicating the modifications that are to be performed upon rollback of the transaction.

3. A database management system, comprising:

a transaction management facility for managing execution of transactions;

a database, including a plurality of tables, each table storing data in one or more rows;

an execution engine for performing an update or delete operation on qualifying rows, if any, from one of the database tables, in accordance with the specified statement;

a transaction log manager for storing, for each updated or deleted row, a before image of the row, the before image representing field values of the row prior to the update or delete operation;

before image generation means, operative when the specified statement includes a predefined set on rollback clause that specifies updates to be performed, in the event of a transaction rollback, to one or more fields of each row updated or deleted by the transaction, for modifying the before image of said each updated or deleted row, prior to the before image being stored, by performing the specified updates to the one or more fields of the row;

the transaction management facility including means for performing a rollback in the event that execution of the transaction is aborted, so as to restore to the database table the stored before images of the updated or deleted rows.

4. A database management system, comprising:

a transaction management facility for managing execution of transactions;

a database, including a plurality of tables, each table storing data in one or more rows;

an execution engine for performing an update or delete operation on qualifying rows, if any, from one of the database table, in accordance with a specified statement, the specified statement including a clause indicating modifications that are to be performed, in the event of a transaction rollback, to one or more fields of each row that is updated or deleted by the transaction;

the transaction management facility including means for;

performing a rollback in the event that execution of the transaction is aborted, so as to reverse the update or delete operation performed on the updated or deleted rows, and

modifying one or more fields of each row that is restored to the database table in accordance with the clause of the specified statement indicating the modifications that are to be performed upon rollback of the transaction.

5. A computer program product for use in conjunction with a computer system, the computer system including a database, including a plurality of tables, each table storing data in one or more rows;

the computer program product comprising a computer readable storage medium and a computer program mechanism embedded therein, the computer program mechanism comprising;

a transaction management facility for managing execution of transactions;

an execution engine for performing an update or delete operation on qualifying rows, if any, from one of the database tables, in accordance with the specified statement;

a transaction log manager for storing, for each updated or deleted row, a before image of the row, the before image representing field values of the row prior to the update or delete operation;

before image generation means, operative when the specified statement includes a predefined set on rollback clause that specifies updates to be performed, in the event of a transaction rollback, to one or more fields of each row updated or deleted by the transaction, for modifying the before image of said each updated or deleted row, prior to the before image being stored, by performing the specified updates to the one or more fields of the row;

the transaction management facility including means for performing a rollback in the event that execution of the transaction is aborted, so as to restore to the database table the stored before images of the updated or deleted rows.

6. A computer program product for use in conjunction with a computer system, the computer system including a database, including a plurality of tables, each table storing data in one or more rows;

the computer program product comprising a computer readable storage medium and a computer program mechanism embedded therein, the computer program mechanism comprising;

a transaction management facility for managing execution of transactions;

an execution engine for performing an update or delete operation on qualifying rows, if any, from one of the database table, in accordance with a specified statement, the specified statement including a clause indicating modifications that are to be performed, in the event of a transaction rollback, to one or more fields of each row that is updated or deleted by the transaction;

the transaction management facility including means for;

performing a rollback in the event that execution of the transaction is aborted, so as to reverse the update or delete operation performed on the updated or deleted rows, and

modifying one or more fields of each row that is restored to the database table in accordance with the clause of the specified statement indicating the modifications that are to be performed upon rollback of the transaction.