ChannelDB2

A Fresh Look at Setting Session Isolation Level

By Sathyaram Sannasi - Confessions of a DB2 Geek

The isolation level determines how data accessed by one application is isolated from the other. In other words, the degree to which rows accessed by an application is available to another.

 

A description of the isolations level can be found here - http://ibm.co/qLofSg

 

It is important to note that the isolation level is associated with an application/session. It is not the property of the database.
The default isolation level for a connection is Cursor Stability (CS). If the database configuration cur_commit is set to YES (introduced in DB2 9.7), then connection uses currently committed cursor stability as the default value.

 

The obvious question is - How to set the isolation level for a session?

This has been answered in various articles, blogs and forums over the years. The official version is here - http://bit.ly/mPzdew .
Except BIND, all the other methods require changes on the client side. The larger and more distributed the environment, the more difficult it is to change this on the client side.

 

What if you want to ‘force' UR isolation level for a read-only user? What if you want the applications connecting in a specific interval of time use UR isolation?

 

Changing isolation level gets trickier if the need for the change is only temporary.

 

This is where the new database configuration parameter CONNECT_PROC parameter introduced in DB2 9.7.3 comes handy.
The CONNECT_PROC configuration parameter identifies the name of the procedure that will be called each time a connection is established. This procedure can be used to set the ISOLATION LEVEL for the connection.

 

An example:
CREATE PROCEDURE SCHEMA1.CONNECTPROC

LANGUAGE SQL

BEGIN

IF ((USER='ROUSER') OR ( (CURRENT TIMESTAMP BETWEEN TIME(CURRENT TIME) > '22.00' OR CURRENT_TIME < '01.00'))

THEN
SET ISOLATION LEVEL=UR ;
END IF ;
END @

 

GRANT EXECUTE ON PROCEDURE CONNECTPROC TO PUBLIC ;
update db cfg using CONNECT_PROC SCHEMA1.CONNECTPROC

 

This procedure changes the isolation level to UR for ROUSER or for connections established between 10pm and 1am.

 

Changing the isolation level using CONNECT_PROC will override connection settings made on the client side. The application can, of course, change the isolation at a later point.

 

The setting will only impact new connections. Existing connections will not be impacted.

 

As with the usual SET ISOLATION LEVEL statement, the isolation level specified applies only for dynamic SQLs. Static SQL continue to use the bind isolation level.

 

So, the next question: is this the recommended approach for setting connection isolation level? No!

 

The application must decide what level of data isolation it requires. Therefore, it is not right to override this isolation level request by stealth at the database level. Such a change may lead to unexpected application behaviour.

 

Therefore, this CONNECT_PROC approach must be adopted only in cases where it is not possible to set it using the normal methods.

 

Contact Sathyaram @sathyaram_s  

 

Views: 569

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by Sathyaram Sannasi on February 16, 2012 at 6:07am

Dave, 
To get the isolation level for the currently running SQL statements , you can use the db2pd command 

db2pd -activestatements -d sample

Of the columns displayed, the following is of interest:

AppHandl - Application Handle 

AnchID, StmtUID - Identify the statement 

EffISO - Effective isolation level 

The effective isolation level is an integer and translates to 


0 - Repeatable Read
1 - Cursor stability
2 - Uncommited Read
3 - Read stability

To get the statement corresponding to AnchID, StmtUID use 

 db2pd -d sample -dynamic

Hope this helps 

Sathyaram Sannasi

Comment by Dave Prentice on February 15, 2012 at 3:22pm

How do I determine the isolation level of a piece of SQL that is currently running?

Featured Downloads

Try BLU Acceleration on Cloud

© 2018   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service