Hello
I am currently using IBM Db2 IDB Version 8. In my Client Configuration Middle Software, I have the TNXIsolation Level set to "Read Uncommitted (Uncommitted Reads).

However I find that periodically something is automatically changing it to "Read Committed" or the isolation setting goes away.

Has anyone witnessed this behavior and know of what could be causing this?
Your help is Greatly Appreciated.

Thanks
Andy

any update/insert/delete will automatically upgrade to CS isolation.

Can you please elaborate on what do you mean by update/insert/delete to what? Are you implying that anytime a new connection is inserted/deleted, the Isolation level for all connections get reverted and is there a way to prevent that.

Since it gets reverted, when we query a databse, we end up locking the rows and causing deadlock issues?

thanks
Andy

the isolation in the database is set per connection.
You can very well set it to UR (uncommited read).

Still - whenever one connection is changing data (doing an update/insert/delete) the isolation will be automatically increased to CS (cursor stability).

How this is handled will depend probably on your middleware. Whether you have methods to set this explicitely or you can submit statements like SET CURRENT ISOLATION UR

New connections have there own isolation - which should be UR (since I dunno your middleware, I can't say how it is explicitely done)

Somekind of isolation is always there - it does not just 'go away'.

Also with CS - when just reading data you won't end up with a dead lock. Parallel reads are always possible. With CS you more or less just have a share lock on the current row, nothing else.

For details see the documentation:
isolation levels:
http://publib.boulder.ibm.com/infoce...n/r0010944.htm
locks and concurrency control:
http://publib.boulder.ibm.com/infoce...n/c0005266.htm
lock compatibility:
http://publib.boulder.ibm.com/infoce...n/r0005274.htm

And in case of problems with locking, ask your middleware provider, how such cases are handled.

Thanks Juliane for your explanation it makes sense. Last question hopefully. My isolation level setting in the middleware changes periodically. I have not updated that connection information in the middle ware and not sure what is causing it to be updated automatically. Is it because I am using that connection to do inserts/updates via a query tool?

Andy

I am not sure.
If your middleware works like an application server and has connection pooling, then the actual database connections will not be disconnected. So then the isolation can be changed from UR to CS when doing updates; and since the connections won't be disconnected, they will keep that until isolation is changed back to UR or disconnecting.

If it is a setting in your middleware - that should not change. Check with the vendor and documentation on what can cause such a behaviour.