CS276 Advanced Oracle Using Java

1. CS276 Advanced Oracle Using Java Advanced Transaction Control Chapter 9

2. ACID Transaction Properties • Atomicity • Consistency • Isolation • Durability

3. ACID Transaction Properties Atomicity • Transactions are committed or rolled back as a group, and are atomic, meaning that all SQL statements contained in a transaction are considered to be a single indivisible unit

4. ACID Transaction Properties Consistency • Transactions ensure that the database state remains consistent, meaning that the database starts at one consistent state and ends in another consistent state when the transaction finishes.

5. ACID Transaction Properties Isolation • Separate transactions should appear to run without interfering with each other

6. ACID Transaction Properties Durability • Once a transaction has been committed, the database changes are preserved, even if the machine on which the database software runs later crashes.

7. Transaction Isolation • The transaction isolation level is the degree to which the changes made by one transaction are separated from other transactions running concurrently. • In the following bullets, I'll use examples of two concurrent transactions that are accessing the same rows to illustrate the three types of potential transaction processing problems:

8. Transaction Isolation • Phantom reads • Nonrepeatable reads • Dirty reads

9. Transaction Isolation • Phantom reads Transaction 1 reads a set of rows returned by a specified WHERE clause. Transaction 2 then inserts a new row, which also happens to satisfy the WHERE clause of the query previously used by Transaction 1. Transaction 1 then reads the rows again using the same query but now sees the additional row just inserted by Transaction 2. This new row is known as a phantom, because to Transaction 1 this row seems to have magically appeared.

10. Transaction Isolation Nonrepeatable reads • Transaction 1 reads a row, and • Transaction 2 updates the same row just read by Transaction 1. • Transaction 1 then reads the same row again and discovers that the row it read earlier is now different. • This is known as a nonrepeatable read, because the row originally read by Transaction 1 has been changed.

11. Transaction Isolation Dirty reads • Transaction 1 updates a row but doesn't commit the update. • Transaction 2 reads the updated row. Transaction 1 then performs a rollback, undoing the previous update. • Now the row just read by Transaction 2 is no longer valid (or it's dirty) because the update made by Transaction 1 wasn't committed when the row was read by Transaction 2.

12. Transaction Isolation To deal with these potential problems, databases implement various levels of transaction isolation to prevent concurrent transactions from interfering with each lather. The SQL standard defines the following transaction isolation levels, shown in larder of increasing isolation:

13. Transaction Isolation • READ UNCOMMITTED • Phantom reads, non repeatable reads, and dirty reads are permitted. • READ COMMITTED • Phantom reads and nonrepeatable reads are permitted, but dirty reads are not.

14. Transaction Isolation • REPEATABLE READ • Phantom reads are permitted, but nonrepeatable and dirty reads are not. • SERIALIZABLE • Phantom reads, nonrepeatable reads, and dirty reads are not permitted.

15. Transaction Isolation • The Oracle database supports the READ COMMITTED and SERIALIZABLE transaction isolation levels; • It doesn't support READ UNCOMMITTED or REPEATABLE READ levels. • The default transaction isolation level defined by the SQL standard is SERIALIZABLE, • but the default used by the Oracle database is READ COMMITTED, which is acceptable for nearly all applications.

16. Transaction Isolation • TIP • Although you can use SERIALIZABLE with the Oracle database, it may increase the time your SQL statements take to complete, so you should only use SERILIZABLE if you absolutely have to.

17. A worked Example Using JDBC • A worked example that the uses two JDBC Connection objects, each of which will run a concurrent transaction. • One Connection object will use the default transaction isolation level of READ COMMITTED • The other Connection object will use the SERIALIZABLE level. • Both transactions will then read the rows in the products table • The READ COMMITTED transaction will add a new row and update an existing row. • I'll then show that the SERIALlZABlE transaction doesn't "see" either of these changes.

18. A worked Example Using JDBC • The following example creates the first Connection object, named rcConnection, and disables auto-commit: Connection rcConnection = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:0RCL", "store_user" , "storepassword" ) ; rcConnection.setAutoCommit(false) ;

19. A worked Example Using JDBC • NOTE • When you create a connection or OracleConnection object in jDBC that object will use the default Oracle database transaction isolation level of READ COMMITTED. • A transaction performed using rcConnection will have the default transaction isolation level of READ COMMITTED. The following example creates the second Connection object, named serConnection and disables auto-commit.

20. A worked Example Using JDBC Connection serConnection = DriverManager.getConnection( "jdbe:oracle:thin:@localhost:1521:0RCL", "store_user" , "storepassword" ) ; serConnection.setAutoCommit(false) ;

21. A worked Example Using JDBC • You can change the transaction isolation level for a Connection or OracleConnection object from the default using the setTransactionIsolation () method. The following statement sets the transaction isolation level to SERIALIZABLE for serConnection using the setTransactionIsolation () method: serConnection.setTransactionIsolation( oracle.jdbc.OracleConnection. TRANSACTION_SERIALIZABLE ) ;

