Transactions in Open SQL/JDBC 

Use

The portability, which Open SQL/JDBC provides across different databases, imposes certain requirements for the transactional behavior of the database connections. Therefore, when working with Open SQL you must consider the specifics, which distinguish Open SQL/JDBC resources from pure JDBC resources.

Features

When working with the persistence layer, you may use two type of transactions:

·        JTA transactions – defined by the Java Transaction API (JTA) specification, which is part of the J2EE standard. These are distributed transactions, managed by a transaction manager, which is external to the data store. JTA transactions are either container-driven, or component-driven. To demarcate a component-driven JTA transaction, you must use the javax.transaction.UserTransaction interface.

·        Local transactions – these are transactions, which work with a single resource manager that manages their execution. You can demarcate a local transaction using the appropriate API for the datastore you work with.

For more information, see JTA and Local Transactions.

Connection Auto-commit Mode

With Open SQL/JDBC the underlying datastore is a relational database. To work with it, your application component must obtain an instance of java.sql.Connection. A new connection instance is created in auto-commit mode, which means that each SQL statement executed using this connection is commited separately in the database.

If you call the setAutoCommit(false) method on a connection before a JTA transaction has started and before any SQL statement has been executed using this connection, the auto-commit mode of the connection is disabled. Then an invocation prepareStatement() on the Connection object, execute(), executeUpdate(), executeQuery(), executeBatch() on a Statement object that has been created through this Connection object, starts a local transaction, which is completed when you call connection.commit() or connection.rollback(). The auto-commit mode after the end of the local transaction remains unchanged. To enable it, you must invoke setAutoCommit(true).

Within the scope of a JTA transaction the auto-commit mode is disabled and you cannot manage it – that is, you cannot invoke the setAutoCommit() method on the connection.

 

See also:

Using Local Transactions

 

Transaction Isolation Levels

The transaction isolation level defines what data is visible to an SQL statement within a transaction. The SQL99 standard defines four isolation levels, which are also adopted in the JDBC standard: TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, and TRANSACTION_SERIALIZABLE.

For portability reasons Open SQL/JDBC supports only the following isolation levels:

·        TRANSACTION_READ_UNCOMMITTED – this isolation level enables a transaction to read data, which is being modified by a concurrently running transaction. This might cause the so-called “dirty read” problem – that is, the read data might not be consistent with the persistent data because the changes might not be committed.

·        TRANSACTION_READ_COMMITTED – this isolation level makes changes done within a transaction invisible to other concurrent transaction, which may only read the changes after they have been committed to the database. Thus, the “dirty read” problem is solved, but still there are other issues, such as the “nonrepeatable read” (data between two consequtive read operations has changed), or the “phantom read” (new data is added in the database between two consequtive read operations.)

If you need to use a higher isolation level, you must use the server-side locking mechanism. For more information, see Architecture of the Locking Adapter Service

A new java.sql.Connection instance is assigned TRANSACTION_READ_UNCOMMITTED isolation level. You can change the isolation level using the setTransactionIsolation() method of the java.sql.Connection interface:

Connection conn = dataSource.getConnection(); conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

 

You cannot change the transaction isolation level when the connection is in use in a JTA or a local transaction. Invoking setTransactionIsolation() within the scope of a transaction results in an SQLException.