0

The app is connected to an oracle 11G database using the JDBC driver provided from the official website. When many users (Around 50) from different instances connected to the same schema start using the application, i experience some freezes all around the app and when i run a query to get the locking sessions and the locked objects i find only "Row Exclusive" lock type, which normally should not lock all the table and permits multiple sessions to perfom DML queries. Thus my question is when can a row exclusive table lock the whole table or else provoque these freezes.

Note: i have looked around in forums and saw some MAXTRANS and ITL configurations, could these parameters be generating these freezes ?

Thank you

AyoubK
  • 11
  • 1
  • 3
  • 1
    You're correct that row locks never escalate in Oracle. Are you performing any DDL, or using particular rows to keep running totals or maintain sequence numbers ... or anything else where multiple sessions might have to modify a single row? – David Aldridge May 16 '16 at 14:15
  • Yes i am using sequences in order to insert into address table, can this cause the problem, and all my users only perfom SELECT/INSERT/UPDATE/DELETE Queries – AyoubK May 16 '16 at 14:20
  • Use of an Oracle sequence object wouldn't -- you'd want to check the cache size of course, but it would not cause table row locks. – David Aldridge May 16 '16 at 14:30
  • But do you agree, if i had a small maxtrans maybe below 50 assuming i have a transaction per user, can filling the ITL cause the sessions to enter WAIT state ? – AyoubK May 16 '16 at 14:37
  • To diagnose that you'd be better off tracing them through wait events, as an enqueue on an ITL wait would explicitly show up there. – David Aldridge May 16 '16 at 14:55

2 Answers2

0

i think you have your terms confused.. "Row Exclusive" locks mean 'i have locked this row.. no other session is allowed to update it'.

so if you have 50 sessions all trying to update or delete a specific row then yes.. you are going to have contention. and that will seriously limit your performance.

so I would guess that its possible your application is missing a commit statment that would free the lock after the row has been modified.

you say you are using sequences.. are you using an actually oracle sequence (ie create sequence my_seq; ) or are you doing to custom thing that like select max(id)+1 from sequence_table which would be another bad idea.

ShoeLace
  • 3,476
  • 2
  • 30
  • 44
  • I am using oracle sequences not custom ones, and for the commit part yes i have set the JDBC driver to autocommit every transaction plus that my users do not forcibly change the same row but they are working on the same table – AyoubK May 16 '16 at 15:31
  • okay.. then another option is your app code is doing something like `select * from table for update` and lopping teh results rather then `select * from tbale where id = :myid for update` – ShoeLace May 16 '16 at 15:35
  • I have checked all my queries but none of them miss the where clause, i understand that such query can raise a TM lock and that was what i first checked – AyoubK May 16 '16 at 16:01
  • then i think you maybe stuck with tracing sessions. sorry – ShoeLace May 16 '16 at 16:20
0

Maybe it's too early to blame Oracle. It can be a servlet container configuration such as not enough exec threads. Or it can be an internal contention. Many things can go wrong. A quick way to identify the bottleneck is to get a thread dump when the application is experiencing "some freezes all around the app" and see where your threads as stuck. You can get a thread dump by sending kill -3 to your Java process. Post it here and will be happy to look at it.

Slava Imeshev
  • 1,360
  • 10
  • 14
  • Hello, thank you for the answer. But i forgot to mention that this is a desktop version of the app. I am using an applet to deliver the content to users – AyoubK May 19 '16 at 09:24
  • That's fine. The best way out of this situation is to know exactly what is going on inside your applet. If I were you, I'd get JProfiler (they have 2 weeks of free eval period) and profile the applet and see exactly what's up. In addition to solving the problem you'll a acquire a very useful skill. – Slava Imeshev May 20 '16 at 17:48