Method and mechanism for identifying transaction on a row of data

• US 8,650,169 B1
• Filed: 02/10/2003
• Issued: 02/11/2014
• Est. Priority Date: 09/29/2000
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Jacobs, Kenneth R., Ganesh, Amit, Klein, Jonathan, Johnson, Archna Kalra, Raja, Vivekanandhan
• Primary Examiner(s): Alam, Hosain
• Assistant Examiner(s): HARPER, ELIYAH STONE
• Application Number:
  US10/364,209
• Time in Patent Office: 4,019 Days
• Field of Search: 707/9, 707/103.R, 707/201, 707/2, 707/3, 707/4, 707/5, 707/100, 707/104.1, 707/10
• US Class Current: 707/704
• 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 for identifying one or more transactions that have modified or created an object in a database system. Also disclosed is a method and mechanism to compensate for the effects of a transaction in a database system is disclosed. The disclosed approach can be applied to perform auditing for changes and/or transactions in a database system and for identifying the scope of changes and data accesses made by a transaction in the system.

Claims

1. A method for identifying a transaction that accessed a data item of interest in a database system, comprising:

identifying the data item from a database table in the database system;

identifying a primary key of the database table which uniquely identifies a row containing the data item, in which a committed transaction previously performed a data access on the data item, and the committed transaction constitutes the transaction to be identified;

using the primary key to identify the committed transaction from a log record comprising information relating to one or more committed transactions, each of which previously performed at least one data access on the database system containing the data item, wherein the committed transaction is identified by;

using the primary key to determine a storage unit corresponding to the data item;

accessing the log record associated with the storage unit; and

identifying one of the one or more committed transactions based upon a result from the act of determining the storage unit and the act of accessing the log record; and

displaying a result of the act of using the primary key to identify the committed transaction from a log record or storing the result in a tangible computer usable medium.

2. The method of claim 1 in which the storage unit comprises a database block in which the data item is stored.

3. The method of claim 1 in which the log record comprises a transactions list.

4. The method of claim 3 in which the transactions list is associated with multiple rows of data.

5. The method of claim 3 in which the transactions list is associated with a single row of data.

6. The method of claim 1 further comprising stepping backwards through prior versions of the storage unit until a change is identified for the data item.

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

8. The method of claim 1, further comprising compiling a list of committed transactions that have accessed the data item.

9. The method of claim 8 in which the last N transactions are listed for the data item.

22. The method of claim 1, wherein the result comprises the information from the log record.

25. The method of claim 1, further comprising:

determining whether a critical dependency exists between the committed transaction identified and a second committed transaction with respect to the data item, in which the critical dependency affects the data item identified.

26. The method of claim 25, further comprising:

compensating for the data access to the data item performed by the committed transaction.

27. The method of claim 25, further comprising:

determining whether to execute an immediate abort.

28. The method of claim 25, further comprising:

determining whether the critical dependency is resolvable.

29. The method of claim 1, in which the critical dependency exists for the committed transaction where correctness of a subsequent transaction for the database system depends upon the data access performed by the committed transaction.

30. The method of claim 1, in which the second committed transaction is determined by selecting a latest committed transaction by accessing information contained in the log record relating to one or more committed transactions.

31. The method of claim 1, in which the log record comprises a list of one or more committed transactions, and the second log record comprises data access performed by the one or more committed transactions.

32. The method of claim 1, in which the second committed transaction constitutes the first committed transaction where the verifying whether the data access performed by the second committed transaction was performed on the data item succeeds.

33. The method of claim 1, further comprising:

applying information contained in the second log record to the database system.

35. The method of claim 1, in which the log record comprises a first chain linking the committed transaction to a second committed transaction of the one or more committed transactions.

36. The method of claim 1, in which the log record further comprises:

verifying whether the at least one data access was performed on the data item of interest by comparing a second information in a second log record with the data item of interest.

10. A computer program product comprising a computer storage medium, said computer storage medium comprising a non-volatile media or a volatile media, and having executable code to execute a process for identifying a transaction that accessed a data item of interest in a database system, the process comprising the acts of:

identifying the data item from a database table in the database system;

identifying a primary key of the database table which uniquely identifies a row containing the data item in which a committed transaction previously performed a data access on the data item, and the committed transaction constitutes the transaction to be identified;

using the primary key to identify the committed transaction from a log record comprising information relating to one or more committed transactions, each of which previously performed at least one data access on the database system containing the data item, wherein the committed transaction is identified by;

using the primary key to determine a storage unit corresponding to the data item;

accessing the log record associated with the storage unit; and

identifying one of the one or more committed transactions based upon a result from the act of determining the storage unit and the act of accessing the log record.

14. The computer program product of claim 10, wherein the process further comprises stepping backwards through prior versions of the storage unit until a change is identified for the data item.

15. The computer program product of claim 10, wherein the process further comprises compiling a list of committed transactions that have accessed the data item.

23. The computer program product of claim 10, wherein the result comprises the information from the log record.

42. The computer program product of claim 10, in which the log record further comprises:

a second chain linking the committed transaction to a third committed transaction of the one or more committed transactions which did not previously perform the at least one data access on the data item.

43. The computer program product of claim 10, in which the act of identifying the committed transaction from a transaction list of the process further comprises:

