Method and system for load balancing a distributed database

• US 8,209,696 B2
• Filed: 02/13/2007
• Issued: 06/26/2012
• Est. Priority Date: 02/13/2006
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Ferguson, Gregory, Heisz, Jeffrey, Tung, David, Jamal, Muhammad Mansoor, Kassam, Ariff
• Primary Examiner(s): An, Meng
• Assistant Examiner(s): Chew, Brian
• Application Number:
  US11/674,533
• Publication Number:
  US 20070203910A1
• Time in Patent Office: 1,960 Days
• Field of Search: 718/104, 707/704
• US Class Current: 718/104
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation
• 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

Abstract

A method of processing a transaction request at a database load balancer. The method comprises receiving the transaction request, where the transaction request is comprised of one or more operations; analyzing the transaction request to determine the one or more operations; associating one or more database locks with each of the one or more operations; analyzing one or more of the database locks to determine the one or more sequence numbers associated with each of the one or more operations; and transmitting the one or more operations with the associated database locks and the sequence numbers to one or more databases servers accessible to the database load balancer.

Claims

1. A method of processing a database transaction request, the method comprising:

(a) receiving the transaction request, wherein the transaction request is comprised of one or more operations;

(b) analyzing the transaction request to determine the one or more operations;

(c) associating one or more database locks with each of the one or more operations;

(d) determining one or more sequence numbers for one or more of the database locks associated with each of the one or more operations; and

(e) transmitting the one or more operations with the associated database locks and the one or more sequence numbers to one or more database servers accessible to a database load balancer based on respective processing abilities associated with each of the one or more database servers.

2. The method of claim 1, wherein the one or more operations are selected from the group consisting of:

modifying option settings, read operations, select operations, write operations, commit operations, roll back operations, execute operations, and prepare operations.

3. The method of claim 1, wherein the one or more database servers implement one or more distributed databases.

4. The method of claim 1, wherein components of the database load balancer are distributed across one or more of the database servers.

5. The method of claim 1, wherein management operations are provided along with the one or more operations, the one or more sequence numbers and the one or more locks.

6. The method of claim 5, wherein the management operations are selected from the group consisting of:

requests for user connections, statement allocations, option settings, recovery information, roll-forward information, implicit commit, and roll back operations.

7. The method of claim 1, wherein each of the one or more locks has associated with it lock number, a lock type, a lock scope and a lock sequence.

8. The method of claim 7, further comprising determining a transaction isolation level associated with the transaction request.

9. The method of claim 8, wherein each lock scope is based on the transaction isolation level.

10. The method of claim 9, wherein the lock scope is further based on the lock type.

11. A non-transitory computer-readable medium upon which a plurality of instructions are stored, the instructions for performing:

(a) receiving a transaction request, wherein the transaction request is comprised of one or more operations;

(b) analyzing the transaction request to determine the one or more operations;

(c) associating one or more database locks with each of the one or more operations;

(d) determining one or more sequence numbers for one or more of the database locks associated with each of the one or more operations; and

(e) transmitting the one or more operations with the associated database locks and the one or more sequence numbers to one or more database servers accessible to a database load balancer based on respective processing abilities associated with each of the one or more database servers.

12. A method of processing a transaction request, the method comprising:

(a) receiving at a database server one or more operations of a transaction request from a database load balancer based on processing abilities associated with the database server, where the operations have associated with them one or more sequence numbers and one or more locks;

(b) analyzing the one or more sequence numbers;

(c) placing each of the one or more operations in one or more queues based on results of analyzing the one or more sequence numbers; and

(d) analyzing the queues to determine an order of execution of the one or more operations based on the one or more sequence numbers and one or more locks associated with the one or more operations.

13. The method of claim 12, wherein each of the one or more locks has associated with it lock number, a lock type, a lock scope and a lock sequence.

14. The method of claim 12, wherein a session queue is created for each session established with the database server.

15. The method of claim 14, wherein the session queue is resident upon the database server.

16. The method of claim 14, wherein the session queue is resident upon the database load balancer.

17. The method of claim 12, wherein the one or more queues comprise a record queue that is resident upon the database server.

18. The method of claim 12, wherein the one or more queues comprise a record queue that is resident upon the database load balancer.

19. A non-transitory computer-readable medium upon which a plurality of instructions are stored, the instructions for performing:

(a) receiving at a database server one or more operations of a transaction request from a database load balancer based on processing abilities associated with the database server, where the operations have associated with them one or more sequence numbers and one or more locks;

(b) analyzing the one or more sequence numbers;

(c) placing each of the one or more operations in one or more queues based on results of analyzing the one or more sequence numbers; and

(d) analyzing the queues to determine an order of execution of the one or more operations based on the one or more sequence numbers and one or more locks associated with the one or more operations.

20. A method of processing a transaction request, the method comprising:

(a) receiving the transaction request, wherein the transaction request is comprised of one or more operations;

(b) analyzing the transaction request to determine the one or more operations;

(c) associating one or more database locks with each of the one or more operations;

(d) determining one or more sequence numbers for one or more of the database locks associated with each of the one or more operations;

(e) determining processing abilities associated with one or more database servers associated with a database load balancer; and

