Method and mechanism for rolling back a transaction on a row of data

• US 7,277,900 B1
• Filed: 02/10/2003
• Issued: 10/02/2007
• Est. Priority Date: 09/29/2000
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ganesh, Amit, Klein, Jonathan, Jacobs, Kenneth R., Johnson, Archna Kalra, Raja, Vivekanandhan
• Primary Examiner(s): LE, DEBBIE M
• Application Number:
  US10/364,065
• Time in Patent Office: 1,695 Days
• Field of Search: 707/9, 707/103.R, 707/201, 707/1
• US Class Current: 707/682
• CPC Class Codes: G06F 16/2477 Temporal data queries
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A method and mechanism to compensate for the effects of a transaction in a database system is disclosed. Also disclosed is a method and mechanism for identifying one or more transactions that have modified or created an object in a database system. The disclosed approach can be applied to perform auditing for queries in a database system and for identifying the scope of changes and data accesses made by a transaction in the system.

Claims

1. A computer implemented method to compensate for changes to data in a database system made by a committed transaction, comprising:

identifying a data item accessed by the committed transaction;

identifying the committed transaction using the identified data item;

using the identified committed transaction to identify one or more other data items accessed by the committed transaction;

determining whether a dependency exists with respect to another committed transaction based upon compensating for any access made by the committed transaction to the data item or the one or more other data items; and

compensating for an access made by the committed transaction to the data item or the one or more other data items, wherein the access made by the committed transaction is compensated by performing a roll-back operation.

2. The method of claim 1 in which the action of identifying a data item comprises:

identifying a primary key for the data item; and

using the primary key to locate the data item in the database system.

3. The method of claim 1 in which the act of identifying the committed transaction based upon first identifying the data item comprises:

accessing a log record to identify the committed transaction, the log record tracking one or more transactions that access the data item.

4. The method of claim 3 in which the log record corresponds to a plurality of rows in the database system.

5. The method of claim 1 in which the act of identifying the committed transaction based upon first identifying the data item comprises:

locating a storage unit corresponding to the data item; and

stepping backwards through prior versions of the storage unit until a change is identified for the data item.

6. The method of claim 5 in which undo records are applied to perform the act of stepping backwards.

7. The method of claim 1 in which the transaction is not identified based upon a commit time.

8. The method of claim 1 in which the one or more other data items were identified by:

maintaining linked log records for transactions in the database system;

identifying a first log record for the committed transaction;

following links associated with the first log record identify other log records for the committed transaction; and

accessing the other log records for the committed transaction to identify the one or more other data items.

9. The method of claim 1 in which the act of determining whether the dependency exists comprises:

determining whether the another committed transaction has performed an intervening access to the data item.

10. The method of claim 9 in which the act of determining whether the another committed transaction has accessed the data item comprises:

determining whether the committed transaction is the latest transaction to access the data item or the one or more other data items.

11. The method of claim 9 in which an abort occurs if the another committed transaction has accessed the data item.

12. The method of claim 9 in which the another committed transaction is rolled back.

13. The method of claim 12 in which an ordering exists for rolling back transactions, in which a later transaction is rolled back prior to an earlier transaction.

14. The method of claim 1 in which the act of compensating for the access comprises rolling back changes associated with the access.

15. A computer implemented method to compensate for changes to data in a database system made by a committed transaction, comprising:

committing a transaction;

determining whether a critical dependency exists for the committed transaction, wherein the critical dependency comprises an intervening transaction; and

without restoring an entire database archive, rolling back changes made by the committed transaction based at least in part on a result of the act of determining, thereby compensating for the changes to the data.

16. The method of claim 15 in which the act of rolling back the changes comprises:

identifying the transaction to roll back; and

identifying the changes to roll back for the transaction.

17. The method of claim 16 in which a primary key is used to identify the transaction to roll back.

18. The method of claim 16 in which a log record is accessed to identify the transaction.

19. The method of claim 15 in which the intervening transaction is rolled back.

20. The method of claim 19 in which an ordering exists for rolling back transactions, in which a later transaction is rolled back prior to an earlier transaction.

41. The computer implemented method of claim 15, wherein the changes made by the committed transaction is rolled back if no critical dependency exists for the committed transaction.

42. The computer implemented method of claim 15, if the critical dependency is determined to exist, further comprising determining whether the critical dependency is resolvable.

43. The computer implemented method of claim 42, wherein the changes are rolled back based at least in part on a result of the act of determining whether the critical dependency is resolvable.

21. A computer program product comprising a tangible computer usable medium having executable code to execute a process on a computer to compensate for changes to data in a database system made by a committed transaction, the process comprising the steps of:

committing a transaction;

