Controlling visibility in multi-version database systems

• US 7,305,386 B2
• Filed: 08/22/2003
• Issued: 12/04/2007
• Est. Priority Date: 09/13/2002
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: Netezza Corp (International Business Machines Corporation)
• Inventors: Hinshaw, Foster D., Harris, Craig S., Sarin, Sunil K.
• Primary Examiner(s): Cottingham; John
• Assistant Examiner(s): LOVEL, KIMBERLY M
• Application Number:
  US10/646,522
• Publication Number:
  US 20040249838A1
• Time in Patent Office: 1,565 Days
• Field of Search: 707/4, 707/100, 707/10
• US Class Current: 707/684
• CPC Class Codes: G06F 16/2329 using versioning
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A multi-version database system controls visibility of data during transaction processing. A transaction includes a transaction identifier that identifies the transaction and an invisibility list of transactions whose effects are invisible to the transaction. Changes made by other transactions are visible to the transaction based on the isolation level of the transaction. Records are visible to a transaction based on a creator identifier stored in the record that identifies the transaction that created the record.

Claims

1. A method for controlling visibility of data during transaction processing in a multi-version database management system, comprising:

receiving a request for a record stored in a multi-version database from a requesting transaction, the requesting transaction having an associated transaction identifier which uniquely identifies the requesting transaction, a transaction invisibility list which identifies other transactions whose effects are to be invisible to the requesting transaction, and an isolation level which describes whether changes made by other transactions are to be visible to the requesting transaction;

wherein the transaction identifier is a numeric value and transaction identifiers are assigned to transactions in increasing numerical order based on a start time of the transaction, such that a first transaction can be determined to start before a second transaction if the transaction identifier associated with the first transaction is numerically less than the transaction identifier associated with the second transaction; and

wherein transaction identifiers associated with transactions that operate in the present have an even numeric value, and transaction identifiers associated with transactions operating “

as-of”

a specified “

as-of”

time in the past have an odd numeric value;

wherein the specified “

as-of”

time is a time in the past that, when used as a argument in an “

as-of”

query, produces the same results as would have been produced if a query occurred in the past at the specified “

as-of”

time;

determining whether the record is visible to the requesting transaction based on the isolation level of the requesting transaction, the transaction identifier, the transaction invisibility list of the requesting transaction, and a creator transaction identifier in the requested record which identifies a transaction that created the record;

assigning a Record ID value to the record when the record is first created, the Record ID uniquely distinguishing the record from all other records, and the Record ID preserved across modifications of the record;

finding a transaction identifier for an earliest transaction that started on or after the specified “

as-of”

time;

creating a new transaction, the new transaction having a start-time equal to the specified “

as-of”

time, a transaction identifier equal to the transaction identifier for the earliest transaction, minus one, and an isolation level set to Read Committed;

initializing an invisibility list of the new transaction to include the transaction identifiers of all transactions having transaction identifiers less than the transaction identifier for the new transaction and end-times greater than the specified “

as-of”

time;

removing from the invisibility list of the new transaction, any transactions serialized before transactions visible to the new transaction; and

adding to a visibility list of the new transaction, any transactions with transaction identifiers greater than the transaction identifier of the new transaction, that are serialized before transactions visible to the new transaction.

2. The method of claim 1 wherein the requesting transaction further comprises an associated visibility list, the visibility list including numeric transaction identifiers which identify other transactions whose effects are to be visible to the requesting transaction.

3. The method of claim 2 further comprising:

removing a transaction identifier of a committing transaction from an invisibility list of a specified transaction operating with Read Committed isolation, if the transaction identifier of the committing transaction is less than the transaction identifier of the specified transaction; and

adding the transaction identifier of the committing transaction to the visibility list of the specified transaction, if the transaction identifier of the committing transaction is greater than the transaction identifier of the specified transaction.

4. The method of claim 3 further comprising:

retrieving visible records for the specified transaction operating with Read Committed isolation, comprising;

reading a record from the database;

