8.3 Multithreaded SQLJ Programs
A Java program can perform a number of actions in parallel, with each action being performed by a separate process. Such a process is also known as a thread. For example, a multithreaded program might perform database access using one thread while another thread handles the display. In the case of a SQLJ program, you might want to perform several SQL operations in parallel using a separate thread for each one. Multithreading is a very powerful feature of the Java language. The downside to writing a multithreaded program is the additional complexity of dealing with the thread model.
If a SQLJ program were to use multiple threads, then the program would run faster, right? Not always: it depends on where the program runs. If the program is deployed in the Oracle JServer JVM (described in Chapter 6), then the multithreaded program may not run faster than a comparable program that is not multithreaded. This is because the JServer JVM executes each thread serially through a single operating system thread and schedules the execution of each thread using a round-robin algorithm. However, this doesn't mean that the JServer JVM is slow! The JServer JVM has other ways to increase the performance of Java programs. If a SQLJ program is not going to be run using the JServer JVM, then the program may benefit from the use of threads.
8.3.1 Designing a Multithreaded SQLJ Program
How would you structure a SQLJ program to use threads? The basic problem that must be solved is that when a program has two or more threads that attempt to modify the same row, the threads may conflict with one another. One thread may begin to make modifications to a row, only to have those modifications overwritten by a second thread that is modifying the same row. This is known as a race condition, and might occur when the threads use the same execution context. There are two solutions to this problem:
Both these solutions basically result in each thread using a different execution context, solving the race condition problem. If you use the same connection context for the threads, you should explicitly specify different execution contexts for each thread. Each connection context has its own default execution context, so if you use different connection contexts for each thread, then by default they will use different execution contexts.
8.3.2 Example Program: MultithreadedExample1.sqlj
This section contains a complete program that illustrates how to write a multithreaded SQLJ program. The program MultithreadedExample1.sqlj (Example 8-3) uses two threads, each of which uses a separate execution context to update the same row in the customers table. Because this program requires a little more explanation than the others in this chapter, I have provided a full description of the program after the program listing.
Example 8-3. MultithreadedExample1.sqlj
/* The program MultithreadedExample1.sqlj illustrates the use of multithreading to update customer names. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; import sqlj.runtime.ExecutionContext; import java.util.Random; public class MultithreadedExample1 extends Thread { // declare the iterator class #sql private static iterator CustomerIteratorClass ( int id, String first_name, String last_name ); int customer_id; String first_name; String last_name; MultithreadedExample1( int customer_id, String first_name, String last_name ) { this.customer_id = customer_id; this.first_name = first_name; this.last_name = last_name; } public static void main(String [] args) { try { Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // display all customers displayCustomers( ); MultithreadedExample1 thread1 = new MultithreadedExample1(1, "John", "Doe"); MultithreadedExample1 thread2 = new MultithreadedExample1(1, "Jean", "Smith"); // start the threads using the start( ) method thread1.start( ); thread2.start( ); // wait for each thread to complete using the join( ) method thread1.join( ); thread2.join( ); // display all customers displayCustomers( ); #sql { ROLLBACK }; Oracle.close( ); } catch ( SQLException e ) { System.err.println("SQLException " + e); System.exit(1); } catch ( Exception e ) { System.err.println("Exception " + e); System.exit(1); } } // end of main( ) public void run( ) { try { System.out.println("Updating customer " + customer_id + " name to " + first_name + " " + last_name + "."); // create new execution context ExecutionContext exec_context = new ExecutionContext( ); #sql [exec_context] { UPDATE customers SET first_name = :first_name, last_name = :last_name WHERE id = :customer_id }; } catch ( SQLException e ) { System.err.println("SQLException " + e); System.exit(1); } catch ( Exception e ) { System.err.println("Exception " + e); System.exit(1); } } // end of run( ) private static void displayCustomers( ) throws SQLException { // declare a named iterator object CustomerIteratorClass customer_iterator; #sql customer_iterator = { SELECT id, first_name, last_name FROM customers ORDER BY id }; // access the contents of the iterator while (customer_iterator.next( )) { System.out.println("Customer:"); 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 // close the iterator customer_iterator.close( ); } // end of displayCustomers( ) } The following sections describe some of the more important aspects of this example program.
8.3.2.1 The MultithreadedExample1 class
The MultithreadedExample1 class extends the Thread class; this indicates that the program is multithreaded. The MultithreadedExample1 class contains the following three variables that are used to hold information from the customer table:
int customer_id; String first_name; String last_name;
The constructor for the class MultithreadedExample1 accepts three parameters. These are used to initialize the three variables when a new object of that class is created:
MultithreadedExample1( int customer_id, String first_name, String last_name ) { this.customer_id = customer_id; this.first_name = first_name; this.last_name = last_name; } 8.3.2.2 The main( ) method
Using a call to Oracle.connect( ), the main( ) method creates a default connection context that connects to the fundamental_user schema:
Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" );
All the rows in the customers table are then selected and displayed via a call to the displayCustomers( ) method:
displayCustomers( );
Next, two threads are created. These are named thread1 and thread2:
MultithreadedExample1 thread1 = new MultithreadedExample1(1, "John", "Doe"); MultithreadedExample1 thread2 = new MultithreadedExample1(1, "Jean", "Smith");
The thread named thread1 sets the customer_id, first_name, and last_name object variables to 1, "John", and "Doe" respectively. The thread named thread2 sets the customer_id, first_name, and last_name object variables to 1, "Jean", and "Smith" respectively. Each thread is an instance of the MultithreadedExample1 class, and the class's constructor is used to initialize the object variables when the two thread objects are created. The two threads are then started by calling each thread's start( ) method:
thread1.start( ); thread2.start( );
You don't have to write the start( ) method: it comes with the Thread class. You do, however, have to write a run( ) method in your class, and the start( ) method calls that run( ) method. The run( ) method updates the first_name and last_name columns of the row in the customers table for the customer whose ID number is specified in the customer_id object variable. I will describe more details of the run( ) method shortly. After starting the two threads, the program waits until both threads are completed. This is done by calling the join( ) method for the two threads:
thread1.join( ); thread2.join( );
The next statement in the main( ) method is not executed until both threads are completed. At that point, all the rows in the customers table are displayed again via another call to the displayCustomers( ) method:
displayCustomers( );
Finally, the main( ) method rolls back the changes made to the customers table by the run( ) methods for the two threads and disconnects from the database:
#sql { ROLLBACK }; Oracle.close( ); 8.3.2.3 The run( ) method
The run( ) method begins by creating an execution context named exec_context:
ExecutionContext exec_context = new ExecutionContext( );
In this way, each thread has its own execution context, thereby preventing any race condition that might otherwise occur. The execution context uses the database transaction established by the default connection context at the start of the main( ) method. Both execution contexts created by the two threads share this database transaction.
The execution context exec_context is then used to update the first_name and last_name columns for the row with an id equal to the value stored in the customer_id variable. The values for the first_name and last_name columns come from the variables first_name and last_name:
#sql [exec_context] { UPDATE customers SET first_name = :first_name, last_name = :last_name WHERE id = :customer_id }; 8.3.2.4 Program output
The output from MultithreadedExample1.sqlj is as follows:
Customer: id = 1 first_name = John last_name = Smith Customer: id = 2 first_name = Cynthia last_name = Stevens Customer: id = 3 first_name = Steve last_name = Seymour Customer: id = 4 first_name = Gail last_name = Williams Customer: id = 5 first_name = Doreen last_name = Heyson Updating customer 1 name to John Doe. Updating customer 1 name to Jean Smith. Customer: id = 1 first_name = Jean last_name = Smith Customer: id = 2 first_name = Cynthia last_name = Stevens Customer: id = 3 first_name = Steve last_name = Seymour Customer: id = 4 first_name = Gail last_name = Williams Customer: id = 5 first_name = Doreen last_name = Heyson