verifying whether the at least one data access was performed on the data item of interest by comparing a second information in a second log record with the data item of interest.

11. A system for identifying a transaction that accessed a data item of interest in a database system, comprising:

means for identifying the data item from a database table in the database system;

means for identifying a primary key of the database table which uniquely identifies a row containing the data item in which a committed transaction previously performed a data access on the data item, and the committed transaction constitutes the transaction to be identified;

a processor configured for using the primary key to identify a second committed transaction from a log record comprising information relating to one or more committed transactions, each of which previously performed at least one data access on the database system containing the data item;

wherein the means for using the primary key to identify the committed transaction comprises;

means for using the primary key to determine a storage unit corresponding to the data item;

means for accessing the log record associated with the storage unit; and

means for identifying one of the one or more committed transactions based upon a result from the act of determining the storage unit and the act of accessing the log record; and

a display apparatus configured for displaying a result of the act of using the primary key to identify the committed transaction from a log record or a computer usable medium configured for storing the result in a tangible computer usable medium.

16. The method of claim 11, further comprises means for stepping backwards through prior versions of the storage unit until a change is identified for the data item.

17. The method of claim 11, further comprises means for compiling a list of committed transactions that have accessed the data item.

24. The system of claim 11, wherein the result comprises the information from the log record.

12. A method for identifying a transaction that accessed a data item of interest of a database in a database system, comprising:

identifying the data item of a database in the database system, in which the data item was previously accessed by a committed transaction;

identifying the committed transaction from a transaction list comprising information relating to one or more committed transactions each of which previously performed at least one data access on the database containing the data item, wherein the committed transaction is identified by;

identifying a database block of the database associated with the data item;

after the database block is identified, accessing the transaction list associated with the database block; and

wherein the act of accessing the transaction list comprises accessing an entry in the transaction list to identify one of the one or more committed transactions that previously performed the at least one data access on the database system containing the data item; and

determining whether a critical dependency exists between the committed transaction and a second committed transaction of the one or more committed transactions with respect to the data item, in which the critical dependency affects correctness of the data item identified.

13. The method of claim 12 in which the database block is rolled back to a prior state to allow identification of the committed transaction.

34. The method of claim 12, further comprising:

applying information contained in the log record to the data item.

37. The method of claim 12, in which the act of identifying the committed transaction from a transaction list further comprises:

verifying whether the at least one data access was performed on the data item of interest by comparing a second information in a second log record with the data item of interest.

18. A computer program product comprising a computer storage medium, said computer storage medium comprising a non-volatile media or a volatile media, and having executable code to execute a process for identifying a transaction that accessed a data item of interest in of a database a database system, the process comprising the steps of:

identifying the data item of the database in the database system, in which the data item was previously accessed by a committed transaction;

identifying the committed transaction from a transaction list comprising information relating to one or more committed transactions each of which previously performed at least one data access on the database containing the data item, wherein the committed transaction is identified by;

identifying a database block of the database associated with the data item; and

after the database block is identified, accessing the transaction list associated with the database block,

wherein the act of accessing the transaction list comprises accessing an entry in the transaction list to identify the committed transaction from the one or more committed transactions that previously performed the at least one data access on the database containing the data item; and

determining whether a critical dependency exists between the committed transaction and a second committed transaction of the one or more committed transactions with respect to the data item, in which the critical dependency affects correctness of the data item identified.

19. The computer program product of claim 18, wherein the process further comprises rolling back the database block to a prior state to allow identification of the committed transaction.

38. The computer program product of claim 18, in which the act of identifying the committed transaction from a transaction list of the process further comprises:

verifying whether the at least one data access was performed on the data item of interest by comparing a second information in a second log record with the data item of interest.

39. The computer program product of claim 18, in which the transaction list further comprises:

a second chain linking the committed transaction to a third committed transaction of the one or more committed transactions which did not previously perform the at least one data access on the data item.

20. A system for identifying a transaction that accessed a data item of interest of a database in a database system, comprising:

means for identifying the data item of the database in the database system, in which the data item was previously accessed by a committed transaction;

means for identifying the committed transaction from a transaction list comprising information relating to one or more committed transactions each of which previously performed at least one data access on the database containing the data item, wherein the means for identifying the committed transaction comprises;

means for identifying a database block of the database associated with the data item; and

means for accessing the transaction list associated with the database block after the database block is identified,

wherein the means for accessing comprises means for reviewing an entry in the transaction list to identify the committed transaction from the one or more the committed transaction that previously performed the at least one data access on the database containing the data item; and

means for determining whether a critical dependency exists between the committed transaction and a second committed transaction of the one or more committed transactions with respect to the data item, in which the critical dependency affects correctness of the data item identified.

21. The system of claim 20, further comprising means for rolling back to a prior state to allow identification of the committed transaction.

40. The system of claim 20, in which the means for identifying the committed transaction from a transaction list further comprises:

means for verifying whether the at least one data access was performed on the data item of interest by comparing a second information in a second log record with the data item of interest.

41. The system of claim 20, in which the transaction list further comprises:

a second chain linking the committed transaction to a third committed transaction of the one or more committed transactions which did not previously perform the at least one data access on the data item.