Isolation Level in DB2.

As i mentioned earlier, i wanted to take this concept in a separate post.Isolation level parameter is the one you specify while binding your Application program's DBRM in to Plan/Package.

The problem with Isolation level is no matter how many times you read it or from which source you read it you won't understand it.I mean you just read it but when you have to actually use it in you shop, you wonder how to use it and this is because you don't have any practical example of it .once you have any real world example of each isolation level, you most probably will remember and most importantly use wisely in your instances.

So let's just understand each isolation level in detail with the real world example.

First of all understand what is isolation level and why we need it??

Several different users can access and modify data stored in a DB2 database at the same time, the DB2 Database Manager must be able to allow users to make necessary changes while ensuring that data integrity is never compromised.The sharing of resources by multiple interactive users or application programs at the same time is known as concurrency.
One of the ways DB2 enforces concurrency is through the use of isolation levels, which determine how data accessed and/or modified by one transaction is "isolated from" other transactions.

DB2 recognizes and supports the following isolation levels:
  • Repeatable Read     (RR)
  • Read Stability          (RS)
  • Cursor Stability       (CS)
  • Uncommitted Read (UR)


The Repeatable Read Isolation Level 

The Repeatable Read isolation level is the most restrictive isolation level available.

When it's used, the effects of one transaction are completely isolated from the effects of other concurrent transactions.

When this isolation level is used, every row that's referenced in any manner by the owning transaction is locked for the duration of that transaction. As a result, if the same SELECT SQL statement is issued multiple times within the same transaction, the result data sets produced are guaranteed to be identical. In fact, transactions running under this isolation level can retrieve the same set of rows any number of times and perform any number of operations on them until terminated, either by a commit or a rollback operation. However, other transactions are prohibited from performing insert, update, or delete operations that would affect any row that has been accessed by the owning transaction as long as that transaction remains active.
To ensure that the data being accessed by a transaction running under the Repeatable Read isolation level is not adversely affected by other transactions, each row referenced by the isolating transaction is locked—not just the rows that are actually retrieved or modified.

Thus, if a transaction scans 1,000 rows in order to retrieve 10, locks are acquired and held on all 1,000 rows scanned—not just on the 10 rows retrieved.

            

Real World Example : So how does this isolation level work in a real-world situation? lets's see..

Suppose you use a DB2 database to keep track of hotel records that consist of reservation and room rate information, and you have a Web-based application that allows individuals to book rooms on a first-come, first-served basis.
If your reservation application runs under the Repeatable Read isolation level, a customer scanning the database for a list of rooms available for a given date range will prevent you (the manager) from changing the room rate for any of the room records that were scanned to resolve the customer's query. Similarly, other customers won't be able to make or cancel reservations that would cause the first customer's list of available rooms to change if the same query were to be run again (provided the first customer's transaction remained active). However, you would be allowed to change room rates for any room record that was not read when the first customer's list was produced; likewise, other customers would be able to make or cancel room reservations for any room whose record was not read in order to produce a response to the first customer's query.

The Read Stability Isolation Level

The Read Stability isolation level is not quite as restrictive as the Repeatable Read isolation level; therefore, it does not completely isolate one transaction from the effects of other, concurrent transactions.

When the Read Stability isolation level is used, only rows that are actually retrieved or modified by the owning transaction are locked.

Thus, if a transaction scans 1,000 rows in order to retrieve 10, locks are only acquired and held on the 10 rows retrieved, not on the 1,000 rows scanned.

Because fewer locks are acquired, more transactions can run concurrently. As a result, if the same SELECT SQL statement is issued two or more times within the same transaction, the result data set produced may not be the same each time.

As with the Repeatable Read isolation level, transactions running under the Read Stability isolation level can retrieve a set of rows and perform any number of operations on them until terminated. Other transactions are prohibited from performing update or delete operations that would affect the set of rows retrieved by the owning transaction as long as that transaction exists; however, other transactions can perform insert operations.

Real World Example : We will take the same example and see how read stability change it.

Now, when a customer scans the database to obtain a list of rooms available for a given date range, you (the manager) will be able to change the rate for any room that does not appear on the customer's list. Likewise, other customers will be able to make or cancel reservations that would cause the first customer's list of available rooms to change if the same query were to be run again. As a result, if the first customer queries the database for available rooms for the same date range again, the list produced may contain new room rates and/or rooms that were not available the first time the list was generated.


The Cursor Stability Isolation Level

The Cursor Stability isolation level is even more relaxed than the Read Stability isolation level in the way it isolates one transaction from the effects of other concurrent transactions.

