Database transaction management Java
You'll find out the advantages and disadvantages of using one connection for multiple statements. You'll also read definitions for local transactions vs. distributed transactions and learn how to manage transactions.
TABLE OF CONTENTS
Advantages and Disadvantages
The advantage of using one connection for multiple statements is that it reduces the overhead of establishing multiple connections, while allowing multiple statements to access the database. The overhead is reduced on both the database server and client machines.
The disadvantage of using this method of connection management is that the application may have to wait to execute a statement until the single connection is available. We explained why in "How One Connection for Multiple Statements Works, " page 17.
Guidelines for One Connection for Multiple Statements
Here are some guidelines for when to use one connection for multiple statements:
- Consider using this connection model when your database server has hardware constraints such as limited memory and one or more of the following conditions are true:
a. You are using a cursor-based protocol database.
b. The statements in your application return small result sets or no result sets.
c. Waiting for a connection is acceptable. The amount of time that is acceptable for the connection to be unavailable depends on the requirements of your application. For example, 5 seconds may be acceptable for an internal application that logs an employee's time but may not be acceptable for an online transaction processing (OLTP) application such as an ATM application. What is an acceptable response time for your application?
- This connection model should not be used when your application uses transactions.
Case Study: Designing Connections
Let's look at one case study to help you understand how to design database connections. The environment details are as follows:
- The environment includes a middle tier that must support 20 to 100 concurrent database users, and performance is key. CPU and memory are plentiful on both the middle tier and database server.
- The database is Oracle, Microsoft SQL Server, Sybase, or DB2.
- The API that the application uses is ODBC, JDBC, or ADO.NET.
- There are 25 licenses for connections to the database server.
Here are some possible solutions:
- Solution 1: Use a connection pool with a maximum of 20 connections, each with a single statement.
- Solution 2: Use a connection pool with a maximum of 5 connections, each with 5 statements.
- Solution 3: Use a single connection with 5 to 25 statements.
The key information in this case study is the ample CPU and memory on both the middle tier and database server and the ample number of licenses to the database server. The other information is really irrelevant to the design of the database connections.
Solution 1 is the best solution because it performs better than the other two solutions. Why? Processing one statement per connection provides faster results for users because all the statements can access the database at the same time.
The architecture for Solutions 2 and 3 is one connection for multiple statements. In these solutions, the single connection can become a bottleneck, which means slower results for users. Therefore, these solutions do not meet the requirement of "performance is key."
You might also like
You're just being redundantby informed_opinion
The reason why you're feeling that your GC offers no value is because you're adding a layer of redundancy.
Why do you feel the need to phone the sub's and ask them for dates? Dates in this business are flexible anyways. Unlike the project management of software or database development, project management of homes is a fluid thing.
You're GC's job is to make sure the project gets completed. If one sub fails to show up he must make sure he get another. When building materials fail to arrive he has to quickly re-juggle the project plan to get it off the critical path. There's hundreds of things that a GC has programmed into his head - the least of which is calling up his subs and asking them for starting dates
Java Persistence with Hibernate
Book (Manning Publications)
Oracle Database 11g PL/SQL Programming Workbook (Oracle Press)
Book (McGraw-Hill Osborne Media)