JDBC Transaction Isolation Levels

The JDBC definition provides five transaction isolation levels. NuoDB supports a subset of these levels and provides alternative isolation levels. It also offers additional isolation mechanisms, not defined by JDBC, that can be used in place of unsupported levels. For more information, see Supported Transaction Isolation Levels.

Other client drivers may not support isolation in the same way as the JDBC Driver. Refer to the documentation for each driver to identify the supported isolation levels.

Isolation Levels

The following table describes 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)

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.	Use `TRANSACTION_CONSISTENT_READ` instead which supports repeatable read and prevents both dirty and phantom reads.
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.	Use `TRANSACTION_CONSISTENT_READ` instead which supports repeatable read and prevents both dirty and phantom reads.
8	`Connection. TRANSACTION_SERIALIZABLE`	yes	Dirty reads, non-repeatable reads and phantom reads are prevented.	No Blocking. Update conflicts may occur. `TRANSACTION_SERIALIZABLE` Automatically maps to `TRANSACTION_CONSISTENT_READ` in NuoDB.
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. 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`.

Connection. TRANSACTION_NONE

No Transactions.

Connection. TRANSACTION_READ_UNCOMMITTED

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

Use TRANSACTION_CONSISTENT_READ instead which supports repeatable read and prevents both dirty and phantom reads.

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.

Connection. TRANSACTION_REPEATABLE_READ

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

Use TRANSACTION_CONSISTENT_READ instead which supports repeatable read and prevents both dirty and phantom reads.

Connection. TRANSACTION_SERIALIZABLE

yes

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

No Blocking.
Update conflicts may occur.
TRANSACTION_SERIALIZABLE Automatically maps to TRANSACTION_CONSISTENT_READ in NuoDB.

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. 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.

The default isolation level is TRANSACTION_CONSISTENT_READ.

Configure Isolation Level

The isolation level can be configured using any of the following methods:

Configure the isolation level in the connection URL

To set the isolation property to all the connections created using the connection URL, specify isolation=read_committed|consistent_read.

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

Configure the isolation level in the java program using the setTransactionIsolation() method:

For example:

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

Configure the isolation level using Spring’s transactional support

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

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

Spring does not know about NuoDB consistent-read isolation level. consistent-read is the default isolation level, so setting isolation = Isolation.DEFAULT in @Transactional, will defer to consistent read.

For example:

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

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

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