The cursor stability isolation level only locks the row that is currently referenced by a cursor that was declared and opened by the owning transaction. (The moment a record is retrieved from a result data set, a pointer—known as a cursor—will be positioned on the corresponding row in the underlying table, and that row will be locked. The lock acquired will remain in effect until the cursor is repositioned—more often than not by executing the FETCH SQL statement—or until the owning transaction terminates.) And because only one row-level lock is acquired, more transactions can run concurrently.

The Cursor Stability isolation level is the isolation level used by default.

When a transaction using the Cursor Stability isolation level retrieves a row from a table via a cursor, no other transaction is allowed to update or delete that row while the cursor is positioned on it. Other transactions, however, can add new rows to the table as well as perform update and/or delete operations on rows positioned on either side of the locked row—provided the locked row itself wasn't accessed using an index. Once acquired, the lock remains in effect until the cursor is repositioned or until the owning transaction is terminated. (If the cursor is repositioned, the lock being held is released and a new lock is acquired for the row to which the cursor is moved.) Furthermore, if the owning transaction modifies any row it retrieves, no other transaction is allowed to update or delete that row until the owning transaction is terminated, even though the cursor may no longer be positioned on the modified row.

As you might imagine, when the Cursor Stability isolation level is used, if the same SELECT SQL statement is issued two or more times within the same transaction, the results returned may not always be the same. In addition, transactions using the Cursor Stability isolation level will not see changes made to other rows by other transactions until those changes have been committed.

Real world example: Let's reserve the hotel again by using Cursor Stability

When a customer scans the database for a list of rooms available for a given date range and then views information about each room on the list produced (one room at a time), you (the manager) will be able to change the room rates for any room in the hotel except the room the customer is currently looking at (for the date range specified). Likewise, other customers will be able to make or cancel reservations for any room in the hotel except the room the customer is currently looking at (for the date range specified). However, neither you nor other customers will be able to do anything with the room record the first customer is currently looking at. When the first customer views information about another room in the list, you and other customers will be able to modify the room record the first customer was just looking at (provided the customer did not reserve it for himself). Again, neither you nor other customers will be able to do anything with the room record at which the first customer is currently looking.

The Uncommitted Read Isolation Level

The Uncommitted Read isolation level is the least restrictive isolation level available. In fact, when the Uncommitted Read isolation level is used, rows retrieved by a transaction are only locked if the transaction modifies data associated with one or more rows retrieved or if another transaction attempts to drop or alter the table the rows were retrieved from.

This isolation level is typically used for transactions that access read-only tables and views and for transactions that execute SELECT SQL statements for which uncommitted data from other transactions will have no adverse affect.