ensuring that either (a) the creator transaction identifier of the record is equal to the transaction identifier of the requesting transaction, or (b) the creator transaction identifier of the record is greater than the transaction identifier of the requesting transaction and the creator transaction identifier is on the visibility list of the requesting transaction or (c) the creator transaction identifier of the record is less than the transaction identifier of the requesting transaction and the creator transaction identifier of the record is not on the invisibility list of the requesting transaction;

ensuring that the deleter transaction identifier of the record is either (a) a NULL transaction identifier;

or (b) less than the transaction identifier of the requesting transaction and on the invisibility list of the requesting transaction or (c) greater than the transaction identifier of the requesting transaction and not on the visibility list of the requesting transaction; and

ensuring that the list of deleted records does not include a description of the record, or that record was deleted by a transaction whose transaction identifier was either (a) less than the transaction identifier of the requesting transaction and also on the invisibility list of the requesting transaction or (b) greater than the transaction identifier of the requesting transaction and not on the visibility list of the requesting transaction.

5. The method of claim 4 further comprising:

avoiding unrepeatable reads comprising;

tracking versions of records retrieved during a transaction;

checking if a different version of a record previously retrieved is subsequently retrieved; and

returning the version of the record previously retrieved or aborting the transaction.

6. The method of claim 1 additionally comprising:

initializing the invisibility list of the new transaction by;

searching a list of existing transactions to find transactions whose transaction identifier is less than the transaction identifier of the new transaction, and whose state is an active state; and

inserting the transaction identifiers of such existing transactions into the invisibility list of the new transaction.

7. The method of claim 1 further comprising:

allowing a transaction to delete a record, by storing the transaction identifier of the transaction deleting the record in a deleter transaction identifier field of the record.

8. The method of claim 1 further comprising:

allowing a transaction to delete a record, by adding a deletion descriptor to a list of deleted records, the deletion descriptor including the transaction identifier of the transaction performing the deletion and information uniquely identifying the record.

9. The method of claim 1 further providing the ability for a transaction to modify an existing record, comprising:

creating a new record;

copying fields from the existing record to the new record;

storing a transaction identifier identifying the transaction in a creator-transaction-identifier field of the new record;

copying updated data field values in the existing record into corresponding fields in the new record;

storing the transaction identifier of the transaction in a deleter transaction identifier field of the existing record;

adding a deletion descriptor to a list of deleted records if the deleter transaction identifier field is not present, the deletion descriptor identifying the transaction and the existing record; and

storing a NULL transaction identifier value of the transaction performing the modification in a deleter transaction identifier field of the new record.

10. The method of claim 1 further comprising:

retrieving records visible to a transaction operating with Read Uncommitted isolation comprising;

reading a record from the database;

ensuring that the deleter transaction identifier of the record is a NULL transaction identifier; and

ensuring that a list of deleted records does not include a description of the record.

11. The method of claim 1 wherein the step of determining whether the record is visible to the requesting transaction is carried out in a programmable filter.

12. The method of claim 11 wherein the programmable filter is implemented as a circuit component selected from a group consisting of Field Programmable Gate Array (FPGA), Application Specific Integrated Circuit (ASIC), Application Specific Standard Product (ASSP), discrete logic in a printed circuit board, and an programmable micro-processor.

13. The method of claim 1 further comprising:

retrieving a version of a record visible to a requesting transaction without reference to other versions of the record.

14. A method for controlling visibility of data during transaction processing in a multi-version database management system, comprising:

receiving a request for a record stored in the multi-version database from a requesting transaction, the requesting transaction having an associated transaction identifier which uniquely identifies the transaction, a transaction invisibility list which identifies other transactions whose effects are to be invisible to the requesting transaction, and an isolation level which describes whether changes made by other transactions are to be visible to the transaction;

determining whether the record is visible to the requesting transaction based on the isolation level of the requesting transaction, the transaction identifier, the invisibility list of the requesting transaction, and a creator transaction identifier in the requested record which identifies a transaction that created the record; and

rolling back changes of a transaction, comprising;

examining all records created, updated or deleted by the transaction;

storing an aborted transaction identifier in a deleter transaction identifier field of records created by the requesting transaction, the aborted transaction identifier being less than a numeric value of any other non-NULL transaction identifier;

