Home » SQL & PL/SQL » SQL & PL/SQL » Row lock without blocker
Row lock without blocker [message #647817] Tue, 09 February 2016 08:19 Go to next message
gajini
Messages: 262
Registered: January 2006
Senior Member
Sometime application transactions are getting timed out with this error "ORA-02049: timeout: distributed transaction waiting for lock"
On analysis, We found one table is being getting accessed heavily by application and getting into row lock contention "enq: TX - row lock contention".
But when monitoring, We found 2 to 4 application sessions are waiting for "enq: TX - row lock contention" on this particular table, requesting "TX" lock in mode 6,
but at the same time there was no blockers so not sure why these sessions are waiting.
Please let me know how to find why these sessions are getting into "enq: TX - row lock contention" and requesting "TX" lock in mode 6
even though there is no blocker.

Oracle version is 11.2.0.3.0
DB server: Suse Linux
Application server: Weblogic server
And the database is 3 node RAC database.
And the table is existing in the database only.

Please let me know if any other details are needed.

[Updated on: Tue, 09 February 2016 08:31]

Report message to a moderator

Re: Row lock without blocker [message #647818 is a reply to message #647817] Tue, 09 February 2016 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
"TX" lock in mode 6 occurs when two different sessions are trying to UPDATE or DELETE the same row.
Re: Row lock without blocker [message #647819 is a reply to message #647817] Tue, 09 February 2016 08:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Can you show the queries you are running that diagnose the problem?
Re: Row lock without blocker [message #647820 is a reply to message #647818] Tue, 09 February 2016 08:38 Go to previous messageGo to next message
gajini
Messages: 262
Registered: January 2006
Senior Member
Thanks for your reply.But here We can see 2 to 3 sessions are waiting for lock and can't found who is the blocker. so not sure how to find why these sessions are getting blocked and failing with this error "ORA-02049: timeout: distributed transaction waiting for lock".

The query is

delete from tabname where pkcolname=:1;

Note: pkcolname is the primary key column of this table.

[Updated on: Tue, 09 February 2016 08:43]

Report message to a moderator

Re: Row lock without blocker [message #647821 is a reply to message #647820] Tue, 09 February 2016 08:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://logicalread.solarwinds.com/diagnosing-oracle-wait-for-tx-enqueue-mode-6-mc01/#.Vrn21PkrLIU
Re: Row lock without blocker [message #647822 is a reply to message #647820] Tue, 09 February 2016 08:48 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
look for blocker on the other side, based on "distributed transaction waiting for lock". If session 1, for example issues UPDATE of remote table and session 2 issues UPDATE of same row you have blocking and blocker will be seen on remote side only. You will have to check if that blocker maps to remote or local session.

SY.
Re: Row lock without blocker [message #647823 is a reply to message #647822] Tue, 09 February 2016 10:01 Go to previous messageGo to next message
gajini
Messages: 262
Registered: January 2006
Senior Member

There is no update happening from remote database. All the sessions are from the applications which were connected to 3 nodes of the RAC and doing this DELETE concurrently but each one will be hitting different rows only and this table also not a remote, it is local database table only.
Re: Row lock without blocker [message #647825 is a reply to message #647823] Tue, 09 February 2016 11:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> each one will be hitting different rows only
The error indicates that above is not true.
I know Oracle better reports reality than you.
Re: Row lock without blocker [message #647826 is a reply to message #647825] Tue, 09 February 2016 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
it is local database table only.


Oracle reports this as wrong in the error message.
You have an application accessing different databases at the same time.

Re: Row lock without blocker [message #647827 is a reply to message #647826] Tue, 09 February 2016 11:59 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I suspect that the table name in the

delete from tabname where pkcolname=:1;

is a synonym or simple view that is pointing to a table in another instance. or the table has a trigger updating a table in another instance. type the following query

SELECT OWNER,OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_NAME = '<Your table in uppercase>';
Previous Topic: Insertion error
Next Topic: SQL query help
Goto Forum:
  


Current Time: Thu Mar 28 12:44:05 CDT 2024