As the name implies, transactions running under the uncommitted read isolation level can see changes made to rows by other transactions before those changes have been committed. However, such transactions can neither see nor access tables, views, and indexes that are created by other transactions until those transactions themselves have been committed. The same applies to existing tables, views, or indexes that have been dropped; transactions using the uncommitted read will learn that these objects no longer exist only when the transaction that dropped them is committed. (It's important to note that when a transaction running under this isolation level uses an updatable cursor, the transaction will behave as if it is running under the Cursor Stability isolation level, and the constraints of the Cursor Stability isolation level will apply.)


Real World exampleHaven't you reserve the hotel yet?? ooh poor boy..

When a customer scans the database to obtain a list of available rooms for a given date range, you (the manager) will be able to change the room rates for any room in the hotel over any date range. Likewise, other customers will be able to make or cancel reservations for any room in the hotel, including the room at which the customer is currently looking. In addition, the list of rooms produced for the first customer may contain records for rooms for which other customers are in the processing of reserving or canceling reservations.

Choosing the Proper Isolation Level

So how do you decide which isolation level to use? The best way is to identify which types of phenomena are unacceptable, and then select an isolation level that will prevent those phenomena from occurring. A good rule of thumb is:
  • Use the Repeatable Read isolation level if you're executing large queries and you don't want concurrent transactions to have the ability to make changes that could cause the query to return different results if run more than once.
  • Use the Read Stability isolation level when you want some level of concurrency between applications, yet you also want qualified rows to remain stable for the duration of an individual transaction.
  • Use the Cursor Stability isolation level when you want maximum concurrency between applications, yet you don't want queries to see uncommitted data.
  • Use the Uncommitted Read isolation level if you're executing queries on read-only tables/views/databases or if it doesn't matter whether a query returns uncommitted data values.
Always keep in mind that choosing the wrong isolation level for a given situation can have a significant negative impact on both concurrency and performance—performance for some applications may be degraded as they wait for locks on resources to be released.

Specifying the Isolation Level to Use

Although isolation levels control concurrency at the transaction level, they are actually set at the application level. Therefore in most cases, the isolation level specified for a particular application is applicable to every transaction initiated by that application. (It is important to note that an application can be constructed in several different parts, and each part can be assigned a different isolation level, in which case the isolation level specified for a particular part is applicable to every transaction that is created within that part.)

For embedded SQL applications, the isolation level is specified at precompile time or when the application is bound to a database (if deferred binding is used). In this case, the isolation level is set using the ISOLATION [RR | RS | CS | UR] option of the PRECOMPILE and BIND commands.

DB2 Version 8.1 and later provides a WITH clause (WITH [RR | RS | CS | UR]) that can be appended to a SELECT statement to set a specific query's isolation level to Repeatable Read (RR), Read Stability (RS), Cursor Stability (CS), or Uncommitted Read (UR). A simple SELECT statement that uses this clause looks something like this:
SELECT * FROM employee WHERE empid = '001' WITH RR
 
I hope after reading this article you will no more have any problems on isolation level.

41 comments:

Anonymous said...

Hi Nitin

Iam new to DB2, this topic was very useful

Anonymous said...

really Useful..Thanks a loy Nitin

Anonymous said...

Hi Nitin,

I came across your blog by random google search. reallyyyy very useful stuff.

Anonymous said...

Really good Post Nitin...

I have been struggling a lot to understand the concept of Isolation, now its crystal clear....

Anonymous said...

Hi Nitin,

The best part was the explanation which made it fairly easy to understand and remember the details associated with the levels.

Thanks !!

Anonymous said...

Great post indeed!!!!!

Ganesh said...

Thanks Nithin. Please let me know if I can ask more questions on DB2 to you.

Anonymous said...

Really a good explanation Nithin, thanks!!

Anonymous said...

Thanks Nitin for sharing this!!!!!

Shashikant Chattar said...

Thank you Nitin for sharing this... It really helped me understand the isloation levels in depth.

Anonymous said...

Its really good and very informative...
Thank you Nitin!!

Anonymous said...

Awesome explanation Nithin. Thanks alot for sharing..

Anonymous said...

Very clear explanation. Thanks

Amit Samanta said...

very useful for begineer......

venkat said...

THIS 200% PERCENT HELPFULL FOR ME...

Pallamraju Adabala said...

excellent explanation...

Anonymous said...

Hi Nitin,

This is an excellent write-up. Its not easy to find this information all in one place, as you have presented it.

Thanks very much.
DBA from Australia.

Neeraj Saini said...

It is really awesome stuff shared and explained by you!!!

Thanks a million for this info!!

Anonymous said...

nice

Anonymous said...

Thanks a lot Nitin.. It helped me to understand things better.. Thanks again.. :-)

Anonymous said...

good share of knowledge

Anonymous said...

Goot stuff

Anonymous said...

Thanks a lot..Cleared my concepts on Isolation levels !!!!!

Unknown said...

Excellent explanation to Isolation level...

Sweta Singh said...


Thanks Nitin for explaining it with real life example ...gr88 stuff :)

Unknown said...

Thank you very much very clear and simple...great stuff and very good explanation...

Anonymous said...

Thanks nitin.. it was really useful... gr8 work

Anonymous said...

Thank you very much Nitin...Your explanation cleared all my doubts i had on Isolation.

Anonymous said...

Best explanation ive read regarding isolation levels. Thank you so much Nitin for sharing your knowledge.

Programmer from the Philippines

Anonymous said...

helped a lot..thanks!

Anonymous said...

well explained.. thank you

Anonymous said...

Always ensure you choose a method that is proven to
remove skin tags without causing adverse effects and scarring.

After all, they can be readily sourced from the internet.
But since something was happening I decided to
keep trying for a few more nights.

Here is my web-site :: skintagsremoval16ab.webstarts.com

Anonymous said...

Cruises are also available in themes allowing
you to find the best option for your vacation

my weblog - six flags hurricane harbor los angeles coupons

Bharani said...

awesome.. I now got a fair idea about the isolation levels..

Thanks for the info

Unknown said...

Very nice article...this has brushed my knowledge perfectly.

saeem syed said...

Great info. Thanks!!

Karthik P said...

thanks very useful article

rohit barthwal said...

extremely delighted to land into your blog...understanding DB2 have become easy and interesting...thank you nitin for enlightening...keep writing...!!!

Karthik said...

Thanks a lot ! This was very useful article..

Mainframe developer said...

Well explained!! Thanks

Anonymous said...

Thanks and I have a super provide: House Renovation What To Do First renovate my home

Post a Comment