Two-pass multi-version read consistency

• US 5,452,445 A
• Filed: 04/30/1992
• Issued: 09/19/1995
• Est. Priority Date: 04/30/1992
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Hallmark, Gary, Bamford, Roger
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Pham, Cuan
• Application Number:
  US07/876,626
• Time in Patent Office: 1,237 Days
• Field of Search: 395/600, 395/550, 395/575, 395/650
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

The present invention describes a method and apparatus that forces all parts of an update transaction to commit automatically using a two-phase commit. The present invention uses the two-phase commit messages to negotiate a system commit number (SCN) that is then recorded on each of the nodes as the logical point in time that the distributed transaction committed. The two-phase commit scheme of the present invention consists of a "prepare" phase and a "commit" phase. The transaction in the distributed database environment involves multiple participants (databases). In the prepare phase, one of the N database participants is selected as the "commit point." The commit point includes the log into which the information is written that states the transaction has committed. The remaining N-1 participants enter a "prepare phase." In the prepare phase, the prepare participants lock their databases and enter a state in which they are ready to roll back or commit to the transaction. The mid-point of the two-phase commit is the point at which the commit point database (nth participant) writes the commit transaction to its log. In the second phase, the remaining participants are informed to commit the transaction and release locks that have been set.

Claims

1. A method for executing a distributed transaction in a distributed database having at least first and second databases comprising the steps of:

generating a first system commit number associated with a first data block to be modified at said first database;

generating a second system commit number associated with a second data block to be modified at said second database;

comparing said first system commit number with said second system commit number;

defining said first system commit number as a transaction system commit number if said first system commit number has a value greater than said second system commit number;

defining said second system commit number as a transaction system commit number if said second system commit number has a value greater than said first system commit number;

providing said transaction system commit number to said first database and said second database associated with said first block of data and said second block of data.

2. The method of claim 1 wherein said distributed transaction is executed in a two phase commit protocol having a prepare phase and a commit phase.

3. The method of claim 2 wherein said first system commit number and said second system commit number are associated with said prepare phase of said transaction.

4. The method of claim 3 wherein said transaction system commit number is associated with said commit phase of said transaction.

5. The method of claim 4 further including the steps of:

initiating a read operation at said first database;

assigning a read operation system commit number to said read operation;

allowing said read operation to read a data block only when said read operation system commit number is greater than a system commit number associated with said data block.

6. The method of claim 5 wherein a system commit number associated with a prepare phase of a transaction is defined as T_p, a system commit number associated with a commit phase of a transaction is defined as T_c, and a system commit number associated with a read operation is defined as T_s.

7. The method of claim 6 wherein a read operation cannot read a data block when T_s <

T_p.

8. The method of claim 7 wherein a read operation waits when T_s ≧

T_p and T_c is unknown.

9. The method of claim 8 wherein a read operation reads a data block when T_s ≧

T_p and T_s ≧

T_c.

10. The method of claim 9 wherein a read operation cannot read a data block when T_s ≧

T_p and T_s <

T_c.

11. An apparatus for executing a distributed transaction in a distributed database having at least first and second databases comprising:

first system commit number generating means, said first system commit number generating means generating a first system commit number associated with a first data block to be modified at said first database;

second system commit number generating means, said second system commit number generating means generating a second system commit number associated with a second data block to be modified at said second database;

comparing means coupled to said first system commit number generating means and said second system commit number generating means, said comparing means comparing said first system commit number with said second system commit number;

controlling means coupled to said comparing means, said controlling means selecting a value for a transaction system commit number equal to the value of said first system commit number if said first system commit number has a value greater than said second system commit number;

said controlling means selecting a value for said transaction system commit number equal to the value of said second system commit number if said second system commit number has a value greater than said first system commit number;

transmission means coupled to said controlling means, said transmission means providing said transaction system commit number to said first database and said second database associated with said first block of data and said second block of data.

12. The apparatus of claim 11 wherein said distributed transaction is executed in a two phase commit protocol having a prepare phase and a commit phase.

13. The apparatus of claim 12 wherein said first system commit number and said second system commit number are associated with said prepare phase of said transaction.

14. The apparatus of claim 13 wherein said transaction system commit number is associated with said commit phase of said transaction.

15. The apparatus of claim 14 further including:

initiating means, said initiating means initiating a read operation at said first database;

said first system commit number generating means, said first system commit number generating a read operation system commit number to said read operation;

said comparing means comparing said read operation system commit number to a system commit number associated with said first data block;

said controlling means allowing said read operation to read said first data block only when said read operation system commit number is greater than said system commit number associated with said first data block.

16. The apparatus of claim 15 wherein a system commit number associated with a prepare phase of a transaction is defined as T_p, a system commit number associated with a commit phase of a transaction is defined as T_c, and a system commit number associated with a read operation is defined as T_s.

17. The apparatus of claim 16 wherein a read operation cannot read a data block when T_s <

T_p.

18. The apparatus of claim 17 wherein a read operation waits when T_s ≧

T_p and T_c is unknown.

19. The apparatus of claim 18 wherein a read operation reads a data block when T_s ≧

T_p and T_s ≧

T_c.

20. The apparatus of claim 19 wherein a read operation cannot read a data block when T_s ≧

T_p and T_s <

T_c.