storing a deletion descriptor in a list of deleted records the deletion descriptor including the aborted transaction identifier and information uniquely identifying an associated version of the record;

storing a NULL transaction identifier in the deleter transaction identifier field of records deleted by the requesting transaction;

removing the deletion descriptor from the list of deleted records, the deletion descriptor including the identifier of the requesting transaction; and

writing records with a modified deleter transaction identifier field to a persistent storage device.

15. The method of claim 14 wherein the examining step comprises:

starting the examination at a low-water-mark record and ending the examination at a high-water-mark record for a sequential set of records, the low-water-mark identifying a first record in the sequential set of records created, updated or deleted by the requesting transaction, and the high-water-mark identifying a last record in the sequential set of records created, updated or deleted by the requesting transaction.

16. The method of claim 14 further comprising:

performing online recovery support for transaction processing, comprising;

determining a set of transactions that had started, but had neither committed nor aborted at a time the database had previously stopped operating;

rolling back changes made to records for each such incomplete transaction; and

including the transaction identifiers of each such incomplete transaction on an invisibility list of all new transactions started before the incomplete transactions have been rolled back.

17. The method of claim 14 further comprising:

creating a new record by;

obtaining a unique Record ID for the new record;

storing the transaction identifier of the transaction creating the new record and the Record ID in the new record; and

storing a NULL transaction identifier value in a deleter transaction identifier field of the new record.

18. A method for controlling visibility of data during transaction processing in a multi-version database management system, comprising:

receiving a request for a record stored in the multi-version database from a requesting transaction, the requesting transaction having an associated transaction identifier which uniquely identifies the transaction, a transaction invisibility list which identifies other transactions whose effects are to be invisible to the requesting transaction, and an isolation level which describes whether changes made by other transactions are to be visible to the transaction;

determining whether the record is visible to the requesting transaction based on the isolation level of the requesting transaction, the transaction identifier of the requesting transaction, the invisibility list of the requesting transaction, and a creator transaction identifier in the requested record which identifies a transaction that created the record; and

retrieving visible records for a requesting transaction operating with Repeatable Read isolation comprising;

reading a record from the database;

ensuring that the creator transaction identifier of the record is not on the invisibility list of the requesting transaction and has a value less than or equal to the transaction identifier of the requesting transaction;

ensuring that a deleter transaction identifier of the record is a NULL transaction identifier, or is greater than the transaction identifier of the requesting transaction, or is both less than the transaction identifier of the requesting transaction and is stored on the invisibility list of the requesting transaction; and

ensuring that a list of deleted records does not include a description of the record, or that the record was deleted by a transaction whose transaction identifier was greater than the transaction identifier of the requesting transaction, or that the record was deleted by a transaction whose transaction identifier is both less than the transaction identifier of the requesting transaction and is stored on the invisibility list of the requesting transaction.

19. The method of claim 18 wherein records that are not visible to the requesting transaction, but that otherwise would meet the restrictions imposed by a database query, are flagged as being invisible and temporarily retained in memory.

20. A computer readable storage medium containing instructions readable by a computer to configure the computer to control visibility of data during transaction processing in a multi-version database management system comprising:

a requesting transaction requesting a record stored in a multi-version database, comprising;

an associated transaction identifier which uniquely identifies the requesting transaction;

wherein the transaction identifier is a numeric value and transaction identifiers are assigned to transactions in increasing numerical order based on a start time of the transaction, such that a first transaction can be determined to start before a second transaction if the transaction identifier associated with the first transaction is numerically less than the transaction identifier associated with the second transaction;

wherein transaction identifiers associated with transactions that operate in the present have an even numeric value, and transaction identifiers associated with transactions operating “

as-of”

a specified “

as-of”

time in the past have an odd numeric value;

wherein the specified “

as-of”

time is a time in the past that, when used as a argument in an “

as-of”

query, produces the same results as would have been produced if a query occurred in the past at the specified “

as-of”

time;

a transaction invisibility list which identifies other transactions whose effects are to be invisible to the requesting transaction; and

an isolation level which describes whether changes made by other transactions are to be visible to the requesting transaction;

