what is the default value for IBM Informix set isolation level

1 the stored procedure

create procedure sp_count_demo(
i_user_id varchar(30)
)
returning p_count as num_of_row ;

define p_count integer ;

set isolation to dirty read ;

let p_row = 0 ;

select count(*)
into p_count
from some_table a
where a.user_id = i_user_id
;

return p_row;

end procedure ;

2 The procedure at (1) will be called from java webapps with connection pool

3 Do I need to set the isolation level back to previous value before returning the result? (ie to avoid another process reusing the connection from having "dirty read" isolation level)

4 What is the default isolation level

5 Where/How can I get the default value for isolation level

Thanks in advance

Solution

Since a connection pool is in use the stored procedure should return the isolation level to its previous setting in order to avoid unexpected results when another app uses the same connection. The default isolation level depends on the logging mode of the database:

For an unlogged database it will effectively be "Dirty Read" (shown as NL by the onstat -g ses command).
For a mode ANSI database it will be "Repeatable Read."
For other logged databases it will be "Committed Read."

The onconfig parameter USELASTCOMMITTED can also be used to change how the default isolation level is used. More information on that can be found in the Knowledge Center (search on USELASTCOMMITTED).

It is possible for a session to find out its current isolation level using a query against the sysmaster database. This query was run on Informix 12.10 but should also be valid for 11.70:

select tx.isolevel
from sysmaster:systxptab tx, sysmaster:sysrstcb r, sysmaster:sysscblst s
where s.address = r.scb and tx.owner = r.address
and s.sid = dbinfo("sessionid");

It returns the isolation level as an integer which is an internal value - for example committed read has value 2. I don't believe the mapping of isolation level to integer value is published so you will need to experiment with setting different levels for a session and then running the above query.