<< Isolation levels and concurrency | Configuring isolation levels >>

Transaction anomalies

Derby Developer's Guide

Table 7.

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

When transaction anomalies are possible