Java Reference
In-Depth Information
debit the first account. The issue of managing a series of SQL statements as a single
transaction is discussed in the
next section
.
Transactions
The capability to group SQL statements for execution as a single entity is provided
through SQL's transaction mechanism. A
transaction
consists of one or more
statements that are executed, completed, and either committed or rolled back as a
group. When the method commit or rollback is called, the current transaction ends,
and another one begins.
In the context of database transactions, the term commit means that the change is
made permanently in the database, and the term rollback means that no change is
made in the database.
A new JDBC connection is usually in auto-commit mode by default, meaning that
when a statement is completed, the method commit is called on that statement
automatically. The commit occurs when the statement completes or the next execute
occurs, whichever comes first. In the case of statements returning a ResultSet, the
statement completes when the last row of the ResultSet has been retrieved or the
ResultSet has been closed. In advanced cases, a single statement may return
multiple results as well as output parameter values. Here, the commit occurs when all
results and output parameter values have been retrieved.
Auto-commit mode is controlled by this method:
public void setAutoCommit(boolean autoCommit) throws SQLException
If auto-commit mode has been disabled, a transaction will not terminate until either
the commit method or the rollback method is called explicitly, so it will include all the
statements that have been executed since the last invocation of the commit or
rollback method. In this case, all the statements in the transaction are committed or
rolled back as a group.
When a SQL statement makes changes to a database, the commit method makes
those changes permanent, and it releases any locks the transaction holds. The
rollback method, on the other hand, simply discards those changes.
Clearly, in a situation such as our preceding bank-transfer example, we can prevent
one step in the funds transfer if the other fails by disabling auto-commit and grouping
both updates into one transaction. If both updates are successful, the commit method
is called, making the effects of both updates permanent; if one fails or both fail, the
rollback method is called, restoring the account balances that existed before the
updates were executed.
