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 types of transactions:

●      JTA transactions – defined by the Java Transaction API (JTA) specification, which is part of the Java EE 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 data store you work with.

More information: JTA and Local Transactions

Connection Auto-commit Mode

With Open SQL/JDBC the underlying data store 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 committed 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 call  setAutoCommit(true).

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

More information: 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 “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 consecutive read operations has changed), or the “phantom read” (new data is added in the database between two consecutive read operations).

A new java.sql.Connectioninstance 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. Calling setTransactionIsolation() within the scope of a transaction results in an SQLException.