Method for managing distributed savepoints across multiple DBMS's within a distributed transaction

• US 20020174108A1
• Filed: 05/15/2001
• Published: 11/21/2002
• Est. Priority Date: 05/15/2001
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Cotner, Curt L., Pickel, James W., Watts, Julie A.
• Granted Patent: US 6,816,873 B2
• Time in Patent Office: Days
• US Class Current: 707/3
• CPC Class Codes: G06F 11/1474 in transactions G06F16/20 takes precedence
• CPC Class Codes: G06F 16/2322 using timestamps
• 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: G06F 2201/80 Database-specific techniques
• 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

Abstract

System, method, and program product for managing transactions on a plurality of Database Management Systems residing on separate participant servers and a coordinator server, A single transaction proceeds by multiple steps across more then one of the plurality of Database Management Systems as participants. Savepoints are assigned to the participants across the Database Management System, with at least one savepoint within a multiple step transaction. The DBMS responds to application requests to rollback to savepoint by rolling back the transaction, across the Database Management System participants, to the savepoint Further disclosed is a program product that contains code, which, either as written on the medium, or as instantiated in RAM, or both, controls the operation of a distributed database management system.

Claims

1. A method of managing transactions on a plurality of database management systems residing on separate servers and including a coordinator server, wherein a single transaction comprises multiple steps across more then one of said plurality of database management systems as participants, said method comprising:

a) assigning across the database management system participants at least one savepoint within a multiple step transaction;

b) if an application decision to reject a step of the transaction is found, rolling back the transaction across the database management system participants to a savepoint; and

c) when no application decisions to reject the transaction are found, COMMITING the transaction across the multiple database management system participants.

2. The method of claim 1 wherein COMMITING the transaction releases all savepoints.

3. The method of claim 1 further comprising establishing and managing the savepoints by the method of:

a) assigning savepoint identifiers to savepoints;

b) sending a message from the coordinator server to the multiple database management system participants in the transaction identifying the savepoint and providing savepoint information;

c) the database management system participants sending messages to the coordinator server with respect to executing the savepoint and providing savepoint information; and

d) returning result sets to a requester.

4. The method of claim 3 wherein the step of sending a message from the coordinator server to the multiple database management system participants in the transaction identifying the savepoint and providing savepoint information comprises sending a message from the coordinator server to the multiple database system participants at the beginning of the transaction identifying:

1) savepoints that have been rolled back since the database server was last notified;

2) savepoints that are still in effect; and

3) savepoints that have been released.

5. The method of claim 3 wherein the step of sending messages from replying multiple database management system participants to the coordinator server with respect to the savepoint and providing savepoint information comprises sending a message from the multiple database sever participants to the coordinator server at the time of rolling a step of the transaction back to a savepoint, releasing a savepoint, completing a request, or replying to a request, said method comprising identifying:

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect; and

3) savepoints that have been released

6. The method of claim 3 wherein the step of returning an SQL result set to a requester further comprises flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be positioned correctly within savepoints.

7. A method of managing savepoints on a plurality of database management systems residing on separate servers and including a coordinator server, wherein a single transaction comprises multiple steps across more then one of said plurality of database management systems as participants, said method comprising the steps of:

a. assigning across the database management system participants at least one savepoint with savepoint identifiers within the multiple step transaction;

b. sending a message from the coordinator server to the multiple database server participants in the transaction at the beginning of the transaction identifying;

1) savepoints that have been rolled back since the database server participant was last notified;

2) savepoints that are still in effect;

3) savepoints that have been released;

c. sending messages from the multiple database server participants to the coordinator server at the time of doing one of rolling a step in the multiple-step transaction back to a savepoint, releasing a savepoint, completing a request, or replying to a request, said messages identifying;

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect;

3) savepoints that have been released; and

d. returning an SQL result set to a requester, and flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be positioned correctly within the savepoints.

13. The distributed database management system of claim 7 wherein the system is configured and controlled to release savepoints when a transaction is COMMITED.

8. A distributed database management system comprising a plurality of individual database management systems residing on separate servers and including a coordinator server, said distributed database management system configured and controlled to process transactions which comprise multiple steps across more then one of said plurality of database management systems, as participants, by the method comprising:

a) assigning across the database management system participants at least one savepoint within a multiple step transaction; and

b) if an application decision to reject a step of the multiple step transaction is found, rolling back the transaction across the database management system participants to a savepoint.

