System and method for optimizing database transaction termination

• US 8,655,853 B2
• Filed: 10/01/2004
• Issued: 02/18/2014
• Est. Priority Date: 10/01/2004
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Kuersch, Ralf, Raupp, Thomas, Theobald, Dietmar
• Primary Examiner(s): PHILLIPS, III, ALBERT M
• Application Number:
  US10/956,706
• Publication Number:
  US 20060074877A1
• Time in Patent Office: 3,427 Days
• Field of Search: 707/3, 707/1, 707/4, 707/102, 707/10, 707/100, 707/104.1, 707/205, 707/202, 707/201, 707/684, 707/825, 707/703, 700/19, 711/156
• US Class Current: 707/684
• CPC Class Codes: G06F 16/2455 Query execution

Abstract

Embodiments of the invention are generally directed to a system and method for optimizing transaction termination. A database interface receives an indication to terminate a transaction. The database interface decides whether to send a command to the underlying database server to terminate the transaction. If the database interface does send a command to the database server, it determines which command is appropriate to send.

Claims

1. A method for terminating a physical database connection to a database server, the method comprising:

determining a current transaction state associated with the physical database connection,

wherein the current transaction state is one of;

a first state indicating that a database resource is not allocated for a database transaction,

a second state indicating that the database resource is allocated for the database transaction, and

a third state indicating that data on the database is to be modified;

receiving at a database interface a request to terminate a transaction associated with the physical database connection;

determining to send the transaction termination request to the database server based on the current transaction state being one of the second and the third state but not the first state; and

sending the termination request to the database server when the current transaction state is one of the second state and the third state.

2. The method of claim 1, further comprising;

receiving an indication to adjust the transaction state of the physical database connection by one or more of;

receiving a call to a prepare statement method of the physical database connection; and

receiving a call to an execute method of a statement object derived from physical the database connection.

3. The method of claim 2, further comprising:

determining a database statement type based, on the received indication to adjust the transaction state of the physical database connection, the determining comprises determining that the statement type is one of;

an INSERT statement;

an UPDATE statement;

a DELETE statement;

a SELECT_FOR_UPDATE statement; and

a SELECT statement.

4. The method of claim 3, further comprising, if the statement type is a SELECT statement, determining whether the SELECT statement is one of the following:

a SELECT statement that does not cause the database server to allocate a resource based, on a current transaction isolation level; and

a SELECT statement that causes the database server to allocate a resource based, on the current transaction isolation level.

5. The method of claim 4, further comprising:

transitioning to one of plurality of transaction states based, on the determined database statement type, the transitioning comprises one of;

transitioning to the first state, if the SELECT statement does not cause the database server to allocate the resource;

transitioning to the second state, if the SELECT statement causes the database server to allocate the resource; and

transitioning to the second state, if the database statement type is a SELECT_FOR_UPDATE statement.

6. The method of claim 5, wherein the resource is a database lock.

7. The method of claim 5, wherein receiving the indication to terminate the transaction comprises one or more of:

receiving a call to a commit method;

receiving a call to a rollback method;

receiving a call to enable an auto-commit mode of the transaction; and

receiving a call to a close method.

8. The method of claim 5 wherein transitioning to one of the plurality of transaction states based, on the determined database statement type comprises transitioning to the third transaction state if the database statement type is one of:

an INSERT statement;

an UPDATE statement; and

a DELETE statement.

9. The method of claim 8, wherein receiving the indication to terminate the transaction comprises one or more of:

receiving a call to a commit method; and

receiving a call to a rollback method.

10. The method of claim 9 wherein determining whether to send the termination request to the database server based on the selected transaction state comprises one of:

propagating the call to the commit method to the database server, if the transaction state is the third state; and

propagating the can to the rollback method to the database server, if the transaction state is the third state.

11. The method of claim 1, further comprising selectively sending one of a plurality of termination requests to the database server based;

on the indication to terminate the transaction.

12. The method of claim 11, wherein selectively sending one of a plurality of termination requests to the database based on the indication to terminate the transaction comprises:

sending a commit call to the database server, if the indication to terminate the transaction is a call to a rollback method; and

sending a commit call to the database server, if the indication to terminate the transaction is a call to enable the auto-commit mode of the transaction.

13. A database connection management system comprising:

a database server;

a database processor having a physical database connection to the database server, and logic to perform the method including;

determining a current transaction state associated with the physical database connection,

wherein the current transaction state is one of;

a first state indicating that a database resource is not allocated for a database transaction,

a second state indicating that the database resource is allocated for the database transaction, and

a third state indicating that data on the database is to be modified;

