Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Location
    VA,USA
    Posts
    8

    Wink Unanswered: Consistent Read (Table Locking)

    Hi,
    I am trying to update a table 'X' and then trying to read that same table from a different session. I am using DB2 UDB 7.2.4 on AIX
    But this hangs up, as the whole table is locked. The table is not big and has only 3-4 rows, so there is no question of lock escalation.
    As the whole Table is getting locked (when and insert /Update/Delete) without a commit is performed on the table and nobody is able to access.
    I tried change the isolation level to RS and then repeat the above, but that also does not work....
    Can any one tell he how can I get the same functionality as Oracle has, so that other sessions can read the data from the table till the last consistant state.

    regards
    Arvind

  2. #2
    Join Date
    Apr 2002
    Posts
    188

    Re: Consistent Read (Table Locking)

    Originally posted by arvind_tcs
    Hi,
    I am trying to update a table 'X' and then trying to read that same table from a different session. I am using DB2 UDB 7.2.4 on AIX
    But this hangs up, as the whole table is locked. The table is not big and has only 3-4 rows, so there is no question of lock escalation.
    As the whole Table is getting locked (when and insert /Update/Delete) without a commit is performed on the table and nobody is able to access.
    I tried change the isolation level to RS and then repeat the above, but that also does not work....
    Can any one tell he how can I get the same functionality as Oracle has, so that other sessions can read the data from the table till the last consistant state.

    regards
    Arvind

    Arvin,

    Try using UR isolation level..

  3. #3
    Join Date
    May 2002
    Location
    VA,USA
    Posts
    8
    well UR will not solve the purpose of consistency.....

  4. #4
    Join Date
    Apr 2002
    Posts
    188
    Originally posted by arvind_tcs
    well UR will not solve the purpose of consistency.....
    Arvind_tcs,

    And I do agree with you, but if your app is a very heavy OLTP app, you will find that UR is as godd as it gets, unless you start putting more commits within your app.

    marcos oliva

  5. #5
    Join Date
    Apr 2002
    Posts
    188
    Originally posted by arvind_tcs
    well UR will not solve the purpose of consistency.....
    (sorry for double post)
    Arvind_tcs,

    And I do agree with you, but if your app is a very heavy OLTP app, you will find that UR is as godd as it gets, unless you start putting more commits within your app.


    marcos oliva

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    As far as I understand, DB2 ,when inserting a row, takes an X lock on the row and an IX lock on the table. This is true atleast for large tables ... There is a remote possibility that DB2 will hande the smaller tables different ...

    To add to Marcos' comment, a query which will select only a subset of the data and the search condition uses the primary key, I believe, your application will be able to select ...

    Cheers

    Sathyaram


    Originally posted by Maor71
    (sorry for double post)
    Arvind_tcs,

    And I do agree with you, but if your app is a very heavy OLTP app, you will find that UR is as godd as it gets, unless you start putting more commits within your app.


    marcos oliva
    Last edited by sathyaram_s; 10-21-02 at 14:47.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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