(f) transmitting one or more of the operations with the associated database locks and the sequence numbers to a one of the database servers based on the determination of processing abilities from step e.

21. The method of claim 20, wherein the one or more database servers implement one or more distributed databases.

22. The method of claim 20, wherein components of the database load balancer are distributed across one or more of the database servers.

23. The method of claim 20, wherein one of the processing abilities is availability of each of the one or more database servers.

24. The method of claim 20, wherein one of the processing abilities is processing capability of each of the one or more database servers.

25. The method of claim 20, further comprising analyzing a scheduler table to determine status and response time of one or more database servers that are accessible to the database load balancer.

26. A non-transitory computer-readable medium upon which a plurality of instructions are stored, the instructions for performing:

(a) receiving a transaction request, wherein the transaction request is comprised of one or more operations;

(b) analyzing the transaction request to determine the one or more operations;

(c) associating one or more database locks with each of the one or more operations;

(d) determining one or more sequence numbers for one or more of the database locks associated with each of the one or more operations;

(e) determining processing abilities associated with one or more database servers associated with a database load balancer; and

(f) transmitting one or more of the operations with the associated database locks and the sequence numbers to a one of the database servers based on the determination of processing abilities from step e.

27. A system for processing a database transaction request, the system comprising:

(a) a memory for storing a plurality of instructions; and

(b) a processor coupled to the memory, said processor configured for;

(i) receiving the transaction request, wherein the transaction request is comprised of one or more operations;

(ii) analyzing the transaction request to determine the one or more operations;

(iii) associating one or more database locks with each of the one or more operations;

(iv) determining one or more sequence numbers for one or more of the database locks associated with each of the one or more operations; and

(v) providing the one or more operations with the associated database locks and the one or more sequence numbers to one or more database servers accessible to a database load balancer based on respective processing abilities associated with each of the one or more database servers.

28. The system of claim 27, wherein the one or more operations are selected from the group consisting of:

modifying option settings, read operations, select operations, write operations, commit operations, roll back operations, execute operations, and prepare operations.

29. The system of claim 27, wherein the one or more database servers implement one or more distributed databases.

30. The system of claim 27, wherein components of the database load balancer are distributed across one or more of the database servers.

31. The system of claim 27, wherein management operations are provided along with the one or more operations, the one or more sequence numbers and the one or more locks.

32. The system of claim 31, wherein the management operations are selected from the group consisting of:

requests for user connections, statement allocations, option settings, recovery information, roll-forward information, implicit commit, and roll back operations.

33. The system of claim 27, wherein each of the one or more locks has associated with it lock number, a lock type, a lock scope and a lock sequence.

34. The system of claim 33, wherein the processor is further configured for determining a transaction isolation level associated with the transaction request.

35. The system of claim 34, wherein each lock scope is based on the transaction isolation level.

36. The system of claim 34, wherein the lock scope is further based on the lock type.

37. A system for processing a database transaction request, the system comprising:

(a) a memory for storing a plurality of instructions; and

(b) a processor coupled to the memory, said processor configured for;

(i) receiving at a database server one or more operations of a transaction request from a database load balancer based on processing abilities associated with the database server, where the operations have associated with them one or more sequence numbers and one or more locks;

(ii) analyzing the one or more sequence numbers;

(iii) placing each of the one or more operations in one or more queues based on the analysis of the one or more sequence numbers; and

(iv) analyzing the queues to determine an order of execution of the one or more operations based on the one or more sequence numbers and one or more locks associated with the one or more operations.

38. The system of claim 37, wherein each of the one or more locks has associated with it lock number, a lock type, a lock scope and a lock sequence.

39. The system of claim 37, wherein a session queue is created for each session established with the database server.

40. The system of claim 39, wherein the session queue is resident upon the database server.

41. The system of claim 39, wherein the session queue is resident upon the database load balancer.

42. The system of claim 37, wherein the one or more queues comprise a record queue that is resident upon the database server.

43. The system of claim 37, wherein the one or more queues comprise a record queue that is resident upon the database load balancer.

44. A system for processing a transaction request, the system comprising:

(a) a memory for storing a plurality of instructions; and

(b) a processor coupled to the memory, said processor configured for;

(i) receiving the transaction request, wherein the transaction request is comprised of one or more operations;

(ii) analyzing the transaction request to determine the one or more operations;

(iii) associating one or more database locks with each of the one or more operations;

(iv) determining one or more sequence numbers for one or more of the database locks associated with each of the one or more operations;

(v) determining processing abilities associated with one or more database servers associated with the database load balancer; and

(vi) transmitting one or more of the operations with the associated database locks and the sequence numbers to a one of the database servers based on the determination of processing abilities from step v.

45. The system of claim 44, wherein the one or more database servers implement one or more distributed databases.

46. The system of claim 44, wherein components of the database load balancer are distributed across one or more of the database servers.

47. The system of claim 44, wherein one of the processing abilities is availability of each of the one or more database servers.

48. The system of claim 44, wherein one of the processing abilities is processing capability of each of the one or more database servers.

49. The system of claim 44, wherein the processor is further configured for analyzing a scheduler table to determine status and response time of one or more database servers that are accessible to the database load balancer.