Phenomena

If several transactions access the same database object, such as a table, at the same time, this can result in inconsistencies in the results. The following phenomena can occur:

·        Dirty Read

Transaction T1 changes a row. Transaction T2 reads this row before T1 ends the transaction with COMMIT. T1 then performs a ROLLBACK.

In this case, T2 has read a row that never existed.

·        Non Repeatable Read

Transaction T1 reads a row. Transaction T2 then changes or deletes this row and closes the transaction with COMMIT. If T1 then reads the row again, it either gets the modified row or a message indicating that the row no longer exists.

·        Phantom

Transaction T1 performs an SQL statement S with a search condition that returns a result set M of rows. Transaction T2 then creates at least one additional row that meets this search condition, for example, by adding or changing data. If T1 subsequently executes S again, then the new result set is different to M.

You can configure which phenomena are possible with the isolation level.

Relationship Between the Isolation Level and Phenomena

Phenomenon

Isolation Level

0

1 or 10

2 or 20

3 or 30

Dirty Read

Yes

No

No

No

Non Repeatable Read

Yes

Yes

No

No

Phantom

Yes

Yes

Yes

No

See also:

Locking for Database Objects