DB2 Isolation Level details

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 controls the row & page level locking.

Isolation level parameter decides:

  • What type of lock that should apply on the Db2 resource when any Transaction/application is accessing a database object?
  • What is the duration of that lock?
  • Size of the lock (Row, page)
  • The accessibility of the locked DB object by other applications.
  • What type of data that can be accessed by other applications.

By setting the proper isolation levels the transactions can be isolated to achieve a good concurrency.

There are 5 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 5 isolations levels. 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.

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:

  • It has more locking overhead & locks other rows 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.

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 read’.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Bitnami