AW: How do commits release row level locks?

From: Stefan Jahnke <Stefan.Jahnke_at_bov.de>
Date: Mon, 16 Feb 2004 13:08:28 +0100
Message-ID: <87F172BCF111D0489340AB3CB16A011E0DD1B7@EBMS20.bov.int>

Hi Naveen (sorry if I got the first an last name mixed up)

Good one. I can just guess (maybe one of the experts has an answer): Is = it
possible that, once you have a transaction waiting for a free itl - = slot,
the itl is only cleared after all the transactions that were holding a = slot
commited? Otherwise the waiting transaction should have gotten the = "free
again" slot, right?

Probably wrong, but worth a try ;).

Stefan

-----Urspr=FCngliche Nachricht-----

Von: Naveen, Nahata (IE10) [mailto:Naveen.Nahata_at_honeywell.com] Gesendet: Montag, 16. Februar 2004 13:10 An: oracle-l_at_freelists.org
Betreff: RE: How do commits release row level locks?

Hi All,

Can someone please explain me why the following happens:

Step 1. CREATE TABLE TEMP (COL1 NUMBER) PCTFREE 0 INITRANS 2 MAXTRANS = 3

Step 2.  INSERT values from 1 to 100,000 in the table
Step 3.  COMMIT
Step 4.  Since the insert was sequential, I assume the values 1, 2, 3, =

4, 5
should be in the same block (I can test the same using rowid as well)

Step 5.  From one session issue "DELETE TEMP WHERE col1 =3D 1"
Step 6.  From the second session issue "DELETE TEMP WHERE col1 =3D 2"
Step 7.  From the third session issue "DELETE TEMP WHERE col1 =3D 3"
Step 8.  The third session waits on "enqueue". This is understandable =

since
PCTFREE is 0 and the block has no space to create a third ITL Slot. Step 9. ROLLBACK the second session. This should release the ITL Slot = (???)
Step 10. Still the third session waits on "enqueue". Should it not go = ahead
with the delete now that one transaction has rolled back? Step 11. ROLLBACK the first session and the delete issued by the third session goes ahead.=20

I tried the same experiment with INITRANS set to 3, and the fourth = session
hangs, and doesn't move ahead until all the other three session = rollback or
commit. It seems that the waiting session doesn't go ahead until all = the
sessions have finished their transactions.

Moreover, when I look at V$SESSION_WAIT, it shows the session waiting = on
"enqueue" and SECONDS_IN_WAIT increment from 0 to 3 and then again get = reset
to 0.

Can someone please explain this? It seems I'm missing something, but = what?

9.2.0.3 on Solaris

Regards
Naveen

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

=20

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Mon Feb 16 2004 - 06:08:28 CST