Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Unanswered: TNXIsolation Level reverted

    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

  2. #2
    Join Date
    Oct 2005
    Posts
    109
    any update/insert/delete will automatically upgrade to CS isolation.
    Juliane

  3. #3
    Join Date
    Nov 2005
    Posts
    3

    follow up

    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

  4. #4
    Join Date
    Oct 2005
    Posts
    109
    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.
    Juliane

  5. #5
    Join Date
    Nov 2005
    Posts
    3

    last question

    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

  6. #6
    Join Date
    Oct 2005
    Posts
    109
    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.
    Juliane

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •