Thursday, October 11, 2007

TX Transaction locks - Example wait scenarios

Subject: TX Transaction locks - Example wait scenarios
Doc ID: Note:62354.1 Type: TROUBLESHOOTING
Last Revision Date: 23-SEP-2005 Status: PUBLISHED


Introduction
~~~~~~~~~~~~
This short article gives examples of TX locks and the waits which can
occur in various circumstances. Often such waits will go unnoticed unless
they are of a long duration or when they trigger a deadlock scenario (which
raises an ORA-60 error).

The examples here demonstrate fundamental locking scenarios which should
be understood by application developers and DBA's alike.


Useful SQL statements
~~~~~~~~~~~~~~~~~~~~~
If you encounter a lock related hang scenario the following SQL statements
can be used to help isolate the waiters and blockers:

Show all sessions waiting for any lock:

select event,p1,p2,p3 from v$session_wait
where wait_time=0 and event='enqueue';

Show sessions waiting for a TX lock:

select * from v$lock where type='TX' and request>0;

Show sessions holding a TX lock:

select * from v$lock where type='TX' and lmode>0;


What is a TX lock ?
~~~~~~~~~~~~~~~~~~~
A TX lock is acquired when a transaction initiates its first change and is
held until the transaction does a COMMIT or ROLLBACK. It is used mainly as
a queuing mechanism so that other sessions can wait for the transaction to
complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction
ID of the active transaction.


Example Tables
~~~~~~~~~~~~~~
The lock waits which can occur are demonstrated using the following
tables. Connect as SCOTT/TIGER or some dummy user to set up the test
environment using the following SQL:

DROP TABLE tx_eg;
CREATE TABLE tx_eg ( num number, txt varchar2(10), sex varchar2(10) )
INITRANS 1 MAXTRANS 1;
INSERT into tx_eg VALUES ( 1, 'First','FEMALE' );
INSERT into tx_eg VALUES ( 2, 'Second','MALE' );
INSERT into tx_eg VALUES ( 3, 'Third','MALE' );
INSERT into tx_eg VALUES ( 4, 'Fourth','MALE' );
INSERT into tx_eg VALUES ( 5, 'Fifth','MALE' );
COMMIT;

In the examples below three sessions are required:

Ses#1 indicates the TX_EG table owners first session
Ses#2 indicates the TX_EG table owners second session
DBA indicates a SYSDBA user with access to


The examples covered below include:

Waits due to Row being locked by an active Transaction
Waits due to Unique or Primary Key Constraint enforcement
Waits due to Insufficient 'ITL' slots in the Block
Waits due to rows being covered by the same BITMAP index fragment


Waits due to Row being locked by an active Transaction
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When a session updates a row in a table the row is locked by the sessions
transaction. Other users may SELECT that row and will see row as it was
BEFORE the UPDATE occurred. If another session wishes to UPDATE the same
row it has to wait for the first session to commit or rollback. The
second session waits for the first sessions TX lock in EXCLUSIVE mode.

Eg:
Ses#1: update tx_eg set txt='Garbage' where num=1;
Ses#2: update tx_eg set txt='Garbage' where num=1;
DBA: select SID,TYPE,ID1,ID2,LMODE,REQUEST
from v$lock where type='TX';

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
8 TX 131075 597 6 0
10 TX 131075 597 0 6

> This shows SID 10 is waiting for the TX lock held by SID 8 and it
> wants the lock in exclusive mode (as REQUEST=6).

The select below is included to demonstrate that a session waiting
on a lock will show as waiting on an 'enqueue' in V$SESSION_WAIT
and that the values of P1RAW, P2 and P3 indicate the actual lock
being waited for. When using Parallel Server the EVENT will be
'DFS enqueue lock acquisition' rather than 'enqueue'.
This select will be omitted from the following examples.

DBA: select sid,p1raw, p2, p3
from v$session_wait
where wait_time=0 and event='enqueue';

SID P1RAW P2 P3
---------- -------- ---------- ----------
10 54580006 131075 597
> ~~~~ ~~ ~~~~~~ ~~~
> type|mode id1 id2
> T X 6 131075 597


