<< Locking, concurrency, and isolation | Transaction anomalies >>

Isolation levels and concurrency

Derby Developer's Guide

This section discusses topics pertinent to multi-user systems, in which concurrency is
important.

Derby is configured by default to work well for multi-user systems. For single-user
systems, you might want to tune your system so that it uses fewer resources; see

Lock

granularity

Isolation levels and concurrency

Derby provides four transaction isolation levels. Setting the transaction isolation level
for a connection allows a user to specify how severely the user's transaction should be
isolated from other transactions.

For example, it allows you to specify whether transaction A is allowed to make changes
to data that have been viewed by transaction B before transaction B has committed.

A connection determines its own isolation level, so JDBC provides an application with
a way to specify a level of transaction isolation. It specifies four levels of transaction
isolation. The higher the transaction isolation, the more care is taken to avoid conflicts;
avoiding conflicts sometimes means locking out transactions. Lower isolation levels thus
allow greater concurrency.

Inserts, updates, and deletes always behave the same no matter what the isolation level
is. Only the behavior of select statements varies.

To set isolation levels you can use the JDBC Connection.setTransactionIsolation method
or the SQL SET ISOLATION statement.

If there is an active transaction, the network client driver always commits the active
transaction, whether you use the JDBC Connection.setTransactionIsolation method
or the SQL SET ISOLATION statement. It does this even if the method call or
statement does not actually change the isolation level (that is, if it sets the isolation
level to its current value). The embedded driver also always commits the active
transaction if you use the SET ISOLATION statement. However, if you use the
Connection.setTransactionIsolation method, the embedded driver commits the active
transaction only if the call to Connection.setTransactionIsolation actually changes the
isolation level.

The names of the isolation levels are different, depending on whether you use a JDBC
method or SQL statement. The following table shows the equivalent names for isolation
levels whether they are set through the JDBC method or an SQL statement.

Table 6.

Mapping of JDBC transaction isolation levels to Derby isolation levels

Isolation Levels for JDBC

Isolation Levels for SQL

Connection.TRANSACTION_READ_UNCOMMITTED
(ANSI level 0)

UR, DIRTY READ, READ
UNCOMMITTED

Connection.TRANSACTION_READ_COMMITTED (ANSI
level 1)

CS, CURSOR STABILITY,
READ COMMITTED

Connection.TRANSACTION_REPEATABLE_READ
(ANSI level 2)

Connection.TRANSACTION_SERIALIZABLE (ANSI level
3)

RR, REPEATABLE READ,
SERIALIZABLE

These levels allow you to avoid particular kinds of transaction anomalies, which are
described in the following table.