Architecture of database application with robust online recoverability

• US 7,281,023 B2
• Filed: 12/15/2003
• Issued: 10/09/2007
• Est. Priority Date: 12/15/2003
• Status: Active Grant

• Current Assignee: AT&T Intellectual Property I LP (AT&T, Inc.)
• Original Assignee: AT&T Knowledge Ventures, L.P. (AT&T, Inc.)
• Inventors: Lou, Yongming
• Primary Examiner(s): Gaffin, Jeffrey
• Assistant Examiner(s): Ponikiewski, Tomasz
• Application Number:
  US10/734,112
• Publication Number:
  US 20050131966A1
• Time in Patent Office: 1,394 Days
• Field of Search: None
• US Class Current: 707/684
• CPC Class Codes: G06F 11/1469 Backup restoration techniques
• CPC Class Codes: G06F 11/1471 involving logging of persistent data for recovery
• CPC Class Codes: G06F 11/1474 in transactions G06F16/20 takes precedence
• CPC Class Codes: G06F 16/2308 Concurrency control transaction processing G06F9/466
• CPC Class Codes: G06F 16/2322 using timestamps
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: Y10S 707/915 Image
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

An architecture for efficiently identifying the scope and timeframe of database errors, providing online history images, creating online point-in-time views of application tables, reconstructing equivalent SQL statements of a committed transaction or of a user session, providing a selective audit trail report on demand, and permitting selective online rollbacks in an application database. The rollback can be done on a single row, a single transaction, a group transactions, a user session, or all user tables. The system includes after images tables and before images tables and tracks all before images of user application tables when a user performs an INSERT, UPDATE, or DELETE operation with respect to a row in a table. Also, the system is provided to generate history images of application tables at a point-in-time, and to selectively rollback, or undo whole or a portion of application tables, to generate a selective audit trail report on demand, and to manage the before images.

Claims

1. A method of rollbacking a transaction id set in a plurality of tables of an active database, each of the plurality of tables having a before image table and an after image table, the method comprising:

retrieving an earliest starting time of the transaction id set;

retrieving a latest retention time for each of the plurality of tables;

determining that the earliest starting time is greater than the latest retention time;

locking each of the plurality of tables to be rollbacked and disabling associated referential integrity constraints;

deleting rows from each of the after image tables having an after image table transaction id that is a member of the transaction id set;

selecting earliest before image rows from each of the before image tables having a before image table transaction id that is a proper subset of the transaction id set, or having a changing transaction id that is a proper subset of the transaction id set, and having an earliest timestamp;

deleting any after image of a row from each of the after image tables when the after image table transaction id of the row is a proper subset of the transaction id set and a changing transaction id of the row is not equal to a current transaction id;

deleting any after image of a row from each of the after image tables and inserting the earliest before image from the row into each of the after image tables when a transaction id of the row is not a proper subset of the transaction id set and the changing transaction id is a proper subset of the transaction id set; and

activating the associated referential integrity constraints.

2. A method of rollbacking a transaction id set in a plurality of tables of an active database, each of the plurality of tables having a before image table and an after image table, the method comprising:

retrieving an earliest starting time of the transaction id set;

retrieving a latest retention time for each of the plurality of tables;

determining that the earliest starting time is greater than the latest retention time;

locking each of the plurality of tables to be rollbacked and disabling associated referential integrity constraints;

deleting rows from each of the after image tables having an after image table transaction id that is a member of the transaction id set;

selecting before image rows and any descendants thereof from each of the before image tables having a before image table transaction id that is a proper subset of the transaction id set or having a changing transaction id that is a proper subset of the transaction id set;

grouping the selected before image rows into families;

removing any of the families having an earliest transaction id that is a proper subset of the transaction id set;

inserting an earliest returned before image in a family into each of the after image tables when the last changing transaction id of the family is equal to a current transaction id;

inserting an earliest returned before image in a family into each of the after image tables when the last changing transaction id of the family is a proper subset of the transaction id set and if no after image of the family exist; and

activating the associated referential integrity constraints.

3. A method of rollbacking transactions made in a user session in a plurality of tables of an active database, each of the plurality of tables having a before image table and an after image table, the method comprising:

retrieving each of a starting time, an ending time, and a unigue session id of the user session;

retrieving a latest retention time for each of the plurality of tables;

determining that the starting time is greater than the latest retention time of each of the plurality of tables;

locking each of the plurality of tables to be rollbacked and disabling associated integrity constraints;

deleting rows from each of the after image tables having a unique session id that is equal to the user session'"'"'s unique session id;

selecting before image rows, having an earliest timestamp, from each of the before image tables having a unique session id that is equal to the unique session id of the user session, or having a changing unique session id that is equal to the user session'"'"'s unique session id;

deleting any after image of a row from each of the after image tables when a unique session id of the row is equal to the user session'"'"'s unique session id and a changing unique session id of the row is not equal to a current session'"'"'s unique session id;

deleting any after image of a row from each of the after image tables and inserting a before image from the row into each of the after image tables if the unique session id of the row is not equal to the user session'"'"'s unique session id set and the changing unique session id is equal to the user session'"'"'s unique session id; and

activating the associated integrity constraints.

4. The method according to claim 3, wherein the unique session id comprises a session id and a starting timestamp of the session.

5. The method according to claim 3, wherein the unique session id comprises a unique identifier of the session provided by a database vendor.

6. A method of rollbacking transactions made in a user session in a plurality of tables of an active database, each of the plurality of tables having a before image table and an after image table, the method comprising:

retrieving each of a starting time, an ending time, and a unigue session id of the user session;

retrieving a latest retention time for each of the plurality of tables;

determining that the starting time is greater than the latest retention time of each of the plurality of tables;

locking each of the plurality of tables to be rollbacked and disabling associated integrity constraints;

deleting rows from each of the after image tables whose unique session id is equal to the unique session id of the user session;

selecting before image rows and any descendants thereof from each of the before image tables whose unique session id is equal to the unique session id of the user session or having a changing unique session id that is equal to the unique session id of the user session;

grouping the selected rows into families;

removing any families having an earliest unique session id that is equal to the unique session id of the user session;

inserting an earliest returned before image in a family into each of the after image tables when a last changing unique session id of the family is equal to a unique session id of a current session;

inserting an earliest returned before image in a family into each of the after image tables when the last changing unique session id of the family is equal to a unique session id of the user session and if no after image of the family exists; and

activating the associated integrity constraints.

7. The method according to claim 6, wherein the unique session id comprises a unique identifier of the session provided by a database vendor.

8. The method according to claim 6, wherein the unique session id comprises a session id and a starting timestamp of the session.