The next select shows the object_id and the exact row that the
session is waiting for. This information is only valid in V$SESSION
when a session is waiting due to a row level lock. The statement
is only valid in Oracle 7.3 onwards. As SID 10 is the waiter above
then this is the session to look at in V$SESSION:

DBA: select ROW_WAIT_OBJ#,
ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
from v$session
where sid=10;

ROW_WAIT_O ROW_WAIT_F ROW_WAIT_B ROW_WAIT_R
---------- ---------- ---------- ----------
3058 4 2683 0

> The waiter is waiting for the TX lock in order to lock row 0
> in file 4, block 2683 of object 3058.

Ses#1: rollback;
Ses#2: rollback;


Waits due to Unique or Primary Key Constraint enforcement
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If a table has a primary key constraint, a unique constraint
or a unique index then the uniqueness of the column/s referenced by
the constraint is enforced by a unique index. If two sessions try to
insert the same key value the second session has to wait to see if an
ORA-0001 should be raised or not.

Eg:
Ses#1: ALTER TABLE tx_eg ADD CONSTRAINT tx_eg_pk PRIMARY KEY( num );
Ses#1: insert into tx_eg values (10,'New','MALE');
Ses#2: insert into tx_eg values (10,'OtherNew',null);
DBA: select SID,TYPE,ID1,ID2,LMODE,REQUEST
from v$lock where type='TX';

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
8 TX 196625 39 6 0
10 TX 262155 65 6 0
10 TX 196625 39 0 4

This shows SID 10 is waiting for the TX lock held by SID 8 and it
wants the lock in share mode (as REQUEST=4). SID 10 holds a TX lock
for its own transaction.

Ses#1: commit;
Ses#2: ORA-00001: unique constraint (SCOTT.TX_EG_PK) violated
Ses#2: rollback;


Waits due to Insufficient 'ITL' slots in a Block
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oracle keeps note of which rows are locked by which transaction in an area
at the top of each data block known as the 'interested transaction list'.
The number of ITL slots in any block in an object is controlled by
the INITRANS and MAXTRANS attributes. INITRANS is the number of slots
initially created in a block when it is first used, while MAXTRANS places
an upper bound on the number of entries allowed. Each transaction which
wants to modify a block requires a slot in this 'ITL' list in the block.

MAXTRANS places an upper bound on the number of concurrent transactions
which can be active at any single point in time within a block.

INITRANS provides a minimum guaranteed 'per-block' concurrency.

If more than INITRANS but less than MAXTRANS transactions want to be
active concurrently within the same block then the ITL list will be extended
BUT ONLY IF THERE IS SPACE AVAILABLE TO DO SO WITHIN THE BLOCK.

If there is no free 'ITL' then the requesting session will wait on one
of the active transaction locks in mode 4.

Eg: Ses#1: update tx_eg set txt='Garbage' where num=1;
Ses#2: update tx_eg set txt='Different' where num=2;
DBA: select SID,TYPE,ID1,ID2,LMODE,REQUEST
from v$lock where type='TX';

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
8 TX 327688 48 6 0
10 TX 327688 48 0 4

This shows SID 10 is waiting for the TX lock held by SID 8 and it
wants the lock in share mode (as REQUEST=4).

Ses#1: commit;
Ses#2: commit;
Ses#1: ALTER TABLE tx_eg MAXTRANS 2;
Ses#1: update tx_eg set txt='First' where num=1;
Ses#2: update tx_eg set txt='Second' where num=2;

Both rows update as there is space to grow the ITL list to
accommodate both transactions.

Ses#1: commit;
Ses#2: commit;


Waits due to rows being covered by the same BITMAP index fragment
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Bitmap indexes index key values and a range of ROWIDs. Each 'entry'
in a bitmap index can cover many rows in the actual table.
If 2 sessions wish to update rows covered by the same bitmap index
fragment then the second session waits for the first transaction to
either COMMIT or ROLLBACK by waiting for the TX lock in mode 4.

