Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: Isolation level to allow reads while update in progress

    Hi,
    I have one table which is frequently updated by background process.
    This process runs every 5 mins. During one run of this process it can insert/update upto 5000 records. On average table size is around 20000 records. I have my app gui view which selects all records from the table and shows to user.
    Now what is happening is when this background process is running which may be updating/inserting upto thousands records, if at same time user tries to query the records from the gui view it just blocks for 10 minutes. And once background process has comitted transaction users gets view.
    Same is the case even when there are only few hundreds of records to update by process. Still gui waits for 10 mins.
    Currently i am using table locking.
    This table has around 15 cols. There is one primary key. Few columns have unique constraint. And updates can change any column value.
    While update I use hibernate batch update whose batch size is 512.

    So my question is:
    i) How can i improve table locking so that my reads wont be blocked by background updates ? Atleast If I got current consistent view (that was when prior to background update transaction) that will be good.
    ii) How can i change this 10 mins blocking ? Or is there any thing like that in first place?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Something doesn't add up
    Update every 5 min block for 10 min???
    When last did you update stats or reorg

  3. #3
    Join Date
    Jun 2009
    Posts
    5
    stats or reorg ? Can you please give me some more pointers ?

    My observation is that lets say there is some update txn in progress and after it has started i am trying to do select *, it locks my select query for 10 mins. And later it resumes and returns me results.

    Looking for some kind of locking that will allow me to select while update in progress.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    To answer your initial subject line
    "Isolation level to allow reads while update in progress "
    isolation level 0 will allow this but it will also read uncommitted data so stay away from it

    Read up on
    update index statistics
    and
    reorg rebuild

    My guess is a reorg rebuild on the table is required
    and I assume an index is used for the updates

Posting Permissions

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