receiving at a database interface a request to terminate a transaction associated with the physical database connection

determining to send the transaction termination request to the database server based on the current transaction state being one of the second and the third state but not the first state; and

sending the termination request to the database server when the current transaction state is one of the second state and the third state.

14. The system of claim 13, wherein the logic to further perform the method including:

receiving at the database interface an indication to adjust the transaction state of an internal variable representing the current transaction state; and

determining the database statement type based, on the received indication to adjust the transaction state of the internal variable comprises logic to determine that the statement type is one of;

an INSERT statement;

an UPDATE statement;

a DELETE statement;

a SELECT_FOR_UPDATE statement; and

a SELECT statement.

15. The system of claim 14, wherein, if the statement type is a SELECT statement, further comprising logic to determine whether the SELECT statement is one of:

a SELECT statement that does not cause the underlying database server to allocate a resource based, at least in part, on a current transaction isolation level; and

a SELECT statement that causes the underlying database server to allocate a resource based, at least in part, on the current transaction isolation level.

16. The system of claim 15, wherein the logic to transition to one of a plurality of transaction states based on the determined database statement type comprises logic to:

transition to the first state, if the SELECT statement does not causes the database server to allocate the resource;

transition to the second state, if the SELECT statement causes the database server to allocate the resource; and

transition to the second state, if the database statement type is a SELECT_FOR_UPDATE statement.

17. The system of claim 14, wherein the logic to receive the indication to terminate the transaction comprise logic to perform one or more of:

receive a call to a commit method;

receive a call to a rollback method;

receive a call to enable an auto-commit mode of the transaction; and

receive a call to a close method.

18. The system of claim 17, further comprising logic to selectively send one of a plurality of termination requests to the database server based on the indication to terminate the transaction.

19. The system of claim 18, wherein the logic to selectively send one of a plurality of termination requests to the database based on the indication to terminate the transaction comprises logic to:

send a commit call to the database, if the indication to terminate the transaction is a call to a rollback method; and

send a commit call to the database, if the indication to terminate the transaction is a call to enable the auto-commit mode of the transaction.

20. An article of manufacture comprising:

an electronically accessible storage medium providing instructions that, when executed by an apparatus, cause the apparatus to perform a method for terminating a physical database connection to a database server, the method comprising;

determining a current transaction state associated with the physical database connection,

wherein the current transaction state is one of;

a first state indicating that a database resource is not allocated for a database transaction,

a second state indicating that the database resource is allocated for a database transaction, and

a third state indicating that data on the database is to be modified;

receiving at a database interface a request to terminate a transaction associated with the physical database connection;

determining to send the transaction termination request to the database server based on the current transaction state being one of the second and the third state but not the first state; and

sending the termination request to database server when the current transaction state is one of the second state and the third state.

21. The article of manufacture of claim 20, wherein the instructions that, when executed by the apparatus, cause the apparatus to perform the method further comprising:

receiving at the database interface an indication to adjust the transaction state of an internal variable representing the current transaction state; and

determining the database statement type based on the received indication to adjust the transaction state of the database connection that causes the apparatus to determine that the statement type is one of;

an INSERT statement;

an UPDATE statement,

a DELETE statement;

a SELECT_FOR_UPDATE statement; and

a SELECT statement.

22. The article of manufacture of claim 21, wherein the electronically accessible medium provides further instructions that, when executed by the apparatus, cause the apparatus to perform the method comprising:

determining whether the SELECT statement is one of the;

a SELECT statement that does not cause the database server to allocate a resource based on a current transaction isolation level; and

a SELECT statement that causes the database server to allocate a resource based on the current transaction isolation level.

23. The article of manufacture of claim 22, wherein the instructions that, when executed by the apparatus, cause the apparatus to perform the method comprising:

transitioning to one of the plurality of transaction states based on the determined database statement type that causes the apparatus to perform one of;

transition to a first state, if the SELECT statement does not the database server to allocate the resource;

transition to a second state if the SELECT statement causes the database server to allocate the resource; and

transition to the second state, if the database statement type is a SELECT_FOR_UPDATE statement.

24. The article of manufacture of claim 23, wherein the instructions that, when executed by the apparatus, cause the apparatus to perform the method further comprising:

receiving the indication to terminate the transaction that causes the apparatus to perform one or more of;

receive a call to a commit method;

receive a call to a rollback method,

receive a call to enable an auto-commit mode of the transaction; and

receive a call to a close method.

25. The articlee of manufacture of claim 24, wherein the electronically accessible medium provides further instructions that, when executed by the apparatus, cause the apparatus to:

selectively send one of a plurality of termination requests to the database server based on the indication to terminate the transaction.