a transaction manager which receives a request for a record from the requesting transaction and determines whether the record is visible to the requesting transaction based on the isolation level of the requesting transaction, the transaction identifier, the transaction invisibility list of the requesting transaction, and a creator transaction identifier in the requested record which identifies a transaction that created the record;

a Record ID manager which assigns a Record ID value to the record when the record is first created, the Record ID uniquely distinguishing the record from all other records, and the Record ID preserved across modifications of the record;

wherein the transaction manager finds a transaction identifier for an earliest transaction that started on or after the specified “

as-of”

time, creates a new transaction, the new transaction having a start-time equal to the specified “

as-of”

time, a transaction identifier equal to the transaction identifier for the earliest transaction, minus one, and an isolation level set to Read Committed; and

initializes an invisibility list of the new transaction to include the transaction identifiers of all transactions having transaction identifiers less than the transaction identifier for the new transaction and end-times greater than the specified “

as-of”

time; and

wherein the transaction manager removes from the invisibility list of the new transaction, any transactions serialized before transactions visible to the new transaction and adds to a visibility list of the new transaction, any transactions with transaction identifiers greater than the transaction identifier of the new transaction, that are serialized before transactions visible to the new transaction.

21. The computer readable storage medium of claim 20 wherein the requesting transaction further comprises:

an associated visibility list, the visibility list including numeric transaction identifiers which identify other transactions whose effects are to be visible to the requesting transaction.

22. The computer readable storage medium of claim 21 wherein the transaction manager

removes a transaction identifier of a committing transaction from an invisibility list of a specified transaction operating with Read Committed isolation if the transaction identifier of the committing transaction is less than the transaction identifier of the specified transaction;

and

adds the transaction identifier of the committing transaction to the visibility list of the specified transaction, if the transaction identifier of the committing transaction is greater than the transaction identifier of the specified transaction.

23. The computer readable storage medium of claim 22 wherein to retrieve visible records for the specified transaction operating with Read Committed isolation, the transaction manager

reads a record from the database;

ensures that either (a) the creator transaction identifier of the record is equal to the transaction identifier of the requesting transaction, or (b) the creator transaction identifier of the record is greater than the transaction identifier of the requesting transaction and the creator transaction identifier is on the visibility list of the requesting transaction or (c) the creator transaction identifier of the record is less than the transaction identifier of the requesting transaction and the creator transaction identifier of the record is not on the invisibility list of the requesting transaction;

ensures that the deleter transaction identifier of the record is either (a) a NULL transaction identifier;

or (b) less than the transaction identifier of the requesting transaction and on the invisibility list of the requesting transaction or (c) greater than the transaction identifier of the requesting transaction and not on the visibility list of the requesting transaction; and

ensures that the list of deleted records does not include a description of the record, or that record was deleted by a transaction whose transaction identifier was either (a) less than the transaction identifier of the requesting transaction and also on the invisibility list of the requesting transaction or (b) greater than the transaction identifier of the requesting transaction and not on the visibility list of the requesting transaction.

24. The computer readable storage medium of claim 23 wherein to avoid unrepeatable reads, the transaction manager tracks versions of records retrieved during a transaction, checks if a different version of a record previously retrieved is subsequently retrieved and returns the version of the record previously retrieved or aborting the transaction.

25. The computer readable storage medium of claim 20 wherein to initialize the invisibility list of the new transaction, the transaction manager searches a list of existing transactions to find transactions whose transaction identifier is less than the transaction identifier of the new transaction, and whose state is an active state and inserts the transaction identifiers of such existing transactions into the invisibility list of the new transaction.

26. The computer readable storage medium of claim 20 wherein the transaction manager stores the transaction identifier of the transaction deleting a record in a deleter transaction identifier field of the record to allow a transaction to delete the record.

27. The computer readable storage medium of claim 20 wherein the transaction manager adds a deletion descriptor to a list of deleted records, the deletion descriptor including the transaction identifier of the transaction performing the deletion and information uniquely identifying the record to allow a transaction to delete a record.

28. The computer readable storage medium of claim 20 wherein to modify an existing record, the transaction manager

