Java Programming with Oracle SQLJ

9.1. Transaction Processing Problems

Modern relational database systems, including Oracle, support multiple transactions that access the same data. Concurrent transaction processing must address the following three problems that may occur when multiple transactions access the same rows in a table:

Dirty reads

Transaction 1 updates a row but doesn't commit the change. Transaction 2 reads the updated row. Transaction 1 then performs a rollback. The row that Transaction 2 previously read is invalid, or "dirty," because the update made by Transaction 1 wasn't committed when the row was read by Transaction 2.

Non-repeatable reads

Transaction 1 reads a row. Transaction 2 updates the row just read by Transaction 1. Transaction 1 then reads the same row again, and finds that the row has changed. This is referred to as a "non-repeatable" read, because the row originally read by Transaction 1 was changed by Transaction 2.

Phantom reads

Transaction 1 reads a selection of rows that satisfy a certain WHERE clause. Transaction 2 then inserts a row that happens to satisfy the WHERE clause used by the query in Transaction 1. Transaction 1 then reads the selection of rows again, and finds the additional row. This is referred to as a "phantom" row because it seems to have appeared from nowhere.

To combat these problems, designers of database systems take steps to isolate transactions from each other.