ISOLATION LEVEL IN DB2 with example
DB2 recognizes following isolation level:
A. REPEATABLE READ
B. READ STABILITY
C. CURSOR STABILITY
D. UNCOMMITTED READ
The sharing of Resources by multiple users or application programs at the same time is called'CONCURRENCY'. One of the ways DB2 enforces this concurrency is through the use of 'ISOLATION LEVEL' which determines how the data accessed or modified by one Transaction is "ISOLATED FROM" other Transactions.
REPEATABLE READ(RR): This is the Most Restrictive Isolation level available.Restrictive in the sense,when this is used every row referenced in any manner is locked for the duration of that transaction.
Thus if a transaction ,say: select Fname form employee where emp_id > '25' is run against employee table having 1000 rows .So DB2 scans all the 1,000 rows in the table and only 10 rows are fetched as per the where clause condition, locks are held on all the 1000 rows scanned.
Example in Real life: Lets say your hotel uses DB2 to keep track of its data. It has a web based reservation system and it runs under RR isolation level.
Customer 'A' looks for sea-facing rooms for given date-range. He will book a single room but he is checking all the rooms that are sea-facing side. Now ,You the manager wont be able to make change to any room's rate which were scanned by customer 'A'. Similary customer 'B' wont be able to book any rooms which 'A' scanned.(provided customer 'A's Transaction remains active)
However Manager and Cust 'B' can make any change/reservation to other Rooms which were not included in Customer 'A' s Scan list.
READ STABILITY(RS): This is not as Restrictive as RR. Here only the rows that are actually retrieved or modified are locked. Thus if the same transaction scans 1000 rows in order to retrieve 10 rows, locks will be placed on 10 rows, not on the 1000 rows scanned.
Example Continued : Now the same application has applied RS isolation level.
Suppose the customer 'A' Scans the rooms for the given date-range. You the manager and Cusotmer 'B' can make modifications/reservations for the rooms that does not appear on 'A's list.
CURSOR STABILITY(CS): This is more Relaxed that RS. This isolation level only locks the ROW that is currently referenced by the cursor. The moment the ROW is retrieved, no other Transaction is allowed to update that row while the cursor is positioned on it. Other Transactions can ADD/MODIFY/DELETE rows on either side of the locked row.
Example Continued : Customer 'A' scans for the room. Now rooms will be displayed on the list(one room at a time supppose room number '1') . You the manager can change the rates for any rooms except the room the customer is currently checking.Now suppose the customer 'A' looks for other rooms, you and other customers can modify/reserve room number '1'
""""Default isolation level used is CS in DB2 """"
UNCOMMITTED READ(UR): Least Restrictive. Rows retrieved by a transaction are only locked if the transaction modifies the data or if another transaction attempts to drop/alter the tables, the rows are retrieved from
Example Continued : Customer 'A' Scans for rooms. You being the manager will be able to change the room rates for any rooms. Customer 'B' can make/cancel reservation for ay room, including the room cust 'A' is looking at now. In addition, list of rooms produced for customer 'A' can contain records for which other customers are in the process of reserving or cancelling.
"So when to choose what type of isolation level ?? "
A. RR isolation level is used if we are executing large queries and we dont want concurrent transactions to happen.
B. RS is used when we want some concurrency to happen, yet we want qualified rows to remain stable for duration of our transactions
C. CS is used when we want maximum concurrency to happen and we dont want any queries to see any uncommitted data
D. UR is used if we are querying READ ONLY tables/views and it does not matter whether my query sees uncommitted value.
How to specify isolation levels??
REPEATABLE READ(RR): This is the Most Restrictive Isolation level available.Restrictive in the sense,when this is used every row referenced in any manner is locked for the duration of that transaction.
Thus if a transaction ,say: select Fname form employee where emp_id > '25' is run against employee table having 1000 rows .So DB2 scans all the 1,000 rows in the table and only 10 rows are fetched as per the where clause condition, locks are held on all the 1000 rows scanned.
Example in Real life: Lets say your hotel uses DB2 to keep track of its data. It has a web based reservation system and it runs under RR isolation level.
Customer 'A' looks for sea-facing rooms for given date-range. He will book a single room but he is checking all the rooms that are sea-facing side. Now ,You the manager wont be able to make change to any room's rate which were scanned by customer 'A'. Similary customer 'B' wont be able to book any rooms which 'A' scanned.(provided customer 'A's Transaction remains active)
However Manager and Cust 'B' can make any change/reservation to other Rooms which were not included in Customer 'A' s Scan list.
READ STABILITY(RS): This is not as Restrictive as RR. Here only the rows that are actually retrieved or modified are locked. Thus if the same transaction scans 1000 rows in order to retrieve 10 rows, locks will be placed on 10 rows, not on the 1000 rows scanned.
Example Continued : Now the same application has applied RS isolation level.
Suppose the customer 'A' Scans the rooms for the given date-range. You the manager and Cusotmer 'B' can make modifications/reservations for the rooms that does not appear on 'A's list.
CURSOR STABILITY(CS): This is more Relaxed that RS. This isolation level only locks the ROW that is currently referenced by the cursor. The moment the ROW is retrieved, no other Transaction is allowed to update that row while the cursor is positioned on it. Other Transactions can ADD/MODIFY/DELETE rows on either side of the locked row.
Example Continued : Customer 'A' scans for the room. Now rooms will be displayed on the list(one room at a time supppose room number '1') . You the manager can change the rates for any rooms except the room the customer is currently checking.Now suppose the customer 'A' looks for other rooms, you and other customers can modify/reserve room number '1'
""""Default isolation level used is CS in DB2 """"
UNCOMMITTED READ(UR): Least Restrictive. Rows retrieved by a transaction are only locked if the transaction modifies the data or if another transaction attempts to drop/alter the tables, the rows are retrieved from
Example Continued : Customer 'A' Scans for rooms. You being the manager will be able to change the room rates for any rooms. Customer 'B' can make/cancel reservation for ay room, including the room cust 'A' is looking at now. In addition, list of rooms produced for customer 'A' can contain records for which other customers are in the process of reserving or cancelling.
"So when to choose what type of isolation level ?? "
A. RR isolation level is used if we are executing large queries and we dont want concurrent transactions to happen.
B. RS is used when we want some concurrency to happen, yet we want qualified rows to remain stable for duration of our transactions
C. CS is used when we want maximum concurrency to happen and we dont want any queries to see any uncommitted data
D. UR is used if we are querying READ ONLY tables/views and it does not matter whether my query sees uncommitted value.
How to specify isolation levels??
At the statement level:
SELECT (INTO), DELETE, UPDATE ... WITH {RR, RS, CS, UR}
At precompile or bind time:
ISOLATION (cs)
ISOLATION (RR)
ISOLATION (RS)
ISOLATION (UR)
For dynamic SQL within the current session:
SET CURRENT ISOLATION = {RR, RS, CS, UR} or SET ISOLATION {RR, RS, CS, UR}
No comments:
Post a Comment