Row lock without blocker [message #647817] |
Tue, 09 February 2016 08:19 |
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 #647820 is a reply to message #647818] |
Tue, 09 February 2016 08:38 |
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 #647822 is a reply to message #647820] |
Tue, 09 February 2016 08:48 |
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 |
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 #647827 is a reply to message #647826] |
Tue, 09 February 2016 11:59 |
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>';
|
|
|