Eg: Ses#1: CREATE Bitmap Index tx_eg_bitmap on tx_eg ( sex );
Ses#1: update tx_eg set sex='FEMALE' where num=3;
Ses#2: update tx_eg set sex='FEMALE' where num=4;
DBA: select SID,TYPE,ID1,ID2,LMODE,REQUEST
from v$lock where type='TX';

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
8 TX 262151 62 6 0
10 TX 327680 60 6 0
10 TX 262151 62 0 4

This shows SID 10 is waiting for the TX lock held by SID 8 and it
wants the lock in share mode (as REQUEST=4).

Ses#1: commit;
Ses#2: commit;


Other Scenarios
~~~~~~~~~~~~~~~
There are other wait scenarios which can result in a SHARE mode wait for a TX
lock but these are rare compared to the examples given above.
Eg: If a session wants to read a row locked by a transaction in a PREPARED
state then it will wait on the relevant TX lock in SHARE mode (REQUEST=4).
As a PREPARED transaction should COMMIT , ROLLBACK or go to an in-doubt
state very soon after the prepare this is not generally noticeable..

68 comments:

Anonymous said...

Good post and this mail helped me alot in my college assignement. Gratefulness you on your information.

Anonymous said...

Opulently I assent to but I contemplate the collection should acquire more info then it has.

Anonymous said...

Good morning

We do not agree with this year BRIT awards decision.

Please visit our little survey

http://micropoll.com/t/KDqOnZBCWt

Lady Gaga can not be better than ?????

Poll supported by BRIT awards 2010 sponsor femmestyle
[url=http://www.femmestyle.ch/angebot/operationsergebnis/index.html]vorschau schönheitsoperationen[/url]

PRINCE HARRY WISHES HAPPY BIRTHDAY TO THE BRIT AWARDS
With a special birthday message from Prince Harry for the 30th Anniversary of the BRIT Awards

anonymous transactions said...

I have had great time reading your article. It’s actually quiet awesome, it is surprising how you have learnt to write your feelings and thoughts in so precise manner. Its’good really. anonymous transactions

Anonymous said...

Thank u good post...

Anonymous said...

buy tramadol online purchase tramadol generic ultram - tramadol 50 mg

Anonymous said...

buy xanax online no prescription xanax drug wikipedia - different kinds xanax bars

Anonymous said...

xanax online effexor xanax drug interactions - xanax miracle drug

Anonymous said...

carisoprodol 350 mg carisoprodol 350 mg xanax - carisoprodol drug test

Anonymous said...

buy tramadol online tramadol hcl 37.5 mg - tramadol hcl 50mg tablet amnea

Anonymous said...

generic xanax generic xanax 2mg - xanax bars price street

Anonymous said...

order tramadol online tramadol for dogs ok for humans - tramadol hcl erowid

Anonymous said...

buy tramadol online buy tramadol online mastercard overnight - tramadol withdrawal worse than vicodin

Anonymous said...

buy tramadol online buy tramadol tn - where to buy tramadol for dogs

Anonymous said...

buy tramadol online tramadol for dogs same as humans - tramadol extended release tablets

Anonymous said...

buy tramadol online buy tramadol online from usa - order tramadol overnight cod

Anonymous said...

buy tramadol online tramadol dosage supplied - after effects tramadol overdose

Anonymous said...

cialis online pharmacy cialis refractory period - cialis online mastercard

Anonymous said...

cialis online no prescription overnight cialis online store - generic cialis cipla

Anonymous said...

generic xanax generic xanax xr 1mg - 10 xanax bars

Anonymous said...

buy cialis online can u buy cialis over the counter - cialis quick delivery

Anonymous said...

cheap cialis cheap cialis daily - cheap cialis generic online

Anonymous said...

buy cialis professional online buy cialis australia - buy cialis online with prescription

Anonymous said...

xanax no prescription xanax effects eyes - xanax 8mg day

Anonymous said...

I don't even know how I ended up here, but I thought this post was good. I don't know who
you are but definitely you're going to a famous blogger if you aren't already ;) Cheers!


