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?

Something doesn't add up
Update every 5 min block for 10 min???
When last did you update stats or reorg

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.

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