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 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.
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.
The Microsoft ODBC Driver for DB2 provides flexibility in dealing with issues of isolation levels and transaction state. The ODBC SQLSetConnectAttr function is used to set the isolation level that is to be used for a connection. This function would be called with the attribute parameter set to SQL_ATTR_TXN_ISOLATION and the ValuePtr parameter pointing to an integer value indicating the isolation level requested. This integer value is a 32-bit bitmask that sets the transaction isolation level for the current connection.
The allowable values for isolation level (the ValuePtr parameter when calling SQLSetConnectAttr) can be determined by calling SQLGetInfo with InfoType equal to SQL_TXN_ISOLATION_OPTION. The following table list the allowable values for isolation level using the ODBC Driver for DB2 supplied with Host Integration Server.
| ODBC Isolation Level Attribute | Description |
|---|---|
| SQL_TXN_READ_COMMITTED | When this attribute value is set, it isolates any data read from changes by others and changes made by others by others cannot be seen. The re-execution of the read statement is affected by others. This does not support a repeatable read. This is the default value for isolation level, This isolation level is also called Cursor Stability (CS) in IBM DB2 documentation. |
| SQL_TXN_READ_UNCOMMITTED | When this attribute value is set, it does not isolate data read from changes by others and changes made by others by others can be seen. The re-execution of the read statement is affected by others. This does not support a repeatable read. This isolation level is called Uncommitted Read (UR) in IBM DB2 documentation. |
| SQL_TXN_SERIALIZABLE | When this attribute value is set, it isolates any data read from changes by others and changes made by others by others cannot be seen. The re-execution of the read statement is not affected by others. This supports a repeatable read. This isolation level is called Repeatable Read (RR) in IBM DB2 documentation. |
The SQL_ATTR_TXN_ISOLATION attribute can be set only if there are no open transactions on the connection. An application must call SQLEndTran to commit or roll back all open transactions on a connection, before calling SQLSetConnectAttr with this option. Some connection attributes support substitution of a similar value if the data source does not support the value specified in ValuePtr. In such cases, the driver returns SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (Option value changed). To determine the substituted value, an application calls SQLGetConnectAttr.
DB2 offers four locking isolation levels: Repeatable Read (RR), Read Stability (RS), Cursor Stability (CS), and Uncommitted Read (UR).
Read my previous post on basics of isolation levels. Each of these isolation levels allows the user and application to control the number and duration of read (Share) locks held within a unit of work. When you set the appropriate isolation level, based on a particular application’s requirement, lock resources can be minimized, and the user/program concurrency can be increased. Take the following example -
SELECT LASTNAME, EMPNO FROM EMP WHERE LASTNAME LIKE 'S%' WITH UR
Repeatable Read
RR means that the same query can be executed multiple times within the same unit of work, and the results of the query will be identical every time (repeatable). A Share lock will be set and will stay on each row or page until the query or logical unit of work has completed. All accessed rows or pages are locked, even if they do not satisfy the predicate. For table scans, this would encompass each row/page within the table. For other queries not processing table scans, this would encompass any rows or pages that meet the predicate criteria of the SQL statement.
In the example above, this would be all rows or pages containing last names that begin with S. All Share locks with RR are held until a commit takes place. These share locks would effectively prevent updates, inserts, or deletes (X locks) from occurring on any of the rows/pages from any other process until a commit is executed.
Read Stability
With RS is very much like With RR, except that it will allow inserts from other users. It can at times lock more rows/pages because locks are taken and held on data, even when it goes to stage 2 processing to further check predicates. If there is a stage 2 predicate and the data does not fit the predicate criteria, the RS lock is still placed and held.
Cursor Stability
With CS sets a Share lock on each row or page processed, and the moment the cursor moves on to another row or page, it releases the lock. So at any one time, there is only one lock being held either on a row or page of data. This obviously allows good concurrency and some data integrity. Almost all batch COBOL programs in IT shops today are bound with the locking parameter CS.
This is because as these programs execute cursor processing, they have no need to reread any data processed. The Share locks get freed up as the query moves through the cursor, and the query has data integrity as it processes each current row or page.
This bind parameter, along with another bind parameter, Currentdata(No), provides an opportunity for avoiding locks altogether. With these two bind parameters together, DB2 can test whether a row or page has committed data on it, and if it has, DB2 will not have to obtain any lock.
Uncommitted Read
With UR means that no Share locks are placed on any rows or pages processed by this query, and it does not matter if other processes have any locks on any of the data being retrieved. This can improve efficiency because it reduces overall processing time. But the one issue in using UR is that if some other process has applied updates to data being retrieved, UR will return the updated data from the buffer before the other process has executed a commit.
If for some reason the other process does a rollback of its updates, then this UR process has updated data that was never committed. Even with the issue of possibly picking up non-committed data, there are definitely times when UR can be used.