Download the JDK
Search the Tutorials
Hide the TOC
Trail: JDBC(TM) Database Access
Lesson: JDBC Basics
JDBC Basics
Getting Started
Setting Up a Database
Establishing a Connection
Setting Up Tables
Retrieving Values from Result Sets
Updating Tables
Milestone: The Basics of JDBC
Using Prepared Statements
Using Joins
Using Transactions
Stored Procedures
SQL Statements for Creating a Stored Procedure
Creating Complete JDBC Applications
Running the Sample Applications
Creating an Applet from an Application
Home Page > JDBC(TM) Database Access > JDBC Basics
« Previous • Trail • Next »
Using Transactions

> There are times when you do not want one statement to take effect unless another one completes. For example, when the proprietor of The Coffee Break updates the amount of coffee sold each week, he will also want to update the total amount sold to date. However, he will not want to update one without updating the other; otherwise, the data will be inconsistent. The way to be sure that either both actions occur or neither action occurs is to use a transaction. A transaction is a set of one or more statements that are executed together as a unit, so either all of the statements are executed, or none of the statements is executed.

> ### Disabling Auto-commit Mode

> When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. (To be more precise, the default is for an SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed.)

> The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode. This is demonstrated in the following line of code, where `con` is an active connection:

> >     con.setAutoCommit(false);

> ### Committing a Transaction

> Once auto-commit mode is disabled, no SQL statements are committed until you call the method `commit` explicitly. All statements executed after the previous call to the method `commit` are included in the current transaction and committed together as a unit. The following code, in which `con` is an active connection, illustrates a transaction:

> >     con.setAutoCommit(false);
> >     PreparedStatement updateSales = con.prepareStatement(
> >         "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
> >     updateSales.setInt(1, 50);
> >     updateSales.setString(2, "Colombian");
> >     updateSales.executeUpdate();
> >     PreparedStatement updateTotal = con.prepareStatement(
> >         "UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?");
> >     updateTotal.setInt(1, 50);
> >     updateTotal.setString(2, "Colombian");
> >     updateTotal.executeUpdate();
> >     con.commit();
> >     con.setAutoCommit(true);

> In this example, auto-commit mode is disabled for the connection `con`, which means that the two prepared statements `updateSales` and `updateTotal` are committed together when the method `commit` is called. Whenever the `commit` method is called (either automatically when auto-commit mode is enabled or explicitly when it is disabled), all changes resulting from statements in the transaction are made permanent. In this case, that means that the `SALES` and `TOTAL` columns for Colombian coffee have been changed to `50` (if `TOTAL` had been `0` previously) and will retain this value until they are changed with another update statement. TransactionPairs.java illustrates a similar kind of transaction but uses a `for` loop to supply values to the `setXXX` methods for `updateSales` and `updateTotal`.

> The final line of the previous example enables auto-commit mode, which means that each statement is once again committed automatically when it is completed. Then, you are back to the default state where you do not have to call the method `commit` yourself. It is advisable to disable auto-commit mode only while you want to be in transaction mode. This way, you avoid holding database locks for multiple statements, which increases the likelihood of conflicts with other users.

> ### Using Transactions to Preserve Data Integrity

> In addition to grouping statements together for execution as a unit, transactions can help to preserve the integrity of the data in a table. For instance, suppose that an employee was supposed to enter new coffee prices in the table `COFFEES` but delayed doing it for a few days. In the meantime, prices rose, and today the owner is in the process of entering the higher prices. The employee finally gets around to entering the now outdated prices at the same time that the owner is trying to update the table. After inserting the outdated prices, the employee realizes that they are no longer valid and calls the `Connection` method `rollback` to undo their effects. (The method `rollback` aborts a transaction and restores values to what they were before the attempted update.) At the same time, the owner is executing a `SELECT` statement and printing out the new prices. In this situation, it is possible that the owner will print a price that was later rolled back to its previous value, making the printed price incorrect.

> This kind of situation can be avoided by using transactions, providing some level of protection against conflicts that arise when two users access data at the same time.

> To avoid conflicts during a transaction, a DBMS uses locks, mechanisms for blocking access by others to the data that is being accessed by the transaction. (Note that in auto-commit mode, where each statement is a transaction, locks are held for only one statement.) Once a lock is set, it remains in force until the transaction is committed or rolled back. For example, a DBMS could lock a row of a table until updates to it have been committed. The effect of this lock would be to prevent a user from getting a dirty read, that is, reading a value before it is made permanent. (Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value. If you read a value that is later rolled back, you will have read an invalid value.)

> How locks are set is determined by what is called a transaction isolation level, which can range from not supporting transactions at all to supporting transactions that enforce very strict access rules.

> One example of a transaction isolation level is `TRANSACTION_READ_COMMITTED`, which will not allow a value to be accessed until after it has been committed. In other words, if the transaction isolation level is set to `TRANSACTION_READ_COMMITTED`, the DBMS does not allow dirty reads to occur. The interface `Connection` includes five values which represent the transaction isolation levels you can use in JDBC.

> Normally, you do not need to do anything about the transaction isolation level; you can just use the default one for your DBMS. JDBC allows you to find out what transaction isolation level your DBMS is set to (using the `Connection` method `getTransactionIsolation`) and also allows you to set it to another level (using the `Connection` method `setTransactionIsolation`). Keep in mind, however, that even though JDBC allows you to set a transaction isolation level, doing so has no effect unless the driver and DBMS you are using support it.

> ### Setting and Rolling Back to a Savepoint

> The JDBC 3.0 API adds the method `Connection.setSavepoint`, which sets a savepoint within the current transaction. The `Connection.rollback` method has been overloaded to take a savepoint argument.

> The example below inserts a row into a table, sets the savepoint `svpt1`, and then inserts a second row. When the transaction is later rolled back to `svpt1`, the second insertion is undone, but the first insertion remains intact. In other words, when the transaction is committed, only the row containing `?FIRST?` will be added to `TAB1`:

> >     Statement stmt = conn.createStatement();
> >     int rows = stmt.executeUpdate("INSERT INTO TAB1 (COL1) VALUES " +
> >                                                         "(?FIRST?)");
> >     // set savepoint
> >     Savepoint svpt1 = conn.setSavepoint("SAVEPOINT_1");
> >     rows = stmt.executeUpdate("INSERT INTO TAB1 (COL1) " +
> >                                                "VALUES (?SECOND?)");
> >     ...
> >     conn.rollback(svpt1);
> >     ...
> >     conn.commit();

> ### Releasing a Savepoint

> The method `Connection.releaseSavepoint` takes a `Savepoint` object as a parameter and removes it from the current transaction.

> Once a savepoint has been released, attempting to reference it in a rollback operation causes an `SQLException` to be thrown. Any savepoints that have been created in a transaction are automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question.

> ### When to Call the Method rollback

> As mentioned earlier, calling the method `rollback` aborts a transaction and returns any values that were modified to their previous values. If you are trying to execute one or more statements in a transaction and get an `SQLException`, you should call the method `rollback` to abort the transaction and start the transaction all over again. That is the only way to be sure of what has been committed and what has not been committed. Catching an `SQLException` tells you that something is wrong, but it does not tell you what was or was not committed. Since you cannot count on the fact that nothing was committed, calling the method `rollback` is the only way to be sure.

> TransactionPairs.java demonstrates a transaction and includes a `catch` block that invokes the method `rollback`. In this particular situation, it is not really necessary to call `rollback`, and we do it mainly to illustrate how it is done. If the application continued and used the results of the transaction, however, it would be necessary to include a call to `rollback` in the `catch` block in order to protect against using possibly incorrect data.

« Previous • Trail • Next »
Previous page: Using Joins
Next page: Stored Procedures