determining whether a critical dependency exists for the committed transaction, wherein the critical dependency comprises an intervening transaction; and

without restoring an entire database archive, rolling back changes made by the committed transaction based at least in part on a result of the act of determining, thereby compensating for the changes to the data.

25. The computer program product of claim 21 in which the act of rolling back the changes comprises:

identifying the transaction to roll back; and

identifying the changes to roll back for the transaction.

26. The computer program product of claim 25 in which a primary key is used to identify the transaction to roll back.

27. The computer program product of claim 25 in which a log record is accessed to identify the transaction.

28. The computer program product of claim 21 in which the intervening transaction is rolled back.

29. The computer program product of claim 28 in which an ordering exists for rolling back transactions, in which a later transaction is rolled back prior to an earlier transaction.

44. The computer program product of claim 21, wherein the changes made by the committed transaction is rolled back if no critical dependency exists for the committed transaction.

45. The computer program product of claim 21, if the critical dependency is determined to exist, the process further comprising determining whether the critical dependency is resolvable.

46. The computer program product of claim 45, wherein the changes are rolled back based at least in part on a result of the act of determining whether the critical dependency is resolvable.

22. A system to compensate for changes to data in a database system made by a committed transaction, comprising:

means for committing a transaction;

means for determining whether a critical dependency exists for the committed transaction, wherein the critical dependency comprises an intervening transaction; and

means for rolling back changes made by the committed transaction without restoring an entire database archive based at least in part on a result of the act of determining, thereby compensating for the changes to the data.

30. The system of claim 22 in which the means for rolling back the changes is configured to identify the transaction to roll back, and identify the changes to roll back for the transaction.

47. The system of claim 22, wherein the means for rolling back changes is configured to roll back the changes made by the committed transaction if no critical dependency exists for the committed transaction.

48. The system of claim 22, further comprising means for determining whether the critical dependency is resolvable.

23. A computer program product comprising a tangible computer usable medium having executable code to execute a process to compensate for changes to data in a database system made by a committed transaction, the process comprising the steps of:

identifying a data item accessed by the committed transaction;

identifying the committed transaction using the identified data item;

using the identified committed transaction to identify one or more other data items accessed by the committed transaction;

determining whether a dependency exists with respect to another committed transaction based upon compensating for any access made by the committed transaction to the data item or the one or more other data items; and

compensating for an access made by the committed transaction to the data item or the one or more other data items, wherein the access made by the committed transaction is compensated by performing a roll-back operation.

31. The computer program product of claim 23 in which the action of identifying a data item comprises:

identifying a primary key for the data item; and

using the primary key to locate the data item in the database system.

32. The computer program product of claim 23 in which the act of identifying the committed transaction based upon first identifying the data item comprises:

accessing a log record to identify the committed transaction, the log record tracking one or more transactions that access the data item.

33. The computer program product of claim 23 in which the act of identifying the committed transaction based upon first identifying the data item comprises:

locating a storage unit corresponding to the data item; and

stepping backwards through prior versions of the storage unit until a change is identified for the data item.

34. The computer program product of claim 23 in which the transaction is not identified based upon a commit time.

35. The computer program product of claim 23 in which the one or more other data items were identified by:

maintaining linked log records for transactions in the database system;

identifying a first log record for the committed transaction;

following links associated with the first log record identify other log records for the committed transaction; and

accessing the other log records for the committed transaction to identify the one or more other data items.

36. The computer program product of claim 23 in which the act of determining whether the dependency exists comprises:

determining whether the another committed transaction has performed an intervening access to the data item.

37. The computer program product of claim 23 in which the act of compensating for the access comprises rolling back changes associated with the access.

24. A system to compensate for changes to data in a database system made by a committed transaction, comprising:

means for identifying a data item accessed by the committed transaction;

means for identifying the committed transaction using the identified data item;

means for using the identified committed transaction to identify one or more other data items were accessed by the committed transaction;

means for determining whether a dependency exists with respect to another committed transaction based upon compensating for any access made by the committed transaction to the data item or the one or more other data items; and

means for compensating for an access made by the committed transaction to the data item or the one or more other data items, wherein the means for compensating is configured to perform a roll-back operation.

38. The system of claim 24 in which the means for identifying a data item is configured to:

identify a primary key for the data item; and

use the primary key to locate the data item in the database system.

39. The system of claim 24 in which the means for identifying the committed transaction based upon first identifying the data item is configured to access a log record to identify the committed transaction, the log record tracking one or more transactions that access the data item.

40. The system of claim 24 in which the means for identifying the committed transaction based upon first identifying the data item is configured to:

locate a storage unit corresponding to the data item; and

step backwards through prior versions of the storage unit until a change is identified for the data item.