I'm having a difficult time understanding isolation levels. I know what problems each isolation level solves (i.e. dirty reads, nonrepeatable reads, phantom reads) and the classic textbook description of each level, but I simply cannot understand how it works. Let me explain my understanding of the various isolation levels:

READ_UNCOMMITTED:
The data read by TX 1 is held in a read lock. TX 1 modifies the data. TX 2 therefore can read that data (but cannot write to it, due to the read lock). TX 2 can therefore read uncommitted data.

READ_COMMITTED:
The data read by TX 1 is held in a write lock. TX 1 modifies the data. TX 2 cannot read the data because of the write lock, hence solving the dirty read problem. It cannot read the data TX 1 has so much as read during the course of its transaction (true?).

REPEATABLE_READ:
This is the biggest source of my confusion. How is it that the nonrepeatable read problem is not solved by READ_COMMITTED? TX 1 reads some data, TX 2 cannot read that data due to the read lock...but somehow it manages to modify TX 1's data so that when TX 1 repeats its query, it gets different results? How is this possible? And apart from the read and write lock of the two previous isolation levels, what does the database do to enforce this new isolation level on top of the other two?

SERIALIZABLE:
TX 2 cannot do anything without TX 1 finishing. (But isn't this similar to READ_COMMITTED, whereby TX 2 cannot even read TX 1's data until it has committed.) What is being meant by sequential execution here?

Are isolation levels built upon each other? i.e. REPEATABLE_READ implies READ_COMMITTED, which implies READ_UNCOMMITTED, like how 3NF implies 2NF which implies 1NF?

I think a source of confusion is that I am unaware of whether isolation levels are applied to an entire database, to a transaction, to a query, or some other category. Can one transaction have one isolation level while another transaction has another isolation level?

Any insight into isolation levels would be appreciated. Thanks.

vk101

Ten days and no one has responded, so here goes. Here is what I would give you in terms of direction to address your confusion, rather than attempting to answer your specific questions (which would be onerous). Take each of these items separately.

1 Terms such as ISOLATION LEVEL, READ_UNCOMMITTED, are ANSI SQL, the standards body. They are REQUIREMENTS that each vendor has to comply with, if they are going to state that they implement ANSI SQL 92 or whatever. But they have to be appreciated as theoretical requirements from a standards body. They are not practical terms.

2 Each vendor provides a DBMS. That is practical. In order to prevent lost updates and to reduce contention, they implement a LOCKING SCHEME. Each vendor has a completely different Locking Scheme, which (if you believe the literature) is for competitive purposes, and makes their offering "better" than the others. Terms such as READ_LOCK, WRITE_LOCK are vendor-specific. The Locking Scheme has very little to do with Isolation Levels, ie. understand the Locking Scheme first, then try to understand how the Isolation Level are applied. Knowledge of the particular products Locking Scheme will allow you to reduce the number or duration of locks used in a query, and therefore allow you to write better/worse code, but now we have departed from the ANSI requirement, and we are getting into a product-specific implementation. "How it works" is very specific to a vendor/product. You need to understand not only the type of lock, but the duration for which it is held, and that within the Isolation Level. If the product held "read_locks" the way you think it does under your discussion of Repeatable Read, the system would be completely locked up with contention, so most products do not do that. In general (vast, non-product-specific generalisation), read locks are only held for the duration of the read (Select statement), not the transaction.

3 Each vendor provides a product that is always a superset of ANSI SQL, in order to be provide features and functions that are relevant to the market, and to be competive. ANSI SQL alone will not make a product. Eg. Sybase (and MicroShaft, in the initial codeline which was Sybase) provide Transact-SQL.

4 Each vendor then makes a statement of compliance re Isolation levels. This creates a compliance grid. Some vendors are stricter than others, in the sense that some require a departure ANSI in order to execute effectively, and others are faithful to ANSI while executing effectively.

Eg. Isolation Levels are actually TRANSACTION Isolation Levels.

Eg. Some products allow you to set Isolation Levels for the (a) session, (b) transaction, and (c) change them for a duration within the session/transaction/statement. Others do not, or have lesser ability to change the session setting.

I can answer product-specific questions re Isolation Levels and Locking Schemes for Sybase and MicroShaft SQL Server <insert year, insert new Locking Scheme>, and I have a working but not expert knowledge of Barnacle.

Cheers