9. The distributed database management system of claim 8 being further configured and controlled to establish and manage the savepoints by the method of:

a) assigning savepoint identifiers to savepoints;

b) sending a message from the coordinator server to the multiple database management system participants in the transaction identifying the savepoint and providing savepoint information;

c) sending messages from the multiple database management system participants to the coordinator server with respect to executing the savepoint or completing a step in the transaction, and providing savepoint information; and

d) returning a transaction result set to a requester.

10. The distributed database management system of claim 9 being further configured and controlled wherein the step of sending a message from the coordinator server to the multiple database management system participants identifying the savepoint and providing savepoint information comprises sending a message from the coordinator server to the multiple database server participants identifying:

1) savepoints that have been rolled back since the database server was last notified;

2) savepoints that are still in effect; and

3) savepoints that have been released.

11. The distributed database management system of claim 8 wherein the system is configured and controlled to perform the step of sending messages from the multiple database management system participants to the coordinator server with respect to the savepoint and providing savepoint information by sending a message from the multiple database sever participants to the coordinator server at the time of rolling a step of the multi-step transaction back to a savepoint, releasing a savepoint, or replying to a request, identifying:

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect; and

3) savepoints that have been released.

12. The distributed database management system of claim 8 wherein the system is configured and controlled to the perform step of returning an SQL result set to a requester by flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be associated with and positioned correctly within savepoints.

15. The distributed database management system of claim 14 wherein the system is configured and controlled to release all savepoints relating to a sub-transaction when the multi-step transaction is COMMITED.

17. The program product of claim 16 wherein said program product further configures and controls the database management system to establish and manage the savepoints by the method of:

a) assigning savepoint identifiers to savepoints;

b) sending a message from the coordinator server to the multiple database management system participants in the transaction identifying the savepoint and providing savepoint information;

c) the database management system participants sending messages to the coordinator server with respect to executing the savepoint or completing a step in the transaction, and providing savepoint information; and

d) returning a result set to a requester.

18. The program product of claim 17 wherein the step of sending a message from the coordinator server to the multiple database management system participants identifying the savepoint and providing savepoint information comprises sending a message from the coordinator server to the multiple database server participants identifying:

1) savepoints that have been rolled back since the database server was last notified;

2) savepoints that are still in effect; and

3) savepoints that have been released.

19. The program product of claim 17 wherein the step of sending messages from the multiple database management system participants to the coordinator server at the time of rolling the step in the transaction to a savepoint, releasing a savepoint, completing a request, or replying to a request, comprises sending a message from multiple database server participants to the coordinator server identifying:

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect; and

3) savepoints that have been released

20. The program product of claim 17 wherein the system is configured and controlled to perform step of returning an SQL result set to a requester by flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be associated with and positioned correctly within savepoints.

21. The program product of claim 16 wherein the program product configures and controls the system to release savepoints when COMMITING the transaction.

14. A distributed database management system comprising a plurality of individual database management systems residing on separate servers and including a coordinator server, said distributed database management system configured and controlled to process transactions which comprise multiple steps across more then one of said plurality of database management systems, as participants, by the method comprising the steps of:

a. assigning savepoint identifiers to savepoints;

b. sending a message from the coordinator server to the multiple database server participants identifying;

1) savepoints that have been rolled back since the database server participant was last notified;

2) savepoints that are still in effect;

3) savepoints that have been released;

c. sending a message from the multiple database sever participants to the coordinator server identifying;

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect;

3) savepoints that have been released; and

d. returning an SQL result set to a requester, and flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be associated with and positioned correctly within the savepoints.

16. A program product comprising a storage medium having computer readable program code thereon for controlling the operation of a distributed database management system, the distributed database management system comprising a plurality of individual database management systems residing on separate servers and including a coordinator server, said program code configuring and controlling the database management system to process transactions which comprise multiple steps across more then one of said plurality of database management systems, as participants, by the method comprising:

a) assigning across the database management system participants at least one savepoint within a transaction comprising multiple steps; and

b) if an application decision is made to reject the step is found, rolling back the step across the database management system participants to a savepoint.

22. A program product comprising a storage medium having computer readable program code thereon for managing a distributed database management system, said distributed database management system comprising a plurality of individual database management systems residing on separate servers and including a coordinator server, said program code configuring and controlling the distributed database management system to process transactions which comprise multiple steps across more then one of said plurality of database management systems, as participants, by the method comprising the steps of:

