Discussing with a colegue about defining transactions in our application we finished by talking about transaction isolation levels and finally I got into problems explaining the problems faced in concurrent transactions (I wasn’t able to tell the difference between non-repeatable and phantom reads [sigh/]). I decided to write down for further references.
Transaction isolation levels are defined according to the state of data during the following 3 scenarios (they compair the data accessed from 2 different transactions):
* dirty reads
occur when a transaction reads data written by concurrent uncommited transaction;
* non-repeatable reads
occur when a transaction re-reads data it has previously read and finds that data has been
modified by another transaction (that committed since the initial read);
* phantom read
occur when a transaction re-executes a query returning a set of rows that satisfy a search
condition and finds that the set of rows satisfying the condition has changed due to another
recently-committed transaction;
Note: even if it seems that non-repeatable reads and phantom reads are the same thing, the difference is made from the fact that in the 1st case the same rows are returned but their data is modified, while in the 2nd case another set of rows is returned.
Defined isolation levels:
| DB Isolation Level | Dirty Read | Non-Repeatable Reads | Phantom read |
|---|---|---|---|
| Read uncommitted | Y | Y | Y |
| Read committed | N | Y | Y |
| Repeatable read | N | N | Y |
| Serializable | N | N | N |
| Java Level | Dirty Read | Non-Repeatable Reads | Phantom read |
|---|---|---|---|
| TRANSACTION_NONE | Y | Y | Y |
| TRANSACTION_READ_UNCOMMITTED | Y | Y | Y |
| TRANSACTION_READ_COMMITED | N | Y | Y |
| TRANSACTION_REPEATABLE_READ | N | N | Y |
| TRANSACTION_SERIALIZABLE | N | N | N |
Still I would like to see more details on the separation made on non-repeatable reads and phantom reads.
mindstorms 