9.4 A Transaction Isolation Level Example
I will now guide you through an example showing how the different transaction isolation levels work. In this example, you'll see the effect of transaction isolation levels on two connection contexts that access the same rows in the customers table. Initially, the customers table contains the following rows:
id first_name last_name dob phone -- ---------- ---------- ----------- ------------ 1 John Smith 01-JAN-1965 650-555-1212 2 Cynthia Stevens 05-MAR-1968 650-555-1213 3 Steve Seymour 16-JUN-1971 650-555-1214 4 Gail Williams 01-DEC-1975 650-555-1215 5 Doreen Heyson 20-AUG-1970 650-555-1216
I use two connection contexts in this example: mod_context to modify the data in the table, and ser_context to demonstrate the effect of the SERIALIZABLE transaction isolation level on the visibility of those modifications from other transactions. The following code creates the two connection contexts:
// create a connection context named mod_context DefaultContext mod_context = Oracle.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // create a connection context named ser_context DefaultContext ser_context = Oracle.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" );
Both connection contexts initially have the default Oracle transaction isolation level of READ COMMITTED. The following statement changes the transaction isolation level for ser_context to SERIALIZABLE:
#sql [ser_context] { SET TRANSACTION ISOLATION LEVEL SERIALIZABLE }; At this point, the transaction for ser_context is no longer subject to phantom reads and non-repeatable reads. Next, all the rows in the customers table are read and displayed using a method named displayCustomers( ). This method accepts a connection context as a parameter, and uses a named iterator to retrieve all the rows from the customer table. It is invoked as follows for each connection context:
displayCustomers(mod_context); displayCustomers(ser_context);
The displayCustomers( ) method used here is the same one described in detail in Chapter 8. After displaying the initial state of the customers table from each connection context, the following statements are executed to add a new row to the customers table and to modify the name for customer #1. These statements use mod_context. Notice that the transaction is committed after the changes are made:
#sql [mod_context] { INSERT INTO customers (id, first_name, last_name) VALUES (6, 'Jason', 'Price') }; #sql [mod_context] { UPDATE customers SET first_name = 'John', last_name = 'Doe' WHERE id = 1 }; #sql [mod_context] { COMMIT }; The changes made by these statements are visible to mod_context because changes are always visible to the connection context that makes them. The isolation level, however, controls the visibility of these changes from other connection contexts. Table 9-1 shows the rows that are now visible to mod_context and ser_context. To simplify the table, the dob and phone columns have been omitted.
Table 9-1. Isolation level example
| Context | mod_context | ser_context |
| Isolation level | READ COMMITTED | SERIALIZABLE |
| Data visible | ID FIRST_NAME LAST_NAME -- ---------- --------- 1 John Doe 2 Cynthia Stevens 3 Steve Seymour 4 Gail Williams 5 Doreen Heyson 6 Jason Price | ID FIRST_NAME LAST_NAME -- ---------- --------- 1 John Smith 2 Cynthia Stevens 3 Steve Seymour 4 Gail Williams 5 Doreen Heyson |
You can see that the committed changes are visible to mod_context. Because the isolation level has been set to SERIALIZABLE for ser_context and the rows have already been read and displayed previously in the call to displayCustomers( ), the new row and the modified name for customer #1 are not visible to ser_context. The new row, for customer #6, is considered a phantom row because it was added by mod_context after the rows were initially read by ser_context; therefore, customer #6 is not visible to ser_context. The update to customer #1 represents a non-repeatable read, because the name was changed by mod_context after the row was initially read by ser_context; therefore, as seen from ser_context, the name for customer #1 is still the same initial value of John Smith. The rows visible to mod_context and ser_context may be displayed again using calls to displayCustomers( ):
displayCustomers(mod_context); displayCustomers(ser_context);
Next, the following statements delete the new row, and change customer #1's name back to John Smith:
#sql [mod_context] { DELETE FROM customers WHERE id = 6 }; #sql [mod_context] { UPDATE customers SET first_name = 'John', last_name = 'Smith' WHERE id = 1 }; #sql [mod_context] { COMMIT }; Finally, the connection contexts are closed:
mod_context.close( ); ser_context.close( );
As I said earlier, Oracle's default isolation level of READ COMMITTED is acceptable for most programs. Your other choice is SERIALIZABLE, which may slow the performance of your SQL statements.
9.4.1 Example Program: TransactionExample1.sqlj
This section contains a complete program, TransactionExample1.sqlj (Example 9-1), that contains the steps described previously. The program illustrates the effects of transaction isolation on two connection contexts.
Example 9-1. TransactionExample1.sqlj
/* The program TransactionExample1.sqlj illustrates the use of the SQL SET TRANSACTION statement to set the transaction isolation level. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; import sqlj.runtime.ref.DefaultContext; public class TransactionExample1 { #sql private static iterator CustomerIteratorClass ( int id, String first_name, String last_name ); public static void main(String [] args) { try { // create a connection context named mod_context DefaultContext mod_context = Oracle.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // create a connection context named ser_context DefaultContext ser_context = Oracle.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // use the SET TRANSACTION statement to set the isolation // level to SERIALIZABLE for ser_context, // ser_context will no longer be subject to phantom reads or // non-repeatable reads System.out.println("Setting the transaction isolation level to " + "SERIALIZABLE for ser_context."); #sql [ser_context] { SET TRANSACTION ISOLATION LEVEL SERIALIZABLE }; // display all customers using mod_context System.out.println("List of customers using mod_context."); displayCustomers(mod_context); // display all customers using ser_context System.out.println("List of customers using ser_context."); displayCustomers(ser_context); // add a customer using mod_context System.out.println("Adding customer Jason Price using mod_context."); #sql [mod_context] { INSERT INTO customers (id, first_name, last_name) VALUES (6, 'Jason', 'Price') }; // update customer using mod_context System.out.println("Updating customer 1 name to John Doe using " + "mod_context."); #sql [mod_context] { UPDATE customers SET first_name = 'John', last_name = 'Doe' WHERE id = 1 }; // perform a COMMIT using mod_context System.out.println("Performing COMMIT using mod_context."); #sql [mod_context] { COMMIT }; // display all customers using mod_context System.out.println("List of customers using mod_context."); displayCustomers(mod_context); // display all customers using ser_context, // the new customer (a phantom read), and the modified customer name // (a non-repeatable read) are not visible to ser_context System.out.println("List of customers using ser_context."); displayCustomers(ser_context); // delete the new customer, change the first customer name // back to John Smith and perform a COMMIT using mod_context #sql [mod_context] { DELETE FROM customers WHERE id = 6 }; #sql [mod_context] { UPDATE customers SET first_name = 'John', last_name = 'Smith' WHERE id = 1 }; #sql [mod_context] { COMMIT }; // close the connection contexts mod_context.close( ); ser_context.close( ); } catch ( SQLException e ) { System.err.println("SQLException " + e); System.exit(1); } } // end of main( ) private static void displayCustomers( DefaultContext conn_context ) throws SQLException { CustomerIteratorClass customer_iterator; #sql [conn_context] customer_iterator = { SELECT id, first_name, last_name FROM customers ORDER BY id }; while (customer_iterator.next( )) { System.out.println("id = " + customer_iterator.id( )); System.out.println("first_name = " + customer_iterator.first_name( )); System.out.println("last_name = " + customer_iterator.last_name( )); } // end of while loop customer_iterator.close( ); } // end of displayCustomers( ) } The output from the program is as follows:
Setting the transaction isolation level to SERIALIZABLE for ser_context. List of customers using mod_context. id = 1 first_name = John last_name = Smith id = 2 first_name = Cynthia last_name = Stevens id = 3 first_name = Steve last_name = Seymour id = 4 first_name = Gail last_name = Williams id = 5 first_name = Doreen last_name = Heyson List of customers using ser_context. id = 1 first_name = John last_name = Smith id = 2 first_name = Cynthia last_name = Stevens id = 3 first_name = Steve last_name = Seymour id = 4 first_name = Gail last_name = Williams id = 5 first_name = Doreen last_name = Heyson Adding customer Jason Price using mod_context. Updating customer 1 name to John Doe using mod_context. Performing COMMIT using mod_context. List of customers using mod_context. id = 1 first_name = John last_name = Doe id = 2 first_name = Cynthia last_name = Stevens id = 3 first_name = Steve last_name = Seymour id = 4 first_name = Gail last_name = Williams id = 5 first_name = Doreen last_name = Heyson id = 6 first_name = Jason last_name = Price List of customers using ser_context. id = 1 first_name = John last_name = Smith id = 2 first_name = Cynthia last_name = Stevens id = 3 first_name = Steve last_name = Seymour id = 4 first_name = Gail last_name = Williams id = 5 first_name = Doreen last_name = Heyson );
