JDBC Transaction Isolation Levels Supported by NuoDB

See also Transaction Isolation Levels in the SQL section of this documentation.

The JDBC definition provides five transaction isolation levels although NuoDB does not support all of them. However, NuoDB also supports an additional isolation mechanism that is not in the JDBC standard, which can be used instead of the levels it does not support.

Other NuoDB drivers may not support isolation in the same way as the JDBC Driver. Consult the documentation for each driver you are using to find which isolation levels are supported by that driver.

Isolation Levels

The following table describe the levels that are supported by NuoDB. The definition of read semantics are according to the JDBC specification. NuoDB’s use of consistent-read transactions provides the same or better semantics.

ID JDBC Constant NuoDB JDBC
Driver Support		 Description (Reads only) Description (Updates)

0

Connection.
TRANSACTION_NONE

no

No Transactions.

1

Connection.
TRANSACTION_READ_UNCOMMITTED

no

Dirty reads, non-repeatable reads and phantom reads are allowed.

(a)

2

Connection.
TRANSACTION_READ_COMMITTED

yes

Dirty reads are prevented; non-repeatable reads and phantom reads can occur.

Blocks on conflict;
Waits for other transaction to finish.

4

Connection.
TRANSACTION_REPEATABLE_READ

no

Dirty reads are prevented; non-repeatable reads happen after writes; phantom reads can occur.

(a)

8

Connection.
TRANSACTION_SERIALIZABLE

yes (b)

Dirty reads, non-repeatable reads and phantom reads are prevented.

No Blocking;
update conflicts may occur.

7

com.nuodb.jdbc.
TransactionIsolation.
TRANSACTION_CONSISTENT_READ

yes

NuoDB only. Native level.
This is equivalent to snapshot isolation.
Repeatable reads, no phantoms, but some write anomalies that would not occur if the transactions were run in a single series.

No blocking;
update conflicts may occur. (c)
NOTES

  1. Not supported directly. Use TRANSACTION_CONSISTENT_READ instead which supports repeatable read and prevents both dirty and phantom reads.

  2. Becomes TRANSACTION_CONSISTENT_READ in NuoDB.

  3. It is possible to perform writes during a consistent-read transaction provided no other transaction has changed the data first. In the event of a write conflict the entire consistent-read transaction will be rolled back at commit time. If write conflicts are likely, use TRANSACTION_READ_UNCOMMITTED.

SUMMARY

Unless you explicitly request TRANSACTION_READ_COMMITTED, all transactions use TRANSACTION_CONSISTENT_READ isolation by default.

Defining Isolation

  1. Change the default isolation level

    • Set the isolation property in the connection URL.

      • read_committed or consistent_read are the two possible values (case-sensitive).

    • It will apply to all connections created using this URL.

    • Example:

      jdbc:com.nuodb://localhost/test?isolation=read_committed

  2. Change the isolation level programmatically:

    try (Connection dbConnection = dataSource.getConnection()) {
    dbConnection.setAutoCommit(false);
    dbConnection.setTransactionIsolation(Connection.READ_COMMITTED);
    ... do some work ...
}

  3. Using Spring’s transactional support

    • Annotate a method with org.springframework.transaction.annotation.Transactional annotation:

      @Transactional(isolation = Isolation.READ_COMMITTED)
public Account fetchAccount(String accountNumber) {
    ... do some work ...
    return account;
}

    • Spring does not know about NuoDB’s consistent-read isolation but fortunately it is the default:

      /* When default is specified, Spring defers to the database's default isolation. */
@Transactional(isolation = Isolation.DEFAULT)
public Account fetchAccount(String accountNumber) {
    ... do some work ...
    return account;
}

      If @Transactional is applied to an interface, all the methods on that interface are transactional in any implementation of that interface.

      Note that Spring and Spring Boot use different default implementations for transactions. Spring transactions are implemented using interface based proxies by default, Spring Boot defaults to CGLIB (class-based) proxies instead.