DB2 Isolation Levels: How Isolation Levels are useful, what are the different Isolation Level types(CS, RR, RS & UR), how it controls the concurrency, What is Concurrency?, which one is better to use among the four types? What are the advantages and disadvantages ?These are the questions we often encounter. So this topic explains these queries in detail.
In DB2 or in any Relational databases, the concurrency & data integrity is the key factor. Concurrency is the feature that makes multiple applications or users to access the resources at the same time without affecting each other’s work. Concurrency control is important as without the proper concurrency the applications may end with an invalid data access.
Isolation level parameter is one of the BIND parameters which plays a major role in the concurrency control. Basically it decides how the other applications are isolated from a data base object which is being accessed by one application. It decides when to acquire the locks and when to release the acquired locks.
DB2 supports page & row level locking at the program execution level, which means isolation level applies the row and/or page level locking. One thing to be noted is, regardless of the Isolation Level used database Manager applies the Exclusive locks on the rows that are being updated by any application.
Isolation level parameter decides:
- What type of lock that should apply on the Database resource when any Transaction/application is accessing a database object?
- How much duration that lock should remain on that object.
- Size of the lock, whether it should be at Row level or at page level.
- Accessibility of the locked DataBase object by other applications.
- What type of data that can be accessed by other applications.
- Whether other applications can update that data or only they can read it.
By setting the proper Isolation Level, the transactions can be isolated to achieve a good concurrency.
There are 4 types of isolation levels that can be applied
- Cursor Stability(CS)
- Repeatable read(RR)
- Read stability(RS)
- Uncommitted read(UR)
Cursor Stability (CS):
This is the most commonly used isolation levels among the 4 isolation levels and it is the default isolation level. CS locks the row that is being accessed and lock gets released once the cursor has moved from that row. So this isolation level gives more concurrency by locking only the row that is currently being accessed.
Notes:
- But this isolation level allows other application to change the data that has been accessed and released. So in this the results may with the old data.
- If data being updated using this isolation level, the locks remains on that row until it got committed.
- Advantage is that it locks minimum amount of data (one row) at one time, so that other applications can access this data with less wait time thus it gives more concurrency.
- Access to Uncommitted data is not possible.
- Phantom reads & non-repeatable reads are possible.
Repeatable Read (RR):
Repeatable read isolation level gives the highest level of data integrity which does not allow other applications to access the rows that is being scanned by one application.
For example if one transaction is trying to select 10 rows but in the process it has to scan 100 rows, then it applies locks on the whole 100 rows. In this if the first transaction fires the same query then the same number of rows will be retrieved.
Notes:
- As it locks all the rows that are referenced, it is not possible to have Lost Updates, Access to uncommitted data, non repeatable reads and Phantom reads.
- No other application is allowed to update, insert or delete any row that is referenced until the unit of work completes.
- It locks every row that is being referenced so it has more locking overhead.
- It even locks other rows which just referenced(not part of result set) thus preventing other application to access those data. So it needs to be used carefully to avoid the unnecessary locking.
- Use this for applications which expect the same set of rows for multiple times.
Read Stability (RS):
Read stability is same as the repeatable read isolation level except it locks only the rows which are being accessed by that particular transaction. Unlike the RR it does not lock all the rows which are scanned.
Notes:
- It ensures that any row that is qualified for a UOW can not be changed by other applications until UOW completes.
- Also any row that becomes qualified as part of any other application update, can not be read until it gets committed.
- Non-repeatable reads and uncommitted data access is not possible.
- Phantom reads are possible. As it allows to access the committed updates/inserts done by other applications(which are satisfied by the selection criteria).
- It does not encourage the table level locks to be applied.
Uncommitted read (UR):
Uncommitted read gives the lowest level of integrity as it does not apply any locks on the data that is being accessed. As name suggests it also accesses the rows which are not committed by other transactions/applications, so it also called as ‘Dirty reads€™.
Notes:
- Best suited for read only data bases.
- Generally this is used for reading purpose where there is not much interest on the data that is being returned i;e suited where the applications accepts uncommitted data as well.
