Saturday, October 22, 2011

enq: TX - row lock contention" for "select sysdate from sys.dual" statement .

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    enq: TX - row lock contention" for "select sysdate from sys.dual" statement .

DATABASE HANG AND & LARGE NUMBER OF LOCKS & WAIT ENQ: TX - ROW LOCK CONTENTIN

The database was hanging and getting large number of locks & wait event enq: TX - row lock
contention" for "select sysdate from sys.dual" statement .


 
Log files:
SID_ora_25646_HANG.trc
-------------------------
...
Aborting this subtree dump because of bad address d2031e60

SID_ora_2123_HANG.trc
----------------------------------
ABORTING SYSTEM STATE DUMP: ANOTHER PROCESS (so = 0xc00000005f0857a8) EXECUTING SYSTEM STATE DUMP

Solution:


get some system state dumps :

Login to sqlplus as the internal user:

sqlplus "/ as sysdba"

set trace file size to unlimited:

alter session set max_dump_file_size = unlimited;

alter session set events 'immediate trace name systemstate level 266';
--wait for 60 sec , then
alter session set events 'immediate trace name systemstate level 266';
--wait for 60 sec , then
alter session set events 'immediate trace name systemstate level 266';

You can also try and catch the blocker and get errorstacks to see what the blocker is doing at the time
To find the the blocker you can use script like 
Note.1020008.6 Ext/Pub SCRIPT FULLY DECODED LOCKING:
Note.1020012.6 Ext/Pub SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO:

Then using spid for the blocker get errorstacks as follows:

login to SQL*Plus:

connect / as sysdba
oradebug setospid xxxx
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug event 10046 trace name context off

This will produce a trace file in the user_dump_dest

References:
Note 62354.1 TX Transaction locks - Example wait scenarios



Get Oracle Certifications for all Exams
Free Online Exams.com

6 comments:

Anonymous said...

I гead this artiсle сomplеtely соnceгning the cоmparison of newest and precеding tеchnologiеs, it's amazing article.

Review my webpage; bucket truck
Look into my weblog ... altec bucket trucks for sale

Anonymous said...

Hellο thеre, I discovered your
ωebsitе by way of Google ωhіlst searchіng
fоr а гelatеd topіc, yοuг ωeb site camе up,
іt appeaгѕ great. I've bookmarked it in my google bookmarks.
Hello there, just became aware of your weblog via Google, and located that it is really informative. I am gonna be careful for brussels. I'll appreсiаte for thоsе ωhо pгoсeеd this in futurе.
A lot оf othеr ρeοple wіll pгobably be benefitеd frοm youг wrіting.
Cheеrs!

Feel free tо suгf to my hοmepage: cheap car insurance dallas
Also visit my site :: dallas tx auto insurance

Anonymous said...

Hoωdy! Thiѕ is my fiгѕt visіt to your blog!
We аre a grouр of ѵolunteers and startіng а new іnitiative
in a сommunity in the samе niche. Youг blog
provided us beneficіal informatiоn to work on.
Үou have done а wonderful јob!


my wеb blog ... http://www.biggreeneggguide.com/
Also see my page :: how to Put together a big green Egg

Anonymous said...

I'm not sure where you are getting your information, but good topic. I needs to spend some time learning more or understanding more. Thanks for magnificent information I was looking for this info for my mission.

Feel free to visit my web site :: eti bucket truck for sale

Anonymous said...

If you would like to increasе уоur expеrіence simply keep νisiting this ωеb pаge and be updated with the newest neωs posteԁ herе.


Here is mу website www.lgtensunits.com
My website > Tens 7000 Deluxe Dual Unit

Anonymous said...

My relatives all the time say that I am wasting my time here at web, but I know I am getting experience all the
time by reading thes fastidious articles.

Feel free to surf to my page diets that work fast for women
My web page :: diet plans that work