transaction question

Question

Evidently I was really wrong in this thread

http://stackoverflow.com/questions/3304347/3304365#3304365

how is it that you can get an inconsistent result from a read without a transaction?

edit -- the original question was in the context of nhibernate. Is this nhibernate specific?

Answer 1

No, it is not hibernate specific.

Short answer: Transactions are "atomic units of work" with a consistent view of the world. Once the transaction is left the protected "view" needs to be rectified with the view of the world (COMMITTTED) -- or, in the case of a read-only transaction, the consistent view can simply be absolved (it only needed to be consistent during the transaction).

Longer answer: There are many different types of transactions (READ UNCOMMITTED, READ COMMITTED, SERIALIZABLE, REPEATABLE READ, etc.) that affect the fine details.

See Wiki: Database Transaction and Isolation (DBMS) -- the latter takes a few link clicks to find :-)

Imagine this quickly contrived sequence, A and B represent different actors using the DB and each action runs in it's own one-off implicit transaction (and nothing else):

1. A reads in the list of users
2. B deletes a user (and all associated data)
3. A gets the information for each user (using the previous fetched info). However, there is at least one user who no longer exists. Better deal with it somehow.

See the "Example Queries" section of the Isolation wiki article.