my website http://Amateursfucking.info/?q=amateur+anal&f=m

Anonymous said...

buy tramadol online order tramadol online forum - tramadol 50 mg use dogs

Anonymous said...

buy tramadol tramadol 50mg capsules dosage - buy tramadol safely

Anonymous said...

buy tramadol online no prescription cod tramadol online from usa - tramadol online florida

Anonymous said...

buy tramadol online tramadol 50 mg like vicodin - where to buy tramadol for dogs

Anonymous said...

buy tramadol online tramadol hcl side effects - tramadol 650 om

Anonymous said...

Its such as you read my thoughts! You appear to know
a lot about this, such as you wrote the guide in it or something.
I believe that you simply could do with some p.
c. to power the message home a little bit, but other than that,
this is great blog. A fantastic read. I'll definitely be back.

my web page Music Video - Watch Online Free!

Anonymous said...

I like the helpful information you supply
on your articles. I'll bookmark your blog and take a look at again right here frequently. I'm somewhat
sure I'll learn many new stuff right right here! Best of luck for the next!

Here is my blog :: A scholarships
My web page > see more

Anonymous said...

Hi there, You have done an incredible job.

I will certainly digg it and personally suggest to my friends.
I am confident they'll be benefited from this website.

Feel free to visit my page: Www.kpoplove.com

Anonymous said...

buy tramadol 300 mg tramadol overdose - usual dosage tramadol dogs

Anonymous said...

tramadol online order tramadol online cheap - order tramadol online with visa

Anonymous said...

buy tramadol buy tramadol online no prescription mastercard - ultram tramadol withdrawal symptoms

Anonymous said...

Hurrah! Finally I got a webpage from where I be able to in fact take helpful data regarding my study and knowledge.


Feel free to visit my page; http://www.snippets.tv/blogs/entry/Celebrity-Movie-Stars-That-Used-To-Be-Porn

Anonymous said...

buy tramadol overnight 50 mg tramadol lot - kandungan tramadol hcl

Anonymous said...

buy ativan online ativan generic cost - ativan use in dogs

Anonymous said...

Hmm is anyone else experiencing problems with the pictures on this
blog loading? I'm trying to find out if its a problem on my end or if it's the blog.
Any feed-back would be greatly appreciated.

My site; www.championshipsubdivisionnews.com/log/index.php/2013/01/04/2013-ncaa-division-i-football-1?blog=2

Anonymous said...

ways to buy ativan online ativan dosage mayo clinic - ativan withdrawal caffeine

Anonymous said...

buy tramadol online half life tramadol 50mg - buy tramadol online us

Anonymous said...

tramadol 100 tramadol overnight delivery no prescription - buy tramadol online paypal

Anonymous said...

A huge dick in my pussy,a new warm wet tounge up my personal arse and cum and also pussy juice all over me.
Fuck, ozzy

Feel free to surf to my web blog :: hcg injections

Anonymous said...

buy tramadol mastercard tramadol for dogs mg - buy generic tramadol

Anonymous said...

I am really impressed with your writing skills as well as with
the layout on your weblog. Is this a paid theme or
did you modify it yourself? Anyway keep up the excellent
quality writing, it's rare to see a nice blog like this one these days.

Here is my weblog: click through the up coming web page

Anonymous said...

Υour оωn repοrt features eѕtabliѕhеd beneficial to mе pегsonally.
It’s гeally іnfοrmаtive and you're simply certainly very experienced in this area. You have popped my face to be able to various thoughts about this specific topic together with interesting and strong content.
Here is my blog : wiki.otaku-Irc.Fr

Anonymous said...

Υour current post has established useful to
us. It’ѕ гeally infоrmatіνe and you're simply certainly really knowledgeable in this field. You possess popped my personal face to various thoughts about this subject together with intriguing and strong written content.
My web blog :: elearn.mi-emsis.org

Anonymous said...

I visited several sites but the audio quality for audio songs existing
at this web site is actually marvelous.

my weblog ... on the main page

Anonymous said...