requests a Query execution manager to create a new record,

copies fields from the existing record to the new record,

stores a transaction identifier identifying the transaction in a creator-transaction-identifier field of the new record,

copies updated data field values in the existing record into corresponding fields in the new record,

stores the transaction identifier of the transaction in a deleter transaction identifier field of the existing record,

adds a deletion descriptor to a list of deleted records if the deleter transaction identifier field is not present, the deletion descriptor identifying the transaction and the existing record, and

stores a NULL transaction identifier value of the transaction performing the modification in a deleter transaction identifier field of the new record.

29. The computer readable storage medium of claim 20 wherein, to retrieve records visible to a transaction operating with Read Uncommitted isolation, the transaction manager reads a record from the database, ensures that the deleter transaction identifier of the record is a NULL transaction identifier and ensures that a list of deleted records does not include a description of the record.

30. The computer readable storage medium of claim 20 further comprising:

a programmable filter which determines whether the record is visible to the requesting transaction.

31. The computer readable storage medium of claim 30 wherein the programmable filter is implemented as a circuit component selected from a group consisting of Field Programmable Gate Array (FPGA), Application Specific Integrated Circuit (ASIC), Application Specific Standard Product (ASSP), discrete logic in a printed circuit board, and an programmable micro-processor.

32. The computer readable storage medium of claim 20 wherein the transaction manager retrieves a version of a record visible to a requesting transaction without reference to other versions of the record.

33. A computer readable storage medium containing instructions readable by a computer to configure the computer to control visibility of data during transaction processing in a multi-version database management system comprising:

a requesting transaction requesting a record stored in a multi-version database comprising;

an associated transaction identifier which uniquely identifies the requesting transaction;

a transaction invisibility list which identifies other transactions whose effects are to be invisible to the requesting transaction; and

an isolation level which describes whether changes made by other transactions are to be visible to the requesting transaction;

a transaction manager which receives a request for a record from the requesting transaction and determines whether the record is visible to the requesting transaction based on the isolation level of the requesting transaction, the transaction identifier, the invisibility list of the requesting transaction, and a creator transaction identifier in the requested record which identifies a transaction that created the record. wherein to roll back changes of a transaction the transaction manager;

wherein to roll back changes of a transaction the transaction manager;

examines all records created, updated or deleted by the transactions;

stores an aborted transaction identifier in a deleter transaction identifier field of records created by the requesting transaction, the aborted transaction identifier is less than a numeric value of any other non-NULL transaction identifier;

stores a deletion descriptor in a list of deleted records the deletion descriptor including the aborted transaction identifier and information uniquely identifying an associated version of the record;

stores a NULL transaction identifier in the deleter transaction identifier field of records deleted by the requesting transaction;

removes the deletion descriptor from the list of deleted records, the deletion descriptor including the identifier of the requesting transaction; and

writes records with a modified deleter transaction identifier field to a persistent storage device.

34. The computer readable storage medium of claim 33 wherein to examine all records, the transaction manager starts the examination at a low-water-mark record and ending the examination at a high-water-mark record for a sequential set of records, the low-water-mark identifying a first record in the sequential set of records created, updated or deleted by the requesting transaction, and the high-water-mark identifying a last record in the sequential set of records created, updated or deleted by the requesting transaction.

35. The computer readable storage medium of claim 33 wherein to perform online recovery support for transaction processing, the transaction manager

determines a set of transactions that had started, but had neither committed nor aborted at a time the database had previously stopped operating,

rolls back changes made to records for each such incomplete transactions, and

includes the transaction identifiers of each such incomplete transaction on an invisibility list of all new transactions started before the incomplete transactions have been rolled back.

36. The computer readable storage medium of claim 33 wherein, to create a new record, a Query Execution manager obtains a unique Record ID for the new record from the RecordID manager, stores the transaction identifier of the transaction creating the new record and the Record ID in the new record and stores a NULL transaction identifier value in a deleter transaction identifier field of the new record.

37. A computer readable storage medium containing instructions readable by a computer to configure the computer to control visibility of data during transaction processing in a multi-version database management system comprising:

