Re: Huge wait on Enqueues when selecting for update ...

Hi from Paris

extract from "Oracle9i Database Performance Tuning Guide and Reference" ....
TX enqueue
These are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

  a.. Waits for TX in mode 6: occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete.

  The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

...

in other words, tune your application !!!

But it is true that Oracle way of locking rows is strict and ensure data integrity, and that other RDBMS might allows "phantoms reads"...

From a developper point of view, I would try not to use "select for update" during the "user visualization" phase of a transaction. This algorithm is not scalable, and should consider to use a queuing mecanism for example, if you plan to grow your activity.

Cdlt.

"Sab Yani" <sabyani_at_hotmail.com> a écrit dans le message de news:ce750dee.0305051701.41fccca3_at_posting.google.com...
> I have a table which has several hundreds of rows. Hundreds of users
> are trying to update several rows ( around 10 -15) each time using
> select for update . Different users might try to update the same set
> of rows, causing huge wait on enques and very low throughput.
>
> The TX enqueue with type 6 is the cause for the problem. I've
> optimized the block size and PCTFREE/PCTUSED/INITRANS to increase the
> concurrency at the block level.
>
> I've also tuned all the SQLs to the best I can. I removed all table
> scans. The developer of the code insists that his code work 3 time
> better on other relational databases and therefore, he is now willing
> change his code for the sake of Oracle.
>
> Following is a clip from the statspack out put :
>
>
> Avg Wt
> Wait
> Eq Requests Succ Gets Failed Gets Waits Time (ms)
> Time (s)
> -- ------------ ------------ ----------- ----------- -------------
> ------------
> TX 56,280 55,997 0 21,819 21,413.10
> 467,212
> TM 750,529 750,441 0 3,398 4,254.55
> 14,457
> US 3,291 3,291 0 19 7.53
> 0
> HW 1,865 1,865 0 3 2.00
> 0
> CU 3,107 3,107 0 1 4.00
> 0
> -------------------------------------------------------------
>
> Your help is appreciated ...
Received on Tue May 06 2003 - 12:52:23 CDT