a. assigning savepoint identifiers to savepoints;

b. sending a message from the coordinator server to the multiple database server participants identifying;

1) savepoints that have been rolled back since the participant database server was last notified;

2) savepoints that are still in effect;

3) savepoints that have been released;

c. sending a message from the multiple database sever participants to the coordinator server identifying;

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect;

3) savepoints that have been released; and

d. returning an SQL result set to a requester, and flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be associated with and positioned correctly within the savepoints.

24. The method of claim 23 wherein COMMITING the transaction releases all savepoints.

25. The method of claim 23 further comprising establishing and managing the savepoints by the method of:

a) assigning savepoint identifiers to savepoints;

b) sending a message from the coordinator server to the multiple relational database management system participants in the step identifying the savepoint and providing savepoint information;

c) the relational database management system participants sending messages to the coordinator server with respect to executing the savepoint or completing a step and providing savepoint information; and

d) returning a step result set to a requester.

26. The method of claim 25 wherein the step of sending a message from the coordinator server to the multiple relational database management system participants in the transaction identifying the savepoint and providing savepoint information comprises sending a message from the coordinator server to the multiple database system participants at the beginning of the transaction identifying:

1) savepoints that have been rolled back since the database server was last notified;

2) savepoints that are still in effect; and

3) savepoints that have been released.

27. The method of claim 25 wherein the step of sending messages from replying multiple relational database management system participants to the coordinator server with respect to the savepoint and providing savepoint information comprises sending a message from the multiple database sever participants to the coordinator server at the time of rolling the transaction back to a savepoint, releasing a savepoint, completing a request, or replying to a request, said method comprising identifying:

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect; and

3) savepoints that have been released

28. The method of claim 25 wherein the step of returning an SQL result set to a requester further comprises flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be positioned correctly within savepoints.

35. The distributed relational database management system of claim 29 wherein the system is configured and controlled to release savepoints when a transaction is COMMITED.

23. A method of managing transactions on a plurality of relational database management systems residing on separate servers and including a coordinator server, wherein a single transaction comprises multiple sub-transactions across more then one of said plurality of relational database management systems as participants, said method comprising:

a) assigning across the relational database management system participants at least one savepoint within a transaction having multiple steps;

b) testing the sub-transaction across the relational database management system participants for application decisions to accept or reject a step; and

c) if an application decision to reject the step is found, rolling back the step across the relational database management system participants to a savepoint.

29. A method of managing savepoints on a plurality of relational database management systems residing on separate servers and including a coordinator server, wherein a single transaction comprises multiple stepacross more then one of said plurality of relational database management systems as participants, said method comprising the steps of:

a. assigning across the relational database management system participants at least one savepoint with savepoint identifiers within a transaction comprising multiple steps;

b. sending a message from the coordinator server to the multiple database server participants in the transaction at the beginning of a step identifying;

1) savepoints that have been rolled back since the database server participant was last notified;

2) savepoints that are still in effect;

3) savepoints that have been released;

c. sending messages from the multiple database server participants to the coordinator server at the time of doing one of rolling a step back to a savepoint, releasing a savepoint, completing a request, or replying to a request, said messages identifying;

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect;

3) savepoints that have been released; and

d. returning an SQL result set to a requester, and flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be associated with and positioned correctly within the savepoints.

30. A distributed relational database management system comprising a plurality of individual relational database management systems residing on separate servers and including a coordinator server, said distributed relational database management system configured and controlled to process transactions which comprise multiple steps across more then one of said plurality of relational database management systems, as participants, by the method comprising:

a) assigning across the relational database management system participants at least one savepoint within a step;

b) if an application decision to reject the step is found, rolling back the step across the relational database management system participants to a savepoint; and

c) when no application decisions to reject the step are found, COMMITING the transaction across the multiple relational database management system participants.

31. The distributed relational database management system of claim 30 being further configured and controlled to establish and manage the savepoints by the method of:

a) assigning savepoint identifiers to savepoints;

b) sending a message from the coordinator server to the multiple relational database management system participants in the transaction identifying the savepoint and providing savepoint information;

c) sending messages from the multiple relational database management system participants to the coordinator server with respect to executing the savepoint or completing a transaction, and providing savepoint information; and

d) returning a transaction result set to a requester.

32. The distributed relational database management system of claim 31 being further configured and controlled wherein the step of sending a message from the coordinator server to the multiple relational database management system participants identifying the savepoint and providing savepoint information comprises sending a message from the coordinator server to the multiple database server participants identifying:

