Databases Reference
In-Depth Information
To ensure data integrity, all statements in a transaction are committed or rolled
back as a unit. For example, when you use a computer to transfer money from
one bank account to another, the request involves a transaction—updating val-
ues stored in the database for both accounts. If all parts of that unit of work suc-
ceed, the transaction is committed. If any part of that unit of work fails, the
transaction is rolled back.
Use the guidelines in this section to manage transactions more efficiently.
Committing (and rolling back) transactions is slow because of the disk I/O and,
potentially, the number of network round trips required. What does a commit
actually involve? The database must write to disk every modification made by a
transaction to the database. This is usually a sequential write to a journal file (or
log); nevertheless, it involves expensive disk I/O.
In JDBC, the default transaction commit mode is auto-commit. In auto-
commit mode, a commit is performed for every SQL statement that requires a
request to the database (
Insert
,
Update
,
Delete
, and
Select
statements). When
auto-commit mode is used, your application doesn't control when database work
is committed. In fact, commits commonly occur when there's actually no real
work to commit.
Some databases, such as DB2, don't support auto-commit mode. For these
databases, the database driver, by default, sends a commit request to the database
after every successful operation (SQL statement). The commit request equates to
a network round trip between the driver and the database. The round trip to the
database occurs even though the application didn't request the commit and even
if the operation made no changes to the database. For example, the driver makes
a network round trip even when a
Select
statement is executed.
Let's look at the following Java code, which doesn't turn off auto-commit
mode. Comments in the code show when commits occur if the driver or the
database performs commits automatically.
// For conciseness, this code omits error checking
// Create a Statement object
stmt = con.createStatement();
