Java Programming with Oracle JDBC

Chapter 17. Transactions

So far we’ve been talking about all the ways to connect to a database and how to manipulate data in a database, but we haven’t said much about transactions. If your JDBC Connection is in auto-commit mode, which it is by default, then every DML statement is committed to the database upon its completion. That may be fine for simple applications, but there are three reasons why you may want to turn off auto-commit and manage your own transactions:

• To increase performance

• To maintain the integrity of business processes

• To use distributed transactions

First, if you are performing batch insert, update, or delete operations, then turning off auto-commit and committing results manually at reasonable intervals will improve performance. Note that I said “at reasonable intervals.” If you perform more operations per transaction than can fit into a rollback segment, the database takes additional time to increase the rollback segment to hold your uncommitted transaction statements, and that impairs performance.

The second reason why you may want to manage your own transactions is to maintain the integrity of your business processes. For example, if a customer places an order for some merchandise, the information you’d need to store for that order would include a list of items to purchase, billing and shipping information, and an authorized credit card charge. This information would likely be stored in several different tables. Without a manually managed transaction, it’s possible ...