Standard SQL defines four isolation levels that can be set using the SET TRANSACTION ISOLATION LEVEL statement: Serializable, Repeatable Read, Read Committed, and Read Uncommitted. Although all of the popular relational DBMSs support isolation level, the exact naming and implementation will vary.
The isolation level determines the mode of page or row locking implemented by the program as it runs. Typically, page or row-level locks are acquired by the DBMS as needed. The isolation level determines when locks are released.
Cursor Stability (CS) is a common implementation of the SQL standard Read Committed isolation level. CS is perhaps the most common isolation level in production applications because it offers a good tradeoff between data integrity and concurrency. With CS the program will never read data that is not yet committed.
A higher level of integrity is provided with Repeatable Read (RR). Under an RR isolation level all page (or row) locks are held until they are released by a COMMIT (or ROLLBACK), whereas with CS read-only page locks are released as soon as another page is accessed.
An RR page locking strategy is useful when a program requires consistency for data that may be accessed more than once as the program runs, or when an application requires data integrity that cannot be achieved with CS.
For example, it may make sense to use RR page locking for a reporting program that scans a table to produce a detailed report, and then scans it again to produce a management or summarized report. Suppose you are reporting estimated completion dates for project activities. One report lists every project and its estimated completion date; the second report lists only the projects with a completion date greater than one year. Clearly we want these reports to align.
Why? Let’s say the first report shows two activities scheduled for more than 1 year. After the first report is run but before the second, a manager invokes a transaction to change the estimated completion date of one of her project's activities from 8 months to 14 months. The second report is produced by the same program, but now reports 3 activities.
If the program used RR isolation rather than CS, updates that occur after the first report but before the second would not be allowed. The program would have maintained the locks it held from the generation of the first report and the updater would be locked out until the locks were released. This is just one example of the type of issues that can arise when choosing the wrong isolation level.
So, if CS has the potential to cause problems, why is it so popular? The answer is simple: The types of problems outlined are rare and can often be avoided by judicious coding and scheduling (e.g. don’t run a modification program during report generation). Furthermore, the expense of using RR, can be substantial in terms of concurrency.
The next isolation level to consider is Read Stability (RS), which provides more control than CS but less than RR. With RS, a retrieved row or page is locked until the end of the unit of work; no other program can modify the data until the unit of work is complete, but other processes can insert data that might be read by your application if it accesses the row a second time.
Consider using RS over RR when your program can handle retrieving a different set of rows each time a cursor or singleton SELECT is issued. If your application requires the same number of rows to be returned each time the same (or similar SQL is run), use RR instead of RS.
Finally, we come to the last, and most maligned isolation level, Uncommitted Read (UR). The UR isolation level provides read-through locks, also known as dirty read or read uncommitted. When you're using an uncommitted read, an application program can read data that has been changed but is not yet committed.
Using UR to read uncommitted data can be a performance booster because data is read without taking locks, but it can cause data integrity problems. For more details on dirty reads, consult another article I wrote on the topic at www.dbta.com/Columns/DBA-Corner/The-Danger-of-Dirty-Reads-98511.aspx.
My advice is to avoid UR whenever the results must be 100% accurate. In general, most serious production applications are not viable candidates for dirty reads.
The bottom line is that it is important for DBAs and application developers to know the four isolation levels and their impact on SQL and locking. Setting the Isolation level for your program or SQL statement impacts how the database system acquires locks and can therefore change the behavior of your applications.