I've been surfing on-line more than three hours these days, yet I never discovered any attention-grabbing article like yours. It's lovely
price enough for me. In my view, if all website owners
and bloggers made just right content as you did, the internet can be much
more useful than ever before.

Check out my webpage: diets that work for women

Anonymous said...

buy tramadol onlineno prescription cheap order tramadol online mastercard - buy tramadol180

Anonymous said...

Your report has confirmеd useful tο uѕ.
It’ѕ extremely educational and you arе cеrtainly
reаlly ωell-informеd оf thiѕ tуpe.

You hаve opened our eуe in ordеr to diffeгent viewѕ on
this κind of toрic using intriguіng and solіd written content.


Feel free to surf to my ωeb-site; buy Klonopin online

Anonymous said...

Wow that was unusual. I just wrote an really long
comment but after I clicked submit my comment didn't show up. Grrrr... well I'm not writing all
that over again. Anyways, just wanted to say
wonderful blog!

My site :: Valarie

Anonymous said...

buy lipitor online in canada or buy seroquel online overnight or order abilify online

Anonymous said...

It's really a cool and helpful piece of information. I'm happy that you just shared this helpful info with us.
Please keep us informed like this. Thank you for sharing.



my website: http://pornharvest.com/index.php?m=2084123

Anonymous said...

Hmm is anyone else experiencing problems with the images on this blog loading?
I'm trying to find out if its a problem on my end or if it's the blog.
Any feedback would be greatly appreciated.

My website; This guy

Anonymous said...

Its like you read my mind! You appear to know so much about
this, like you wrote the book in it or something.
I think that you could do with a few pics to drive the
message home a little bit, but other than that, this is
wonderful blog. A fantastic read. I will certainly be back.


Here is my web blog :: sex vids

Anonymous said...

naturally like your web site but you have to take a look at the spelling on several of your posts.
A number of them are rife with spelling problems and I in
finding it very bothersome to inform the truth nevertheless I will surely
come again again.

Here is my web-site more

Anonymous said...

where can i buy ambien in china buy ambien in brazil - buy ambien from canada

Anonymous said...

I am really impressed with your writing skills and also with
the layout on your weblog. Is this a paid theme or did you customize it yourself?
Anyway keep up the nice quality writing, it is rare
to see a great blog like this one today.

my website - fast jazz

Anonymous said...

great post, very informative. I wonder why the other specialists
of this sector do not understand this. You must proceed
your writing. I'm sure, you've a great readers' base already!

Here is my blog: visit website

Anonymous said...

Hi fantastic website! Does running a blog similar to this require a massive amount work?
I have very little understanding of coding but I had been hoping to
start my own blog in the near future. Anyways, should
you have any ideas or tips for new blog owners please share.
I know this is off subject however I just needed to ask.
Thanks a lot!

my blog post - sexy nubile makes her whole body tremble with a new pink vibrator

Unknown said...

dior sunglasses
michael jordan shoes
true religion outlet
nike air max
adidas tubular shadow
adidas superstar
nike polo
fitflops
nike air force 1
james harden shoes

Unknown said...

adidas neo
cheap jordans
longchamp
asics sneakers
yeezys
timberland outlet
nike air force 1
adidas stan smith men
basketball shoes
adidas outlet

jeje said...

adidas eqt support adv
kobe 11
timberland shoes
true religion
air jordan 11
adidas neo online shop
yeezy sneakers
kyrie 3
cheap jordan shoes
baseball jerseys

yanmaneee said...

kenzo
canada goose outlet
yeezy 350
yeezy boost 350 v2
jordan shoes
off white x jordan 1
supreme
paul george shoes
off white jordan 1
off white

Anonymous said...

jordan retro
off white jordan
off white shoes
golden goose shoes
jordan 1
birkin bag
yeezy boost 350
goyard
moncler
golden goose

Anonymous said...

fear of god outlet
jordans
kyrie shoes
golden goose sneakers
yeezy boost 350
fear of god
air jordan
supreme outlet
golden goose sneakers
jordan 4