22. A worked Example Using JDBC • A transaction performed using serConnection will now have a transaction isolation level of SERIALIZABLE. • The constants that may be passed to the setTransactionIsolation () method are defined in the oracle. jdbc. OracleConnection class. • The Oracle database supports two transaction isolation levels: • READ COMMITTED • SERIALIZABLE • The corresponding constants in the oracle. jdbc. OracleConnection class for these isolation levels are: • TRANSACTION READ COMMITTED • TRANSACTION SERIALIZABLE.

23. A worked Example Using JDBC • You can get the transaction isolation level setting for a Connection or OracleConnection object using the getTransactionIsolation () method, which returns an int value. • For example, the following statements display the int values for the transaction isolation level setting for rcConnection and serConnection using the getTransactionIsolation () method:

24. A worked Example Using JDBC System.out.println(reConneetion.getTransactionIsolation()) ; System.out.println(serConneetion.getTransactionIsolation()) ;

25. A worked Example Using JDBC • The next step is to create Statement objects from the Connection objects (remember, a Statement object is used to perform SQL statements). • The following example creates two Statement objects, named rcStatement and serStatement, from • rcConnect ion and • serConnection respectively: • reStatement = rcConnection.createStatement(); • serStatement = serConnection.createStatement();

26. A worked Example Using JDBC • To display the rows in the products table, I'll define a new method named displayProducts () that accepts a Statement object and then retrieves and displays the id and name columns from the products table. The displayProducts () method is defined as follows:

27. A worked Example Using JDBC private static void displayProducts( Statement myStatement ) throws SQLException { ResultSet productResultSet = myStatement.executeQuery( "SELECT id, name" + "FROM products" ) ; while (productResultSet.next()) { System.out.println( productResultSet.getInt("id") + " productResultSet .getString ("name") ) ; productResultSet.close() ; } II end of displayProducts()

28. A worked Example Using JDBC • The following example displays the rows from the products table using the displayProducts () method, passing the reStatement and serStatementobjects to this method: • displayProducts(rcStatement); • displayProducts(serStatement) ; • At this point, since no changes have yet been made to the products table, both of these statements will display the same results:

29. A worked Example Using JDBC • Modern Science • Chemistry • Supernova • Tank War • Z Files • 2412: The Return • Space Force 9 • From Another Planet • Classical Music • Pop 3 • Creative Yell • My Front Line

30. A worked Example Using JDBC Next, I'll add a new row to the products table, update product #1 's name using reStatement, and commit the two changes: rcStatement:executeUpdate( "INSERT INTO products" + "(id, type_id, name, description, price) VALUES" + " (13, 1, 'JDBC Programming " 'Java programming', 49.99)" ) ; rcStatement.executeUpdate( "UPDATE products" + "SET name = 'New Science' " + “ WHERE id = 1" Next, I'll call the displayProduets () method again using reStatement: ); rcConnection. commi t () ; ILdisplayproducts (reStatement) ;

31. A worked Example Using JDBC Next, I'll call the displayProduets () method again using rcStatement: displayproducts (reStatement) ;

32. A worked Example Using JDBC • This time, the following results will be displayed; notice that the updated name and the new row are displayed: • New Science • Chemistry • Supernova • Tank War • Z Files • 2412: The Return • Space Force 9 • From Another Planet • Classical Music • Pop 3 • Creative Yell • My Front Line • JDBC Programming

33. A worked Example Using JDBC • If displayProducts () is called again using serStatement, the original unchanged rows will be displayed: • the new row and the updated name won't show up. • This is because serConnection's transaction isolation level is set to SERIALIZABLE, and such transactions don't see phantom rows or nonrepeatable reads (nor dirty reads). • The new row added by reStatement is considered a phantom row because it was added after the rows were read by serStatement in the first call to the displayProducts () method. • The update is considered nonrepeatable because it also occurred after the row was read; therefore, neither of the changes is seen by serStatement.

34. A worked Example Using JDBC • Example Program: AdvTransExample1.java illustrates transaction isolation and how to set the transaction isolation level to TRANSACTION_SERIALIZABLE for a Connection object, along with the subsequent effect on a transaction

35. Distributed Transactions • You've only seen transactions that involve one database. • You may need to write a transaction that involves multiple databases, and you use distributed transactions for that purpose. • For example, you might be developing a financial system that writes some information to one database and other information to a different database, and both sets of information must be committed or rolled back together.

36. Distributed Transactions • The JDBC optional package provides a set of interfaces that the Oracle JDBC extensions implement to provide support for distributed transactions • These are contained in • the javax. transaction.xa • oracle. jdbc.xa packages TIP • Enterprise javaBeans also supports distributed transactions. For further details, refer to the book Enterprise JavaBeans by Richard Monson-Haefel (O'Reilly, 2000). • If you need to write your own program that uses distributed transactions, you may examine the program shown in the next section to see the various steps you need to follow.

37. Distributed Transactions Example Program: AdvTransExample2.java