a requesting transaction requesting a record stored in a multi-version database comprising;

an associated transaction identifier which uniquely identifies the requesting transaction;

a transaction invisibility list which identifies other transactions whose effects are to be invisible to the requesting transaction; and

an isolation level which describes whether changes made by other transactions are to be visible to the requesting transaction;

a transaction manager which receives a request for a record from the requesting transaction and determines whether the record is visible to the requesting transaction based on the isolation level of the requesting transaction, the transaction identifier of the requesting transaction, the invisibility list of the requesting transaction, and a creator transaction identifier in the requested record which identifies a transaction that created the record;

wherein to retrieve visible records for a requesting transaction operating with Repeatable Read isolation, the transaction manager

reads a record from the database and ensures that the creator transaction identifier of the record is not on the invisibility list of the requesting transaction and has a value less than or equal to the transaction identifier of the requesting transaction,

ensures that a deleter transaction identifier of the record is a NULL transaction identifier, or is greater than the transaction identifier of the requesting transaction, or is both less than the transaction identifier of the requesting transaction and is stored on the invisibility list of the requesting transaction and

ensures that a list of deleted records does not include a description of the record, or that the record was deleted by a transaction whose transaction identifier was greater than the transaction identifier of the requesting transaction, or that the record was deleted by a transaction whose transaction identifier is both less than the transaction identifier of the requesting transaction and is stored on the invisibility list of the requesting transaction.

38. The computer readable storage medium of claim 37 wherein records that are not visible to the requesting transaction, but that otherwise would meet the restrictions imposed by a database query, are flagged as being invisible and temporarily retained in memory.

39. A computer readable storage medium containing instructions readable by a computer to configure the computer to control visibility of data during transaction processing in a multi-version database management system comprising:

means for receiving a request for a record stored in a multi-version database from a requesting transaction, the requesting transaction having an associated transaction identifier which uniquely identifies the requesting transaction, a transaction invisibility list which identifies other transactions whose effects are to be invisible to the requesting transaction, and an isolation level which describes whether changes made by other transactions are to be visible to the requesting transaction;

wherein the transaction identifier is a numeric value and transaction identifiers are assigned to transactions in increasing numerical order based on a start time of the transaction, such that a first transaction can be determined to start before a second transaction if the transaction identifier associated with the first transaction is numerically less than the transaction identifier associated with the second transaction;

wherein transaction identifiers associated with transactions that operate in the present have an even numeric value, and transaction identifiers associated with transactions operating “

as-of”

a specified “

as-of”

time in the past have an odd numeric value;

wherein the specified “

as-of”

time is a time in the past that, when used as a argument in an “

as-of”

query, produces the same results as would have been produced if a query occurred in the past at the specified “

as-off”

time;

means for determining whether the record is visible to the requesting transaction based on the isolation level of the requesting transaction, the transaction identifier, the transaction invisibility list of the requesting transaction, and a creator transaction identifier in the requested record which identifies a transaction that created the record;

means for assigning a Record ID value to the record when the record is first created, the Record ID uniquely distinguishing the record from all other records, and the Record ID preserved across modifications of the record;

means for finding a transaction identifier for an earliest-transaction that started on or after the specified “

as-of”

time;

means for creating a new transaction, the new transaction having a start-time equal to the specified “

as-of”

time;

a transaction identifier equal to the transaction identifier for the earliest transaction, minus one; and

an isolation level set to Read Committed;

means for initializing an invisibility list of the new transaction to include the transaction identifiers of all transactions having transaction identifiers less than the transaction identifier for the new transaction and end-times greater than the specified “

as-of”

time;

means for receiving a request for a record from a requesting transaction, the requesting transaction having an associated transaction identifier which uniquely identifies the transaction, an invisibility list which identifies other transactions whose effects are to be invisible to the requesting transaction, and an isolation level which describes whether changes made by other transactions are to be visible to the transaction; and

means for determining whether the record is visible to the requesting transaction based on the isolation level of the requesting transaction, the transaction identifier, the invisibility list of the requesting transaction, and a creator transaction identifier in the requested record which identifies a transaction that created the record.