This is the articles that I found related to Isolation Level. It’s helpful to handle database concurrency issue.
Databases (and other transactional systems) attempt to ensure transaction isolation, meaning that, from the point of view of each concurrent transaction, it appears no other transactions are in progress. Traditionally, this has been implemented using locking. A transaction may place a lock on a particular item of data, temporarily preventing access to that item by other transactions. Some modern databases such as Oracle and PostgreSQL implement transaction isolation using multiversion concurrency control, which is generally considered more scalable. We discuss isolation assuming a locking model (most of our observations are also applicable to multiversion concurrency).
This discussion is about database transactions and the isolation level provided by the database. NHibernate doesn’t add additional semantics; it uses whatever is available with a given database. If you consider the many years of experience that database vendors have had with implementing concurrency control, you’ll clearly see the advantage of this approach. Your part, as a NHibernate application developer, is to understand the capabilities of your database and how to change the database isolation behavior if required by your particular scenario (and by your data-integrity requirements).
Isolation Issues
First, let’s look at several phenomena that break full transaction isolation. The ANSI SQL standard defines the standard transaction isolation levels in terms of which of these phenomena are permissible:
- Lost update— Two transactions both update a row, and then the second transaction aborts, causing both changes to be lost. This occurs in systems that don’t implement any locking. The concurrent transactions aren’t isolated.
- Dirty read— One transaction reads changes made by another transaction that hasn’t yet been committed. This is dangerous, because those changes may later be rolled back.
- Unrepeatable read— A transaction reads a row twice and reads different state each time. For example, another transaction may have written to the row, and committed, between the two reads.
- Second lost updates problem— This is a special case of an unrepeatable read. Imagine that two concurrent transactions both read a row, one writes to it and commits, and then the second writes to it and commits. The changes made by the first writer are lost. This problem is also known as last write wins.
- Phantom read— A transaction executes a query twice, and the second result set includes rows that weren’t visible in the first result set. (It need not be exactly the same query.) This situation is caused by another transaction inserting new rows between the execution of the two queries.
Now that you understand all the bad things that could occur, we define the various transaction isolation levels and see what problems they prevent.
Isolation Levels
The standard isolation levels are defined by the ANSI SQL standard. You’ll use these levels to declare your desired transaction isolation later:
- Read uncommitted— Permits dirty reads but not lost updates. One transaction may not write to a row if another uncommitted transaction has already written to it. But any transaction may read any row. This isolation level may be implemented using exclusive write locks.
- Read committed— Permits unrepeatable reads but not dirty reads. This may be achieved using momentary shared read locks and exclusive write locks. Reading transactions don’t block other transactions from accessing a row. But an uncommitted writing transaction blocks all other transactions from accessing the row.
- Repeatable read— Permits neither unrepeatable reads nor dirty reads. Phantom reads may occur. This may be achieved using shared read locks and exclusive write locks. Reading transactions block writing transactions (but not other reading transactions), and writing transactions block all other transactions.
- Serializable— Provides the strictest transaction isolation. It emulates serial transaction execution, as if transactions had been executed one after another, serially, rather than concurrently. Serializability may not be implemented using only row-level locks; another mechanism must prevent a newly inserted row from becoming visible to a transaction that has already executed a query that would return the row.