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 1. 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) RS
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.

Table 2. Transaction anomalies
Anomaly Example
Dirty Reads

A dirty read happens when a transaction reads data that is being modified by another transaction that has not yet committed.

Transaction A begins.
UPDATE employee SET salary = 31650
WHERE empno = '000090'
Transaction B begins.
SELECT * FROM employee
(Transaction B sees data updated by transaction A. Those updates have not yet been committed.)
Nonrepeatable Reads

Nonrepeatable reads happen when a query returns data that would be different if the query were repeated within the same transaction. Nonrepeatable reads can occur when other transactions are modifying data that a transaction is reading.

Transaction A begins.
SELECT * FROM employee
WHERE empno = '000090' 
Transaction B begins.
UPDATE employee SET salary = 30100
WHERE empno = '000090'
(Transaction B updates rows viewed by transaction A before transaction A commits.) If Transaction A issues the same SELECT statement, the results will be different.
Phantom Reads

Records that appear in a set being read by another transaction. Phantom reads can occur when other transactions insert rows that would satisfy the WHERE clause of another transaction's statement.

Transaction A begins.
SELECT * FROM employee
 WHERE salary > 30000 
Transaction B begins.
INSERT INTO employee
(empno, firstnme, midinit,
lastname, job,
salary) VALUES ('000350', 'NICK',
'A','GREEN','LEGAL COUNSEL',35000)
Transaction B inserts a row that would satisfy the query in Transaction A if it were issued again.

The transaction isolation level is a way of specifying whether these transaction anomalies are allowed. The transaction isolation level thus affects the quantity of data locked by a particular transaction. In addition, a DBMS's locking schema might also affect whether these anomalies are allowed. A DBMS can lock either the entire table or only specific rows in order to prevent transaction anomalies.

The following table shows which anomalies are possible under the various locking schemas and isolation levels.

Table 3. When transaction anomalies are possible
Isolation Level Table-Level Locking Row-Level Locking
TRANSACTION_READ_UNCOMMITTED Dirty reads, nonrepeatable reads, and phantom reads possible Dirty reads, nonrepeatable reads, and phantom reads possible
TRANSACTION_READ_COMMITTED Nonrepeatable reads and phantom reads possible Nonrepeatable reads and phantom reads possible
TRANSACTION_REPEATABLE_READ Phantom reads not possible because entire table is locked Phantom reads possible
TRANSACTION_SERIALIZABLE None None

The following java.sql.Connection isolation levels are supported:

Related concepts
Configuring isolation levels
Lock granularity
Types and scope of locks in Derby systems
Deadlocks
Related reference
Scope of locks