No question at this time
DBA Top 10
1 M. Cadot 25100
2 A. Kavsek 16900
3 B. Vroman 11000
4 M. Hidayathullah ... 10500
5 T. Boles 7600
6 F. Diaz 6700
7 P. Wisse 6600
8 J. Schnackenberg 4700
9 A. Hudspith 4300
10 P. Knibbs 2500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users47901
Total active users1905
Act. users last 24h8
Act. users last hour1
Registered user hits last week262
Registered user hits last month1168
Go up

row lock waits
Next thread: oracle secure file
Prev thread: Identify the Source of Sessions having Terminal UNKNOWN

Message Score Author Date
Hi Guys I have an application module that keeps...... Tso P Jan 05, 2017, 12:53
Can you contact the application vendor with this i...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Philip Wisse Jan 05, 2017, 14:54
Thanks Philip I gave our in-house developers th...... Tso P Jan 05, 2017, 15:24
Hi Tso, I had to dig up my notes with the links...... Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts Ales Kavsek Jan 05, 2017, 15:56
Hello Tso, if the cause is <<<i>select for upda...... Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts Bruno Vroman Jan 05, 2017, 17:05
Thanks a lot guys for the replies... Much appre...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Tso P Jan 06, 2017, 13:55

Follow up by mail Click here


Subject: row lock waits
Author: Tso P, South Africa
Date: Jan 05, 2017, 12:53, 81 days ago
Os info: rhel5
Oracle info: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Message: Hi Guys

I have an application module that keeps on hanging due to the row lock waits.

I have the table and the sql statement that gets the row lock contention. I have the select for update sql statement that causes the contention.

I have the addm and the AWR reports that shows exactly that.

Now, how do I know if it was TX mode 4 or mode 6 , etc and also if the "ENQ: TX", was waiting for an ITL slot in a block.

NB I had to kill the session as such I rely more on the AWR report.

Please pardon me I know this topic has been widely discussed I have tried to find the answers and I struggled.

Please Help!!!

Thanks in advance...



Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: row lock waits
Author: Philip Wisse, Netherlands
Date: Jan 05, 2017, 14:54, 81 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Can you contact the application vendor with this information?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: row lock waits
Author: Tso P, South Africa
Date: Jan 05, 2017, 15:24, 81 days ago
Message: Thanks Philip

I gave our in-house developers the info. They informed me that they had the issue before but it was a case of once in while...

I just want to make sure that its not something we can manage on the database...

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: row lock waits
Author: Ales Kavsek, Slovenia
Date: Jan 05, 2017, 15:56, 81 days ago
Score:   Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts
Message: Hi Tso,

I had to dig up my notes with the links, I barely remember that I was trying to find out the same info about TX mode post festum, in 10g timeframe for sure (so be warned!! Constants are likely the same in 11g/12c)...

check Wait Event History -> P1 parameter...

P1 = 1415053318 --> Mode = 6
P1 = 1415053316 --> Mode = 4

I have a slightly different version of the document, but the one available online can be found here (check page 42-44):

http://www.slideshare.net/khailey/ooug-oracle-transaction-locking

Overall, since you already identified conflicting statement (select ... for update), I don't think you can do much about it, but to notify developers (and in my case, this was a real pain, because developers were not aware that they should use those statements whenever needed and not by default...coffee stains are still all over the walls of the conference room;-).

Regards,
Ales

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: row lock waits
Author: Bruno Vroman, Belgium
Date: Jan 05, 2017, 17:05, 81 days ago
Score:   Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts
Message: Hello Tso,

if the cause is <<select for update>> then I would say that the behaviour is "expected".

The application designer didn't want a session B to modify (or simply lock) some data when session A has issued "select for update" (in other words: the 'for update' is their on purpose)

Maybe this can be questioned, but it is not at database level that you can act.

People using this piece of the application have to know that they shouldn't work concurrently -at least not on the same set of data- and that they should keep their transactions as quick as possible (not a good idea to lock the data and go to the coffee machine).

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: row lock waits
Author: Tso P, South Africa
Date: Jan 06, 2017, 13:55, 80 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Thanks a lot guys for the replies...

Much appreciated!!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here