A Fresh Look at Setting Session Isolation Level
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 – https://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 – https://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
« Previous | Next »