1) savepoints that have been rolled back since the database server was last notified;

2) savepoints that are still in effect; and

3) savepoints that have been released.

33. The distributed relational database management system of claim 30 wherein the system is configured and controlled to perform the step of sending messages from the multiple relational database management system participants to the coordinator server with respect to the savepoint and providing savepoint information by sending a message from the multiple database sever participants to the coordinator server at the time of rolling the step back to a savepoint, releasing a savepoint, or replying to a request, identifying:

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect; and

3) savepoints that have been released.

34. The distributed relational database management system of claim 30 wherein the system is configured and controlled to perform step of returning an SQL result set to a requester by flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be associated with and positioned correctly within savepoints.

37. The distributed relational database management system of claim 36 wherein the system is configured and controlled to release all savepoints relating to a step when the transaction is COMMITED.

39. The program product of claim 38 wherein said program product further configures and controls the relational database management system to establish and manage the savepoints by the method of:

a) assigning savepoint identifiers to savepoints;

b) sending a message from the coordinator server to the multiple relational database management system participants in the transaction identifying the savepoint and providing savepoint information;

c) the relational database management system participants sending messages to the coordinator server with respect to executing the savepoint or completing a step, and providing savepoint information; and

d) returning a step result set to a requester.

40. The program product of claim 39 wherein the step of sending a message from the coordinator server to the multiple relational database management system participants identifying the savepoint and providing savepoint information comprises sending a message from the coordinator server to the multiple database server participants identifying:

1) savepoints that have been rolled back since the database server was last notified;

2) savepoints that are still in effect; and

3) savepoints that have been released.

41. The program product of claim 39 wherein the step of sending messages from the multiple relational database management system participants to the coordinator server at the time of rolling the step back to a savepoint, releasing a savepoint, completing a request, or replying to a request, comprises sending a message from multiple database server participants to the coordinator server identifying:

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect; and

3) savepoints that have been released

42. The program product of claim 39 wherein the system is configured and controlled to perform step of returning an SQL result set to a requester by flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be associated with and positioned correctly within savepoints.

43. The program product of claim 38 wherein the program product configures and controls the system to release savepoints when COMMITING the transaction.

36. A distributed relational database management system comprising a plurality of individual relational database management systems residing on separate servers and including a coordinator server, said distributed relational database management system configured and controlled to process transactions which comprise multiple steps across more then one of said plurality of relational database management systems, as participants, by the method comprising the steps of:

a. assigning savepoint identifiers to savepoints;

b. sending a message from the coordinator server to the multiple database server participants identifying;

1) savepoints that have been rolled back since the database server participant was last notified;

2) savepoints that are still in effect;

3) savepoints that have been released;

c. sending a message from the multiple database sever participants to the coordinator server identifying;

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect;

3) savepoints that have been released; and

d. returning an SQL result set to a requester, and flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be positioned correctly within the savepoints.

38. A program product comprising a storage medium having computer readable program code thereon for controlling the operation of a distributed relational database management system, the distributed relational database management system comprising a plurality of individual relational database management systems residing on separate servers and including a coordinator server, said program code configuring and controlling the relational database management system to process transactions which comprise multiple steps across more then one of said plurality of relational database management systems, as participants, by the method comprising:

a) assigning across the relational database management system participants at least one savepoint within a step; and

b) if an application decision is made to reject the step is found, rolling back the step across the relational database management system participants to a savepoint.

44. A program product comprising a storage medium having computer readable program code thereon for managing a distributed relational database management system, said distributed relational database management system comprising a plurality of individual relational database management systems residing on separate servers and including a coordinator server, said program code configuring and controlling the distributed relational database management system to process transactions which comprise multiple steps across more then one of said plurality of relational database management systems, as participants, by the method comprising the steps of:

a. assigning savepoint identifiers to savepoints;

b. sending a message from the coordinator server to the multiple database server participants identifying;

1) savepoints that have been rolled back since the participant database server was last notified;

2) savepoints that are still in effect;

3) savepoints that have been released;

c. sending a message from the multiple database sever participants to the coordinator server identifying;

1) savepoints that have been rolled back during this request;

2) savepoints that are still in effect;

3) savepoints that have been released; and

returning an SQL result set to a requester, and flowing a sequence number associated with a cursor associated with the result set to the requester so that the result set can be associated with and positioned correctly within the savepoints.