Re: few perf tuning related questions;

Vladimir Stavitsky wrote:

> 2. Locks issues. I have highly interactive web site with some 50 users
> doing selects on a table at the same time; a few updates/inserts (say up
> to 5) might be initiated in parallel with those selects. The question is
> twofold: do i have to explicitly lock the table? Or Oracle takes care of
> that? Important thing is that updates/inserts never ever collide,
> meaning they access ALWAYS different rows. They do collide though with
> selects, but i do not care as to what status is read - old or new.
> Second question is - if i do not use any explicit locking, is there a
> possibility of deadlocks because of the internal locking in the above
> scenario? And, also, what kind of performance would you expect on
> SunUltra2 with 4 cpu and some 256 RAM? The table accessed has some
> 15,000 rows. What i am seeng is that there are a lot of processes doing
> select running forever - just hanging.

The following situation applies for the read committed isolation level:

  Oracle will not impose any lock when you issue SELECT ... FROM table unless you use FOR UPDATE option. Even you have an update on the same row, another use can still be select that row. All of them are automatically carried out by Oracle if you use the default lock setting in the initialization parameter file.

  If the transaction contains an update without rollback or commit, another user will only see the old information. Alternatively, if the update is committed or rollback, another user will see the new information.

  For the deadlock, Oracle will detect and resolves it automatically by rollback.

  If you found that some processes are hanging during selection. It is better to check whether a select ... for update is issued.

---
Name   : Lun Wing San
Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841